Chapter 6. Operating system DMVs
This chapter covers
- Identifying why your queries are waiting
- Obtaining performance counter information
- Capturing DMV data periodically
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.