Making the Most Out of the SQL Server 2005 Performance Dashboard
|
By : Brad McGehee |
If you have been using SQL Server 2005 for a while now, you may wonder what I am talking about when I refer to the SQL Server 2005 Performance Dashboard. No, you are not suffering from overwork and fatigue, causing you to lose your mind and forget what new features have been included with SQL Server 2005. The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.
In brief, the SQL Server 2005 Dashboard is a custom report (custom reports are a new feature of Service Pack 2) for Management Studio that gathers data from the many Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) available in SQL Server 2005. It is not a replacement for other performance tools, but an adjunct tool to help DBAs better identify and troubleshoot performance problems. In many ways, the SQL Server 2005 Dashboard reports are similar to the many built-in reports already offered by Management Studio.
Because the SQL Server 2005 Performance Dashboard is an add-on tool, you must take the time to download the free tool from Microsoft's Web site and install it. Fortunately, this is an easy process, and I highly recommend that all DBAs download and install the Performance Dashboard on every SQL Server 2005 instance that has Service Pack 2 installed.
Where to Get the SQL Server 2005 Performance Dashboard?
In order to install the SQL Server 2005 Dashboard, you must download at least two files from Microsoft. First, if you have not done so already, you must download SQL Server 2005 Service Pack 2. This is because Service Pack 2 includes new functionality that has been added to support the Performance Dashboard.
You can download SQL Server 2005 Service Pack 2 from:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
You can download the SQL Server 2005 Performance Dashboard add-on from:
How Do You Install the SQL Server 2005 Dashboard?
Before you can install the SQL Server 2005 Dashboard, you must first install Service Pack 2. Yes, you are right, maybe you haven't upgraded to Service Pack 2 yet. In that case, you will have to wait until you upgrade before you can install the Performance Dashboard. There are several reasons why you must first install Service Pack 2, some of which include the addition of the custom report feature to Management Studio, added server-side functionality, and bug fixes.
The Performance Dashboard custom reports don't require you to install Reporting Services on each SQL Server. The custom reports run under the client-side report viewer control, which is a part of Management Studio. This control allows you to run custom reports, but not to create them. If you want to create custom reports, you can, but you will need the SQL Server Business Intelligence Development Studio to do this.
Assuming you have installed Service Pack 2, and you have downloaded the Performance Dashboard add-on (SQLServer2005_PerformanceDashboard.msi), here is how you install it.
1. Double-click on SQLServer2005_PerformanceDashboard.msi to begin the installation, and you see the above window. Click Next to proceed.
2. Accept the license agreement and click Next.
3. Enter the registration information and click Next.
4. Before you automatically accept the default installation path for storing the reports that make up the Performance Dashboard, you may want to consider another option. And that is to store the reports on the share of a file server instead of the local server. Why would you want to do this? While it is not required, the benefit of doing this is that all your SQL Server instances can share this same shared folder to access the reports, which means you won't have to install the Performance Reports on each and every SQL Server instance. This also can come in handy if you create custom reports yourself and want to easily share them among all SQL Server instances. While there is no requirement that the report definition files for Performance Dashboard have to be located on each SQL Server instance, it is important that all of the files be located in the same folder. In this example I will use the default folder. Once you have selected the folder location, click Next.
5. Now you are ready to install the Performance Dashboard, so click Install. After a very quick installation, you see the final dialog box.
6. Click Finish. Now, I bet you think you are done and ready to begin using the Performance Dashboard. Nope, there is one more step you need to do first before you can begin using the Performance Dashboard.
7. In the folder where the Performance Dashboard report definitions were installed, you will find a script called: setup.sql. You will need to run this script in every instance of SQL Server 2005, SP2, where you want to use the Performance Dashboard.
Above is a screen shot of the folder that was created when you installed the Performance Dashboard. There are several things to note in this folder. First, is the setup.sql script already described. Second, the PerfDash.chm file, which is the help file for the Performance Dashboard. I recommend that you read this help file, as it is very useful. Third, note the performance_dashboard_main.rdl report definition file. This is the key report file that you will need to access when you begin the Performance Dashboard. We will talk more about this later. The rest of the .rdl files are sub-reports of the performance_dashboard_main.rdl report definition file and cannot be used directly. You may also note that there are no binaries in this folder. This is because installing the Performance Dashboard does not install any binaries in SQL Server. Other than the custom report definitions and the setup.sql script, there are no other components of the Performance Dashboard.
8. To run the setup.sql script, launch Management Studio, load the script, and run it. A partial view of the script is shown below.
This script runs in the context of the msdb database and creates a variety of functions and stored procedures used to create the data supplied by the Performance Dashboard. No new tables or databases are created by this script. If you like to play with code, this script is a great example of how you can take advantage of the power of DMVs and DMFs. Be sure you run this script in every instance. This, unfortunately, is a manual process.
You are now done with the initial setup of the Performance Dashboard, and it is ready for use.
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.
Current Activity
Most of the data from this section of the screen is self-evident, but not all of it. For example, when you see a number below the User Requests and User Sessions, this number is the count taken when the Performance Dashboard was last refreshed. On the other hand the elapsed time and cache hit ratio figures is the total elapsed time for all previously completed requests for this particular Performance Dashboard session.
You can drill down for more information by clicking on either User Requests or User Sessions. When you click on User Requests, you see the current user requests as of the moment of the last refresh, as shown below.
As with most of the reports shown here, this one has been truncated to fit the space available. The actual report includes much more detail than what you see above.
When you click on User Sessions, you get this report:
This report is very similar to the information provided by Management Studio Current Activity, except it provides more information. Again, this report has been truncated, and the actual report show much more detail.
Historical Information
While the Performance Dashboard does not collect historical information, some of the SQL Server DMVs do, and that is where we get the limited historical information shown below. In this section, we will take a quick look at each of the following reports: Waits, IO Statistics, and Expensive Queries.
Waits
This report shows a historical snapshot of all the wait states that have occurred since the last time this specific SQL Server instance was restarted.
In the example above, we see that besides the sleep wait state, the biggest wait state encountered by this SQL Server instance was from the Network IO category. For more specific information, you can drill down into each wait state category, which I have done above for the Network IO wait state.
This is a powerful report you can use to help determine what, if any wait states, are negatively impacting SQL Server's performance.
IO Statistics
These historical reports show you what databases are producing the most IO, along with a wealth of additional information. The screenshot below is of the top part of the report, which summarizes IO by database.
The above screen shot is another part of the report that lets you see what specific objects are creating the most IO. In addition, if any missing indexes are indicated, you can drill down into the report to see exactly what the missing indexes are so that they can be added.
Expensive Queries
The information provided by this report is similar to the other query reports we saw, except these represent only those queries that are currently cached by SQL Server. This will give us a better view of what is happening in our server.
You have the option to sort the results six different ways (with each being a separate report).
The above screen shot (which is truncated) shows you the top 20 most expensive queries, along with useful statistics. You can also drill down into the execution plan of each query.
Miscellaneous Information
This last section of the Performance Dashboard screen provides information on three subjects.
Active Traces
The first report, Active Traces, just identifies any current traces being done on this SQL Server instance. Even if you are not running an active Profiler Trace, you will always see one active trace. Why? This is because SQL Server automatically traces some events for you, all the time, and this is the trace you are seeing. When you perform a regular Profiler Trace on this instance, then you will see two traces.
While this particular report is interesting, I have not found it all that useful, yet.
Databases
The Databases report provides a quick review of all the databases on this instance, which can be handy if you need a quick look at key database configuration options.
Missing Indexes
This last report lists all of the missing indexes, as determined by SQL Server. This analysis is not as comprehensive as that done by the Database Engine Tuning Advisor, but it will identify obvious missing indexes. Your goal should be to have no missing indexes listed.
As you can see, the SQL Server 2005 Performance Dashboard provides a wealth of information on how SQL Server is working. It is a new and powerful tool for all SQL Server 2005 DBAs.
What is the Performance Hit of Using the Performance Dashboard?
One of the advantages of the Performance Dashboard is that the only time there is any performance hit at all is when the reports are actually run. When reports are run for the first time, or refreshed, they hit many of the DMVs and DMFs to gather data and a very small amount of resources are used. When reports are not running, there is no resource usage. This means that you can use Performance Dashboard on all your servers without having to worry about any negative performance impact.
What Are You Waiting For?
By this time, you should already be downloading the Performance Dashboard. If you aren't, then what are you waiting for? This is a free tool that can help you do a better job of performance monitoring and troubleshooting your SQL Servers. There is no downside to this tool, and it will only get better as Microsoft continues to improve it over time.