Chapter 6. Operating system DMVs

SQL Server provides several operating system-related DMVs that allow you to understand, at the server level, why your queries are waiting. Investigating these in conjunction with the SQL queries that are running should enable you to identify the problem areas and provide solutions to improve your SQL queries. I’ll provide a script to show your most common waits, both at the server level and those that occur when you run a given SQL query or batch.

Another DMV allows you to access the Windows internal performance counters for various SQL Server-related objects. These performance counters provide information on many aspects of Windows components. Inspecting these counters will often highlight areas of concern that can be targeted for improvement.

Correlating the wait types with various performance counters is a well-known and much-used method of performance tuning. We’ll examine the causes of waiting together with the changes in the performance counters to suggest reasons for the problems and how to rectify them.

6.1. Understanding server waits

6.2. Identifying your most common waits

6.3. Identifying your most common waits—snapshot version

6.4. Identifying why queries wait

6.5. Queries that are waiting

6.6. Finding what’s blocking running SQL

6.7. SQL Server performance counters

6.8. Effect of running SQL queries on the performance counters

6.9. How performance counters and wait states relate

6.10. SQL queries and how they change the performance counters and wait states

6.11. Correlating wait states and performance counters

6.12. Capturing DMV data periodically

6.13. Summary