The ReportServer Database

Amazing, I have just spent all morning on Microsoft websites to determine what number is the TimeDataRetrieval column displaying. Thank you I know it's an INT. There is just a serious lack of documentation as to what this database is and how it populates its data.

View: ExecutionLog
InstanceNamenvarchar(38)NOT NULLName of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportIDuniqueidentifierNULLThe ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times).
UserNamenvarchar(260)NULLWindows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestTypebitNOT NULLUser or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Formatnvarchar(26)NULLThis is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
ParametersntextNULLParameters and the values they were submitted with.
TimeStartdatetimeNOT NULLTime report started to run.
TimeEnddatetimeNOT NULLTime report finished running? Need to check what finished?
TimeDataRetrievalintNOT NULLMilliseconds spent retrieving the data.
TimeProcessingintNOT NULLMilliseconds spent processing the report.
TimeRenderingintNOT NULLMilliseconds spent rendering the report.
SourceintNOT NULLSource of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Statusnvarchar(32)NOT NULLeither rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCountbigintNOT NULLSize of rendered reports in bytes.
RowCountbigintNOT NULLNumber of rows returned from queries.

Conversions Needed:
  1. I want to convert a start and end date to a single time value (hours minutes seconds mseconds)
  2. I want to total 3 of the columns (which measure in milliseconds) and convert to a time value with milliseconds showing.
  3. Now why don't these match?
  4. Using in an aggregate, how do I get the milliseconds between start and end times instead of summing the remaining columns?
  5. Correction, sum will only give me the total time but what if a report has been run twice more often then another, then is it slower? Not necessarily.
  6. Like all measures in bytes, I would like a thousand separator, and lose the decimal ".00".
  7. I need the size of the report but the value has to be returned as part of the aggregate query.
  8. I want the SQL to return just numbers and let the MDX query add the thousand separator.

My answer for everything:
  1. CAST((TimeEnd - TimeStart) AS TIME)
  2. CAST(CONVERT(CHAR, DATEADD(millisecond, [TimeDataRetrieval]+[TimeProcessing]+[TimeRendering], '00:00:00'), 121) AS TIME)
  3. Unaccounted time.
  4. SUM(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenSum]
  5. AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenAvg]
  6. LEFT(CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountSum]
  7. LEFT(CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountAvg]
  8. Use the data format: "$#,##0;($#,##0)"
Show me the Name!
A quick query to show the execution log with at least the report name for us human users:
copyraw
SELECT a.[InstanceName]
      ,b.[Name]
      ,a.[UserName]
      ,a.[RequestType]
      ,a.[Format]
      ,a.[Parameters]
      ,a.[TimeStart]
      ,a.[TimeEnd]
      ,a.[TimeDataRetrieval]
      ,a.[TimeProcessing]
      ,a.[TimeRendering]
      ,a.[Source]
      ,a.[Status]
      ,a.[ByteCount]
      ,a.[RowCount]
  FROM [ReportServer].[dbo].[ExecutionLog] a
  INNER JOIN [ReportServer].[dbo].[Catalog] b
  ON a.ReportID = b.ItemID
  1.  SELECT a.[InstanceName] 
  2.        ,b.[Name] 
  3.        ,a.[UserName] 
  4.        ,a.[RequestType] 
  5.        ,a.[Format] 
  6.        ,a.[Parameters] 
  7.        ,a.[TimeStart] 
  8.        ,a.[TimeEnd] 
  9.        ,a.[TimeDataRetrieval] 
  10.        ,a.[TimeProcessing] 
  11.        ,a.[TimeRendering] 
  12.        ,a.[Source] 
  13.        ,a.[Status] 
  14.        ,a.[ByteCount] 
  15.        ,a.[RowCount] 
  16.    FROM [ReportServer].[dbo].[ExecutionLog] a 
  17.    INNER JOIN [ReportServer].[dbo].[Catalog] b 
  18.    ON a.ReportID = b.ItemID 

The Top 5 Most Frequent
copyraw
-- Top 5 Most Frequent:
SELECT TOP 5
	COUNT(Name) AS ExecutionCount
	, Name
	, CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg]
	, AVG(ByteCount) AS [ByteCountAvg]
	, AVG([RowCount]) AS [RowCountAvg]
	, SUM(ByteCount) AS [ByteCountSum]
	, SUM([RowCount]) AS [RowCountSum]
	, SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
	, SUM(TimeProcessing) AS TimeProcessingSum
	, SUM(TimeRendering) AS TimeRenderingSum
FROM
(
	SELECT 
		TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
	FROM
	Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
	WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2
) AS RE
GROUP BY
	Name
ORDER BY
	COUNT(Name) DESC
	, Name
  1.  -- Top 5 Most Frequent: 
  2.  SELECT TOP 5 
  3.      COUNT(Name) AS ExecutionCount 
  4.      , Name 
  5.      , CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg] 
  6.      , AVG(ByteCount) AS [ByteCountAvg] 
  7.      , AVG([RowCount]) AS [RowCountAvg] 
  8.      , SUM(ByteCount) AS [ByteCountSum] 
  9.      , SUM([RowCount]) AS [RowCountSum] 
  10.      , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum 
  11.      , SUM(TimeProcessing) AS TimeProcessingSum 
  12.      , SUM(TimeRendering) AS TimeRenderingSum 
  13.  FROM 
  14.  ( 
  15.      SELECT 
  16.          TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount] 
  17.      FROM 
  18.      Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID 
  19.      WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2 
  20.  ) AS RE 
  21.  GROUP BY 
  22.      Name 
  23.  ORDER BY 
  24.      COUNT(Name) DESC 
  25.      , Name 
Category: Databases :: Article: 375

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.