Looking under the hood of SSRS

Posted by Jim Giercyk on Geeks with Blogs See other posts from Geeks with Blogs or by Jim Giercyk
Published on Mon, 02 Apr 2012 13:27:50 GMT Indexed on 2012/04/02 23:31 UTC
Read the original article Hit count: 406

Filed under:

SSRS is a powerful tool, but there is very little available to measure it’s performance or view the SSRS execution log or catalog in detail. Here are a few simple queries that will give you insight to the system that you never had before.

ACTIVE REPORTS: Have you ever seen your SQL Server performance take a nose dive due to a long-running report? If the SPID is executing under a generic Report ID, or it is a scheduled job, you may have no way to tell which report is killing your server. Running this query will show you which reports are executing at a given time, and WHO is executing them.

USE ReportServerNative

SELECT runningjobs.computername,
            runningjobs.requestname,
            runningjobs.startdate,
            users.username,
           Datediff(s,runningjobs.startdate, Getdate()) / 60 AS    'Active Minutes'
FROM runningjobs
INNER JOIN users
ON runningjobs.userid = users.userid
ORDER BY runningjobs.startdate

SSRS CATALOG: We have all asked “What was the last thing that changed”, or better yet, “Who in the world did that!”. Here is a query that will show all of the reports in your SSRS catalog, when they were created and changed, and by who.


	USE ReportServerNative SELECT DISTINCT catalog.PATH, catalog.name, users.username AS [Created By], catalog.creationdate, users_1.username AS [Modified By], catalog.modifieddate FROM catalog INNER JOIN users ON catalog.createdbyid = users.userid INNER JOIN users AS users_1 ON catalog.modifiedbyid = users_1.userid INNER JOIN executionlogstorage ON catalog.itemid = executionlogstorage.reportid WHERE ( catalog.name <> '' )

SSRS EXECUTION LOG: Sometimes we need to know what was happening on the SSRS report server at a given time in the past. This query will help you do just that. You will need to set the timestart and timeend in the WHERE clause to suit your needs.

USE ReportServerNative

SELECT catalog.name AS report,
       executionlogstorage.username AS [User],
       executionlogstorage.timestart,
       executionlogstorage.timeend,
       Datediff(mi,e.timestart,e.timeend) AS ‘Time In Minutes',
       catalog.modifieddate AS [Report Last Modified],
       users.username
FROM   catalog (nolock)
       INNER JOIN executionlogstorage e (nolock)
         ON catalog.itemid = executionlogstorage.reportid
       INNER JOIN users (nolock)
         ON catalog.modifiedbyid = users.userid
WHERE executionlogstorage.timestart >= Dateadd(s, -1, '03/31/2012')
       AND executionlogstorage.timeend <= Dateadd(DAY, 1, '04/02/2012')

LONG RUNNING REPORTS: This query will show the longest running reports over a given time period. Note that the “>5” in the WHERE clause sets the report threshold at 5 minutes, so anything that ran less than 5 minutes will not appear in the result set. Adjust the threshold and start/end times to your liking. With this information in hand, you can better optimize your system by tweaking the longest running reports first.

USE ReportServerNative

SELECT executionlogstorage.instancename,
       catalog.PATH,
       catalog.name,
       executionlogstorage.username,
       executionlogstorage.timestart,
       executionlogstorage.timeend,
       Datediff(mi, e.timestart, e.timeend) AS 'Minutes',
       executionlogstorage.timedataretrieval,
       executionlogstorage.timeprocessing,
       executionlogstorage.timerendering,
       executionlogstorage.[RowCount],
       users_1.username
       AS createdby,
       CONVERT(VARCHAR(10), catalog.creationdate, 101)
       AS 'Creation Date',
       users.username
       AS modifiedby,
       CONVERT(VARCHAR(10), catalog.modifieddate, 101)
       AS 'Modified Date'
FROM   executionlogstorage e
       INNER JOIN catalog
         ON executionlogstorage.reportid = catalog.itemid
       INNER JOIN users
         ON catalog.modifiedbyid = users.userid
       INNER JOIN users AS users_1
         ON catalog.createdbyid = users_1.userid
WHERE ( e.timestart > '03/31/2012' )
       AND ( e.timestart <= '04/02/2012' )
       AND Datediff(mi, e.timestart, e.timeend) > 5
       AND catalog.name <> ''
ORDER BY 'Minutes' DESC

I have used these queries to build SSRS reports that I can refer to quickly, and export to Excel if I need to report or quantify my findings. I encourage you to look at the data in the ReportServerNative database on your report server to understand the queries and create some of your own. For instance, you may want a query to determine which reports are using which shared data sources.

Work smarter, not harder!

Developer IT

Looking under the hood of SSRS - Developer IT

Looking under the hood of SSRS

Categories cloud