Microsoft® SQL Server® 2012 Bible

934

Part VII: Monitoring and Auditing

If can be useful to clear the values returned from this DMV for testing purposes. To do this,

execute the following statement:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

GO

It’s also a great idea to take regular snapshots of the contents of this DMV and store it in

a table for reporting purposes. This allows you to trend over time where your SQL Server

instance spends most of its time waiting.

Waiting Requests

The DMV sys.dm_os_waiting_tasks (see Figure 39-3) can reveal those sessions currently

waiting for some resource. This DMV is a great place to start when you think you may have

performance problems because you can quickly see the number of waiting tasks, the reason

the task is waiting, and diagnose blocking issues, or view excessive parallelism. This DMV

is also useful because it allows you to see wait patterns that you would not easily see with

the DMV sys.dm_os_wait_stats.

FIGURE 39-3

This query returns all requests in the “waiting list” that are user processes. This query returns

any session that may be blocking, the duration of the wait, and the reason the request is

waiting.

c39.indd 934c39.indd 934 7/31/2012 10:04:45 AM7/31/2012 10:04:45 AM

http://www.it-ebooks.info