This project has moved and is read-only. For the latest updates, please go here.

Project description

New v6 release

There is a lot of scripts or tools to get information of SQL Server instance and databases but I didn’t find one that give all information I need at the same place with exporting or printing capabilities. Starting with SQL Server 2005, there is some nice standard reports available in SQL Server Management Studio, but these reports couldn’t be enough to get a good overview for diagnostic purposes : you have to run also a lots of custom scripts to get more advanced information.

This set of SSMS custom reports will help to have such an quick overview of instance and database (on premise) and to answer the main questions when you discover a new server.

Index report

You'll get various information, easy available :

  • Server information : version, edition, build, os platform, hardware, services, configuration options, memory configuration, security, etc…
  • Storage : disk allocated, file sizes and allocation for all databases
  • Last errors in SQL Server errorlog
  • SQL Agent jobs information (since v6)
  • Performance information : wait states, cpu, memory usage and main performance counters, io latency, tempdb utilization, etc…
  • Databases informations : database options, storage allocation, transaction logs and backups information, mixed collations, objects information, etc...
  • Tables and index informations : primary keys (clustered, nonclustered, no primary key), unique constraints, existing indexes, tables without index, size of index keys, etc…

TSQL scripts of these reports are coming from various sources : my own scripts and some other inspired by internet resources like scripts of Glenn Berry or Paul Randall.

System requirements and limitations

This custom SSMS report is intended to use only with Management Studio 2012 or later, but can get the information from instances of versions from 2005 to 2017 (some informations will be not available with oldest versions).
Sysadmin rights are required to run the reports : the reports temporarly modify instance options (sp_configure), and create a temporary udf.

Reports description

List of reports : 

  • Main Server Dashboard.rdl : main information and starting point, other reports are linked to this one  (but they can be used also separately).
  • Server Report.rdl : instance information
  • All Databases Report.rdl : databases overview
  • Server Performance.rdl : instance performance informations
  • Database Report.rdl : general database information. Could also to be run from a database in object explorer.
  • Tables Report : tables informations. Could also to be run from a database in object explorer.
  • Index Report : index informations. Could also to be run from a database or a single table object in object explorer.
  • Statistics Report (since v2) : informations about columns and index statistics. Could also to be run from a database, a single table or a single statistic.
  • Jobs report (since v6) : a dashboard for monitoring jobs execution and performance
  • Job detailed report (since v6) : detailed information, could also be run from a single job in SSMS

There is also a SQLAzure SSMS report, available with another download.

Reports diagram

Installation and usage

These reports are provided "as is". Even the risk is low, use with your production servers is at your own risk.

Copy the rdl files to a folder that is accessible to the SQL Server Management Studio client. Right-click on the SQL instance in object explorer, select "custom reports" and browse to the location of the rdl file.

 

Last edited May 26 at 11:14 AM by arianp, version 25