Geeks With Blogs
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!

Posted on Monday, April 2, 2012 3:27 PM | Back to top


Comments on this post: Looking under the hood of SSRS

# re: Looking under the hood of SSRS
Requesting Gravatar...
These are great, but they don't quite cover my need and I'm beginning to think what I need isn't possible.

Our SSRS reports use parameters, so that one report can do the job of a dozen. Each of the report configurations have a daily subscription and I need a reliable way to link the subscription to the correct ExecutionLogStorage record.

With more than 1000 reports distributed every day, the server frequently has reports waiting in the queue, so I can't use the subscription's scheduled start time as a match to the logs TimeStart. In some instances these times can vary by more than an hour.

Am I correct that there isn't a 100% reliable way to make this link?
Left by Andy Lindquist on Jun 08, 2016 10:56 AM

# re: Looking under the hood of SSRS
Requesting Gravatar...
I haven't been able to give this a lot of time, but will this get you going in the right direction:

SELECT c.NAME 'Report'
,c.path 'ReportLocation'
,s.description 'Subscription'
,c.Parameter
,el.*
FROM [dbo].[ExecutionLogStorage] el
LEFT JOIN [dbo].[ReportSchedule] rs ON rs.ReportID = el.ReportID
JOIN [dbo].[Subscriptions] s ON s.SubscriptionID = rs.SubscriptionID
JOIN [dbo].[Catalog] c ON c.ItemID = el.ReportID

You'll need to add a Where clause on the date on the execution log to suit your needs or you'll get every subscription run since the beginning of time. :) Good luck.
Left by Jim Giercyk on Jun 09, 2016 2:01 PM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net