Skip to content

SQL Server Management Studio (SSMS) Information Custom Reports

License

Notifications You must be signed in to change notification settings

datafly/SSMSInfoReports

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

38 Commits
 
 
 
 

Repository files navigation

SSMSInfoReports

SQL Server Management Studio (SSMS) Information Custom Reports (V9.2)

New Query Store report in this version !

This set of 18 custom reports is intended to provide a quick overview of an instance and its databases. It is easier to answer the main questions that arise when you discover and diagnose a new server or database.

Reports that come with SSMS installation are useful but they do not provide this kind of summary information. They also lack some advanced information that is accessible only through queries. One of the benefits of using reports rather than queries is their ability to be exported or printed.

Index report

You'll get various information, easy readable :

  • 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
  • Logins, roles, users, permissions (new in v8)
  • SQL Agent jobs information and history
  • Always On Availability Groups information
  • 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 well known SQL Server experts.

Installation and usage

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. The main report is "Main Server Dashboard".

The reports are intended to be used with SSMS 2012 and later (they don't work with earlier SSMS versions) but they can get information from instances from 2008 to 2019.

Sysadmin rights are required to run the reports: dmv, xp_cmdshell and extended stored procedures are used to access all information.

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

Reports description

  • 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
  • Server Security Report.rdl : instance security information
  • Always On Report : availability groups information, new in V7
  • All Databases Report.rdl : databases overview
  • Server Performance.rdl : instance performance informations
  • Resources per database.rdl : resources per database informations
  • Locks-Deadlocks.rdl : lock waits and deadlocks information
  • Database Report.rdl : general database information. Could also to be run from a database in object explorer.
  • Database Security Report.rdl : database security 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 : informations about columns and index statistics. Could also to be run from a database, a single table or a single statistic.
  • Missing Index Report : show missing index, for one or for all databases
  • Query Store Report (new) : show top 50 queries in the last 4 hours (for query store enabled databases)
  • Jobs report : a dashboard for monitoring jobs execution and performance
  • Jobs history planning report : shows history of last 24 hours, new in V7
  • Job detailed report : detailed information, could also be run from a single job in SSMS
Reports are linked to each other and the top report is "Main Server Dashboard.rdl" :

Reports diagram

You can also use some reports in the context : database report with right click on a database, index report with right click on a table, ...

Release history

  • 9.2 : new report added (Query Store), bug corrections and improvements
  • 9.0 : new report added (Locks-Deadlocks), huge number of improvements for all reports
  • 8.0 : new reports added (Server Security, Database Security), improvements for all reports
  • 7.0 : new reports added (AlwaysOn, Jobs planning history), improvements for all reports

About

SQL Server Management Studio (SSMS) Information Custom Reports

Resources

License

Stars

Watchers

Forks

Packages

No packages published