Monitoring Ssrs Report Usage: Strategies For Performance Analysis

Monitoring the usage of SSRS reports is a crucial aspect of SQL Server Reporting Services (SSRS) administration. It helps identify underutilised or unused reports, optimise server performance, and ensure information security. By tracking who is viewing which reports and when, administrators can make informed decisions about report retention, user access, and resource allocation.

SSRS provides built-in logging capabilities and system tables/views that store valuable information about report usage. The ReportServer database contains the ExecutionLog table, which records details such as the report name, user, execution time, data retrieval time, and more. Additionally, views like ExecutionLog2 and ExecutionLog3 offer aggregated data for analysis.

To gain deeper insights, administrators can create custom queries and reports using T-SQL to extract, transform, and load data into a data mart for further analysis, ad-hoc queries, and reporting. This enables the identification of usage patterns, popular reports, and performance issues.

By regularly monitoring SSRS report usage, administrators can ensure efficient server utilisation, optimise report performance, and enhance overall user experience.

Using T-SQL queries to monitor SSRS report usage

Monitoring SQL Server Reporting Services (SSRS) can help ensure that your servers are offering your users the best of both worlds, i.e. effective reports, cleaner servers, and faster access time.

SSRS has a built-in logging capability that can provide information on who is running which reports and when. The ReportServer database has a table named ExecutionLog which contains columns such as InstanceName, ReportID, UserName, RequestType, Format, Parameters, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Source, Status, ByteCount, and RowCount.

To access this data, you can use T-SQL queries to select from the ExecutionLog view in the ReportServer database. Here are some examples of T-SQL queries that you can use to monitor SSRS report usage:

Sql

- Query to see who viewed which report, when they viewed it, and which parameters were passed

USE ReportServer;

GO

SELECT el2.username, el2.InstanceName, el2.ReportPath, el2.TimeStart, el2.TimeEnd, el2.[Status], isnull(el2.Parameters, 'N/A') as Parameters

FROM ExecutionLog2 el2

GO

- Query to determine when reports are being viewed to gain insight into when processes generating data should complete

USE ReportServer;

GO

SELECT username, convert(varchar(25),TimeEnd, 120) as AccessTime

FROM ExecutionLog2

WHERE status='rsSuccess' AND username='STAFF\user01' AND ReportPath='/Sales/YTDSalesByProductCategory'

ORDER BY AccessTime desc

GO

- Query to return the report access counts per user and report for the current month

USE ReportServer;

GO

SELECT username, ReportPath, count(*) as ViewCount

FROM ExecutionLog2

WHERE status='rsSuccess' AND TimeEnd>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

GROUP BY username, ReportPath

ORDER BY username, ViewCount desc

GO

- Query to list each report once with the last time it was run

WITH RankedReports AS (SELECT ReportID, TimeStart, UserName, RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank

FROM dbo.ExecutionLog t1

JOIN dbo.Catalog t2 ON t1.ReportID = t2.ItemID)

SELECT t2.Name AS ReportName, t1.TimeStart, t1.UserName, t2.Path, t1.ReportID

FROM RankedReports t1

JOIN dbo.Catalog t2 ON t1.ReportID = t2.ItemID

WHERE t1.iRank = 1

ORDER BY t1.TimeStart;

}

By using these and other similar T-SQL queries, you can gain valuable insights into SSRS report usage, such as who is viewing which reports, when they are being viewed, how often they are accessed, and any parameters passed. This information can help you make informed decisions about report maintenance, optimization, and removal of unused reports.

Analysing report execution and usage statistics

The ReportServer database, accessible through SQL Server Reporting Services (SSRS), offers built-in logging capabilities to support this analysis. The ExecutionLog table within this database records essential details about report executions, including the report name, user, execution time, data retrieval time, and more. This data can be leveraged to generate reports and gain a deeper understanding of report usage patterns.

To enhance the analysis, it is possible to create a data mart, a separate database designed specifically for analytics. By periodically extracting data from the ReportServer database, transforming it into a dimensional model, and loading it into the data mart, you can facilitate ad-hoc queries, custom reports, and in-depth analysis of report executions.

Additionally, you can utilise T-SQL queries to further explore report usage statistics. For instance, you can identify the most used reports, determine report access counts per user, and analyse the time of day when reports are typically viewed.

By combining the data from the ReportServer database, data mart, and T-SQL queries, you can generate comprehensive reports that offer insights into who is running reports, when they are running them, and how report usage patterns change over time. This information is invaluable for making informed decisions about report optimisation, server maintenance, and ensuring that your servers are efficient and effective.

Creating a quick and dirty SSRS monitoring tool

Much time and money can be spent on creating reports for end users, so it can be frustrating when they are not viewed. It is also good practice to review who is looking at reports for security purposes. This article will outline the steps to create a quick and dirty monitoring tool for SQL Server Reporting Services (SSRS) to address these issues.

Step 1: Understanding the Execution Log

SSRS has a built-in logging capability that can provide information on who is running reports and when. The ReportServer database has a table named ExecutionLog which contains columns such as InstanceName, ReportID, UserName, RequestType, Format, Parameters, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, Source, Status, ByteCount, and RowCount.

You can get the report name from the Name column in the Catalog table by joining the ExecutionLog.ReportID to Catalog.ItemID.

Note: Check if the built-in logging is enabled and how long data is being retained.

Step 2: Creating the Data Mart

The amount of data collected in the ExecutionLog table will grow quickly on a busy server. To conserve space, you should retain only a small amount of historical data. However, you may want to analyse report usage patterns over time. Therefore, 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.

You can find a nice example of how to do this in the SQL Server 2005 samples available on the Code Plex site:

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

This sample includes:

• Createtables.sql - a T-SQL script to create the tables for the data mart
• 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 to extract, transform, and load data into the data mart
• Execution Summary SSRS Report - shows report execution statistics such as total reports run, average reports run, number of successful/failed reports, and charts of report executions per day and week
• Report Summary SSRS Report - drilldown for the Execution Summary Report

Step 3: Reviewing Sample Reports

Take a close look at the Execution Log Sample Reports and SSIS package as they provide an excellent starting point for understanding who is running reports, when they are running them, how much data is retrieved, and how long it takes.

Step 4: Strategies to Mitigate Performance Problems

If you are experiencing performance problems and can identify specific reports as the cause, you can use the sample reports to develop strategies to improve performance. Typical strategies include:

• Scheduling reports to run during off-peak hours
• Using data-driven subscriptions to deliver reports to users
• Creating report snapshots
• Taking advantage of report caching

Step 5: Ad-hoc Queries

After creating the data mart and updating it by running the SSIS package, you will be able to run ad-hoc queries to perform additional analysis on report executions.

Example Queries

Viewing who has accessed what report and when:

USE ReportServer;

GO

SELECT el2.username, el2.InstanceName, el2.ReportPath, el2.TimeStart, el2.TimeEnd, el2.[Status], isnull(el2.Parameters, 'N/A') as Parameters

FROM ExecutionLog2 el2

GO

Determining when reports are being viewed to gain insight into behavioural patterns:

USE ReportServer;

GO

SELECT username, convert(varchar(25),TimeEnd, 120) as AccessTime

FROM ExecutionLog2

WHERE status='rsSuccess' AND username='STAFF\user01' AND ReportPath='/Sales/YTDSalesByProductCategory'

ORDER BY AccessTime desc

GO

Returning the report access counts per user and report for the current month:

USE ReportServer;

GO

SELECT username, ReportPath, count(*) as ViewCount

FROM ExecutionLog2

WHERE status='rsSuccess' AND TimeEnd>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

GROUP BY username, ReportPath

ORDER BY username, ViewCount desc

GO

Viewing the execution log for a specific report

To view the execution log for a specific report, you can use the ExecutionLog, ExecutionLog2, or ExecutionLog3 views in the ReportServer database. These views provide information about report execution statistics, including the number of times a report is requested, output formats used, and processing time spent on each phase.

• Connect to the ReportServer database using SQL Server Management Studio (SSMS).
• Write a T-SQL query to select data from the desired execution log view. For example:

Sql

USE ReportServer;

GO

SELECT * FROM ExecutionLog3

WHERE ReportName = 'YourReportName'

ORDER BY TimeStart DESC;

This query will retrieve all columns from the ExecutionLog3 view for the specified report name and order the results by the start time in descending order.

• Execute the query.
• Review the results to see detailed information about the report's execution, including the report name, user who executed the report, start and end times, data retrieval time, processing time, rendering time, status, and any additional information.

You can also customize the query to include specific columns or filter the results based on certain criteria, such as execution time or user.

Additionally, you can create custom reports or use third-party tools to analyze the execution log data and gain insights into report usage and performance.

Generating a server and database status report

To generate a server and database status report, you can use the following methods and tools:

SQL Server Reporting Services (SSRS)

SSRS provides a built-in logging capability that can help you monitor and analyse report execution and usage statistics. The ReportServer database contains an ExecutionLog table with various columns such as InstanceName, ReportID, UserName, RequestType, TimeStart, TimeEnd, ByteCount, and RowCount. You can query this table to gain insights into report usage patterns and identify specific reports impacting server performance.

Additionally, you can create custom reports and data marts to further analyse report execution data. Microsoft provides sample scripts and SSIS packages to facilitate this process.

File Server Resource Manager (FSRM)

FSRM allows you to generate reports on demand to analyse current disk usage on the server. You can specify the data to be included in the reports, such as backup and archival files, and choose from various report formats like Dynamic HTML, HTML, XML, CSV, and Text. The reports can be saved in a default location or a specified folder. You also have the option to email the reports to administrators.

SQL Server Database Mail

To receive a daily database status report, you can configure SQL Server Database Mail and create an agent job with two steps. The first step is to check the database status and insert the output into a table in the master database. The second step is to use sp_send_dbmail to send the query results to a specified email address. By setting a daily schedule for this job, you will receive regular updates on the database status.

Frequently asked questions