Tuesday, April 7, 2015

My Favorite FREE Reports in SSMS

Microsoft has given us DBAs/Developers/etc. a wealth of free reports integrated into SQL Server Management Studio (SSMS) in order to see what is happening in our environment. These reports are really nice because you do not need to know the DMV/DMF in order to get the results.

The first report I use in on a database - Disk Usage. In order to get to this report, I need to Right-Click on the database and go to Reports menu.

image

 

Disk Usage

From there, drill into Standard Reports –> Disk Usage

image

This reports gives me a visual of the data and log usage as well as what is going with the data files. From here, I can see if the log file is getting large and/or how full. The Data/Log File Autogrow/Autoshrink indicates that the log file grew on 4/7 and added 200MB (it is autogrow sized correct). I can also see the percentages of data, index, etc. usage on the File Groups.

 

Disk Usage by Top Tables (or Disk Usage by Tables)

The next report is Disk Usage by Top Tables or by Tables. The Top tables will order the report Reserved Data Size. The by Tables report orders the list alphabetically by schema name and then table name.

 

image

 

image

 

Schema Changes

Being able to see changes on the database without too much effort is reserved for this report. It feeds from the default trace, so some things fall off with recycling of the trace files.

image

 

Index Usage Statistics

This report gives usage and operational information about indexes.  The usage statistics are related to Seek, Scans and Updates while the operational is for Insert, Updates and Deletes.

image

These are just a few of the integrated reports in SSMS. I am sure you will find your favorite or even find some at the instance level you might enjoy.

image        image

 

Thomas