It’s T-SQL Tuesday again and this time it’s being hosted by the SQL Soldier. He’s picked the subject of Waits. As in, why am I having to wait for my query to finish. Or more specifically what is my query waiting for. Waits are a popular subject. You can find lots of great articles by some really smart people out on the interweb. Brent Ozar, Kevin Kline and Paul Randal to name but a few. That being the case I’m not going to discuss wait problems. I’m going to make a brief statement about the importance of knowing which waits aren’t a problem. And let’s be very clear, not all waits are a problem.

Waits are a natural part of life and you can’t avoid them. Almost every time you have parallelism you are going to have CXPACKET waits. It’s all but impossible to break out the parallel threads so exactly that they will all end at the same time. I mean you might get lucky, which is why I said “Almost”, but it’s going to be rather unusual. If you have a service broker or email system that isn’t constantly in use you are going to see some waiting. Your network is going to cause some waiting. So is your IO subsystem. There is a lot of waiting to be done even on a good healthy system.

So what is my point? Next time you are doing performance tuning (server, query, etc) you will be reviewing waits. Looking at sys.dm_os_wait_stats, sys.dm_exec_requests, etc searching for your “wait problem”. Just remember that not all of the wait’s you are seeing are a problem. Be aware of what’s normal for your system and what isn’t. One place you can start is Erin Stellato’s article Capturing Baselines on SQL Server: Wait Statistics and another is John Sterrett’s Benchmark SQL Server Wait Statistics. Once you have a good set of wait statistic baselines you will know what information you are seeing will help you and what won’t.

Filed under: Microsoft SQL Server, Performance, SQLServerPedia Syndication, T-SQL Tuesday Tagged: language sql, microsoft sql server, Performance, T-SQL, T-SQL Tuesday, Waits