Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Automatically Send Performance Dashboard Reports during a database event using Reporting Services


By:   |   Last Updated: 2016-03-15   |   Comments (5)   |   Related Tips: More > Monitoring

Problem

You are using SQL Server Management Studio Performance Dashboard Reports to monitor and troubleshoot SQL Server and you want to receive an email from the report when there is blocking on the database, as an example. In this tip I will show you how to import Performance Dashboard reports into Reporting Services and to configure a data driven subscription to send an email with the report when the conditions apply.

Solution

In my previous tip SQL Server Management Studio Performance Dashboard Reports Overview I showed you the capabilities of this set of reports to troubleshoot performance related issues and to monitor your SQL Server instance. Now I will show you how to import this set of reports into Reporting Services, which will allow us to subscribe to the reports and receive them via e-mail. If we take advantage of Data Driven Subscriptions we can program the report to be delivered only when there is something interesting to report.

First we need to create a new Report Server project in the SQL Server Data Tools and import the reports that usually are located in the following path C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard. To do so, on the Solution Explorer window right click on the Reports folder and from the drop down menu select Add -> Existing Item.

Add the reports to the project.

Since all of the reports use a shared data source named DataSource1 we must create a shared data source with that name pointing to the msdb database of the SQL Server instance we are planning to monitor.

Create a shared data source named DataSource1.

Another item to take care of is that all of the reports receive a parameter named version_string from the main report to validate the SQL Server version. This is necessary to run all of the reports separately with a data driven subscription. The way I do so is to define a default value for this parameter. I choose to configure the reports to get the parameter value from the mayor_version field of the CHECK_DEPENDENCIES data set like the following image shows.

Configure the parameter to use mayor_version field of the CHECK_DEPENDENCIES data set.

Once you deploy the project to the Report Server you should verify that the credentials of the data source are stored in the server, otherwise it won't be possible to create a subscription.

Creating the Data Driven Subscription

To create the Data Driven Subscription for the blocking report as well as any other report you want to subscribe, you should right click on the report and on the drop down menu select the Manage option. You may be tempted to select the Subscribe option, but it will create a standard subscription and that is not what we want.

Right click on the report and select Manage from the contextual menu.

When the management page opens, go to the Subscription tab and press the New Data-Driven Subscription button to launch the Data-Driven Subscription wizard.

Click New Data-Driven Subscription button to launch the Data-Driven Subscription wizard.

The first step of the wizard asks us for a description to easily identify the subscription and the delivery method i.e. E-Mail. Also this step will ask you for a data source that will be the source of information to trigger the report. Since we deployed all of the reports with a shared data source, it is wise to select that we will use that shared data source.

Screen capture of step 1 of the New Data-Driven Subscription wizard.

The second step asks us for that shared data source.

Screen capture of step 2 of the New Data-Driven Subscription wizard.

Step three is the most important to create a data driven subscription. Here is where we create a query that will trigger the report delivery. The wizard suggests a set of extensions that we can use to dynamically configure the report delivery that we can use in our query. For the blocking report we can use the following query to the sys.dm_exec_requests dynamic management view and look for values greater than zero in the column blocking_session_id. So, when there are rows that match this condition, the query returns a column named [TO] with the E-Mail address we are sending the report, otherwise the query does not return any value.

USE master
GO

SELECT  TOP 1 '[email protected] ' AS [TO]
FROM    sys.dm_exec_requests
WHERE   blocking_session_id > 0

GO

You can press the Validate button to check the query for errors and establish a command timeout.

Screen capture of step 3 of the New Data-Driven Subscription wizard.

The next step of the wizard is to map the delivery extension settings with the values returned by our query and to assign static values to other settings that we consider constants. In this example I mapped the "To" delivery extension with the value from the query and set the Render Format with the static value "Excel" to receive the report as an attachment.

Screen capture of step 4 of the New Data-Driven Subscription wizard.


Screen capture of step 4 of the New Data-Driven Subscription wizard.

Step five asks us for a value for the version_string parameter. Due to the fact that when we deployed the report we configured the report to get the parameter value from the mayor_version field of the CHECK_DEPENDENCIES data set, we can set it to use the default value.

Screen capture of step 5 of the New Data-Driven Subscription wizard.

The sixth step lets us select when the subscription is going to be processed. I decided to use a new schedule, but you can use a shared schedule if you want. If you subscribe to other reports then maybe you will benefit from using a shared schedule.

Screen capture of step 6 of the New Data-Driven Subscription wizard.

Finally the last step is to create the schedule. I configured the report to be executed every one minute, but you can change the time period as you need.

Screen capture of last step of the New Data-Driven Subscription wizard.
Next Steps


Last Updated: 2016-03-15


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 13, 2018 - 9:38:57 PM - Daniel Farina Back To Top

 

Hi David

Please apologize my late response. No, you only need one central Reporting Services instance.

 


Tuesday, March 22, 2016 - 2:30:19 PM - David Wood Back To Top

I like this idea but wouln't this methodology require Reporting Service to be installed on every SQL instance where you want this functionality?  I use a centralized Reporting Server on my central management server which is used to run consolidated reports.    I have over 160 SQL instances many of which are FCI's and cannot add the additional features ie Reporting Services.

 


Tuesday, March 15, 2016 - 5:52:53 PM - Daniel Farina Back To Top

Hi Bryan!

Peter is right! Also you can simulate the advantages of Data Driven Subscriptions like settiing dinamically the recipient. I will write a tip about this!

 

Thank you very much for reading!


Tuesday, March 15, 2016 - 1:20:30 PM - Peter Back To Top

Bryan, you could create a scheduled report (no data-driven part), then tweak the job schedule to run on a similar timed basis. You could add a step in the job to check for some existing condition and only run the report step if the condition is true.


Tuesday, March 15, 2016 - 5:22:38 AM - PerfDashboard send by mail on scheduled basis Back To Top

 Hello,

I don't have the data driven subscription option because i am using the standard version of  reporting services.

How can i achieve the same result as you without using this option?

 

Thanks,

Bryan

 

 


Learn more about SQL Server tools