Custom reports in Management Studio, using the Performance Dashboard

If you are using SSMS and SQL Server 2005 sp2 You've probably tried out the database reports that are available in the Performance Dashboard, and decided how useful they are, but did you know that you can create your own dashboard reports?

You’ve probably tried out the database reports that are available in the Performance Dashboard, and decided how useful they are, but did you know that you can create your own dashboard reports?

SQL Server Management Studio (SSMS) was modified in SQL Server 2005 SP2 to display rendered Reporting Services reports (RDL files) without requiring that Reporting Services be installed. You can now use your Reporting Services reports, or build new reports, to extend the reporting capabilities within SSMS. This modification was done primarily to allow performance reporting on the server to be accessible from within SSMS, and Microsoft supplies a special set of Report Definition Language (RDL) files known as the “Performance Dashboard”. This set of RDL files can be used to monitor and pin point performance problems within your server, and can be used and modified in Reporting Services as well. You can even create your own reports and access them by right-clicking on the object you wish to investigate in the object browser pane

The Performance Dashboard

The Dashboard Menu
413-larsen3.jpg

The Performance Dashboard is a series of Reporting Services RDL files that allow you to obtain SQL Server 2005 performance information. The dashboard allows you to start looking at performance information starting from the 100,000 foot level and then drilling down until you get to ground level. At the lowest level the dash board will identify the specific queries that are causing performance issues within your SQL Server instance, such as those consuming the most CPU, taking the longest time, most I/O’s, and most CLR Time. It will report on missing indexes, blocking, latch contention and other performance issues;

The Performance Dashboard is available as a downloadable msi file from Microsoft. You can obtain the msi download file here:

Installing the Performance Dashboard

The first step to install the Performance Dashboard is to install the msi file you downloaded from the link above. The installation process is similar to most msi installs. As you are going through the msi installation you may want to pause when you get to the “Feature Selection” panel of the installation. Here you will be given the opportunity to identify the “Installation Path” for the RDL files. The Installation path defaults to “C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\”. You can install the files associated with the dashboard anywhere, but you might want to consider a couple of things before you select a location.

If you have a number of SQL Server machines, and a number of people that will want to use the Performance Dashboard then you might as well identify a network Installation path that is accessible by everyone that will be running the dashboard dashboard RDL files which might someday include your own custom reports.

Once the msi installation completes, you will need to run the “Setup.sql” script that can be found in the installation directory you identified in step 1. The “Setup.sql” file is just a TSQL script that will installs a number of objects (functions and strored procedures) in the msdb database. The objects installed will be used to gather database performance information to help populate the different Performance Dashboard RDL reports. You will need to run the “Setup.sql” script on every instance that you want Performance Dashboard information from.

The SQL Server instance being monitored must be running SP2 or later. After completing the installation, you must:

Run the Setup.sql file on each instance of SQL Server 2005 that you wish to monitor with the SQL Server 2005 Performance Dashboard Reports. Open the performance_dashboard_main.rdl file with the Custom Reports functionality new to Management Studio in Service Pack 2.

Quick Preview of Using the Performance Dashboard

To view the Performance Dashboard you must first bring up SSMS, and connect to one of the SQL Server 2005 instances where you installed the msdb components of the dashboard. Then right click on the server name in object explore, navigate to the “Reports” item, then click on the “Custom Reports” item. This will bring up an “Open File” panel. Use this panel to “Open” the “performance_dashboad_main.rdl” file from the location where you installed the msi file. When you do this the main menu for the “Performance Dashboard” should be displayed, and should look similar to this:

413-larsen1.jpg

This main menu screen is displaying overall performance statistics for the developer edition of SQL Server 2005 running on my SERVER1 instance. On this report you can see overall “System CPU Utilization”, whether or not there are any requests are waiting for resources, as well as some current, historical and miscellaneous activity or information. There are a number of hyperlinks on this page that allows you to drill down and get more specific information regarding your server performance. Below are a couple of drill down reports, one showing current sessions and the other that shows the most expensive requests.

413-larsen2.jpg

This report will show you the top 20 cached query plans that have accumulated the most CPU. From this report you can drill down even more to review query plans for an individual cached command. To see all 20 cached query plans you will need to use the scroll bar.

The Performance Dashboard is a useful tool. I would suggest you install it and browse around all the hyperlinks to see all the valuable performance data this tool brings to SSMS. This tool demonstrates lots of different reporting capabilities that can be incorporated into SSMS by building custom Report Services reports.

So now let me show you how you can build your own custom reports.

Incorporating Your Own Custom Reports into SSMS

The Performance Dashboard reports are just a series of Reporting Services RDL files. These RDL files query the instance you are on when you open dashboard and generate useful reports from the query result sets. You can use similar reporting capabilities to build your own custom reports.

To build your own custom report is as simple as creating a Reporting Services RDL file and then referencing the RDL from SSMS. When SSMS renders your RDL file, it also provides your reports with SSMS node information as parameters. The following object node parameters, which are self-explanatory, are available:

  • ObjectName,
  • ObjectTypeName,
  • Filtered,
  • ServerName,
  • FontName
  • DatabaseName.

By using these object node parameters you can customize your report based on the object node you are on when you right click to display you custom report. We’ll look at a couple of example RDL files to demonstrate how this works.

For my first example I am going to use the “DisplayObjectNodeInfo.rdl” file. This RDL file merely displays the SSMS object node parameters based on where you are at in the SSMS object explorer tree when you display this custom report. To run this report, copy the above file and store it somewhere that you have access to from a machine in which has the SSMS tool installed. Then within the object explorer of SSMS expand a database node, then the table node, and then right click on a particular table. On the menu place your mouse on the “Report” item and the click on the “Custom Report” menu item. From the “Open File” panel open the DisplayObjectNodeInfo.rdl from the location where you saved it. When you do this you should see the report being rendered, and upon completion of the rendering you should see an “AllReportParameters” report. This report shows you all the values of the object node parameters for the specific object you where on when you opened this customer report, like ObjectName, ObjectTypeName, Filtered, etc.

If you review the RDL file for the “AllReportParameters” report you can find the parameter specifications for the object node parameters. If you want to include one of these object node parameters in your custom report all you have to do is add that parameter to your report using the appropriate parameter name and data type as identified in the following table:

Parameter Name Data Type
ObjectName String
ObjectType String
Filtered Boolean
ServerName String
FontName String
DatabaseName String

To show you how you might use the object node parameters to make your report parameter driven I will show you a report that displays the record counts for every table in a databases.

413-larsen4.jpg

To run this demo you need to save the “RecordCount.rdl” file to some location where you can get at in from SSMS. After saving the RDL file, right click on a database in SSMS, go to “Reports” item, then click on the “Custom Reports” item. In the “Open File” dialog box browse to the location where you saved the above RDL file and open it. When you do that you will see a report that will show a record count for all the tables in your database for the specific database node you were on when you opened my custom “Record Counts” report. Now navigate to a different database node in SSMS, and bring up the report again using the same steps as above. This time the report should render a different report containing the record counts for the new node you selected.

Here is a fragment from the RDL file that shows the actual SQL Query that generates the data

 

There is a limitation you should know about when using the any one of the object node parameters. These parameters are only populated for the first report rendered. So if you try to include one of these parameters in a drill down report it will not be populated with a value when the report is rendered. To get around this you need to pass an object node parameters you need in your drill down report from the first report rendered when selecting a custom report. Review Books Online for other limitations.

While working with the custom reporting capability of SSMS I found a feature or, should I say, a quirk regarding the rendering process. When a report is rendered the information for your data source and database in your report is not used. But, instead, the node information from SSMS is used to determine what server and database your query should run against. This is great for those reports that you want to select data based on the database context. If you want your query to run against a specific database then you will need to use a three part naming convention (<databases>.<owner>.<object>) to fully qualify the objects you reference. To demonstrate this functionality save my RecordCount.rdl file to a location you can get at with SSMS. Bring up SSMS and right click on a database, then go to the “Reports” item, click on “Custom Report” and then browse and open up the “RecordCount.rdl” file. When this report is rendered you should notice that it displays records counts for the database you right clicked on. Now close the custom report and right click on another database then open up the “RecordCount.rdl” file again. This time you should see the new set of database record counts based on the second node you clicked on.

For more information on the SQL server 2005 Performance Dashboard Reports, please refer to the help file (PerfDash.chm) located in the installation directory.

Conclusion

With the installation of SP2 for SQL Server 2005 you now have the ability to easily incorporate your own Reporting Services reports into SSMS, without having to install Reporting Services. Being able to do this allows you to build your own reports to augment those provided with SQL Server 2005. By placing your own reports into SSMS you can now make SSMS your one stop shopping for all your reporting and database management needs. Next time you have a need to build a custom Reporting Services report related to database management, consider incorporating it into SSMS by using the custom report option.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Downloads

Tags: , , , ,

  • 44700 views

  • Rate
    [Total: 31    Average: 4/5]
  • mnguyen

    Working on SQLDashboardApp
    I am developing SQLDashboard App for my client using VB.NET 2005. I am wondering you e-mail me your source code of this topic.

    My client is still have sql server 2000. I appreciate a feed back.

  • Greg Larsen

    Source Code
    All the source code for those two custom reports I developed are available in this article by clicking on the RDL links.

    Keep in mind Custom Reports within SSMS only work against SQL Server 2005 databases.

  • Paul

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    Hi

    This article is just what i was looking for but when i try it out i get error.

    ‘rd’ is an undeclared namespace. Line 17, position 3.

    Thanks
    Paul

  • Paul

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    Hi

    This article is just what i was looking for but when i try it out i get error.

    ‘rd’ is an undeclared namespace. Line 17, position 3.

    Thanks
    Paul

  • CB

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    I have the same issue.

  • Greg Larsen

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    I’d like to say I know why you are getting that error.

    What version, edition of SQL Server 2005 are you running this on? Also what OS are you using. If you could also explain the steps you used to produce this error that might also help.

    I’m hoping I can build an environment that gets this error, so I can help troubleshoot this error.

    Sorry I’m not help here, but instead am justing asking question to narrow down the scope of when this occurs.

  • Paul

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    Hi Greg

    I am running SP2 on Windows XP. I also get the problem with SP2 on Windows Server 2003

    Thanks
    Paul

  • Anonymous

    Error on rdl file
    Yup me too. Same issue with the rdl file. Any ideas on fixes?

  • Anonymous

    error on rdl file
    I’m running SSMS on an XP sp2 box with the rdl sitting on a 2003 sp1 machine.

    SQL version is 9.0.3175 on both machines.

    Cheers,

  • Anonymous

    error on rdl file
    I’m running SSMS on an XP sp2 box with the rdl sitting on a 2003 sp1 machine.

    SQL version is 9.0.3175 on both machines.

    Cheers,

  • Anonymous

    error on rdl file
    I’m running SSMS on an XP sp2 box with the rdl sitting on a 2003 sp1 machine.

    SQL version is 9.0.3175 on both machines.

    Cheers,

  • Anonymous

    error on rdl file
    I’m running SSMS on an XP sp2 box with the rdl sitting on a 2003 sp1 machine.

    SQL version is 9.0.3175 on both machines.

    Cheers,

  • Greg Larsen

    error on rdl file
    For those of you that are having problems, here is what components and version that Management Studio reports when I right click on “Help” and then click on “About…”. Are yours the same or different.

    Management Studio 9.00.3042.00
    Microsoft Analysis Services Client Tools 2005.090.3042.00
    Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
    Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
    Microsoft Internet Explorer 6.0.2900.2180
    Microsoft .NET Framework 2.0.50727.832
    Operating System 5.1.2600

  • Anonymous

    ‘rd’ is an undeclared namespace
    I get the same “undeclared namespace” error as above, and I have all the latest versions installed. When I compare the DisplayObjectNodeInfo.rdl file (originating from 413-DisplayObjectNodeInfo.htm) with the existing SSMS .rdl files, then DisplayObjectNodeInfo simply seems to be incomplete. the existing ones start with “?xml version=”1.0 …” and “Report xmlns = “http://schemas …”. I am not experienced enough to make corrections, Leendert.

  • Anonymous

    An error occurred while parsing Entityname. Line 81, position 26
    Who said: “I am not experienced enough to make corrections” ? For the provided files I replaced the first line “<Report><Report>” by:
    “<?xml version=…” plus
    “<Report xmlns=…” from an existing rdl file.
    DisplayObjectNodeInfo: works like a charm.
    With the RecordCountFile.rdl file I get an error: “… parsing Entityname. Line 81 …”, which refers to the line with “Parameters!DatabaseName.Value”. Sorry, I am to tired, to attach this one. Thanks, Leendert.

  • Anonymous

    An error occurred while parsing Entityname. Line 81, position 26, Part 2
    We all love writers who bring us the latest developments, we love them even more when their code is correct, don’t we ? This is how I made corrections for the RecordCountFile.rdl example:
    1. Make modifications as in my mail dd. August 04, 2007.
    2. Modify “Database = ” & Parameters!DatabaseName.Value into “Database = ” + Parameters!DatabaseName.Value. Rationale: this is not VB.
    3. Idem, “Server = ” & Parameters… into “Server = ” + Parameters…
    4. Modify “b.index_id < 2” into “b.index_id in (0, 1, 2)”. Argument: I assume the original “< 2” conflicts with the XML/RDL syntax. Even better: put it in a stored procedure.
    As always, thanks Mr. Larsen, Leendert.

  • Anonymous

    Some abusive language
    No, no abusive language. But I just read the simple-talk Joubert, Part 4 article, that also deals with Custom reports, and saw that there is code download at the TOP of the article. Back to Larsen (that’s: here), and YES: code at the TOP.
    Let’s try it again: both examples work without any modification. So forget all my above corrections, and just don’t follow the links in the article body, bot use the two TOP links.
    What did we learn:
    1. Happiness is in small locations, and
    2. Consistency has some drawbacks too widespread.
    As always, thanks Mr. Larsen, Leendert.

  • Paul

    ‘rd’ is an undeclared namespace. Line 17, position 3.
    Absolutely correct, if you download the files they work, if you cut and past the code then they don’t.

    Happy now, Thanks Greg really usefull report.

  • Mega

    ‘object_schema_name’ is not a recognized built-in function name.
    Hi,

    while i am executing the “Setup.sql” i am getting the following errors.

    Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27
    ‘object_schema_name’ is not a recognized built-in function name.
    Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42
    Incorrect syntax near the keyword ‘as’.
    Msg 15151, Level 16, State 1, Line 1
    Cannot find the object ‘usp_GetPageDetails’, because it does not exist or you do not have permission.

    give me a idea to resolve this problem

  • Brian K. McDonald

    RE: RD Not Defined
    All of those posting saying that RD is not defined and stating your version is: SQL version is 9.0.3175

    Try running this statement in your SQL 2005 instance:

    print @@version

    The first sentence in this article states that you should be running “SQL Server 2005 SP2”, which I believe the above statement should return 9.0.3042 for SQL SVR 05 SP2.

    I hope this helps,

    Brian K. McDonald

  • Anonymous

    Solution to ‘object_schema_name’ is not a recognized built-in function name.
    You just need to download SP2 and install. After that you will not get this error

  • Anonymous

    Solution to ‘object_schema_name’ is not a recognized built-in function name.
    You just need to download SP2 and install. After that you will not get this error

  • Anonymous

    Solution to ‘object_schema_name’ is not a recognized built-in function name.
    You just need to download SP2 and install. After that you will not get this error

  • Siva

    Performance Dashboard reports
    I want to open Performance Dashboard reports through command line

    SQLWB.EXE -s mysserver ,”C:Program FilesMicrosoft SQL Server90ToolsPerformanceDashboardperformance_dashboard_main.rdl”

    does’t work.

    Can you help me

  • LenN

    Works like a charm.
    Thanks for contributing! Great to see people sharing their wealth of knowledge!

  • Anjan

    ‘object_schema_name’ is not a recognized built-in function name.
    I am also getting the same error but mine SP 2 is already installed does it work in SQL express editon.

    Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    Pls tell wat to do

  • mrhassell

    SQL 2008 needs minor modification to work!
    All that you need do is open the “SETUP.SQL”script file in Microsoft SQL Management Studio and navigate in the script to – Line 217

    change the part ‘cpu_ticks_in_ms’

    –select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

    to read ‘ms_ticks’

    select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info

    This is due to a change in the sys.dm_os_sys_info DMV from SQL Server 2005 to 2008 (the cpu_ticks_in_ms column was removed in 2008.

    http://msdn.microsoft.com/en-us/library/ms175048.aspx

    Works!

  • John W.

    May be what I’m looking for…
    I’m running SQL Server 2008, SP2 and was able to fix the ms_ticks issue with no problems & it ran successfully.
    But when I try to run the report I get the following reply:

    “Error:

    The ‘version_string’ paramater is missing a value”

    Any ideas?

    Thanks!