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:
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:
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.