Wait Stats in SQL Server

When you are optimizing a SQL Server installation you must look at the Wait Stats in SQL Server and the easier way to check them is using the following DMVs  sys.dm_os_waiting_tasks, sys.dm_exec_requests and sys.dm_os_wait_stats.

The sys.dm_os_wait_stats contains aggregated information about wait statistics for SQL Server, you must be aware that these wait statistics are aggregated at the server level, and there is no detail level available in this DMV. The following scripts queries this DMV.

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

image

The previous query returns the all waits with an accumulated wait time expressed in milliseconds. Because these wait stats are cumulative since the SQL Server service started to run, one way to clear these counters is to execute the DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR) and from this point SQL Server begins to accumulate the wait stats again.

Best Regards,

Eduardo Castro

Microsoft SQL Server MVP

http://tiny.cc/comwindows

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: