Setting an Alert on a Stored Procedure

Recently I came across an issue where I needed to set an alert that when a report is populated it needs to send out an alert. Unfortunately in SSRS you can only do subscriptions and I have not found a way to do those kinds of alerts on an SSRS report.

A workaround I found for this was to set an alert on the stored procedure the report is using. (Hoping you are using stored procedures for SSRS reports and not a large query in the report dataset) You can do this by applying the IF statement with the Execute dbmail proc. See below:

CREATE PROCEDURE dbo.DepartmentAlert
FROM HumanResources.EmployeeDepartmentHistory

--If there are more than or equal to 1 record pulled back it will fire the email.	
	FROM HumanResources.EmployeeDepartmentHistory
) >= 1
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerting'
								,@recipients = ''
								,@body = 'Department history available'
								,@subject = 'Department History Availability'
								,@body_format = 'HTML'

Next I created an SSIS package that uses a SQL execute task to execute my Stored Procedure. Then scheduled this to run every few hours and when it meets the IF statement validity then the mail is sent out to alert whoever is in the @recipients list.

Hope this helps you as much as it helped me.

SSRS Cancel Report Still Running In The Background

There are some people who cancel a SSRS report because its taking to long by just closing visual studio or their browser. Then they reopen SSRS/browser and try and view the report again. In the mean time the previous report they were trying to run the query is still running on the database. This takes up quite a bit of resources and people think that there query has been cancelled, but this in not the case.

This is a common problem with users accessing reports. There is a way to stop the query from running in the background by using the KILL command, however you will need a DBA to do this for you unless you have permission to do this your self.

Hope this helps other who are struggling with the same issue 🙂

PowerBI Campaign/Brand Management for Twitter

PowerBI has released a fantastic template for Twitter called Campaign/Brand Management for Twitter. After following the “wizard” you will have a complete report filled with sophisticated analytics.

In order to set this up, all you need is a twitter account and an Azure account. If you would like to play with this you can set up a trial Azure account.

To get started you can go to the following link
This will open the following page. Select install to start the “wizard”.

Next you will get a summary of what is needed to complete this Twitter template.

On the Login page you will connect to Azure. If you do not have a subscription there is a link to set up a free Azure trial. You will then select whether you have a personal or work Azure account.

If you are not sure what to put in the Azure Tenant section you can find this in the azure portal. If you hover over your icon on the top right hand corner it shall show you some details. It is the Domain details that you shall place in the Azure Tenant. It will have the “”

Once you click Connect to Azure you will need to accept that the Business Platform Solution Template can access your Azure account.

Once accepted you shall get a confirmation page that all was successfully validated and you can click on next to progress to the Source page.

On the Source page you will specify the database name to store the Twitter data, a username and a password. Make sure the password contains upper case, lower case and a special character.

Next you will need to check for availability, sometimes the database name you choose is already taken on another azure server.

Once validated you shall get the below screen and you can select next to go onto the Twitter page.

On this page you shall connect to Twitter. This will connect to your actual Twitter account. You can get data about any Twitter account using this. It is not limited to your own just because you are logging in. In this demo I take a look at the @MSPowerBI twitter account.

Complete your Twitter login details and authorize the Azure app to have access.

Once authorised you will get a screen to show you that your Twitter account has been validated and you can move onto the next page.

On the Search Terms page you can place in the twitter handles you would like to take a look at or the keywords you are keen on. You can do both by separating the two with the word OR.

Once validated you move onto the Twitter Handles page. This is where you can search all the Twitter handles you are interested in by separating them with a space. For this I took a look at @MSPowerBI and @Azure

After this you reach the Summary page. Once you click on run it starts to process all the data and build the report.

You then move onto the Deploy page where you can see the progress of the data being extracted and compiled in a report.

Once complete you shall get a notification and you can download your pbix file to view on your desktop.

SSRS Reporting Server Recycle Time

Many people complain about their reports taking a long time to load when they access them first thing in the day. This is due to the reporting server’s recycle time. What happens is SSRS regularly restarts the application domain after a specific time period. Upon first request to the report server after the application domain has been restarted, SSRS needs to load all the settings and this takes quite a long time.

The default recycle time for the application domain is 720 minutes. This can be increased to a more reasonable time, however this does still not solve the problem. The first request to the report server will still take a long time to process.

If you would like to increase the recycle time you can do this in the rsreportserver.config file. The file can be located in “C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer”

You can search for the below line of code in the config file and increase the time:


The better solution would be to schedule a PowerShell script that refreshes the report server twice a day. This will make the access to the report server for the first user fast. They will no longer have to wait long for the report to load.

There are two PowerShell scripts that you can use. The first script will access the report server:

$root = 'https://Hostname/reports/'
Invoke-Webrequest $root

The second script will start up IE and open up a report that is deployed on the server. You can specify which report to open up.

v=$root = 'http://Hostname/reports/report/ReportNameToAccess'
$IE=new-object -com internetexplorer.application

You can schedule either of these scripts to run with Windows Task Scheduler.

Hope this helps you with speeding up the load time of the reports!


Management Data Warehouse (MDW) Reports

Management Data Warehouse reports can be very useful and you can get quite a lot of information regarding your server out of them. In this post I will be showing some of the reports and how to get access to them. The descriptions in the reports are hopefully intuitively obvious enough for the casual observer to interpret the reports.

Server Activity History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Server Activity History
ActivityHistory1 ActivityHistory2

System CPU Usage
You can launch this report by clicking on the green System line in the %CPU Utilization chart in the Server Activity History report.
CPUUsage1 CPUUsage2

System Disk Usage
You can launch this report by clicking on the green System line in the Disk I/O Usage chart in the Server Activity History report.
DiskUsage1 DiskUsage2

SQL Server Memory Usage
You can launch this report by clicking on the light blue SQL Server line in the Memory Usage chart in the Server Activity History report.
MemoryUsage1 MemoryUsage2

System Memory Usage
You can launch this report by clicking on the green System line in the Memory Usage chart in the Server Activity History report.

SQL Server Waits
You can launch this report by clicking anywhere within the SQL Server Waits chart in the Server Activity History report.
ServerWaits1 ServerWaits2

Query Statistics History
This can be accessed under Management > Right-click Data Collection > Reports > Management Data Warehouse > Query Statistics History or by clicking on the blue SQL Server line in the % CPU Utilization chart in the Server Activity History report.

Only 10 queries are listed here. I would recommend clicking on the biggest one first to check the Query Details in order to optimize it better.

Query Details
You can launch this report by clicking on any hyperlink text in any of the reports that has them. For example, SQL Server Observed Waits, SQL Server Active Requests, and the Query Statistics reports all have links to Transact SQL queries.

You can also click on the Duration, Physical Reads and Logical Writes links to sort the plans by these dimensions.
QueryDetails1 QueryDetails2

Query Plan Detail
You can launch this report by clicking on the Query Plan line in the chart or the Plan # in the list in the Query Details report. The top part of the report is the same as the Query Details report, but the awesome demo portion is shown below.
Hope this post was useful for you.

Choice to learn

Management Data Warehouse (MDW)

Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s.

MDW provides 3 reports and you are able to create your own custom reports. The reports will provide suggestions for improving performance, based on information gathered by the Data Collector. The 3 main reports are Server Activity History, Query Statistics History and System Disk Usage.

Using a basic configuration of the MDW enables a DBA to perform tasks such as:

  • Proactive Tuning
  • Historical Query Analysis
  • Performance Baselining
  • Database Growth Forecasting
  • Storage Planning

With just a few simple clicks you can answer questions such as:

  • What are my most expensive queries in terms of a given resource measure (CPU/Duration/IO)?
  • What is my SQL Server instance waiting on typically?
  • Which are my fastest growing databases?
  • Why is query X blocking?
  • Is SQL Server busier than normal for this time of day?

Setting up MDW takes about 15 minutes, it is quick and simple!

To configure MDW you can follow these steps:

Step 1:
Expand the Management node in Object Explorer and right click on Data Collection and select Configure Management Data Warehouse. This will open up a wizard.

Step 2:
Select Create or upgrade a management data warehouse. Then click Next.

Step 3:
Type in a Server Name, by default the server you are currently on will be populated in the name. You are able to set up MDW on a separate server and do the data collection for another server.

Step 4:
Click on New next to Database Name. A pop-up will appear to create a database for the MDW to collect and write the data to. Type in Database Name and click ok.

Step 5:
The Database Name will now be populated and you can click on next.

Step 6:
Select a user to map to the MDW and set the Database membership role and click Next.

Step 7:
A summary window for the configuration will display and you can click Finish.

Step 8:
Next we need to set up a data collection to collect data from the server and databases you are interested in getting information from. Right click on the Data Collection node again and select Configure Management Data Warehouse. This time you will select Set up data collection and click Next.


Step 9:
Select the Server and Database where you want to store the data and click Next.


Step 10:
A summary screen will display and click Finish. Make sure SQL Server Agent is running, otherwise the setup will fail.

Once complete you will see the Data Collection sets in the Object Explorer

To access the reports, you will right-click on the Data Collection node > Reports > Management Data Warehouse

In my next post I will be going into more detail about the MDW reports and how to get the most value out of them!