Monitoring and Troubleshooting SQL Server the Easy Way
SQL Server is such a big, multi-faceted product that it is difficult to become familiar with all its features. One very useful but little-used feature is the set of standard reports accessible from the Management Studio object browser.
These reports can display important performance information with a couple of clicks of your mouse.
This quick reporting feature originated with SQL Server 2005 and is in all subsequent versions. There are minor differences in implementation between the different versions. This article will use examples from SQL Server 2008r2 but you should have no problem following along if you have another version.
Most of the standard reports are accessed by right-clicking on the SQL Server instance name or a database name in the object browser. However virtually every folder in the object browser has a Reports menu you can right-click.
Many of these other menus are empty, but some contain standard reports. For example, if you right-click the Security\Logins folder you will find three standard reports: Login Statistics, Login Failures and Resource Locking Statistics by Logins.
All report folders allow you to create and add custom reports to the menu. We will discuss custom reports later in this article.
Instance Level Reports
The most useful standard reports occur at the instance level or the database level. If you right-click on the instance name and select Reports you will see menus for standard reports and custom reports. You will also see a list of the most recently used reports. Unless someone has created and loaded custom reports on this server, the custom reports menu will be empty.
The standard reports menu contains seventeen reports that you can view. These reports can help identify blocking code, show you what queries are using the most io and cpu, whether someone has dropped or changed a stored procedure and much more.
Server Standard Reports
Each database has over 20 standard reports in its folder. these reports can give you the details of disk usage, index fragmentation, index usage, schema change history and much more.
I recommend that you explore the standard reports to familiarize yourself with all the useful information that is only a mouse click away. (a A few of these reports use enough resources that you should consider running them during low-usage times if your server is heavily loaded.)
You might notice that some of these reports bear a strong resemblance to the reports available in many of the third-party monitoring tools. That might be because those tools can leverage these reports from their application code.
Creating Custom Reports
You can create reports for the Custom Reports Menu in the same way that you create reports for SQL Server Reporting Service. Available methods vary with the version of SQL Server. You might need to use Business Intelligence Development Studio (BIDS), Visual Studio or SQL Server Data Tools (new for 2012). Any reporting tool will work as long as it produces an .RDL report file.
Place your RDL files in an accessible directory. To load the reports, open the custom reports folder on the object browser node where you want the report to appear. Browse to the directory where you have placed the RDL file and select it.
If you plan to create custom reports you might want to look at this Microsoft article. It contains information about securing the reports and lists a number of rules that you must observe when creating these reports.
Adding History with the Management Data Warehouse (MDW)
We can’t say much about MDW in the space we have left in this article but we wanted to mention it as a way to provide a history of the activity, performance and configuration on SQL Server.
MDW uses much the same technology as the standard reports to gather performance data. It allows you to store this data from one SQL Server or many SQL Servers in a single database. Having this repository of historical data for all your SQL Servers can be very useful for troubleshooting, planning, auditing, etc.
MDW makes it quite easy to create and manage all this data. Someday soon we will do an article focused on MDW.