Hi Everyone. I wanted to ask a question before I begin my post today. And the question is:
When you’re trying to troubleshoot SQL Server, Where do you start looking?
This is one of the most hardest question to answer when you are to troubleshoot your SQL Server in terms of performance tuning. And if you don’t know where to focus your efforts, it’s easy to jump to the wrong conclusion.
Then how do you go about troubleshooting your SQL Server. Well, the key is to look at multiple sources of information, and not to take your data as multiple separate entities. A troubleshooting framework at such scenario, helps enormously. For me this basically trims down to the following items:
1. Wait statistics: A great starting point is to query sys.dm_os_wait_stats. You’re looking for significant system waits, such as an I/O bottleneck. This won’t tell you the root cause, but it’s a great start.
2. Virtual file statistics: Next, you should look at the virtual stats in the sys.dm_io_virtual_file_stats. These, combined with your wait stats, provide further evidence for the source of your problem. From the above mentioned statistics, you’ll be able to see the level of I/O activity and where it’s happening.
3. Performance counters: sys.dm_os_performance_counters provides you with performance counters that specifically relate to SQL Server. For example, if you’re seeing a problem in the disk I/O subsystem, high Full Scans/Sec may indicate a missing index or inefficient code.
4. Plan cache usage: Finally, query the execution information in sys.dm_exec_query_stats, and look for problems related to your findings in the previous stages of analysis. Find statements that have taken the most CPU, the longest execution time, and more.
Combining information from multiple statistics as shown above can help you pinpoint the source of your issue and come up with a solution that can improve the performance of your SQL Server.
Let me know what other statistics do you look at when you have to troubleshoot your SQL Servers and what kind of approach do you take while you’re at it?