Every now and then I see this big question from SQL Server users that when to use a stored procedure?

Well there is a second part to this question as well, think about caching of such stored procedures There is no such database management software that can guarentee you flawless query execution over a period of time. Performance problems caused by misuse or mismanagement of plan cache, or inappropriate recompilation, can manifest themselves as simply a decrease in throughput or an increase in query response time. Problems with caching can also show up as out-of-memory errors or connection time-out errors, which can be caused by all sorts of different conditions, this is a common error you will see when a paticilar query is executed many times. A wealth of information available in SQL Server books online and other blog websites etc. few I would like to present here from my experience.

Coming back to original question on when to use, there are few guidelines that I would like to propose when you are deciding whether to use stored procedures or one of the other mechanisms:

• Stored procedures These objects should be used when multiple connections are executing batches in which the parameters are known. They are also useful when you need to have control over when a block of code is to be recompiled.

Since SQL Server 2005 version, many administrative and informational activities can be performed by using system stored procedures. The system stored procedures are grouped into the categories shown in the following table (from BOL):

Category	Description
Active Directory Stored Procedures	Used to register instances of SQL Server and SQL Server databases in Microsoft Windows 2000 Active Directory.
Catalog Stored Procedures	Used to implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.
Cursor Stored Procedures	Used to implements cursor variable functionality.
Database Engine Stored Procedures	Used for general maintenance of the SQL Server Database Engine.
Database Mail and SQL Mail Stored Procedures	Used to perform e-mail operations from within an instance of SQL Server.
Database Maintenance Plan Stored Procedures	Used to set up core maintenance tasks that are required to manage database performance.
Distributed Queries Stored Procedures	Used to implement and manage Distributed Queries.
Full-Text Search Stored Procedures	Used to implement and query full-text indexes.
Log Shipping Stored Procedures	Used to configure, modify, and monitor log shipping configurations.
Automation Stored Procedures	Enable standard Automation objects to be used within a standard Transact-SQL batch.
Notification Services Stored Procedures	Used to manage SQL Server 2005 Notification Services.
Replication Stored Procedures	Used to manage replication.
Security Stored Procedures	Used to manage security.
SQL Server Profiler Stored Procedures	Used by SQL Server Profiler to monitor performance and activity.
SQL Server Agent Stored Procedures	Used by SQL Server Agent to manage scheduled and event-driven activities.
Web Task Stored Procedures	Used for creating Web pages.
XML Stored Procedures	Used for XML text management.
General Extended Stored Procedures	Provide an interface from an instance of SQL Server to external programs for various maintenance activities.

• Adhoc caching This option is beneficial only in limited scenarios. It is not dependable enough for you to design an application expecting this behavior to correctly control reuse of appropriate plans.

Consider the same procedure being called dozens or hundreds of times. Remember that SQL Server DB engine optimizer will cache the adhoc shell query that includes the actual parameter for each individual call to the procedure, even though there may be only one cached plan for the procedure itself. As SQL Server starts experiencing memory pressure, the work to insert the entry for each individual call to the procedure can begin to cause excessive waits resulting in a drop in throughput or even out-of-memory errors.

Also other kind of caching issues such as, with prepared queries, you actually specify the parameter datatype, so it’s easier to make sure you are always using the same type. When SQL Server parameterizes, it makes its own decisions as to datatype. If you look at the parameterized form of your queries of type Prepared, you’ll see the datatype that SQL Server assumed.

• Autoparameterization This option can be useful for applications that cannot be easily modified. However, it is preferable when you initially design your applications that you use methods that explicitly allow you to declare what your parameters are and what are their datatypes.

If the recompile is caused by a change in a SET option, the SQL Trace text data for TSQL statements immediately preceding the recompile event can indicate which SET option changed. It’s best to change SET options when a connection is first made, and avoid changing them after you have started submitting statements on that connection, or inside a store procedure.

In this scenario I have seen that Optimizer hints can also be used to force SQL Server to come up with a new plan in those cases in which it might be using an existing plan. Although there are dozens of hints that you can use in your Transact-SQL code to affect the plan that SQL Server comes up with, we will cover about PLAN GUIDES on seperate post or look within Kalen Delaney's blog.

•  Execution of those Stored Procedures then look at the two suggestions below:
• The sp_executesql procedure This procedure can be useful when the same batch might be used multiple times and when the parameters are known.
• The prepare and execute method These methods are useful when multiple users are executing batches in which the parameters are known, or when a single user will definitely use the same batch multiple times.

Mixing both parameterization & execution, if you try to force using sp_executesql or Prepare/Execute, all the statements in the batch must be parameterized for the plan to be reusable. If a batch has some parameterized statements and some using constants, each execution of the batch with different constants will be considered distinct, and there will be no value to the parameterization in only part of the batch. Finally as more queries are run, the amount of memory used for data page caching should increase along with the amount of memory used for plan cache.  

A hint of how you can monitor such activity there are two main tools for detecting excessive compiles and recompiles. Keep in mind that compiling and recompiling are not the same thing. Recompiling is done when an existing module or statement is determined to be no longer valid or no longer optimal. All recompiles are considered compiles, but not vice versa. For example, when there is no plan in cache, or when executing a procedure using the WITH RECOMPILE option, or executing a procedure that was created WITH RECOMPILE, SQL Server considers this a compile but not a recompile. You can use either System Monitor (PERFMON) or SQL Trace (PROFILER) to detect compilations and recompilations.

More to come....

The subject line of this post is a common factor that every DBA might have (once in lifetime). 

Performance Monitoring is a big subject when it comes to practice,  many factors include such as what to monitor, how often, and 'basic' rules of thumb. For the sake of performance you cannot simply compromise any SLA or Availability of application, there needs to be an effective approach to monitor and manage Database Maintenance issues in specific to DBCC, backup and spotlight on tempdb and Transaction log backup performance.  Let us spot light on few common issues that you will face in the DB maintenance scenario.

The key areas are:

1. Memory Available to SQL Server
2. CPU Utilization
3. Disk I/O
4. Response Time for Commonly Run Queries
5. SQL Error Logs

There are big links (inter-operability) between these issues, if you see one issue then another component is also affected. It is a fact that if a system suffers the percentage of free memory available to be used by any applicaiton (SQL Server, in this case) is consistently low (20% or less) it will significantly degrade the performance of SQL Server. By default leaving the memory to DYNAMIC is preferrable unless otherwise if a thorough (end-to-end) data testing has been done on your SQL Server platform, in general this will be suggested by Microsoft PSS/CAT team if you have raised the issue to support. If adjusting how memory is allocated to SQL Server does not free up enough memory for the SQL Server instance to function properly, the physical memory for the server may need to be increased, or the concurrent application load on the server may need to be decreased. 

In general kind of memory issues are sufferred when the server executing any resource intensive process, such as DBCC CHECKDB or DBREINDEX on an active database. It is a good practice to monitor the SQL Server error log even though when you don't see any problem or alerts on the instances, in this case you may see the checkdb that reported a error in tempdb as below:

There are various methods you could apply to secure SQL Server platform, that includes the areas of physical hardware and networking systems connecting clients to the database servers, and the binary files that are used to process database requests!

It is not that easy to implement the strict measures of security when the databases are spread out in an enterprise-wide network. There are tons of information about best practices for physical security strictly limit access to the physical server and hardware components. But it is not possible in all the occassions. Implementing physical network security starts with keeping unauthorized users off the network, a single-step to ensure the level of access is restricted. Next level is surface area reduction, a security measure that involves stopping or disabling unused components. This helps improve security by providing fewer avenues for potential attacks on a system and key to limiting the surface area of SQL Server includes running required services that have "least privilege" by granting services and users only the appropriate rights.  Most of this topic is available and I refer to Books Online and suggest to go through the pages of information on Security topic and also various blogs on web.

Next in the picture comes about client connection and authentication to SQL Server instance, instantly the TCP/IP protocol comes into consideration. By default, clients are configured to try all protocols until one of them works. If the TCP/IP protocol is disabled, clients continue with the next protocol. If TCP/IP is enabled but the endpoint is stopped, the connection attempt is not rejected, so the client does not try other protocols, but the stopped connection cannot be used. In this case, you must explicitly connect to a active endpoint and any additional user-defined endpoints behave the same way as default endpoints. Also BOL refers that When an endpoint is created for an IP address (or all IP addresses, by using IP_ANY) and a specific TCP port, permission to connect to the endpoint is granted to users in a process called provisioning. They retain the provisioning regardless of whether or not the server is actually listening on the IP address/TCP port combination.

You can restrict access to an endpoint to EVERYONE group, which is a good practice using DENY CONNECT statement when the SQL Server instance is accessed over internet or between enterprise domains. The best method is to grant permission to specific individuals or roles using GRANT CONNECT statment, but do not attempt to use GRANT CONNECT permission to the PUBLIC group unless you are assured about the accessibility and privileges.

Same in the series of GRANT and DENY permissions you can control the SQL Server login accessibility and enforce the password policies, say when your SQL Server has not be enabled with NT authentication which is by default. It is a bonus that SQL Server does provide such an option of enforcing password policy for SQL logins too. Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, the new password must meet these changes, also when SQL is running on Windows Server 2003 or later, SQL Server can use Windows password policy mechanisms.. Say when creating a SQL Login you can specify CHECK_POLICY=ON and again BOL refers certain conditions to enforce this policy:

• When CHECK_POLICY is changed to ON, the following behaviors occur:
  
• CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
• The password history is initialized with the value of the current password hash.
  
• When CHECK_POLICY is changed to OFF, the following behaviors occur:
  
  
  • CHECK_EXPIRATION is also set to OFF.
  • The password history is cleared.
  • The value of lockout_time is reset.
    

Some combinations of policy options are not supported.

• If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
• If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.
  

As per the BOL server operating system (Windows 2003 and later) takes care of policy, and in the case of Windows XP & 2000 systems the windows password policy only checks if the password is complex enough. As the policy of minimum length and history (based on security policy to use if the same password as previous N passwords), password minimum life and maximum life can be check within Windows 2003 and later systems, where CHECK_EXPIRATION policy will take care and in such cases the login can be locked out by default when there 3 wrong attempts. By default both of these options are off on SQL Server and you can enforce whenever required by using ALTER LOGIN statement. Just a note from BOL (again) about known issue in Windows 2003 operating system that it prevent the bad password count from resetting after the Account Lockout threshold has been reached. This could cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON. For more information about the Account Lockout threshold, see Your User Account May Be Prematurely Locked Out article.

How about finding the vulnerabilities and security issues from the code?

You may think on the subject line why TEMPDB is a key factor, a simple term is the size and physical placement of the tempdb database can affect the performance of a system.

Even a simple query requires the access/process to TEMPDB, which is a part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server.

So if the SQL Server instance TEMPD is spread across multiple files then you need to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. This is required to avoid any sort of latch timeout or file initialization you need to limit the AUTOGROW operation depending upon your disk setup/configuration. As you may be aware in SQL Server engine/architecture the data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations, as such it is an advantage of instant file initialization reclaims used disk space without filling that space with zeros.

This is where a DBA must pre-allocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment, how can you achieve this task? You have to monitor the queries that are getting exeuted, monitor the TEMPDB periodically during any changes to the database maintenance scheduled jobs. As any such changes to such processes can trigger the files expansion and pre-sizing will help tempdb from expanding too frequently, which can affect performance. Also there is a good reason to leave the TEMPDB database setting to AUTOGROW as you don't know when a smalll process can even trigger the file expansion that should be used to increase disk space for unplanned exceptions.

To monitor the growth or usage of tempdb SQL Server provides Dynamic Management Views (DMVs) that can provide lot of information with an additional planning of collecting performance counters in Windows System Monitor. Books OnLine provides much of information on such DMV that can help to monitor the TEMPDB:

sys.dm_db_file_space_usage. Returns space usage information for each file in the database. For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage. Returns page allocation and deallocation activity by session for the database. For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage. Returns page allocation and deallocation activity by task for the database. For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators. Returns a virtual table for the objects producing the most versions in the version store. It groups the top 256 aggregated record lengths by database_id and rowset_id. Use this function to find the largest consumers of the version store. For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store. Returns a virtual table that displays all version records in the common version store. For more information, see sys.dm_tran_version_store (Transact-SQL).

sys.dm_tran_active_snapshot_database_transactions. Returns a virtual table for all active transactions in all databases within the SQL Server instance that use row versioning. System transactions do not appear in this DMV. For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot. Returns a virtual table that displays snapshots taken by each transaction. The snapshot contains the sequence number of the active transactions that use row versioning. For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction. Returns a single row that displays row versioning-related state information of the transaction in the current session. For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot. Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. If the current transaction is using snapshot isolation, this function returns no rows. sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

As you may be aware that SQL Server 2005 started to offer such an helpful statistics with the help of DMVs, not only that with the service pack releases there has been few changes about transaction processing. One of them is the introduction of version store concept and snapshot isolation transaction isolation level. these two subjects are completely related. If you are aware about Oracle's feature of undo tablespace, which is a place where the previous version of data is stored. Similar to this feature SQL Server offers the transaction isolation level SNAPSHOT which is a picture of the actual situation is taken and the transaction asking for reading this data is redirected to the version store. So all this information is stored in TEMPDB, which is another good step to keepup the space of TEMPDB in order to get an optimum statistics when they are required.

Lastly, winding up to the PERFMON counters you must followup the SQL Server related counters for system performance impacted by SQL Server processes. You will find these counters under SQLServer:Transactions performance object, further to Free Space in tempdb (KB) to monitor the KB offree space in the tempdb database which will give you a snapshot of requirement of enough free space in tempdb to handle the version store that supports snapshot isolation. One of the Technet article provides the formula of providing estimate of size of version store:

[size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

The above will provide a rough estimate of the size of the version store and in case of long running transactions you must monitor the the above counter, also you should rule out the online index rebuild information (if enabled/exists) that such longest running transactions will not consists as such online index builds use a separate version store. The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

Finally to keepup the performance of your SQL Server you must take care of TEMPDB database and create as many files as needed to maximize disk bandwidth. It is evident that using multiple files reduces tempdb storage contention and yields significantly better scalability. At the same time do not simply create too many files because this can reduce performance and increase management overhead. One of the best practice is to create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary, in case of dual-core CPU then consider it as 2 CPUs.

It has been over 6 months for this post about TEMPDB importance, recently I had to upgrade my understanding of such concept with the help of book "Microsoft SQL Server 2008 Internals" by our own Kalen Delaney. This SQL 2008 bible talks that in order to achieve your goal of TEMPDB management you must have a  consistent view of the database which is necessary, because DBCC CHECKDB must analyze all allocated pages in the database and check the various links between structures on multiple pages. By all means the process will be handled within TEMPDB while the pages being analyzed (that is, the whole database) cannot change while the consistency checks are running;

The following are very useful to know why TEMPDB is useful for every SQL instance:

 Common problems and practices to the TEMPDB issues resolution:

To wrap up on disk consideration you must have faster spindles for better performance, a good practice to ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency. As usual option on filegroups for administration requirements such as backup / restore, partial database availability, etc. is required to recover the databases when any issue strikes. Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.  Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files / LUNs / RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.

I always trust how good your resources are to find out a root cause than how bad the problem is?

It is one of the best point of disaster recovery position I wanted to be to get to work on solution on quicker basis than worrying about the impact, the business will have their say about availability or scalability for their application! Well we cannot work on both of the requirements at the same time and anytime a DBA must be hands-on to solve the problem in a flexible manner than in panic.

Lately, it has been much of Architecture and Solution design tasks for me rather than working on a real-time performance problem and honest opinion is I miss much of those good old 'DBA' days. One of the best features that I consider in SQL Server 2008 version is 'Multiserver Administration' taking the help of SQL Agent that takes advantage of the self-tuning features of SQL Server, does it work that way! But you should rather concern on normal conditions of the platform and additional job tuning is not necessary. In any case the performance between client and server suffers if the network is not good enough and loads increase when you run jobs, generate alerts, and notify operators. 

Back to the old school and in order to determine whether your SQL Server system is performing optimally, take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.

The following areas affect the performance of SQL Server:

• System resources (hardware)
• Network architecture
• The operating system
• Database applications
• Client applications

The minimum requirement of basline measurement is essential to determine:

• Peak and off-peak hours of operation.
• Production-query or batch-command response times.
• Database backup and restore completion times.

The format of Baseline and Benchmarking methods in SQL Server  which helps the DBA to establish the performance baseline and helps to compare the baseline statistics to current server performance. Numbers far above or far below your baseline are candidates for further investigation, bigger or smaller is not the question and the important task is how good is your system and even smaller ones may indicate areas in need of tuning or reconfiguration. For example, if the amount of time to execute a set of queries increases, examine the queries to determine if they can be rewritten, or if column statistics or new indexes must be added.

So the first step to add on the tasks is identify the processes that are blocked for more than 10 seconds:

SELECT *  FROM sys.sysprocesses WHERE blocked<>0 AND waittime > 10000

This brings a good old question by a SQL user that my application is designed that do not typically requests locks directly, then why do I see locking on my SQL Server? The answer is usual that locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a TSQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions, this is irrespective of how the application queries are managed and at best leave it to SQL Server than using your own hints.

So coming to the handy TSQL code that I use to query that will display lock information :

(you might observe that both the above code is available on BOL to get handy)

Also I would like to get the resource usage which returns information that associates a session ID with a Windows thread ID. This is essential to know how the Windows operating system manages the threads and how it helps on performance of the thread which can be monitored in the PERFMON tool and be aware that this query does not return session IDs that are currently sleeping. 

When you talk about Performance problems then fragmentation & database integrity issues are dearest friends of DBA!

As data is modified in the databases, the tables and indexes will become fragmented, which means the data is scattered everywhere in the database. So talking in DBA sense the moral is as more fragmented a clustered or non-clustered index becomes, it is nothing but database engine requires to add more pages to fulfil the same query request. So it is a required practice to defragment these indexes on a regular basis using your own methods or industry best practices. So to check the allocation of all database pages and internal structures based on the regular activity on the database, delete/insert/modify processes and instant statement pops up is DBCC CHECKALLOC. When it is executed an internal database snapshots are created to maintain the transactional consistency during the operation of checkout. To get consistent results I have seen that some of  the adhoc scripts specifies TABLOCK then creation of database snapshot may not occur causing exclusive database lock during the execution of this command which will potentially affect the database concurrency.

Ok, enough talk on index fragmentation and now I'm more concerned on disk fragmentation as a DBA you are more responsiblefor server hardware as well. You must understand the value that professional grade disk defragmentation software brings to their servers. Storage servers can experience high levels of disk thrashing (the constant writing and rewriting of small amounts of data) caused from excessive file fragmentation for database MDF & LDF files. When we refer the operating system based fragmentation then you will see network and application issues have a much more visible impact on the performance of network-based services, especially when problems with those functions are encountered.

Optimal disk performance translates into better ROI, but how can you assure that present hardware can bring it all?

Testing the current hardware disks or involve the hardware vendor to get certfied on the testing pattern it is followed. As you can easily obtain results for impact of server disk defragmentation by looking at common tasks that network servers, both physical and virtual, encounter, ranging from maintenance tasks such as server backup and anti-virus scans.Many out there think that involving the tests of a basic user tasksscha s opening files stored on the host server and virtual machines, and manipulating email are not included, but they must be included too. They will help to add on more fuel to the rigorous database testing such as database queries, index creation, and bulk updates.

So doing such a tests requirement I always depend upon the fantastic tool from Diskeeper which can produce fantastic reports about the file fragmentation on to the root level on the servers. Using this tool you need to perform a benchmark tests involving the hardware and I have performed such benchmarking tests using HP's DL380 G5 equipped with dual quad-core 2.83 GHz Xeon processors, each with a 2x8MB L2 cache, 32 GB of RAM and eleven 72 GB 10,000 RPM SCSI drives attached to an HP Smart Array P400 controller that has a 256 MB cache and that supports both serial attached SCSI and SATA drives. The disk volums varies from 30GB to 170 GB due to the nature of the application data I have to go on such range that keeps the database sizes to a minimum of  500 GB within 7200 RPM locally attached SATA drives and 4 x 500GB for backup purpose only. So this Diskeeper tool helped to achieve such a results by performing 3 levels of fragmentations low, medium and high with  Diskcrusher fragmentation utility to createfragmented files and directories. So the results that are produced are phenominal on the level of fragmentation you’ll encounter in usual production environments and pretty much dependent upon the level of use and types of applications the server deals with.

So to refer about complete testing on SQL Server side, we used 2008 version  with a bulk insert of 500,000 rows of data. The bulk insert is often the fastest
method of getting data into a SQL Server database. Obviously captured the resource usage of the server by using PERFMON (SYSMON) for CPU, Memory and disk related counters. Also the testing of table key creation times are directly related to how much data SQL Server had to touch, and the level of fragmentation that had to be dealt with. SQL Server 2008 does a very good job of managing its databases, but defragmentation shows appreciable
improvement in the performance of tasks such as this with a performance improvement of over 11 percent in the most fragmented environments. Coming to the query execution that the test involved differ primarily due to the amount of data returned by SQL, in response to the query. The tests depict the effects of
manipulating the data on a fragmented drive with peak performance improvements of approximately 21%.

SQL Servers benefit from defragmentation; reading and writing data with either application simply works better when the files are not fragmented. Which means whenever a DBA follows the regular practice of defragmenting the indexes then underlying file defrag methods are easy to manage and you will instantly see the result as is improved performance during the busy times at the server. I see that many such solutions are simply de-tour'd by suggesting to throw more storage resources (hardware) at a problem and it should be the last resort, because it only masks the potential problems that intelligent disk
defragmentation addresses. The reason I mention on Diskeeper is which it reported that server storage levels are consistently exceeding 75 percent or so, due to the affect of transactional and usage of users about the  data off of the servers causing the fragmentation which isn’t a direct result of reduced capacity, the chances for fragmentation increase as free storage space decreases and the operating system is forced to write data into an everincreasing
number of non-contiguous spaces.  The tool comes with the option of manual or auto defragmentation method, by using the automated the same disk volume sees absolutely minimal fragmentation even though it is in continual use by applications and users.

To wrap up the discussion it is better to defragment the operating system files too on regular basis or whenever there is a low server activity on the SQL Server, specifically.

There was an interesting discussion about User Defined Fuctions (UDF) performance and patterns, the actual phenomenon is that UDF has got parameters to pass on during the execution.

Just to talk about introduction of such options in the SQL Server history, stored procedures and views have been available in SQL much longer than UDFs, but each of these objects has their niche in SQL Server development. Stored procedures are great for processing complex SQL logic, securing and controlling access to data, and returning a rowset to a calling routine whether that routine is a VB or any other frontend program or another TSQL batch. Unlike views, stored procedures are compiled, making them ideal candidates to represent and process frequently run SQL statements. Views are great for controlling access to data, but they do it differently than stored procedures. Views are limited to only certain columns and rows from the underlying SELECT statement that generated the view. Thus a view is often used to represent a commonly used SELECT statement that may join several tables, employ a WHERE clause, and expose specific columns. Views are often found in the FROM clause of a SQL statement joined to other tables and views.

The default behaviour of scalar UDFs will be like single statement execution, not like batch one where the difference between them is optimizer will have to pickup the best plan to execute. Due to this SQL Server must execute each function on every row, using any function incurs a cursor like performance penalty.  One way UDFs are simple to complete the task for that reason and can be coverted into single-expression, the actual execution of Scalar UDF will have the performance issue whatever way you may try to optimize the list of TSQL statements. So the actual execution time will vary due to fetching the large number of rows, in a sense 1000 or more. As it works like cursor mechanism, during the followup of each row the UDF is executed which will cause usual performance loss. To add more fuel to the problem if the WHERE clause on that UDF includes any filtering then complete execution will have to move slowly as it must invoke each UDF on each row where it is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing.

Coming to the subjective discussion the 2 logic scenarios to be followed as declare local variables, set values to the variables & issue a query referring to the variables in the query filter. So the execution of batch will have the first 2 steps and then actual part of execution needs to be optimized which is in vaccum as the variable values are unknown which ends up using a hard coded guess (which happens to be 30% selectivity for a range filter). On the other scenario (2) with specified constants in the filter, SQL Server expands the definitions of of the UDFs, substituting the parameter values with the specified constants, and the optimizer will optimize the expanded query with knowledge of the values in the filter. So the actual execution part of UDF will be far better on the second scenario of specified constants will be better as indexes are better used. To construct above I have referred following TSQL statements from Microsoft technet links (revisiting old material):

use northwind;
go

create function dbo.FuncWV(@d as datetime) returns table
as return
select orderid, orderdate, customerid, employeeid
from dbo.orders
where orderdate >= @d;
go

So here if the code has the declaration of variable to a value as follows::

declare @myd as datetime;
set @myd = '19980506';

select orderid, orderdate, customerid, employeeid from dbo.f1(@myd) as t;

So look at the execution plan of above query where you will see the predicate identified the optimize which is the ORDERDATE variable: orderdate >= @myd

So the optimizer is required to fine tune the query where this value in the predicate is unknown at optimization time:

select orderid, orderdate, customerid, employeeid from dbo.orders where orderdate >= ?;

Scalar datatype-returning UDFs can be used in various situations to make the code more maintainable, reusable, and less complex. This can be very useful when the same segment of T-SQL code is used in several places, perhaps by several stored procedures and batch SQL statements.  Every now and often I see that UDF execution involves full table scan (clustered index too) causing performance loss as per the range filter causing the hard-code guess of rows selectivity. As the same discussed above on scenario 2 and to demonstrate the same the above code has been modified to use a constant:

select orderid, orderdate, customerid, employeeid from dbo.f1('19980506') as t;

So the above query's execution plan  will have to predicate identified as per the optimizer, which is ORDERDATE >=.
As per this optimizer already takes on the value in the predicate which helps to fetch selectivity estimate to nearest possible with better performance, such as:

select orderid, orderdate, customerid, employeeid from dbo.orders where orderdate >= '19980506';

When you look at the execution plan the lookup on the orderdate column, calculate the selectivity of the predicate with an option of using index on orderdate column. Also in some cases the optimizer will not use previously cached plan, due to the query statements against the function use similar selectivity ranges. Back in SQL 2000 days UDFs are seen as evil, it seems that SQL Server’s query optimisation fails to deal with UDFs properly. Basically it optimises and builds the execution plan without ever looking inside the UDFs to find out what they’re doing, so in all the optimisation isn’t worth the cycles it consumes.

In some cases when you have nesting within UDF then one catch with nesting functions is that built-in functions that are nondeterministic, such as the getdate function, cannot be nested inside of another UDF (otherwise, a SQL Server error is raised). A nondeterministic function is one which may not return the same result when called multiple times with exactly the same parameters. The getdate function falls into this category since every time it is called, it returns the new current date and time. Another commonly used nondeterministic built-in function is the NewID function. It is also nondeterministic as it always returns a unique GUID and, as such, the NewID function is not allowed to be nested within a UDF. SO as per the BOL discussion talking about table-valued UDFs: Within the category of table-valued UDFs there are two sub-types: inline table value-returning UDFs and multistatement table value-returning UDFs. UDFs that return inline tables return a rowset via the SQL Server table datatype. They are defined with a single SELECT statement making up the body of the function. Inline table value-returning UDFs cannot contain additional T-SQL logic outside of the SQL SELECT statement that defines the table it will return. However, they are simpler to create than UDFs that return multistatement tables since they do not have to define the exact table structure to be returned. UDFs that return inline tables extrapolate the structure of the rowset from the SELECT statement itself.

To sumup the discussion assume that there is a UDF that performs a SQL SELECT statement that takes one second to execute. If this UDF is used in a SELECT or a WHERE clause it will be executed for every row. Thus the time the main query takes to execute could increase drastically depending on such factors as the number of rows evaluated and returned and the types of indices in place. Before using a UDF in this type of situation, carefully weigh the options and do some performance testing.  There are good references on the blogs about the UDF performance, that I would like to refer:

An Introduction to SQL Server Scalar UDFs

Alexander Kuznetsov : Not all UDFs are bad for performance.

Are UDFs Harmful to SQL Server Performance?

Using schema binding to improve SQL Server UDF performance

How many times in a day you observe the TEMPDB in your SQL Server enviornment? 

How many times in a week you monitor the TEMPDB in your SQL Server environment?

Well, these are not an interview questions to ask and as you aware that because TEMPDB is so much more heavily used in SQL Server 2005 than in previous versions, you have to take much more care in managing it.  Talking about a database TEMPDB is no different to any other database, but it has an unique behaviour that its a mutual dependancy with the SQL Server instance which is recreated if you restart the SQL instance and not recovered. So this is where the attributes of TEMPDB as a workspace is refreshed which is used for temporary user objects and internal objects that are explicitly created (on fly) by SQL Server processes.

Another topic I would like to refer that logging for TEMPDB is entirely different as compared to other databases in SQL Server, some of you may assume that there is no logging in TEMPDB, which is not true! Every operation in TEMPDB is logged so that transactions on a temporary objects can be rolled back (until certain point of time). Whereby certain records in the log contain only enough information to rollback a transaction and not to recover it. Talking about objects there are 3 objects within TEMPDB, user objects, internal objects and version store. User objects can be identified by the way of using temporary tables or global temporary tables that are created by the user processes. Also its worth to mention about table variables and table-value functions, whereby the free space availability is most important and you can see the user objects by refering to the system catalog views such as sys.objects and sys.partitions along with sys.allocation_units views. Internal objects are not visible to the user or with usual management studio tool. They are not listed in the catalog views because their metadata is managed/stored in the memory.

Talking about Transactions in the subject line above, you will see that DMVs starting with DM_TRAN_* are related to transactional processing within TEMPDB and other user databases. Few changes to the transactional processing within SQL 2005 brings out the concept of version store and snapshot isolation, as they are related to each other. To refer the same to Oracle world this works as UNDO tablespace, which is a place a previous version of data is stored. Using the transaction isolation level SNAPSHOT is a click of actual situation is taken and transaction referring to read the same data is redirected to this version store. That is why it is most important factor for the DBA to keepup the size and free space availability to the TEMPDB, so you must monitor the tempdb to ensure that there is enough space there to support the transactions asking for records located in the version store. Not only that TEMPDB manages the tasks such as hash operations, eagle spool and table spool operations, temporary objects and so on (your best bet to read about these topics is to refer latest BOL).

To get a glimpse of  data for the transactions within TEMPDB execute:

select * from sys.dm_tran_active_snapshot_database_transactions

select * from sys.dm_tran_database_transactions

To get all the information regarding the user transctions and LSN information refer to sys.dm_tran_locks:

Select * from sys.dm_tran_locks;

--To display virtual table with records in version store itself, a little bit confused because the record information comes in a binary format.

Select * from sys.dm_tran_version_store;

Here is the script that I got it when dealing a problem with MS PSS team:

Select transaction_id, db_name(database_id)as database_name,
database_transaction_begin_time,
case database_transaction_type
when 1 then 'Read/Write'
when 2 then 'Read only'
when 3 then 'system transsction' end as transaction_type,
case database_transaction_state
when 1 then 'not initialized'
when 2 then 'init. but no log records'
when 3 then 'log records'
when 4 then 'prepared'
when 5 then 'committed'
when 6 then 'rolled back'
when 7 then 'being committed' end as transaction_state,
database_transaction_log_record_count,
database_transaction_log_bytes_used,
database_transaction_begin_lsn,
database_transaction_last_lsn,
database_transaction_most_recent_savepoint_lsn,
database_transaction_commit_lsn,
database_transaction_last_rollback_lsn,
database_transaction_next_undo_lsn
from sys.dm_tran_database_transactions
where transaction_id>1000;

Also the reference from Kalen Delaney's site: http://www.insidesqlserver.com/companion/SE_Chapter8code.txt & http://www.insidesqlserver.com/companion/QTO_Chapter6code.txt, too good to get you complete code snippets on finding out the locking & concurrency  and their problems.

So the bottom line is best to keepup the transactions in smaller portions that can keepup the performance of the TEMPDB and overall Applications too. The important point and outcome I get for such action is, it avoids lock contentions and deadlocks on a busy database/SQL instance.

Finally a good reference on TEMPDB basics by Sunil Agarwal here:

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions) and corresponding blog posts there, in addition to TempDB Monitoring and Troubleshooting: Allocation Bottleneck , TempDB Monitoring and Troubleshooting: IO Bottleneck and Managing TempDB in SQL Server: TempDB Configuration links.

You may have seen the sp_replwriterovarbin - New SQL Server Injection-Based Attack Found vulerability about the new public reports of a vulnerability that could allow remote code execution on systems with supported editions of SQL Server 2000, 2005, 2005 Express Edition, 2000 Desktop Engine (MSDE 2000), and Windows Internal Database (WYukon).

The only exceptional SQL Server systems are SQL Server 7.0 Service Pack 4, SQL Server 2005 Service Pack 3, and SQL Server 2008. MSA961040 highlights that this vulnerability is not exposed anonymously. An attacker would need to either authenticate to exploit the vulnerability or take advantage of a SQL injection vulnerability in a Web application that is able to authenticate and by default, MSDE 2000 and SQL Server 2005 Express do not allow remote connections. An authenticated attacker would need to initiate the attack locally to exploit the vulnerability, so it is always better to tighten the permissions on these databases. Review more information from this  CVE reference link and the general recommendation from Microsoft Security Advisory that all customers should apply the most recent security updates released by Microsoft to help ensure that their systems are protected from attempted exploitation.

As usual the systems that are enabled Automatic Updates will get this windows update, if not you must refer to  Microsoft Security Central site for more information. Here is the workaround referred by MSA961040  link :

Deny permissions on the sp_replwritetovarbin extended stored procedure

Use one of the following procedures:

•

To deny access to the stored procedure, connect to SQL Server as a sysadmin using osql.exe or sqlcmd.exe or through SQL Server Management Studio and execute the following T-SQL script: use master deny execute on sp_replwritetovarbin to public

•

To deny access to the stored procedure using SQL Server administration: • For SQL Server 2000: 1. Connect to SQL Server using Enterprise Manager as a sysadmin 2. From the SQL Server Enterprise Manager window, select the desired server 3. Expand the databases 4. Expand Master 5. Click Extended Stored Procedures. A list of stored procedures appears. 6. From the list of stored procedures, right-click sp_replwritetovarbin and select Properties 7. In the Properties window, click Permissions 8. Under Users/Database Roles/Public, find Public, then click the box in the EXEC column. The box turns into a red X. 9. Click OK twice • For SQL Server 2005: 1. Connect to SQL Server using SQL Server Management Studio as a sysadmin 2. From the Object Explorer window, select the desired server 3. Expand the databases and the system databases 4. Expand Master 5. Expand Programmability 6. Click Extended Stored Procedures. A list of stored procedures appears. 7. From the list of stored procedures, right-click sp_replwritetovarbin and select Properties 8. In the Properties window, click Permissions 9. Click Deny execution beside the desired user IDs and click OK

Impact of Workaround: Disabling the sp_replwritetovarbin extended stored procedure prevents updates to subscription tables by all users. The impact of this workaround only affects customers that use transactional replication with updatable subscriptions. Customers using transactional replication with read-only subscriptions, bi-directional transactional replication, or peer-to-peer transactional replication are not impacted. For more information on transactional replication with updatable subscriptions, see MSDN.

How to undo the workaround

As referred above these steps will reduce the problem by blocking the known attack vectors, as I have also successfully applied on the affected SQL Server systems at my end. For more information refer to this SQL 2005 Security best practices link and list of webcasts on security best practices:

• Authentication and Authorization
• Encryption
• Execution Context
• SQL Server Security Best Practices

PERFMON and Resource Governor - two great tools in hand for a DBA to monitor/resolve the performance problems, agreee!

Back in olden days you may remember about SQLDiag and PSSDiag, the utility used as a general purpose diagnostics collection utility that can be run as a console application or as a service. Both of these Diag tools are helpful to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft CSS.

Ok, most of the web resources out there talk pages and pages together about features/utilities you can use, but not how you could make use of these tools (freely available from Microsoft) to do 'a definitive'  type of analysis near the beginning of troubleshooting; for example, if you don't know whether the primary bottleneck on the system is disk or CPU, how do you know whether to focus on Query A (the most expensive query in terms of CPU) or Query B (the most expensive in terms if I/O)? Hence from a getting started to performance monitoring the tool to look forward is the good old PerfMon Tool.

So when going about current used version for instance SQL Server 2005 instance, loads of counters are available that can be related to Database Engine (such as Access Methods, Buffer Cache ratio, Cursor, Locks and Transactions etc.). Talking about this version it is good to refer about Service Broker related and replication (if any involved) related too. Why Service Broker, as it is a set of performance counters that provide information on its activities. Service Broker also logs serious errors to the SQL Server error log and the Windows application event log, a reference from Books Online :

First, use the WAITFOR clause. Messages seldom arrive at predictable intervals. Even in a service where messages arrive at roughly the same rate that the stored procedure processes the messages, there may be times when no messages are available. Therefore, the procedure should use a WAITFOR clause with a RECEIVE statement or with a GET CONVERSATION GROUP statement. Without WAITFOR, these statements return immediately when there are no available messages on the queue. Depending on the implementation of the stored procedure, the procedure may then loop back through the statement, consuming resources needlessly, or the procedure may exit only to be reactivated shortly thereafter, consuming more resources than simply continuing to run.

You allow for the unpredictability in timing by using the WAITFOR clause with the RECEIVE or GET CONVERSATION GROUP statement. If your application runs continuously as a background service, you do not specify a time-out in the WAITFOR statement. If your application is activated by Service Broker, or runs as a scheduled job, you specify a short time-out, for example, 500 milliseconds. An application that uses the WAITFOR statement gracefully handles unpredictable intervals between messages. Likewise, an activated application that exits after a short time-out does not consume resources when there are no messages to process.

So then moving onto SQL Server 2008 specific there are some new counters that get introduced with certain features like Resource Governor (RG). These features are exciting and open new options to developers and administrators when working with SQL Server. RG introduces a whole new concept of Resource Pools  and Workload groups and we have monitors for the same under separate counters set.

Mixing together with PERFMON (SYSMON) counters with Resource Governor will give you much leverage on the performance monitoring concept, lets take a look at the following counters that will help to identify where is the problem:

Above all lets not forget the Dynamic Management Views (DMVs), another mix and match tool with Resource Governor to get more information on the health of SQL instance *& problem resolution.

When the SQL Server is faced to the internet then you have to take utmost care to ensure that the each row in the table is not compromised to avoid any unprecedented activity.

As it is getting popular that rise in SQL injection attacks has reminded the best practices and rules to secure the database & web environment to avoid any escalation in a class of attacks targeting Web sites that use ASP and ASP.NET technologies. I don't need to say that these SQL injection attacks do not exploit a specific software vulnerability, but instead target Web sites that do not follow secure coding practices for accessing and manipulating data stored in a relational database. When a SQL injection attack succeeds, an attacker can compromise data stored in these databases and possibly execute remote code. Clients browsing to a compromised server could be forwarded unknowingly to malicious sites that may install malware on the client machine.

When you have a threat then the user will obviously look for an advisory from the Product Vendor or search on web to deploy best practicies on their side, this is an important aspect that every DBA and System Administrator must follow by identifying and correcting vulnerable ASP and ASP.NET Web application code which does not follow best practices for secure Web application development.

You may have secured the access to the tables but what if the hacker can get hands-on for the user password, this is where you should need a validation process and having such a failure to properly validate user input can allow an attacker to inject SQL commands into input fields, which may then execute against a data source leading to database corruption or code execution on the server. In the recent times we have asked Microsoft TAM many times to provide such an advisory to avoid any sort of mishaps within the data environment.

This is what we have been given the information about several tools that are available to secure the platform such as these tools can cover detection, defense, and identifying possible coding which may be exploited by an attacker.

Few months ago I had an excellent opportunity to learn and share the knowledge within SQL Server performance tuning, Tech-Ed Online talks [remember Tech-ed Online Panel: Leveraging ... ].

Same in the series Amit Bansal, SQL Server MVP from India and myself had a brief chalk-talk online session on query tuning techniques and also brief tips on how you can fine tune a query performance problem. Amit highlights the best usage of query hints to take advantage on performance tuning and I have given brief insight on how you can go about resolving a database performance problem.

Check it out the talk from here: Tech-Ed_Online Talk - Performance-Tuning_Techniques

(You may not open the video if you click on above link, rather you may save locally on your machine and open the same with your media browser)

Hope this will help you to set a baseline your performance techniques.

Format: asx
Duration: --:--

You may be aware that creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. No difference between the data validation between them and as usual the query optimizer does not differentiate between a unique index created by a constraint or manually created. For the need of data integrity you should consider UNIQUE or PRIMARY KEY constraint and it may not be achievable if  duplicate key values exist in the data. Say if the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. For additional optimization it is better to consider creating non-clustered indexes that contains included nonkey column, see Index with Included Columns link.

Filtered indexes can provide the following advantages over full-table indexes:

• Improved query performance and plan quality
  A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.
  
• Reduced index maintenance costs
  An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.
  
• Reduced index storage costs
  Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.
  

It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. However, the query optimizer is more likely to choose a filtered index if it covers the query.

How many tools do you (need/use0 to monitor the performance of your SQL Server instance(s)?

Well, I'm not asking any count on the x number of tools you require to monitor the SQL Server instances within your environment, and Performance Tuning using Management Studio unlike any other tool to figure out to monitor the performance tuning. With SQL Server 2008 tools such as Management Studio (SSMS) can do all on the  management, performance tuning and monitoring etc. aspects. Then it comes a flurry of questions to ask yourself that :

• How many times you have used the Database tuning advisor?
• How many times you have used the Profiler (on day to day/weekly/monthly)?
• How often you use and depend on the DMVs?
• How many times you use the Performance Reports (Standard) in SSMS?
• Do you know how to monitor the performance on your SQL Server?

If you are not a DBA then don't worry, today the amount of resources we have on the cyber-world can get you on wheels to act like a DBA to take hands-on work to monitor your SQL platform. You may be aware that there are new reports to the standard reporting tool on SSMS, check them out and we will see them in the end... 2005 and 2008.

Management Data Warehouse is new kid on SQL Server 2008 town that is loaded with new performance monitoring features, you need a good understanding on the architecture of how SQL Server 2008 collects performance and diagnostic data and presents the information in Management Studio.  For an automated performance monitoring you should develop a plan for enabling performance data collection and analyzing the results for troubleshooting and diagnostics.

For all such questions in addition to the SQL Server 2008 Books ONline and online resources on web about SQL Server 2008 I would like to refer the presentation "Performance Monitoring Using SQL Server Management Studio - power of Management Data Warehouse" that was delivered at SQLBits conference on 13th September in United Kingdom.

I'm sure this will help you to learn how to create your own collection sets and manage the performance data collection warehouse database.

Here is the presentation file (stream) and thanks to Camtasia for making this happen.

Also the powerpoint show - http://sqlserver-qa.net/Satya_SQL2008_PoweofMDW.pps for  your reference.

Format: avi
Duration: 01 Hour 11 Minutes