Speed Up Your SQL Server Database with Performance Dashboard

As a database administrator you may not know every single database on your SQL Server intimately, though you may still be expected to make it perform like you do. No matter how many times you explain the importance of indexes and even what an index is, sometimes your users just don’t get it, just as they may explain their queries and data to you many times, but it just doesn’t make sense. Luckily, Microsoft has developed a set of tools that can help: the SQL Server 2005 Performance Dashboard Reports.

It is pretty simple to install the Performance Dashboard once you have downloaded it from the above link. I will point you to this link which walks you through installation as they to a fine job of describing the process.

The great thing about these reports is that they keep track of activity on your server for you. You don’t have create a trace or anything like that. Simply have your users go about their every day jobs of using their applications that go against the SQL Server and the information that Performance Dashboard needs will be captured.

Once installed you access the reports by right clicking either the server name or database name in Management Studio and then selecting “Reports” and then “Custom Reports…”.

Navigate to the folder that chose to put the reports in during installation (the default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard). You will see a list of reports. I suggest starting with performance_dashboard_main.rdl. Click Run to run the report. You will see the following report:

I ran this just after a restart of SQL Server, so there isn’t a lot of information in there yet. However, by the end of the day this report will prove very valuable. The part I want to concentrate on, though, is the link in the bottom left that says “Missing Indexes”. Click on that to run the Missing Indexes report:

(click for full size)

The columns you should concentrate on here (or at least the ones I concentrate on) are Average User Impact and Proposed Index. The Average User Impact column tells us how much of a benefit the user would see if you apply the proposed index. The higher the number, the more you should consider applying the index. The Proposed Index column actually contains the code you need to create the index. What I do here is I sort this report by Average User Impact, then export the report to Excel (right click anywhere in the white space and click “Export” then “Excel”).

This allows me to select the text in the Proposed Index column, copy it, and then paste the code and run it in Management Studio.

I have seen some great benefits with the Performance Dashboard. In one case we had a query that once took nearly 10 minutes to run return results in under a minute after we applied some of the Proposed Indexes.

As always, feel free to leave any questions in the comments of this post. I’m also interested to hear any success stories for those who have also used these reports.