How to Start the Performance Dashboard
How you start the Performance Dashboard is not obvious. But once you learn how, it is very easy. Here’s how you do it.
1. If you have not done so already, launch Management Studio.
2. Select a server and database, then right-click on the database, select Reports, then select Custom Reports. The following screen appears.
From this screen, select performance_dashboard_main.rdl, and click on Open. This will bring up the Performance Dashboard, which you see below. Once you open this report for the first time, Management Studio will remember it and you can pick it from a list instead of browsing for it each time.
How to Use the Performance Dashboard
The Performance Dashboard includes a lot of useful information for DBAs. We will begin our tour with the initial Dashboard screen, and then branch off into many of the sub-reports offered by this handy tool. I do want to mention that this is a 1.0 release, and because of this, it is a little unrefined and somewhat buggy. But even with these small problems, the Performance Dashboard provides a lot of useful information.
The Performance Dashboard Main Screen
The Performance Dashboard does not collect or store any information, but instead extracts data that currently exists from inside of SQL Server. Because of this, much of the data you will see is as of a specific moment in time. But in some cases, you will see some historical data that is stored as a natural byproduct of how SQL Server works. This historical data is limited, but very useful, as we will see later in this article.
The reason I point this out is because you will have to manually refresh the Performance Dashboard to get a current up-to-date snapshot of your SQL Server’s activity. This is easily done by clicking on the Refresh Icon at the top of the Performance Dashboard, as you see below.
Now, let’s take a look at the major sections of the Performance Dashboard main screen and see what it is telling us, along with sneaking a peak at some of the many drill-down sub-reports available.
System CPU Utilization
For most DBAs, the System CPU Utilization chart will make sense. What you are seeing is the last 15 minutes of SQL Server CPU activity, at one-minute intervals, since SQL Server was first started. Note carefully what I just said. For example, if you just started the SQL Server service, then there will be no CPU activity because there have not been any one minute intervals since it was first started. It will take 15 minutes before you see data in all the columns. Microsoft also wants to point out that the CPU utilization you see is not an exact number, but an approximation — but an approximation that is good enough for our purposes. In the example below, you can see that there are 15 minutes of CPU measurement, and each time the report is refreshed, this chart will always show the last 15 minutes of CPU activity.
In the above example, we can see that our SQL Server is very busy, probably too busy. In fact, if the Performance Dashboard thinks that current CPU activity is causing a hardware bottleneck, you will get a warning like you see above this chart. Such warnings only occur under heavy loads.
Another obvious, and useful feature of the above graph is that you can quickly see how much of the CPU utilization is from SQL Server, and how much is taken by other tasks on the server. This can be very handy to know, as sometimes performance issues on a server aren’t SQL Server-related, and this graph quickly tells you where CPU resources are being used most on the server.
Another very useful feature of the above graph is not so obvious. In fact, it is virtually hidden, unless you know what to do. And that is, if you click on any of the blue portion of the bar graph, a drill-down report will appear that lists the top CPU resource consuming queries.
This drill down report shows a lot of detail, unfortunately, it is hard to replicate here in this article. So what I want to do is to show you some sections of this report in detail, letting you know about the wealth of information that is available from this drill-down report.
First, let’s take a close look at the first query in this report.
Of all the queries that have run recently, these have been the most CPU intensive. You can see the actual query code, how many times it has executed in the last 15 minutes, how many execution plans it has created, when the plan was first cached, and when it was last executed. Also notice that the query is highlighted in blue. Anything highlighted in blue in a report can be clicked on and drilled down even more. While I am not going to show you now, if you do drill down on the query, you will be able to see its execution plan.
To the right of this same information, you see the following:
In this part of the report, you see the total time this CPU as taken during the last 15 minutes. In other words, what you see here is not the total time this query took to run once, but for 55 times. You also see the duration, physical reads, logical reads, logical writes, and if applicable, CLR processing time. This information is very useful when it comes time to identify and troubleshoot poorly performing queries. Also note in the above screen shot the little plus signs inside square boxes. When you click on them, the reports expand to show you even more detailed information, which we don’t have time to examine in this article.
We have barely just touched the surface of all the information that is available to us just from the CPU utilization part of the Performance Dashboard. Let’s now look at another key area of the Performance Dashboard main screen.
Current Wait States
At any particular time, SQL Server can be performing thousands of operations per second. Unfortunately, not all of them can be accomplished in the same instant of time. That means that very often, many activities have to “wait” for very short time periods until it is their turn. In fact, SQL Server uses several hundred different types of wait states to manage all of its complexity. As a DBA, it is our goal to minimize wait states, as the more there are, or the longer they are, the slower our performance is. While wait states are normal, extended wait states are not and should be identified and corrected.
SQL Server tracks many different types of wait states with various DMVs, and what is interesting about these DMVs is that some of them collect historical data on wait states since the last time the SQL Server service was restarted. Both current and historical wait state information can be very useful to the DBA.
In the initial Performance Dashboard screen, you may see the following chart. Note that I said “may.” This is because this particular chart displays information about current wait states as of the moment the Performance Dashboard was last refreshed. It is very possible that at that instant in time there were no current wait states, and if that is the case, no chart appears on the screen. But if there are some current wait states going on during that instance, then they will appear in this chart.
In the screen shot above, you can see that there was one wait state detected that was taking 60 ms at this particular moment in time, and that it was in the “other” category. Because there are so many different types of wait states, Microsoft has classified them into larger categories to make them somewhat easier to understand. Also note the warning above the chart. Like the CPU utilization chart, if the Performance Dashboard thinks that the current wait state is indicative of a performance issues, then it will provide such a warning. In this case, Performance Dashboard feels that a wait time of 60 ms for this particular type of wait is excessive and might be contributing to a performance issue.
To find out more about the current wait states, and what is causing them, you can click on the blue in the chart to drill-down to the details. The example I am showing you next is a drill down, but not of the above example, which was not very interesting, but of a different wait state example.
Again, because of the limitations of the size of this Web page, I can’t easily show you the entire report with a single screen. But what we do see above is a case where there is a Network IO wait state (actually 3 of them). Essentially, this is telling us that there are three queries waiting to be executed because of a backup in the Network IO wait states. In the example above, we only see one of the three queries; the other two are below the first in the actual report. But seeing one is more than enough to show you the kind of information available to you by drilling down into current wait states. This information can be very useful to the DBA in tracking down what is causing wait states.