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!
