Identify and troubleshoot slow running queries in SQL Server

Achieving the performance for slow running queries is not a rocket science to understand.

The database performance is dependant on implementing optimum database design with sensible hardware-based configuration and capacity planning. So how you can identify where the performance is degraded, say when you desing it is easy to identify the large tables in the database and the more complex processes that the database will perform. In case of a normal database that has been outgrown since last few years having difficulty in achieving the performance, then take help of this essential checklist of PerformanceAudit to understand the behaviour of queries and other processes in the SQL Server.

The first event you will observe is CPU spikes that includes I/O storage bandwidth and memory bandwidth. Also poor query design will contribute 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. To support this performance degradation another biggest hardware bottlenecks of any SQL Server is disk I/O. As usual the default action by a DBA is to reduce disk I/O bottlenecks include:

• Tuning queries to minimize the amount of data returned.
• Using fast disks and arrays.
• Using lots of RAM, so more data is cached.
• Frequent DBCC REINDEXing of data to remove logical database fragmentation.

If your SQL Server is highly transactional, with mostly INSERTS, UPDATES, and DELETES, physical disk fragmentation is less of an issue because few data pages are read, and writes are small. But if you are performing lots of SELECTS on your data, especially any form of a scan, then physical file fragmentation can become a performance issue as many data pages needs to be read, causing the disk head to perform a lot of extra work. 

Also identifying correct indexes by avoiding missing indexes have to be maintained, this does not impact the performance of a query, but does impact the DML queries. 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. There are various Performance-Tuning related blogs in this website you can take help of.

It is obvious that long running queries (with slow performance) can contribute to excessive resource consumption and be the consequence of blocked queries. Blocking causes due to the lock so as the wait to acquire an X lock on a resource or the waits that results from lower level synchronization primitives such as latches. Refer to this MonitorBlocking KBA to take more into the issue. When a query is blocked, it is not consuming any system resources so you will find it is taking longer but the resource consumption is low. For details on the refer to Concurrency-RowLevel versioning article.

With SQL Server 2005 DMVs are very helpful to provide more detailed information with reporting facility on various wait types as compared to the previous SQL Server versions. For the sake of this blocking discussions  relevant information ranges from sys.dm_os_wait_statistics for overall and cumulative waits for SQL Server to the session-specific sys.dm_os_waiting_tasks that breaks down waits by session.

Also you could take help of Blocked Process Threshold in SQL Server 2005 version, this option is useful to manually detect the long-term deadlock is to configure the blocked process threshold. Follow as suggested on the BOL entry beofre, then monitor the Blocked Process Report event class in SQL Server Profiler, or use the sp_trace_create stored procedure and the sp_trace_setevent stored procedure for server-side tracing. Just in case if using SQL Trace, use sp_trace_setevent and event_id=137. If using SQL Server Profiler, select the Blocked Process Report event class (under the Errors and Warnings object)

Another DMV Sys.dm_db_index_operational_stats provides comprehensive index usage statistics, including blocks. In terms of blocking, it provides a detailed accounting of locking statistics per table, index, and partition. Examples of this includes a history of accesses, locks (row_lock_count), blocks (row_lock_wait_count), and waits (row_lock_wait_in_ms) for a given index or table.

To wrap up trail the events and look for:

• I/O pressue on the storage, with big spikes on IO with table scans means missing indexes. 
• Index contention. Look for high lock and latch waits in sys.dm_db_index_operational_stats. Compare with lock and latch requests.
• A potentially beneficial index is missing on join columns resulting in HASH joins. Indexes on join columns may avoid the hash.
• Sudden drops or consistently low SQL Cache hit ratio. Drops or low cache hit may indicate memory pressure or missing indexes. Memory bottlenecks: track with Perfmon counters -SQL Server Buffer Manager, Memory Grants Pending.
• Check network bandwidth, poor network latency will cause more traffic between server and client causing more reads for simple query.

• Check for missing indexes in sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details.

• Avoid performing any bulk load during the reporting processes time to avoid any sort of waits and locking betwen these 2 operations on the same table. Bulk load and indexes are very sensitive, enabling non-logged operations with a RECOVERY MODEL setting during the bulk copies are that indexes must be dropped. However not advisable for a huge table such as billion over rows, rather take advantage of Data-partitioning.

• Kimberly Tripps demonstration on data partitioning in SQL Server 2005 if you are using PARTITIONING.