SQL Server Bottlenecks: Causes and Solutions

1. There’s not enough memory

SQL Server loves memory, and will usually take as much as it can get, because accessing data in memory is far faster than accessing it from disk storage. Keeping data in memory is so important for performance that SQL Server will not only fetch the rows a query needs from disk, but will take extra rows from the same table and store them in memory, assuming that they will also need to be processed. This ability to “think ahead” and pull rows surrounding the accessed data from disk and store it in memory makes subsequent fetches from the same table much faster. Eventually, if there is enough memory and the database is small enough, all of the data from the database will be stored in memory. However, when there is not enough memory to store all the data, SQL Server will need to write data to disk to free up space in memory so it can bring in new data from disk. This is normal behavior, and is how SQL Server is designed to operate. When this happens too frequently, however, SQL Server experiences a memory bottleneck, and performance suffers.

2. Disk IO

Data can only be retrieved from disk as fast as the data channels will allow, and too much demand for data on disk will max out the Disk IO capacity, and a disk bottleneck may occur. Typically, we can see this when large reports access a large amount of data or a lot of data in a table that is not normally used, and the data will need to be pulled from the disk, saturating the I/O channels. Again, once the large amount of data has been pulled from disk, the bottleneck is resolved, but the after effects can be considerable as SQL Server services the queries that have been waiting.

3. High CPU utilization

Typically, we prefer that CPU utilization averages about 40% or below because this allows capacity to handle short term spikes in usage without causing a bottleneck. A higher average, or too many queries needing to be processed at once can cause CPU utilization to skyrocket, and cause a CPU bottleneck. When the CPU usage hits 100%, queries have to wait to be processed, and a bottleneck may happen. Data waits to be processed, overall throughput drops, and the hourglass keeps spinning with nothing happening.