A good starting point for performance tuning on your SQL Server is by establishing a baseline for your server’s current performance. To help, Microsoft provides a SSMS dashboard to monitor your server’s performance. The dashboard provides valuable information on:
- Server Waits
- Expensive Queries
- 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 Server110ToolsPerformance 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 Server110ToolsPerformance 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:
For a tutorial on the dashboard, its features and more detail into the installation step follow this link: http://sqlserverfinebuild.codeplex.com/wikipage?title=Install%20SQL%20Performance%20Dashboard
In my next post, we’ll explore changing some default installation settings on the SQL Server to increase your server’s performance.
