What kind of values should I worry about in in sys.dm_os_wait_stats?

Question

While reading Will disabling hyperthreading improve performance on our SQL Server install I started wondering what the values I get for Jeff's first query:

SELECT * FROM sys.dm_os_wait_stats 
order by signal_wait_time_ms desc

Which gives me a result for which I have no idea if I am headed for trouble or not (I don't seem to be having any performance issues yet). What would be considered a red flag for impending performance issues?

My top values are LATCH_EX, SOS_SCHEDULER_YEILD, CXPACKET, ASYNC_NETWORK_IO and PAGELATCH_EX.

Answer 1

It depends on a number of factors. What problems are you having on the server? How long has the server been online since the last restart? What are the specific numbers for waiting_tasks_count, wait_time_ms for those waits?

There are two great resources on wait stats on the internet. The first is the Performance Tuning Waits and Queues Best Practices Whitepaper and the second is Bob Ward's blog post The SQL Server Wait Type Repository on the PSS Team Blog.

Its possible that the numbers you see are perfectly fine, and just a part of SQL Servers normal operation. At some point wait time will accumulate for various wait types, but by the same token, time spent waiting is time lost in execution so minimizing waits is a good way to tune things. You have to look at things in perspective to how long the server has been running or how long it has been since the last time the Wait Stats got reset. If I am looking at a server that has been up for a long time, I will usually use DBCC SQLPERF to clear the wait stats and then look at them at a fixed point in time later where I know that a representative workload for the environment has run, and I know when it was reset. That way I have a frame of mind for what the information I am looking at.