SQL Performance killers and bottlenecks

SQL Server Performance Killers

•          Poor indexing
•          Inaccurate statistics
•          Poor query design
•          Poor execution plans, usually caused by bad parameter sniffing
•          Excessive blocking and deadlocks
•          Non-set-based operations, usually T-SQL cursors
•          Poor database design
•          Excessive fragmentation
•          Non-reusable execution plans
•          Frequent recompilation of queries
•          Improper use of cursors
•          Improper configuration of the database log
•          Excessive use or improper configuration of tempdb

Hardware Resource Bottlenecks

Typically, SQL Server database performance is affected by stress on the following hardware resources:

•          Memory
•          Disk I/O
•          Processor
•          Network

Identifying Bottlenecks

There is usually a relationship between resource bottlenecks. For example, a processor bottleneck may be a symptom of excessive paging (memory bottleneck) or a slow disk (disk bottleneck). If a system is low on memory, causing excessive paging, and has a slow disk, then one of the end results will be a processor with high utilization since the processor has to spend a significant number of CPU cycles to swap pages in and out of the memory and to manage the resultant high number of I/O requests. In a case like this, increasing memory is a more appropriate solution because it will decrease pressure on the disk and processor as well. In fact, upgrading the disk is probably a better solution than upgrading the processor.

Note The most common performance problem is usually I/O, either from memory or from the disk.

Memory Bottleneck Analysis

Memory can be a problematic bottleneck because a bottleneck in memory will manifest on other resources, too. When SQL Server runs out of cache (or memory), a process within SQL Server (called lazy writer) has to work extensively to maintain enough free internal memory pages within SQL Server. This consumes extra CPU cycles and performs additional physical disk I/O to write memory pages back to disk.

The good news is that SQL Server 2012 has changed memory management. A single process now manages all memory within SQL Server; this can help to avoid some of the bottlenecks previously encountered because max server memory will be applied to all processes, not just those smaller than 8k in size.

I/O Bottlenecks

               SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQL Server uses tempdb for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

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.