SSRS analytics Queries

gistfile1.sql

Use ReportServer
go
/* Performance Tips
     1. High TimeDataRetriveal
          * Removed unused datasets
          * Analyze the dataset query in SQL Profiler
          * Combine Datasets where possible
     2. High TimeProcessing
          * Use less report parts (tables, charts...)
          * Do Grouping and Sorting on the SQL Side
     3. High Rendering Time
          *
*/
SELECT top 100
     r2.InstanceName     -- Server Report is on
	 , r2.ExecutionId
     ,r2.ReportPath      -- Location of the Report
     ,r2.UserName        -- Who ran the report
     ,r2.Format          -- How the report was Rendered
     ,r2.[Parameters]    -- Parameters used for the Report
     ,r2.TimeStart
     ,r2.TimeEnd
     ,r2.TimeDataRetrieval -- Time spent creating the connection and getting the data
     ,r2.TimeProcessing    -- Time spent in the report Processing Enging
     ,r2.TimeRendering     -- Time Spent rendering
     ,(r2.TimeProcessing + r2.TimeDataRetrieval + r2.TimeRendering) as TotalTime
     ,r2.Source            -- type of execution: live = dataset queries, Session = Subsequent request within an already established connection
     ,r2.[Status]
     ,r2.ByteCount         -- total bytes received by all Datasets
     ,r2.[RowCount]        -- total rows received by all datasets
     ,r2.AdditionalInfo       
FROM ExecutionLog2 r2
JOIN ExecutionLog3 r3
on r2.ExecutionId = r3.ExecutionId
ORDER BY r2.TimeStart DESC
;

-- Most Active users.
SELECT Username, COUNT(*) as Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY UserName
ORDER BY COUNT(*) DESC


-- Most popular reports
SELECT Name,b.path, COUNT(*)AS Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY COUNT(*) DESC


-- Reports with most Dataretrieval
SELECT TOP 25 Name, b.path, AVG(BYTECOUNT/(1024)) AS AVG_KB,
            SUM(BYTECOUNT/(1024)) as SUM_KB, COUNT(*)AS Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY SUM_KB desc


-- Reports with most execution time
SELECT TOP 25 Name, b.path,
      SUM(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS ExecutionTotalTimeinSec, 
      COUNT(*) AS Executed, 
      AVG(TimedataRetrieval + Timeprocessing + TimeRendering) AS AVGExecutionTimeinSec
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name, b.path
ORDER BY ExecutionTotalTimeinSec desc


-- Reports with most Rendering time
SELECT TOP 25 Name,b.path, 
    SUM(TimeRendering)/1000 AS RenderingTotalinSec, 
    COUNT(*) as Executed, 
    AVG(TimeRendering) AS AvgRenderinginMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY RenderingTotalinSec desc


-- Reports with most Data retrieval time
SELECT TOP 25 Name,b.path, 
     SUM(TimeDataRetrieval)/1000 AS DataRetrievalTimeinSec, 
     COUNT(*), AVG(TimeDataRetrieval) AS AvgTimeDataRetrievalinMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY AvgTimeDataRetrievalinMs desc


-- Slowest executed report.
SELECT TOP 10 Name,b.path, Parameters, FORMAT,TimeStart, 
     TimeEnd, ByteCount,
     (TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS TotalTimeinSec 
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
ORDER BY (TimedataRetrieval + Timeprocessing + TimeRendering) DESC