SQL Server Health
The SQL Server Health reports are in the process of being replaced with the Database Health Application.
The free SQL Server Health report evaluates a number of areas of your database, and provides details on problems and what can be done to fix them.
The SQL Server Health report is a collection of SQL Scripts that I have used for many years to evaluate the status of a server, and to find problem areas, or to detect problem areas before they occur.
The SQL Server Health reports project is an SSRS project that you can download and run on your desktop through Visual Studio or with Business Intelligence Development Studio (BIDS). It was developed using SSRS 2008, has been tested on SSRS 2008 and 2008R2. If you are using 2008R2, the first time you open the project, you will be prompted to upgrade it to SSRS 2008R2 format, which works fine.
One question that comes up is “what do I need to install on my SQL Server?” The answer is NOTHING, you can run the reports from your desktop or laptop and point them to your SQL Server, or you can optionally install into your SSRS server, but its not required. There are no databases to be created, and no agent to run. All of the data shown in the reports already exists on your SQL Server, and you could get it directly with the queries used in the reports.
Some of the things that the Server Health report evaluates are the following
- Index Fragmentation
- Big One Time Use Queries
- Long Running Queries
- Duplicate Indexes
- CPU Utilization
- Database Size
- Backup Set
- Download the Database Health Reports
After downloading and attempting to use the report, you will need to configure the datasource to connect to your server. See the help page for more details.
SQL Server Health Reports History
11/19/2011 – Created Initial Version
11/26/2011 – Added Long Running Queries
11/27/2011 – Added Duplicate Indexes
12/03/2011 – Added CPU Utilization Report
12/07/2011 – Released the database size report
12/08/2011 – Released the Backup Set Report
12/10/2011 – Fixed a couple of bugs in the Backup Set report with the line sizes in the chart. Added the new Statistics report.
01/08/2012 – Fixed some layout issues in the Index Fragmentation Report, and cleaned up some general layout issues in the rest of the reports.
03/03/2012 – Major UI Fixup. Standard header on all pages, linking from any report to any other, ability to change the database from within the report, Updated help links, added Top IO report, fixed several bugs Updated.
03/11/2012 – Minor UI Fixup. Added included columns into the Duplicate Indexes
03/16/2012 – A little more clean up and final polishing for SQL Saturday Vancouver tomorrow.
03/18/2012 – Added 2 new reports Top Cache Queries and top CPU Queries.