When looking at SQL Server performance, there is a number of different aspects (or moving parts) that we could consider for what could be impacting the overall performance. The two very effective ways of understanding what is going on is analyzing either server waits (you know, what is happening around the server, wait activity, or where it is doing its work) and also around query waits.

Today I am going to focus on the server waits to show you how through Diagnostic Manager, you are able to drill in and look at what activity is happening from a server wait perspective. So when we bring up the console which you see on the screen right now, we can choose an instance on the left-hand side, this is where we have our navigation pane, select the instance and at the top we have a variety of different tabs, Overview, Sessions, Queries, Resources, as you can see, Databases, Health, Service Monitoring, Log Scraping, and the Prescriptive Analysis which provides recommendations for how to rune performance.

Those same objects also reside on the left-hand side. It is the same thing, just two different ways to navigate essentially. So, where we can go to analyze server waits is available under the Resources tab.

So I go to the Resources tab. The first thing we see is a summary of what is happening with the workload, with CPU memory, disk activity. We can drill into other aspects of performance like memory performance or disk activity (specifically look at counters around disk I/O [input and output]). But I am going to go over here to the server waits.

When I select the server waits, it will bring up this view with charts indicating what is happening in all these different areas, all these different wait categories, from backup to I/O activity to memory resources to locks that are happening.

And you can also see at the bottom what is happening with different wait activities as well. I see that I have a number of different locks that are popping up here, so I might want to focus purely on that category and not muddy the water with anything else going on.

So if I go up here to the top and choose the little dropdown for all wait types, go to categories. As you can see, I have the lock option that I can drill into, and now I am focusing purely on the different types of locks that are happening. Or instead, I could go to the categories and change it up to look at I/O [input and output] activity, like that. Or hit the dropdown for I/O [input and output] activity and we would go to Others.

Whatever category I choose, down at the bottom, we are going to see the list of available details modified. You can also just keep it with all of the different waits if you want to and sort by any category, like here I have the total wait time, I might sort by that category instead. And then, of course, I can see all the wait time breakdown and the descriptions if applicable. Some of this information, we do have descriptions for what it is, some of it we do not but in this case, we do have some details here (some descriptions) it will tell you a little bit more about what the wait it. And sometimes even some helpful information on the right-hand side for how to approach that wait.

Here is a matter of fact is CXPACKET wait, that has some help details. And if I click on that, it is going to bring up a little window, and in the window, it will provide some descriptive information on what a CXPACKET wait is, typically tied to CPU pressure and how to approach that as far as dealing with a max degree of parallelism. So you can review that and then take the next necessary action. You might even drill into the query waits which would be available under the Queries tab to analyze underlying activity on the SQL Server with different queries executing that might play into this as well.

But from this view, this will help us understand if there are any hotspots trending on the server in any of these areas of I/O [input and output] activity or backup operations or resource utilization. And again you can hit the little categories to see the different areas that we can look at, And even drill into specific waits in any one of those categories.

In this case, I am looking at other types of waits, which are going to display by total waits, signal waits, or resource waits. In this case, I am going to look at resource waits.

So that is how we would use the tool to analyze server waits. At this point, I recommend you look at this in your own environment to see what is going on. You are looking at my demo environment. Of course, the proof is in the pudding in what you see with the performance in your own environment.

If you have any questions, feel free to reach out to your account manager at IDERA. We can set up a more one-to-one type of a presentation or discussion around what you are seeing in your environment and hopefully this information will be helpful to you to understand where the actual activity is happening where the pressure may be created in some areas of activity.