How To Monitor SSRS Report Execution Times With The Data Collector

The SQL Server Data Collector is like a Swiss army knife sometimes. So much stuff you can do with it. The challenge is to know how. So how do we use it to monitor SSRS report execution times and know your SLAs are up and about?

That’s what we’re going to look at now.

This article assumes working knowledge of the following:

The SSRS ReportServer Database.
The SQL Server Data Collector, or SSDC for short.
And by consequence, the SQL Server Management Data Warehouse, of MDW for short.

All the code shown is mean to be idempotent enough to be included in an SSDT Database Project, either as a model object or in the Post-Deployment Script itself. Therefore, remember to replace the SQLCMD variables, like $(SomeDB), with proper names if you’re running this outside of an SSDT Deployment.

Create A Delta Control Table

You’ll need this to ensure the Data Collector is only bringing in the newest rows from the SSRS Catalog, as opposed to the whole table with each snapshot. You can create this table straight on ReportServer database, but I strongly recommend you create a separate Monitoring database on the same server to put your own code in. The code below can be run on its own or added as a Table object in an SSDT Database Project.

CREATE TABLE [dbo].[SSRSDataCollectorStatus]
(
	LastTimeStart DATETIME NOT NULL PRIMARY KEY
)

Seed The Delta Control Table

This allows the future collection code not to worry about the table being empty or not. You only need to run this code once. You can also tuck it into an SSDT Post-Deployment Script.

PRINT 'Seeding the [SSRSDataCollectorStatus] control table.';
GO

WITH Seed AS
(
	SELECT
		CAST(0 AS DATETIME) AS LastTimeStart
)
MERGE INTO dbo.SSRSDataCollectorStatus AS T
USING Seed AS S
ON 1 = 1
WHEN NOT MATCHED BY TARGET THEN
	INSERT (LastTimeStart)
	VALUES (LastTimeStart)
;
GO

Create Code To Retrieve Deltas

This sample Stored Procedure will be used by the SSDC to import deltas of the ExecutionLog view and Catalog table, both from the ReportServer database, into the MDW. Each single execution will identify and return the latest delta. Note that, for lack of a proper ascending unique identifier in the ExecutionLog view, we have to default to the next best thing, the TimeStart column.

The code below is meant to be added as a Stored Procedure object in a separate SSDT Database Project. You’ll also need to add a database reference to a dacpac of the ReportServer database. See How To Reference SQL Server Data Collector’s Management Data Warehouse In An SSDT Database Project for an example.

If you’re running this on your own, remember to replace the $(ReportServer) SQLCMD variable with the name of your ReportServer database.

CREATE PROCEDURE [dbo].[usp_CollectSSRSExecutionLog]
AS

/* fetch the previous delta threshold */
DECLARE @DeltaStart DATETIME =
(
	SELECT
		S.LastTimeStart
	FROM
		dbo.SSRSDataCollectorStatus AS S
);

/* fetch the current delta threshold */
DECLARE @DeltaEnd DATETIME =
(
	SELECT
		TimeStart
	FROM
		[$(ReportServer)].dbo.ExecutionLog
	ORDER BY
		TimeStart DESC
	OFFSET 0 ROWS
	FETCH NEXT 1 ROW ONLY
);

/* return the current delta */
SELECT
	E.[InstanceName],
    E.[ReportID],
    E.[UserName],
    E.[RequestType],
    E.[Format],
    CAST(E.[Parameters] AS NVARCHAR(4000)) AS [Parameters],
    E.[TimeStart],
    E.[TimeEnd],
    E.[TimeDataRetrieval],
    E.[TimeProcessing],
    E.[TimeRendering],
    E.[Source],
    E.[Status],
    E.[ByteCount],
    E.[RowCount],
	C.[Name],
	C.[Type]
FROM
	[$(ReportServer)].dbo.ExecutionLog AS E
	INNER JOIN [$(ReportServer)].dbo.Catalog AS C
		ON C.ItemID = E.ReportID
WHERE
	TimeStart > @DeltaStart
	AND TimeStart <= @DeltaEnd
;

/* replace the delta threshold */
UPDATE dbo.SSRSDataCollectorStatus
SET
	LastTimeStart = @DeltaEnd
;

GO

Create A Data Collector Set

We’ll be calling it SSRS Catalog Information. You can use the SSDC interface for this, or you can just customize the code below at your will. This code will check if the Data Collector Set already exists, and if so, will try to stop it, so we can upgrade its Data Collector Items properly.

PRINT 'Stopping the [SSRS Catalog Information] Data Collector Set...';
GO

DECLARE @CollectionSetName SYSNAME = N'SSRS Catalog Information';
DECLARE @CollectionSetID INT =
(
	SELECT
		collection_set_id
	FROM
		msdb.dbo.syscollector_collection_sets WHERE name = @CollectionSetName
);

IF @CollectionSetID IS NOT NULL
BEGIN
	EXECUTE msdb.dbo.sp_syscollector_stop_collection_set
		@collection_set_id = @CollectionSetID;
END
GO

PRINT 'Creating the [SSRS Catalog Information] Data Collector Set...';
GO

/* create the new collector set if it doesn't exist yet */
DECLARE @CollectionSetName SYSNAME = N'SSRS Catalog Information';
DECLARE @CollectionSetID INT = NULL;
DECLARE @CollectionSetUID UNIQUEIDENTIFIER = NULL;
SELECT
    @CollectionSetID = collection_set_id,
    @CollectionSetUID = collection_set_uid
FROM
    msdb.dbo.syscollector_collection_sets WHERE name = @CollectionSetName

IF @CollectionSetID IS NULL
BEGIN

    /* grab the schedule identifier we are going to use */
    DECLARE @ScheduleUID UNIQUEIDENTIFIER =
    (
        SELECT schedule_uid
        FROM msdb.dbo.sysschedules
        WHERE name = 'CollectorSchedule_Every_5min'
    );

    EXECUTE msdb.dbo.sp_syscollector_create_collection_set
        @name = @CollectionSetName,
        @collection_mode = 1, /* non-cached mode */
        @description = N'Records SSRS Catalog Event Information.',
        @days_until_expiration = 30,
        @schedule_uid = @ScheduleUID,
        @collection_set_id = @CollectionSetID OUTPUT,
        @collection_set_uid = @CollectionSetUID OUTPUT

END;
GO

Create A Data Collector Item

The code below with create a Data Collector Item named SSRS Execution Log and add it to the SSRS Catalog Information Data Collector Set. This code is meant to be used in an SSDT Post-Deployment Script, but you can run this on your own too. If you do, remember to replace the $(DatabaseName) SQLCMD variable with the name of the database where you created the usp_CollectSSRSExecutionLog Stored Procedure.

PRINT 'Creating the [SSRS Execution Log] Data Collector Item...';
GO

/* find the parent collector set */
DECLARE @CollectionSetName SYSNAME = N'SSRS Catalog Information';
DECLARE @CollectionSetID INT = NULL;
DECLARE @CollectionSetUID UNIQUEIDENTIFIER = NULL;
SELECT
    @CollectionSetID = collection_set_id,
    @CollectionSetUID = collection_set_uid
FROM
    msdb.dbo.syscollector_collection_sets WHERE name = @CollectionSetName

-- get the collector type identifier we are going to use
DECLARE @CollectorTypeUID UNIQUEIDENTIFIER =
(
    SELECT collector_type_uid
    FROM msdb.dbo.syscollector_collector_types
    WHERE name = 'Generic T-SQL Query Collector Type'
);

/* create the [SSIS Event Messages] Data Collector Item */
DECLARE @CollectionItemName SYSNAME = 'SSRS Execution Log';
DECLARE @CollectionItemID INT = NULL;

/* first we clear the old definition */
SELECT
	@CollectionItemID = collection_item_id
FROM
	msdb.dbo.syscollector_collection_items
WHERE
	name = @CollectionItemName;

IF @CollectionItemID IS NOT NULL
EXECUTE msdb.dbo.sp_syscollector_delete_collection_item
    @collection_item_id = @CollectionItemID;

/* the we add the new definition */
DECLARE @Parameters XML =
'EXECUTE [dbo].[usp_CollectSSRSExecutionLog];
SSRSCatalog_ExecutionLog
$(DatabaseName)    
';
EXECUTE msdb.dbo.sp_syscollector_create_collection_item
    @name = @CollectionItemName,
    @parameters = @Parameters,
    @collection_item_id = @CollectionItemID OUTPUT,
    @collection_set_id = @CollectionSetID,
    @collector_type_uid = @CollectorTypeUID
;
GO

Restart The Data Collector Set

Well, if you really want to.

The code below is meant to be added at the end of your SSDT Post-Deployment Script. It will start the SSRS Catalog Information Data Collector Set, but only if a $(EnableDataCollector) SQLCMD variable is set to True. You can run this on your own if you remove all the extra bits of SSDT.

IF '$(EnableDataCollector)' = 'True'
BEGIN
	PRINT 'Starting the [SSRS Catalog Information] Data Collector Set...';
END
GO

IF '$(EnableDataCollector)' = 'True'
BEGIN
	DECLARE @CollectionSetName SYSNAME = N'SSRS Catalog Information';
	DECLARE @CollectionSetID INT =
	(
		SELECT
			collection_set_id
		FROM
			msdb.dbo.syscollector_collection_sets WHERE name = @CollectionSetName
	);

	IF @CollectionSetID IS NOT NULL
	BEGIN
		EXECUTE msdb.dbo.sp_syscollector_start_collection_set
			@collection_set_id = @CollectionSetID;
	END
END
GO

Create Code To Report On The MDW

Your data will now be available in the [custom_snapshots].[SSRSCatalog_ExecutionLog] table in your designated MDW database. The sample T-SQL below will calculate a couple of measures over the gathered data. You can use this as a base for your SLA related reports.

SELECT
	T.Name,
	COUNT (*) AS ExecutionCount,
	SUM (T.TimeDataRetrieval) / COUNT (*) AS AvgTimeDataRetrieval,
	SUM (T.TimeProcessing) / COUNT (*) AS AvgTimeProcessing,
	SUM (T.TimeRendering) / COUNT (*) AS AvgTimeRendering
FROM
	[custom_snapshots].[SSRSCatalog_ExecutionLog] AS T
WHERE
	T.Type = 2
	AND T.TimeStart = @TimeStart
GROUP BY
	T.Name

Celebrate

I hope this has helped getting that project of yours going. If you have any suggestions, please feel free to comment and I’ll revise the post accordingly.

On Distributed Computing