Methodology:

There can be many reasons for a slowdown in SQL Server. We should use the following three key symptoms to start diagnosing problems

Resource bottlenecks: A memory bottleneck can lead to excessive paging that ultimately impacts performance.

Tempdb bottlenecks: Since there is only one tempdb for each SQL Server instance, this can be a performance and a disk space bottleneck. A misbehaving application can overload tempdb both in terms of excessive DDL/DML operations and in space. This can cause unrelated applications running on the server to slow down or fail.

A slow running user query: The performance of an existing query may regress or a new query may appear to be taking longer than expected. There can be many reasons for this. For example:

·    Changes in statistical information can lead to a poor query plan for

     an existing query.

·     Missing indexes can force table scans and slow down the query.

·     An application can slow down due to blocking even if resource

     utilization is normal.

Excessive blocking, for example, can be due to poor application or schema design or choosing an improper isolation level for the transaction.

The causes of these symptoms are not necessarily independent of each other. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU.

Tools for resolving resource bottlenecks:

One or more of the following tools are used to resolve a particular resource bottleneck.

System Monitor (PerfMon):

This tool is available as part of Windows. For more information, please see the System

Monitor documentation.

SQL Server Profiler:

See SQL Server Profiler in the Performance Tools group in the SQL Server 2005 program group.

DBCC commands:

See SQL Server Books Online and Appendix A for details.

DMVs: See SQL Server Books Online for details. 

Resolution Tempdb bottlenecks:

User objects, internal objects, and version storage can all cause space issues in tempdb. In this section, we consider how you can troubleshoot each of these categories.

Since user objects are not owned by any specific sessions, you need to understand the specifications of the application that created them and adjust the tempdb size requirements accordingly. You can find the space used by individual user objects by executing exec sp_spaceused @objname='<user-object>'.

SQL Server 2005 provides a row versioning framework that is used to implement new and existing features. Currently, Triggers MARS, Online index, use row versioning framework.

Resolution for slow running user query:

Consider the following options if you have detected inefficient query plans.

• Tune the query with the Database Engine Tuning Advisor to see if it

   produces any index recommendations. 

• Check for issues with bad cardinality estimates. Are the queries

   written so that they  use the most restrictive WHERE clause that

   is applicable? Unrestricted queries are  resource intensive

   by their very nature. Run  UPDATE  STATISTICS on the   tables

   involved in the query and check to see if the problem persists.

Does the query use constructs for which the optimizer is unable to accurately estimate cardinality? Consider whether the query can be modified in a way so that he issue can be avoided.

If it is not possible to modify the schema or the query, SQL Server 2005 has a new plan guide feature that allows you to specify query hints to add to queries that match certain text. This can be done for ad hoc queries as well as inside a stored procedure. Hints such as OPTION (OPTIMIZE FOR) allow you to impact the cardinality estimates while leaving the optimizer its full array of potential plans. Other hints such as OPTION (FORCE ORDER) or OPTION (USE PLAN) allow you varying degrees of control over the query plan.

Slow or long running queries can contribute to excessive resource consumption and be the consequence of blocked queries. Excessive resource consumption is not restricted to CPU resources, but can also include I/O storage bandwidth and memory bandwidth. Even if SQL Server queries are designed to avoid full table scans by restricting the result set with a reasonable WHERE clause, they might not perform as expected if there is not an appropriate index supporting that particular query. Also, WHERE clauses can be dynamically constructed by applications, depending on the user input. Given this, existing indexes cannot cover all possible cases of restrictions. Excessive CPU, I/O, and memory consumption by Transact-SQL statements are covered earlier in this white paper.

In addition to missing indexes, there may be indexes that are not used. As all indexes have to be maintained, this does not impact the performance of a query, but does impact the DML queries.

Queries can also run slowly because of wait states for logical locks and for system resources that are blocking the query. The cause of the blocking can be a poor application design, bad query plans, the lack of useful indexes, and an SQL Server instance that is improperly configured for the workload. 

Posted By: JG Kiran Kanth