Log in
After upgrading our SQL Server Reporting Services (SSRS) environment, we noticed some isuses: reports were slow, users were complaining, and we had no idea where to start. The good news is that SSRS has been quietly collecting detailed execution data the whole time — and a set of built-in views makes it surprisingly easy to query.
This post walks through the ExecutionLog views, what they contain, and how to turn that data into actionable performance insights.
What are the ExecutionLog views?
SSRS logs every report execution to the ReportServer database. Three views expose this data at different levels of detail:
ExecutionLog— A simple view covering the basics: report name, user, start time, and duration. Good for quick lookups.ExecutionLog2— AddsAdditionalInfo, an XML column with richer metadata such as estimated row counts and data source connection details.ExecutionLog3— The most complete view. Breaks execution time into three distinct phases —TimeDataRetrieval,TimeProcessing, andTimeRendering— which is where the real diagnostic power lies.
For most performance work, ExecutionLog3 is the one you want.
Understanding the key columns
Before diving into queries, it helps to know what you're working with in ExecutionLog3:
| Column | Description |
ReportPath | The full path to the report on the server |
UserName | The account that ran the report |
RequestType | Interactive (user-initiated) or Subscription |
Format | Output format: HTML4.0, PDF, EXCEL, etc. |
TimeStart / TimeEnd | Wall-clock start and end of execution |
TimeDataRetrieval | Milliseconds spent fetching data from sources |
TimeProcessing | MMilliseconds spent processing/aggregating data |
TimeRendering | Milliseconds spent rendering the output format |
Status | rsSuccess, rsProcessingAborted, or an error code |
RowCount | Number of rows returned by the primary dataset |
ByteCount | Size of the rendered output in bytes |
Source | Whether the result came from Live, Cache, or Snapshot |
The time breakdown is the most valuable part. A report with high TimeDataRetrieval probably needs query tuning or indexing. High TimeProcessing suggests complex expressions or large datasets being crunched in SSRS itself. High TimeRendering often points to reports with too many elements, sub-reports, or large images.
Useful queries to get started
1. Find your slowest reports
SELECT
ReportPath,
COUNT(*) AS ExecutionCount,
AVG(TimeDataRetrieval) AS AvgDataRetrieval_ms,
AVG(TimeProcessing) AS AvgProcessing_ms,
AVG(TimeRendering) AS AvgRendering_ms,
AVG(TimeDataRetrieval
+ TimeProcessing
+ TimeRendering) AS AvgTotal_ms,
MAX(TimeDataRetrieval
+ TimeProcessing
+ TimeRendering) AS MaxTotal_ms
FROM ExecutionLog3
WHERE TimeStart >= DATEADD(day, -30, GETDATE())
AND Status = 'rsSuccess'
GROUP BY ReportPath
ORDER BY AvgTotal_ms DESC;This gives you a ranked list of your worst performers over the past 30 days. Focus on reports that combine high average time and high execution count — those have the most impact on your environment.
2. Diagnose where time is being spent
SELECT
ReportPath,
AVG(TimeDataRetrieval) AS AvgData_ms,
AVG(TimeProcessing) AS AvgProcessing_ms,
AVG(TimeRendering) AS AvgRendering_ms,
AVG(TimeDataRetrieval) * 100.0
/ NULLIF(AVG(TimeDataRetrieval + TimeProcessing + TimeRendering), 0)
AS PctData,
AVG(TimeProcessing) * 100.0
/ NULLIF(AVG(TimeDataRetrieval + TimeProcessing + TimeRendering), 0)
AS PctProcessing,
AVG(TimeRendering) * 100.0
/ NULLIF(AVG(TimeDataRetrieval + TimeProcessing + TimeRendering), 0)
AS PctRendering
FROM ExecutionLog3
WHERE TimeStart >= DATEADD(day, -7, GETDATE())
AND Status = 'rsSuccess'
GROUP BY ReportPath
HAVING AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) > 5000
ORDER BY AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) DESC;The percentage columns immediately show you where to focus. If 85% of execution time is data retrieval, start with the underlying queries — not the report layout.
3. Spot failed executions
SELECT
ReportPath,
Status,
COUNT(*) AS FailureCount,
MAX(TimeStart) AS MostRecent
FROM ExecutionLog3
WHERE TimeStart >= DATEADD(day, -7, GETDATE())
AND Status <> 'rsSuccess'
GROUP BY ReportPath, Status
ORDER BY FailureCount DESC;Failures that users never report are surprisingly common. This query surfaces them.
4. Identify peak usage times
SELECT
DATEPART(hour, TimeStart) AS HourOfDay,
DATEPART(weekday, TimeStart) AS DayOfWeek,
COUNT(*) AS ExecutionCount,
AVG(TimeDataRetrieval
+ TimeProcessing
+ TimeRendering) AS AvgTotal_ms
FROM ExecutionLog3
WHERE TimeStart >= DATEADD(day, -30, GETDATE())
GROUP BY DATEPART(hour, TimeStart), DATEPART(weekday, TimeStart)
ORDER BY DayOfWeek, HourOfDay;Knowing when your environment is under the most load helps you schedule subscriptions more intelligently and plan maintenance windows.
5. Check cache effectiveness
SELECT
ReportPath,
Source,
COUNT(*) AS ExecutionCount
FROM ExecutionLog3
WHERE TimeStart >= DATEADD(day, -7, GETDATE())
GROUP BY ReportPath, Source
ORDER BY ReportPath, Source;If a frequently-run report shows Live for nearly every execution, it's a strong candidate for caching or snapshots. Reports served from Cache are dramatically faster for end users and lighter on your data sources.
Practical tips
Mind the log retention. By default, SSRS keeps 60 days of execution history. You can change this in Report Server Configuration Manager, or schedule a job to archive data to a separate table before it rolls off.
Watch for parameter-driven outliers. A report might average 3 seconds but occasionally spike to 45 seconds because someone runs it with a 5-year date range. Look at your MAX values alongside averages, and consider adding parameter validation to the report itself.
Combine with subscription data. Subscriptions that run at 6am and take 20 minutes may be blocking interactive users who arrive at 6:30. Knowing both patterns lets you schedule more thoughtfully.
Look at RowCount alongside time. A report returning 500,000 rows into an Excel export isn't necessarily broken — but it might be a sign a user is working around a missing feature elsewhere in your system.
Don't ignore ByteCount. Very large rendered outputs (say, multi-MB PDFs generated repeatedly) can quietly stress your network and storage. Flag these for review with the report owners.
Wrapping up
I'm happy to see that Microsoft integrated telemetry out-of-the-box. The ExecutionLog views require no configuration, no agents, and no third-party tools — the data is already there, waiting to be queried. Starting with ExecutionLog3 and a handful of the queries above, you can move from reactive firefighting to proactive performance management in an afternoon.
