Milena Petrovic

Using custom reports to improve performance reporting in SQL Server 2014 – the basics

September 8, 2014 by
SQL Server Management Studio provides three built-in performance monitoring features. These are Activity Monitor, Data collection sets, and standards reports.

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:

    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.

  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.

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey

Useful resources

Custom Reports in Management Studio
Add a Custom Report to Management Studio
SQL Server Data Tools (SSDT)

Milena Petrovic

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
Milena Petrovic
3,271 Views