Installing SQL Server 2005 Performance Dashboard Reports

Problem
I am trying to leverage the new SQL Server 2005 DMVs for performance analysis. While they're very useful, it would be nice to be able to see graphical representations of the data collected by the DMVs. Is there a graphical utility within SQL Server I can use as well?

Solution
In SQL Server 2005 Service Pack 2, Microsoft introduced a new feature that affords DBAs the ability to run Custom Reports. This feature allows DBAs to run their own Reporting Services custom reports from within the SQL Server Management Studio. To leverage this feature, Microsoft has released a Performance Dashboard that you can download and use to identify performance issues in your database server. The dashboard abstracts the data that is collected from the SQL Server 2005 dynamic management views (DMVs). However, it should not be used as a substitute for learning the various DMVs and their purpose. Note that the custom reporting feature is only available as of Service Pack 2 of SQL Server 2005. Both your database server and the Management Studio must be at Service Pack level 2 or greater. You don't need Reporting Services installed to run the dashboard.

Open a Management Studio session and set your focus to the Object Explorer pane. Right clicking on any node will display a Reports option which leads to the Custom Reports option.

You can download the Performance Dashboard from the following link

http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

The following is from the web-site in the above link:

Common performance problems that the dashboard reports may help to resolve include:

• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention

The name of the download file from the above link is DownloadSQLServer2005_PerformanceDashboard.msi. It is a Windows installer file that, when executed, will create a folder named PerformanceDashboard on your server. If you choose the default location, the installer will place the files in the following default location on the server: C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard.

Double clicking the installer, we're presented with the Welcome screen. Click Next.

We're presented with License Agreement screen. Enter your license information and click Next.

We're presented with a registration screen. Enter your registration information and click Next.

Accept the default features by clicking Next

We're now ready to install. Click Install

Installation is now complete!

Now that the Dashboard has been installed, we need to set our server up to run it. Open a new query window within SQL Server Management Studio. Navigate to the location to where you chose to install the dashboard files (the default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard). Execute script setup.sql. This script will connect to the msdb database and install everything needed to run custom reports.

Once you've run the set up script, you're ready to run the custom report. Right click on any node in the Object Explorer and click on Custom Reports...

A file search dialog box will open. Navigate to the folder where you installed the dashboard files. The dashboard report file is performance_dashboard_main.rdl.

Once you open the .rdl file via the dialog, you're presented with the following warning screen.

Clicking Run in the warning dialog, we're presented with the report!

SQL Tips : Clearing Cache for Valid Performance Testing

Problem
When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem. To determine the best overall solution, each option is tested and the results are recorded. As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves. Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison. How can I ensure during each execution of a new set of code that the data is not cached?

Solution
If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command. Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers. Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool. Here is a quick code snippet to serve as an example:

USE ; GO CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO

Although the CHECKPOINT and DBCC DROPCLEANBUFFERS commands seem to be the most elegant approach because they can be included in your T-SQL test scripts, you also can achieve the same results by either restarting the SQL Server instance or restarting Windows. If you are testing via a batch file (or similar) then you could issue 'net stop mssqlserver' and 'net start mssqlserver' DOS commands. As a side note, you also have the option to shutdown SQL Server via the T-SQL SHUTDOWN command, but would need to restart the services via either the 'net start' command or via one of the GUI tools. Although these options are possible, they are not recommended. These last set of commands will shut down your SQL Server instance or machine, which is probably unneeded.

A few words of caution...

It is not recommended to issue the CHECKPOINT\DBCC DROPCLEANBUFFERS, the 'net stop mssqlserver', T-SQL SHUTDOWN command or restarting Windows on production systems just for the sake of testing. These commands could have detrimental results to your environment. It is recommended to only issue these types of commands in testing environments with coordination among your team due to the impact to the overall SQL Server. In addition, keep in mind that if you do issue these commands only in test environments that if multiple tests are being conducted simultaneously issuing the CHECKPOINT and DBCC DROPCLEANBUFFERS commands may skew results for other testers.

Next Steps

• As you conduct performance testing in the future consider including the CHECKPOINT and DBCC DROPCLEANBUFFERS command in each of your scripts to ensure cached data is not benefiting later executions of your code.
• As you test, it may be a good idea to capture the query execution results for cold and warm cache.
• Although, time needed for a query is important, it is also a good idea to review the query plans for the code to determine the best cost among the options.
• For related information check out the following:
  • DBCC DROPCLEANBUFFERS
  • CHECKPOINT
  • SHUTDOWN
  • MSSQLTips.com - Testing Category