Click here to monitor SSC
  • Av rating:
  • Total votes: 29
  • Total comments: 28
Greg Larsen

Custom reports in Management Studio, using the Performance Dashboard

18 July 2007

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

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:

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.


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.

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

<DataSet Name="DataSet1">

<Query>

 <rd:UseGenericDesigner>true</rd:UseGenericDesigner>

 <CommandText>SELECT b.row_count, a.name table_name
FROM sys.objects a join sys.dm_db_partition_stats b
on a.object_id = b.object_id WHERE a.type='u'
AND b.index_id < 2
</CommandText>

 <DataSourceName>DataSource1</DataSourceName>

 </Query>

<Fields>

<Field Name="row_count">

 <rd:TypeName>System.Int64</rd:TypeName>

 <DataField>row_count</DataField>

 </Field>

<Field Name="table_name">

 <rd:TypeName>System.String</rd:TypeName>

 <DataField>table_name</DataField>

 </Field>

 </Fields>

 </DataSet>

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.

Greg Larsen

Author profile:

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held five different DBA jobs and managed a number of different database management systems. Currently works as a DBA for Department of Health in Washington State managing SQL Server databases, and also does part-time consulting. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He also is a SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at gregalarsen@msn.com.

Search for other articles by Greg Larsen

Rate this article:   Avg rating: from a total of 29 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Working on SQLDashboardApp
Posted by: mnguyen (view profile)
Posted on: Friday, July 20, 2007 at 11:06 AM
Message: 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.

Subject: Source Code
Posted by: Greg Larsen (view profile)
Posted on: Friday, July 20, 2007 at 5:38 PM
Message: 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.

Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: Paul (view profile)
Posted on: Wednesday, July 25, 2007 at 3:36 AM
Message: 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


Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: Paul (view profile)
Posted on: Wednesday, July 25, 2007 at 8:55 AM
Message: 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


Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: CB (not signed in)
Posted on: Wednesday, July 25, 2007 at 9:52 AM
Message: I have the same issue.

Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: Greg Larsen (view profile)
Posted on: Thursday, July 26, 2007 at 11:49 PM
Message: 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.

Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: Paul (view profile)
Posted on: Friday, July 27, 2007 at 7:04 AM
Message: Hi Greg

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

Thanks
Paul

Subject: Error on rdl file
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 29, 2007 at 7:58 PM
Message: Yup me too. Same issue with the rdl file. Any ideas on fixes?

Subject: error on rdl file
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 29, 2007 at 8:00 PM
Message: 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,

Subject: error on rdl file
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 29, 2007 at 8:18 PM
Message: 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,

Subject: error on rdl file
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 29, 2007 at 8:18 PM
Message: 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,

Subject: error on rdl file
Posted by: Anonymous (not signed in)
Posted on: Sunday, July 29, 2007 at 8:19 PM
Message: 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,

Subject: error on rdl file
Posted by: Greg Larsen (view profile)
Posted on: Monday, July 30, 2007 at 10:14 AM
Message: 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


Subject: 'rd' is an undeclared namespace
Posted by: Anonymous (not signed in)
Posted on: Friday, August 03, 2007 at 11:43 PM
Message: 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.

Subject: An error occurred while parsing Entityname. Line 81, position 26
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 04, 2007 at 12:11 AM
Message: 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.

Subject: An error occurred while parsing Entityname. Line 81, position 26, Part 2
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 04, 2007 at 10:15 PM
Message: 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.

Subject: Some abusive language
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 04, 2007 at 10:42 PM
Message: 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.

Subject: 'rd' is an undeclared namespace. Line 17, position 3.
Posted by: Paul (view profile)
Posted on: Monday, August 06, 2007 at 9:30 AM
Message: 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.

Subject: 'object_schema_name' is not a recognized built-in function name.
Posted by: Mega (not signed in)
Posted on: Tuesday, August 07, 2007 at 4:30 AM
Message: 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

Subject: RE: RD Not Defined
Posted by: Brian K. McDonald (not signed in)
Posted on: Wednesday, August 08, 2007 at 10:01 AM
Message: 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

Subject: Solution to 'object_schema_name' is not a recognized built-in function name.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 08, 2007 at 11:30 AM
Message: You just need to download SP2 and install. After that you will not get this error

Subject: Solution to 'object_schema_name' is not a recognized built-in function name.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 08, 2007 at 1:03 PM
Message: You just need to download SP2 and install. After that you will not get this error

Subject: Solution to 'object_schema_name' is not a recognized built-in function name.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 08, 2007 at 1:04 PM
Message: You just need to download SP2 and install. After that you will not get this error

Subject: Performance Dashboard reports
Posted by: Siva (not signed in)
Posted on: Thursday, August 30, 2007 at 10:07 PM
Message: I want to open Performance Dashboard reports through command line

SQLWB.EXE -s mysserver ,"C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\performance_dashboard_main.rdl"

does't work.

Can you help me


Subject: Works like a charm.
Posted by: LenN (not signed in)
Posted on: Tuesday, September 11, 2007 at 11:10 AM
Message: Thanks for contributing! Great to see people sharing their wealth of knowledge!

Subject: 'object_schema_name' is not a recognized built-in function name.
Posted by: Anjan (not signed in)
Posted on: Tuesday, June 24, 2008 at 12:23 PM
Message: 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

Subject: SQL 2008 needs minor modification to work!
Posted by: mrhassell (view profile)
Posted on: Monday, December 06, 2010 at 1:37 AM
Message: 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!

Subject: May be what I'm looking for...
Posted by: John W. (view profile)
Posted on: Thursday, August 04, 2011 at 9:11 AM
Message: 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!

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.