Looking under the hood of SSRS
- by Jim Giercyk
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!
