Activity Monitor provides values for a pre-defined set of SQL Server performance metrics. These sets cannot be customized, nor additional ones added. Data collection also has pre-defined performance metrics sets, although it enables creating new ones through code. Both features provide only basic performance information, insufficient for any deeper analysis or troubleshooting.

Standards SQL Server Management Studio reports provide basic information about memory consumption, processor time used by queries, SQL Server configuration, settings, etc., commonly needed for performance monitoring and troubleshooting. These are available for different database object types and at different levels. The most numerous are standard reports at SQL Server instance level. Although they provide a wide range of performance related information, the standards reports don’t provide enough details for analysis, issue pinpointing, and troubleshooting.

To help SQL Server DBAs obtain more details about performance metrics, Microsoft has created a set of free performance dashboard reports . These are actually custom reports created to provide frequently required deeper performance monitoring information, not available in the standard reports. As such, they can be additionally modified and tweaked.

If these still don’t provide enough information about performance metrics, you can create a custom report yourself.

What is a custom report?

A custom report is an rdl file created using Report Definition Language (RDL).

It presents the data obtained by T-SQL queries and stored procedures from SQL Server databases in a format you specify. You can use any of the built-in report layouts and tweak it later.

To understand an rdl file, start with opening it in a text editor. It’s an XML file where the first part contains the report layout information (in the ReportItems or ReportParameters tag) and the second contains code used to retrieve the data shown in the report (in the DataSets or DataSources tag).

Custom reports are available in SQL Server 2005 Service Pack 2 and later.

Prerequisites

To create a custom report, you need SQL Server Data Tools (SSDT).

SQL Server Data Tools can be installed using its standalone installer or the SQL Server installer. It is available in all SQL Server 2012 and later editions, except Express and Express with Tools, and is a replacement for Business Intelligence Development Studio (BIDS).

SQL Server Data Tools is contained in the Visual Studio Shell.

How to create a custom report

1. Start SQL Server Data Tools
2. In the menu, select File, then New | Project
3. In the Installed Templates list, select Business Intelligence, Report Server Project Wizard

4. Modify the Name, Location, Solution, and Solution name if needed and click OK

   

5. In the Report Wizard, select New data source, enter a name that describes the data source, and select Microsoft SQL Server as Type

   

6. If you want to enable other reports in the project used this data source, check the Make this a shared data source option
7. Click Edit

8. Select the SQL Server instance name from the drop-down list, authentication type, and database name

   

9. Test the connection to check whether the settings are correct
10. Click OK

11. In the Design the Query step, click Query Builder

    

    Query Builder enables you to create code that retrieves the data shown in the report either by using visual presentation of tables, views, functions, and synonyms (similar to View designer in SQL Server Management Studio), or by writing code manually.

    

    If you use the visual presentation, add the objects you will query using Add Table in the menu and select the check boxes of the columns you want to see in the report.

    Keep in mind that some of the statements, including the CROSS APPLY that we will use in the following example, cannot be presented graphically. The objects used in such queries will not be shown in the Diagram pane, and an error will be shown. Nevertheless, the T-SQL statement will be executed correctly. Also, note that system objects (including dynamic management views) cannot be added via the Add Table icon in the menu.

    There is a workaround to get a system object in the Diagram pane and combine it with other objects. Paste into the SQL pane and execute a statement that contains the system object you want to show, such as:

    1 2 3

    SELECT * FROM Sys.Dm_Exec_Query_Stats

    And use Add Table to add additional user tables.

    Query Builder supports two command types: text and stored procedure. To toggle between these two types, click Edit as Text in the menu.

    The text command type works with T-SQL statements, the same as in SQL Server Management Studio Query tabs.

    When you want to execute a stored procedure, use the stored procedure command type. You don’t have to write anything other than the stored procedure name (not even EXEC or EXECUTE). You can add the stored procedure parameters once you click Run in the menu.

    

    In this example, we’ll create a report that returns 20 most expensive queries executed after 5 PM. You can paste the following script directly into the Query Builder SQL pane.

    1 2 3 4 5 6 7 8 9

    SELECT TOP 20 QText.Text AS QueryText, QStat.Last_execution_Time, QStat.Total_Worker_Time AS CPU_Time FROM SYS.Dm_Exec_Query_Stats QStat CROSS APPLY SYS.Dm_Exec_Sql_Text(QStat.SQL_Handle) AS QText WHERE CAST(QStat.Last_execution_Time AS TIME) > '17:00:00';

12. To execute code and check whether it returns expected results, click Run in the Query Builder menu. You’ll receive a warning that the query cannot be presented graphically as the CROSS APPLY statement is not supported
13. Click Continue. The results will be shown in the Result pane
14. Click OK and then Next
15. In the following three steps, define the report layout and design. The first one prompts you to select a report type: tabular or matrix. Although a matrix report is not adequate for all result sets returned by the query, the option is always enabled
16. The next step prompts you to specify the fields that will be used to group the data in the table or matrix. In this example, we’ll leave all fields blank
17. In the next step, select one of several predefined table styles. The difference is only in the colors and fonts used. You can skip this step and make font and color modifications later

18. The Choose the Deployment Location step is related only to the report server in SQL Server Reporting Services (SSRS). You will be able to use the custom report through SQL Server Management Studio even if you don’t have SQL Server Reporting Services installed, configured, and running. In that case, you can just leave the values offered by default and click Next

    

19. Specify the report name and click Finish

If you checked Preview report, the report will be shown in SQL Server Data Tools and you can tweak its layout in the Design tab if needed.

In this article, we presented SQL Server Management Studio custom reports, explained when they should be used, and gave a stop-by-step guide to create them using SQL Server Data Tools. In the next part of this article, we will give show how to run and modify custom reports.

• Author
• Recent Posts

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic

Latest posts by Milena Petrovic (see all)

• Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports - September 12, 2014
• Using custom reports to improve performance reporting in SQL Server 2014 – the basics - September 8, 2014
• Performance Dashboard Reports in SQL Server 2014 - July 29, 2014