Published on March 19, 2020 by Ralph Mast
What causes SQL Server bottlenecks?
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.
Preventing bottlenecks comes down to making sure SQL Server has the resources it needs to access and process the data queries demand. Below are a few key preventative measures to stop SQL Server bottlenecks from happening.
- Set a baseline for appropriate CPU usage. IT can analyze average CPU usage under normal processing loads and monitor if the utilization is increasing or decreasing. If the usage is increasing, bottlenecks are more likely to happen - and more processors may need to be added to prevent this. When performance is suffering, check CPU utilization. If it approaches 100% and does not come back down, you might need to add processing power.
- Monitor memory pressure. One way to do this is to monitor how long on averages data pages remain in memory, and if that is coming down over time. If Page Life Expectancy drops, it means that SQL Server is needing to free up room in memory to bring data in from disk to process. Monitoring Page Life Expectancy may give you advance warning of memory pressure and allow you to address it before it becomes a bottleneck.
- Throttle SQL Server memory usage to allow the operating system the memory it needs. As stated earlier, SQL Server will use as much memory as it can get its hands on, and if SQL Server takes memory the operating system needs, SQL Server will give up some, but will have to write data to disk to free up the memory, which makes the operating system and SQL Server wait until that is complete. Microsoft recommends at least 4GB left of data for the operating system with more for additional CPUs, and a formula can be applied to determine the right amount of memory to leave for the operating system.
- Monitor disk utilization. When I/O channels are saturated over extended periods of time, you may need to migrate to faster disks or spread your data out over more channels to relieve the pressure.
If you're experiencing issues relating to SQL Server bottlenecks, Solvaria can help. Learn more about our Database Health Assessments that provide a customized full-scale audit on your SQL Server database instances.
Share on
