Analyze Report Execution and Usage Statistics in SQL Server Reporting Services

By:   |   Comments (19)   |   Related: > Reporting Services Monitoring


Problem

We have SQL Server Reporting Services (SSRS) 2005 running on a single server that supports a large user base.  There are specific times during the month where the response time is not acceptable.  Based on performance data that we have gathered we have been able to pinpoint the times when the server is extremely busy.  What we haven't been able to figure out is what reports were running and who was running those reports.  In this tip, I will go over some of the logging mechanisms available in SSRS to answer these questions.

Solution

SSRS has a built-in logging capability that can provide the information that you need.  The ReportServer database has a table named ExecutionLog which contains the following columns:

COLUMN_NAME                              DATA_TYPE
---------------------------------------- ----------------------------------------
InstanceName                             nvarchar
ReportID                                 uniqueidentifier
UserName                                 nvarchar
RequestType                              bit
Format                                   nvarchar
Parameters                               ntext
TimeStart                                datetime
TimeEnd                                  datetime
TimeDataRetrieval                        int
TimeProcessing                           int
TimeRendering                            int
Source                                   tinyint
Status                                   nvarchar
ByteCount                                bigint
RowCount                                 bigint

The column names are pretty self-explanatory.  You can get the report name from the Name column in the Catalog table by joining the ExecutionLog.ReportID to Catalog.ItemID.  However, you need to check if the built-in logging is enabled and how long the data is being retained.  To do that please refer to our earlier tip Logging Options for Reporting Services 2005

The amount of data collected in the ExecutionLog table will grow steadily on a busy server.  In order to conserve space you will want to retain only a small amount of history.  However, you would like to be able to go back and see over time how report usage patterns are changing.  The logical next step is to periodically extract data from the ReportServer database, transform it into a dimensional model, and load it into a data mart on another server to support analysis, ad-hoc queries, and reporting.  You can pickup a nice example of how to do this in the SQL Server 2005 samples available on the Code Plex site.  Assuming you installed the samples in the default location, you will find the sample in the following folder:

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports

The sample includes the following:

  • Createtables.sql - a T-SQL script to create the the tables for the data mart.  You should create a new database then execute this script in your new database.
  • Cleanup.sql - a T-SQL script to purge data from the data mart that is older than a specified report time start.
  • RSExecutionLog_Update.dtsx -  a SQL Server Integration Services (SSIS) package that you can run to periodically extract data from the ReportServer, transform it, and load it into the data mart.
  • Execution Summary SSRS Report - for a user-defined date range, shows report execution statistics such as total reports run, average reports run, number of successful reports, number of failed reports; also shows charts of report executions per day and week; shows top 10 of report users, most executed, longest running and largest reports.
  • Report Summary SSRS Report - drilldown for the Execution Summary Report.

Data Mart Schema

The Createtables.sql script creates the following schema for the data mart:

data mart schema

This is the typical star schema that we would expect.  There is no calendar dimension which would be convenient for analyzing reports run on weekends, holidays, etc.  The various dimensions can easily be used to populate dropdown boxes for report parameters, allowing you to filter by report, report type, format type, user, etc.


SSIS Package

The control flow for the SSIS package is shown below:

ssis package

The following are the main points about the SSIS package control flow:

  • Get Max TimeEnd executes a query to retrieve the maximum report ending time in the data mart; this is used to extract data for reports that have ended since the last time the package was run.
  • Set Time Period formulates some queries based on the Max TimeEnd
  • Update Dimensions contains data flows to extract and populate the various dimensions
  • Update Execution Logs extracts and populates the ExecutionLogs fact table
  • Update Parameters extracts and populates the ExecutionParameters dimension which shows the parameters used when a report is run
  • Cleanup Parameters sets the Parameters column in the ExecutionLogs table to NULL (since the parameters have been populated in the ExecutionParameters dimension)
  • Write Run Log inserts rows into the RunLogs table detailing activity performed during execution of the package

Running the Reports

Now let's review some sample report from the Execution Summary SSRS Report.  The report is shown below in multiple screen shots just for convenience of copying and pasting.  The first section shows report executions per day of the month and per week:

execution summary part1

You may recognize the pattern in the Report Executions per Day of Week chart above.  I worked on a project where the sales numbers and commissions for the previous week became available on Friday morning.  There is a definite spike in report usage on Fridays and Saturdays.  Since this data doesn't change after it's computed, it's an excellent candidate for a report snapshot where you run the report during off hours, a snapshot is created, then users get the results from the snapshot instead of retrieving the data from the database each time the report is run.  The snapshot typically provides a nice performance boost where the data changes happen at predictable times.

The second section of the report shows the top ten lists for most executed, longest running, largest, and users:

execution summary part2

Finally the Report Name column above is a hyperlink to the Report Summary which provides details on the execution of a single report, including the parameters used by the report.

Next Steps
  • Take a close look at the Execution Log Sample Reports and SSIS package as they provide an excellent starting point for getting a handle on who's running reports, when are they running them, how much data is retrieved, and how long does it take to run the report.
  • To the extent that you are experiencing performance problems and you can identify specific reports as the culprits, you can use the sample reports to come up with various strategies to mitigate your performance problems.  Typical strategies include scheduling reports to run in off hours, using data-driven subscriptions to deliver reports to users, creating report snapshots, and taking advantage of report caching.
  • After creating the data mart and updating it by running the SSIS package, you now have the capability to run ad-hoc queries to perform additional analysis on report executions.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, July 1, 2016 - 3:48:47 AM - Stuart Back To Top (41803)

 Hi Raymond,

Yes I've been working on that yesterday, probably today and most of the weekend to update the SSIS package to point to ExecutionLog3; which is erm, interesting considering it's my first foray into SSIS, ever.

I searched online on how to do it and got some pointers but looks like I'll have my head buried in Knight's Integration Services 24 Hour Trainer for most of the weekend!

If anyone has any pointers to how I can get this task completed faster please email me stuart dot unitt at gmail dot com.

Regards

Stuart

 


Thursday, June 30, 2016 - 2:42:21 PM - Raymond Barley Back To Top (41801)

I did a little bit of checking for SQL Server 2014 and I did not find an updated version of the sample reports that I used in this tip.  

I did find out that SQL Server 2014 has a view named ExecutionLog3 which you can find the details on it here: https://msdn.microsoft.com/en-us/library/ms159110(v=sql.120).aspx 

 

 

 


Thursday, June 30, 2016 - 6:25:21 AM - Stuart Back To Top (41795)

 Hi Ray,

I am using SQL Server 2014 and trying to implement this reporting package.

a) Is this possible with the code etc. available and;

b) Are there any instructions on how to update if it isn't yet possible?

I'm very new to SSIS and so struggling a little bit.

Regards

Stuart

 


Thursday, May 2, 2013 - 9:51:48 AM - Meike Back To Top (23685)

Hi Ray - it is the Execution Summary report that came with the sample package.

When I deploy to Test it is connecting to the same database as when I am editing it in Visual Studio.
All the lists above and below the charts are returning the same values as when I run in VS.

 

The NoDataMessage property is set to No Data Available (as per the sample)

 

If it makes a difference I am executing the report in IE9

 

Thank you again for your asssitance.


Thursday, May 2, 2013 - 8:08:41 AM - Raymond Barley Back To Top (23682)

Are you connecting to the same database when you deploy as when you're running in Visual Studio?  Are you sure your dataset that populates the chart is returing results?  An empty result set would probably cause the chart not to display.  Is there a property on the chart to show a message if the dataset is empty - maybe set that to see if no rows is your problem.


Thursday, May 2, 2013 - 6:46:35 AM - Meike Back To Top (23681)

Hi Ray and Thanks for your response.

It is working beautifully in 2012.

The only funny is with the charts.... when I preview the report in Visual Studio (2010) then they display fine... once I deploy the reports to the ReportServer the charts don't display... any thoughts on this? otherwise I will keep digging.

Thanks
Meike


Tuesday, April 30, 2013 - 2:11:37 PM - Raymond Barley Back To Top (23637)

Take a look at this tip: http://www.mssqltips.com/sqlservertip/2692/migrating-sql-reporting-services-to-a-new-server-by-moving-the-reporting-services-databases/


Tuesday, April 30, 2013 - 11:07:13 AM - vinay ahire Back To Top (23632)

Hi Team,

I want to migrate reports from one server to another,plz let me know the steps by step approach.

From server windows 2003 to windows 2008 both having same sql 2008 r2 edition.


Monday, April 29, 2013 - 11:13:07 AM - Raymond Barley Back To Top (23617)

I don't know if the samples I used for this tip have been updated for later versions of SQL Server.  I downloaded the samples that I used in this tip (from http://sqlserversamples.codeplex.com/releases/view/4000), installed, installed them and tested on SQL Server 2012 and they work.  I assume they would work with SQL Server 2008 R2 but I haven't tried.


Monday, April 29, 2013 - 9:31:35 AM - Meike Back To Top (23609)

Hi Ray - Thank you for your incredibly usefull posts.

Regarding this one on Analyzing Report execution I cannot find the Sample package for 2008 R2.

The CodePlex package at http://msftrsprodsamples.codeplex.com/releases/view/72275 doesn't include ANY of the scripts or reports that you mention here.

Please advise where I can get the relevant samples.

 

Thanks


Wednesday, January 19, 2011 - 3:15:43 PM - Miles Back To Top (12641)

Nice post. I've been working on a way to query SSRS reports in Sharepoint Integrated Mode, and found a way to join the RSSharepoint.Catalog table with the Execution Log table, then create a temp table to join on the Sharepoint metadata for each report.  This will give you a list of reports, last time used, number of executions, as well as report metadata stored in sharepoint such as business group, or other custom metadata.  Here's a sample of the query:

http://surfpointtech.com/2011/01/19/how-to-get-ssrs-report-usage-from-sharepoint-in-sharepoint-integrated-mode/


Monday, November 29, 2010 - 12:04:15 PM - Ray Barley Back To Top (10402)

I've only implemented using SSRS 2005


Monday, November 29, 2010 - 11:53:56 AM - Adam Cassel Back To Top (10401)

Ray,

First want to thank you for this excellent and helpful post, only one of an amazing number of fascinating and superbly relevant posts you've contributed to MSSQLTIPS!

I'm about to follow the article with the objective of implementing for SSRS 2008 & 2008 R2, but thought it might be worth asking if you knew of any not-so-obvious issues I should be aware of.

Either way, thank you for your dedication to the community and for your commitment to sharing your substantial expertise!

Adam


Tuesday, February 23, 2010 - 3:24:32 PM - zinggm Back To Top (4948)

 I got it to run by changing the size of [Value] from 'MAX' to '4000' which is the max size. The script completed although I did receive a warning when finished: 

 

"Warning: The table 'ExecutionParameters' has been created but its maximum row size (12057) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes."


Tuesday, February 23, 2010 - 3:21:48 PM - zinggm Back To Top (4947)

 Hmmm.. I'm using developer as well. That (is where it's failing, on this line: 

 

[Value] [nvarchar] (MAX) NOT NULL,

 

I'll play around with it and see if I can figure out the problem. Thanks.

 


Tuesday, February 23, 2010 - 3:07:57 PM - raybarley Back To Top (4946)
I created a new database and ran the createtables.sql script; I couldn't reproduce your error. The only place I see MAX in the script is here: PRINT 'Creating ExecutionParameters...' CREATE TABLE [dbo].[ExecutionParameters] ( [ExecutionParameterID] [uniqueidentifier] NOT NULL DEFAULT NEWID() PRIMARY KEY , [Name] [nvarchar] (2000) NOT NULL , [Value] [nvarchar] (MAX) NOT NULL , [ExecutionLogID] [uniqueidentifier] NOT NULL FOREIGN KEY REFERENCES [dbo].[ExecutionLogs] ([ExecutionLogID]) ) ON [PRIMARY] GO I'm using the developer (i.e. enterprise) edition.

Tuesday, February 23, 2010 - 2:16:24 PM - zinggm Back To Top (4943)

 Much thanks. I did read that and forgot to do that step. However, I just created a new database in sql server 2005, on my reporting server. I ran the script this time but got one error: 

 

Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'MAX'.

 

Any ideas?

Thanks.

Mike 

 


Tuesday, February 23, 2010 - 1:54:20 PM - raybarley Back To Top (4941)
You should create a new database then run the Createtables.sql script in the new database.

Tuesday, February 23, 2010 - 12:31:14 PM - zinggm Back To Top (4940)

 I followed the instructions in your article, but when I try to execute the 'createtables.sql' script, I receive a bunch of errors.I have included the text of the entire script results.

 

--=================

Dropping tables...
Msg 3726, Level 16, State 1, Line 3
Could not drop object 'dbo.Users' because it is referenced by a FOREIGN KEY constraint.

Creating ReportTypes...
Creating Reports...
Creating Users...
Msg 2714, Level 16, State 6, Line 10
There is already an object named 'Users' in the database.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserKey'.

Creating Machines...
Creating RequestTypes...
Creating SourceTypes...
Creating FormatTypes...
Creating StatusCodes...
Creating ExecutionLogs...
Msg 1770, Level 16, State 0, Line 9
Foreign key 'FK__Execution__UserK__2101D846' references invalid column 'UserKey' in referenced table 'dbo.Users'.
Msg 1750, Level 16, State 0, Line 9
Could not create constraint. See previous errors.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'MAX'.

Creating RunLogs...
Script completed.

--==================

Could you tell me how I should go about getting past these errors?

Much thanks

Mike

 

 

 

 















get free sql tips
agree to terms