Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
Thanks for the detailed answer and links to useful resources. FYI, The whitepaper url is missing a 'd'. It should be download.microsoft.com/download/4/7/a/… –  lambacck Oct 31 '10 at 20:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.