SQL doctor Development Blog

We recently released SQL doctor 2.0, which now quickly prioritizes wait statistics and provides expert level advice for alleviating bottlenecks and other performance issues. To create the wait stat recommendations we worked closely with industry experts like SQL Server MVP and SQLAuthority.com founder, Pinal Dave, to help users analyze wait statistics and quickly improve server and application performance. Our own Juan Rogers recently sat down with Pinal Dave to talk about wait stats:

JUAN: Pinal, what is it about wait statistics that perplexes so many database administrators? Can you tell us what wait stats are and talk about the problems associated with them?

PINAL: The question about wait statistics is very confusing to many people. Some people call it wait types while others call it wait queues or wait statistics. At the end of the day, it can be very confusing. Basically, after running any query, you will have to wait for the results to be returned. Often the results come back very quickly or sometimes the results take a lot of time. What made the latter happen? Is the query more expensive? Is it processing more data? Sometimes, it is the same amount of data, but the query is taking more time to complete. Now, when it is returning the same amount of data and everything else is the same, why did it take more time? Even though all the resources are the same, the query is taking different durations to execute. So, if there is the same amount of work involved to get your data, where do the additional seconds come from in the delay? In a nutshell, wait statistics are nothing more than the total or summation of the time that a query has to internally wait for any type of resources.

JUAN: So this is a type of problem that any DBA would be interested in solving?

PINAL: Absolutely! As discussed, the query can vary in how long it takes to execute and you may not know the reason behind it. Everyone wants to know that if any query is taking more time now and why. This ‘WHY’ is what has piqued the interest of everyone in this performance tuning field.

Juan: What are the most prevalent wait statistics you have found based on your personal interaction with your customers?

PINAL: Awesome question! These are the three main resources – CPU, memory, and I/O. Any wait type, which is building a queue on these resources, will need to be examined. The most common wait type related to CPU is CXPACKET. It is because parallel threads are running in the CPU, and one thread often has to wait for another thread to finish. Another wait type related to the CPU is SOS_Scheduler. It is mainly related to SQLOS Schedulers. I often see a lot of I/O-related wait types, such as PAGEIOLATCH. If there is an issue with the network, then ASYNC_IO_COMPLETION is the usual wait type encountered.

JUAN: Are certain wait statistics specific to the versions of SQL Server, 2000, 2005, 2008, andR2?

PINAL: The answer is both yes and no. Many people think that wait statistics were introduced in 2005, but the reality is that they were included in 2000. It was in 2008 when many new wait types were introduced. So, the basic wait statistics are seen between all the versions in 2000, 2005, and 2008. Also, the SQL Server code Name ‘Denali’ CTP1 has these basic wait statistics. However, new wait statistics were added along with each version as new features were added. If you go in Denali, you can see some of the wait statistics, but the basic ones are the same among all of the versions.

JUAN: How difficult is it to identify a frequently re-occurring wait statistic? I mean, does it depend on the type of wait statistic?

PINAL: Very good question! There are a few Dynamic Management Views, which you can run to know more about the wait statistics. I use sys.dm_os_wait_stats and sys.dm_os_waiting_tasks to identify the top wait types. This DVM can be cleared explicitly, as well, as they get reset when SQL Server services restart. The wait statistics really depend on the resources bottleneck.

JUAN: That is a great segue to my next question? What tools do you think are best at identifying the problem and actually offering ways to resolve them?

PINAL: This is a tricky question. Honestly, I think SQL Server does offer a lot of functionality to us in regard to performance tuning. Wait type analysis is a fairly new subject. The interpretation of the wait statistics, the reason for, and the resolution of them are the new learning points for the DBAs. If you are talking about tools, my primary tool is very simple; I use the Dynamic Management View sys.dm_os_wait_stats. I use this DMV to figure out what is the highest or the most common wait statistic. Also, I use sys.dm_os_waiting_tasks to figure out the queries responsible for them. If you talk about third-party tools, I use Idera SQL doctor. It does a good job.

JUAN: Is there anything that a database administrator can do to proactively reduce the chances of an extended wait statistic adversely impacting their SQL Server applications and utilities?

PINAL: In short, it really depends on various factors. When it is about performance tuning, I pull up a lot of best practices and don’t think about what wait statistics will occur or not. Instead, I just blindly follow the best practices. For example, I make sure that my TempDB is on separate drive from my ldf and mdf file. I use partitioning on a very large table if there is need of the same. I do maintenance of indexes. Once I am done with these, then the real questions begin. I often take the help of native SSMS tools. I have used SQL diagnostic manager and SQL doctor to reinforce my recommendation and keep an eye on the best practices.

JUAN: So wait statistics are something that happens all the time, it’s the length of the delay and the cause that present the problem?

PINAL: Absolutely! There will always be the wait types listed in the Top 10 when looking at the system. There are few wait types that are harmless and can be ignored. There are a few that are very harmful and need to be taken resolved. You should worry about any wait type related to basic resources of I/O, memory, or CPU. The real key is learning and gaining experience with performance tuning.

JUAN: Finally, do you have any closing thoughts on wait statistics?

PINAL: Dynamic Management View, which gives a clear view about wait statistics, is essential. I encourage everyone to work along with DMV, which is a great starting point. However, wait types are not everything. There are many more aspects of the database that need to be examined in terms of performance. You should treat wait statistics as your best friend who will quickly tell you where there is resource bottleneck in your server. Using the advice of the wait types, you can quickly take action on the bottleneck and resolve the issue. For more information, visit my blog, SQLauthority.com, where you can find a month-long series on wait types and queues called SQL SERVER – Summary of Month – Wait Type – Day 28 of 28.