Tips for DBAs to Troubleshooting SQL Server Performance Issues
Troubleshooting SQL Server performance issues is one of the common and important task of DBAs. It is not very easy to troubleshoot performance issues without prior experience or good knowledge about how SQL Server query execution works and what factors can contribute to performance issues with the SQL Server instances. It is very common to see many entry level DBAs or even DBAs with more than five years of experience still struggle dealing with performance issues, as there are just many things that can cause the performance problems and many symptoms may be interrelated and not easy to diagnose and come to a conclusion after which the solution to be found and implemented. In many organizations troubleshooting performance problems take many hours to days and even weeks too.
To help DBAs to be able to find a quick list of things which they can check or look into in case any SQL Server performance issues are related. It is important to note that troubleshooting performance issues with SQL Server is very broad ranging from SQL Server configuration, OS configuration, Network or other hardware configuration, blocking, waits, fragmentation, outdated statistics, etc. Below are some of the things which DBAs can look into when performance issues are reported with SQL Server instances.
– Understand about the problem. Generally performance issues are reported by developers, Managers, application users or some monitoring tools. Depending on the source of the issue reported, gather data by asking questions like what do their interpretation of performance issues mean, why do they think SQL Server is cause of performance issues, what data they have collected, what symptoms do they see with application, did they collect any data so far, etc. Collecting as much data can be helpful to move in the right direction.
– If the performance issues are reported by all users, that means it is general slowness, can be anything from Hardware configuration issues to SQL Server query problems. We can start looking into basic SQL Server configuration settings like MaxDoP, Max Server Memory, SQL Server version and patch details, platform, etc. Gather OS configuration details like CPU, Memory and disk details and if they are as per best practices like data and log files on different disks, tempdb on its dedicated disk, etc. Also, sometimes OS settings related to instant file initialization, lock pages in memory, CPU power plan set to balanced can show impact too.
– Quickly check on perform to see if there are any resource bottleneck. Look into counters for CPU, Memory, Disk IO and network to see any issues or any values which are outside of expected values.
– From SQL Server, we can check if there are regular maintenance like rebuild of indexes and statistics updates. Check for fragmentation and when stats were last updated. Check if there are any jobs currently running, if jobs related to reindexing, stats update, checkdb or backups running, then stop them. Check if there is any blocking happening on the SQL Server instance, if found, check the headblocker and take appropriate action.
– Check wait stats on the server and see the top contributing wait and see if we can related to any physical resource bottleneck.
– Check currently running sessions which are taking more time and see why they are taking more time. Try tune them by creating any missing indexes or rewrite queries, etc.
– If the application is related to Vendor, then check for any best practices from Vendor for improving the performance.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings