Making the Most Out of the SQL Server 2005 Performance Dashboard

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.

]]>

Leave a comment

Your email address will not be published.