Reports of database activity information using SQL Server Management Studio

  • Article

In one of my previous blogs I presented some of the tools you can use to start troubleshooting your performance issue or monitor the activity on your SQL Server .

If the tools presented in that blog are a little too complicated for you, or you do not have the time to read and analyze all the information presented there, you have another possibility to access some reports using SQL Server Management Studio.

Starting with SQL Server 2005 you have the possibility to access reports about: disk usage , blocking transactions or even index usage or statistics on a specific database.

To access these reports you first have to be sure you have installed SQL Server Management Studio.

Then all you have to do is to right click on the database you want to analyze – Reports and there you have the possibility to use an existing Standard report or to import a new Custom Report:


 
1. Standard Reports:

In the section of Standard Report you have available the following reports:


Note: It is not recommended to access these reports on a production environment, when it is a high activity on the server because it can cause performance issues.

So you have the following options:

  • Disk Usage
  • Disk Usage by Top Tables
  • Disk Usage by Table
  • Disk Usage by Partition
  • Backup and Restore Events
  • All Transactions
  • All Blocking Transactions
  • Top Transactions by Age
  • Top Transactions by Blocked Transactions Count
  • Top Transactions by Locks Count
  • Resource Locking Statistics by Objects
  • Object Execution Statistics
  • Database Consistency History
  • Index Usage Statistics
  • Index Physical Statistics
  • Schema Changes History
  • User Statistics

For each one of the options presented above you have to possibility to access a report with useful information and very clear graphics.

For example the Disk Usage for the database Adventure Works:
 

In the picture above you can analyze and see the space usage for Data Files and Transaction Log files. Very useful if you want to plan a truncation or shrink operation on these files.

We can take another example like Backup and Restore events where you can see what is the average time taken for backup operations for that database, when where take the last backups and also if there were any errors during the backups:

Another very important report that could help you on performance issue is the blocking transactions report that can show you the description of transactions which are blocking other transactions.

The reports that can help you monitor the server and the maintenance done on the server, are:  

  • Top Transactions by Age - can show you a report with the oldest transactions and also the currently running transactions on this database;
  • Resource Locking Statistics by Object - This report enumerates all objects within the Database on which locks have been acquired and provides details on the kind of locks held and who is holding them.
  • Object Execution Statistics - This report provides detailed historical execution data for all currently cached plans for objects within the Database. This execution data is aggregated over the time during which the plan has been in the cache.
  • Index Usage Statistics - This report provides details on usage of individual Indexes within the Database as well as data on the cost of maintaining them.
  • Index Physical Statistics - This report provides overview of the utilization of disk space within the Database.
  • User Statistics - This report provides details on the activity of all currently connected users within the Database.

All these reports can help you in your daily maintenance job or analyzing and tuning your database.

2. Custom Reports: Custom Reports in Management Studio

Custom reports are stored as report definition (.rdl) files and are created by using Report Definition Language (RDL).

For more information about how to implement the customer reports, how to run it or even the limitations you have for this kind of reports you can access the Custom Reports in Management Studio

 

I hope this blog helped you in better managing and maintaining your database if you are a beginner.