Improving your SQL Server’s performance

A good starting point for performance tuning on your SQL Server is by establishing a baseline for your server’s current performance. To help that Microsoft provides a SSMS dashboard to monitor your server’s performance. The dashboard provides valuable information on:

  • Server Waits
  • Expensive Queries by
    • CPU
    • Logical Reads
    • Logical Writes
  • Missing Indexes

You can install the dashboard by downloading the software package located here: http://www.microsoft.com/en-us/download/details.aspx?id=29063

Once the software is installed, each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the dashboard reports. Using SQL Server Management Studio (SSMS), open the setup.sql script from your installation directory (default of C:\ProgramFiles(x86)\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script. Close the query window once it completes.

To open the dashboard in the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory (default of C:\ProgramFiles(x86)\Microsoft SQL Server\110\Tools\Performance Dashboard) and open the performance_dashboard_main.rdl file. Explore the health of your server by clicking on the various charts and hyperlinks in the report. Here is what the dashboard looks like at the main screen.

SQL dashboard

For a tutorial on the dashboard, its features and more detail into the installation steps follow this link: http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20SQL%20Performance%20Dashboard

In my next post, we’ll explore changing some default installation setting on SQL Server to increase your Server’s performance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s