Transcript

Hello everyone. In this video, we are going to cover query wait monitoring and SQL Diagnostic Manager. Query waits are one of the most detailed views that are available within SQL Diagnostic Manager. And as you can see we can break down wait times by statement, by application, by database, by client, by session, and by user. So by doing that you have a lot of flexibility to really pinpoint exactly where the bottlenecks are coming from. Now it is important to understand how query weight monitoring is configured. By default query weight monitoring is going to be looking at real-time information only. Now on this particular SQL Server, I have told the tool to collect query weight statistics 24/7, or indefinitely. You certainly can do that. It may not be something that you want to do on every SQL Server, it is a lot of information to collect.

You have the option to schedule this to run during certain windows. And then I will also point out in a second, I will show you how you can have an alert response trigger wait monitoring for a set period of time. The method that we are using to collect query weights can differ. I do recommend that you use the default method which would be through a dynamic management view, so DMVs [dynamic management views] are a very efficient way to collect query weights, but on certain servers, he may opt for extended events or query store. Again I generally would go with a dynamic management view.

Now to get these weights to be collected throughout an alert response is relatively simple. If you go under alert actions and responses, if I look at this particular rule that I have created called enable query waits, it is a really simple rule. At the top, I defined my conditions the middle is the action the bottom is the rule so basically what I have said is if any of my production servers have OS [operating system] this time and SQL Server CPU hitting a critical level I want to enable query wait monitoring for 60 minutes for an hour. So this gives me the benefit of letting the tool target certain problematic time periods and then have it escalate its monitoring to a much more detailed wait view as we can see here. Now the waits that we are viewing are in a timeline format so we can see at the bottom the time that the weight occurred, the weight type, and then the duration.

But you can also change this view to a total duration view. So by focusing on duration view, it is going to be looking at whatever the weight type was over a certain period of time. Now the period of time is something that you define. So in the history browser range, my time is defined right now as 4 hours. Maybe I want to look at this over a 24-hour window, or I want to look at eight hours or 15 minutes, it is really up to you guys. But basically what we are looking at here is that right log was the most intensive wait type or the most prevalent wait type that we had over the last four-hour window of time. And then I could drill down into that, and it would start breaking down this individual wait type by application, statement, database. Etc.

So there is plenty of opportunity for you to really use this to diagnose query performance. So for example, right now what I am doing is just saying look at the queries that have been picked up, top ten in this case, and then show me what wait types we were seeing. Or I want to understand which databases, so one of my larger content databases in this SharePoint farm had the most wait types. So maybe we want to focus on the statements that we are coming into that particular database. So, this looks like the auditing, right. So if I view the text here, this is an insert into audit statement that is giving me the most wait times over the last four hours on this particular server. So query waits are a very important way of getting a better understanding of how a SQL Server is performing in a detailed fashion that I think is pretty remarkable, not only in real-time but you can also use the history browser with this particular feature to go look at three weeks ago and see what the waits look like or an hour ago, a minute ago if that is what you need to do. So hopefully this gets you interested in query waits if you have not worked with them, please go in there and take a look. If you need help, let us know. Thank you.