Joey Javas

SQL 2005 provides Dynamic Management Views (DMV). One of these is sys.dm_os_wait_stats, which can be queried to give information about waits encountered by threads that are in execution. This information can be used to evaluate performance issues with SQL Server or, even more granularly, with specific queries.

To query this view, you must have VIEW SERVER STATE permission.

The columns available in this view are:

wait_type – Name of the wait type
waiting_tasks_count – Number of waits on the wait type
wait_time_ms – Total wait time for the wait type in milliseconds
max_wait_time_ms – Maximum wait time for the wait type in milliseconds
signal_wait_time_ms – Difference between the time the waiting thread was signaled and when it ran

Future posts will examine how to utilize this information to performance tune SQL queries.

A new feature of SQL 2005 is Dynamic Management Views, commonly referred to with the acronym DMV.

DMVs give the database administrator information about the current state of the machine. DMVs can be used in conjunction with SQL Server Profiler and System monitor to actively identify problems with database systems. DMVs return internal, implementation-specific details on database performance and thus are an indispensable tool in the arsenal of any seasoned DBA. If you have not utilized DMVs in your database environment, well, now is as good a time as any to get up to speed on this new, easy-to-use feature. We will be writing a series of articles over the next few weeks that explore DMVs – What are they?; What DMVs are available?; How can they be used to tune your production environment?

In the meantime, if this short article has got you all a-tingle with anticipation, DMVs are described in this article in Microsoft’s Books Online.