December 13, 2011 at 11:06 am
Anyone have a ReportServer-db query to find out which reports are the largest contributors to CPU usage in a SQL-2008 SSRS server?
We frequently get CPU maxing out at 100% and would like to find the cause behind it.
Our report server is separated from the RS databases into its own box (scaleout deployment).
Thanks for any help!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
December 13, 2011 at 2:16 pm
If you know when the CPU spikes occurred, you can use the view ReportServer.dbo.ExecutionLog2 to go back and see which reports were running during that time. I use the queries below to find problem reports. The last 2 queries would give you info on reports that might cause CPU spikes on the front end report servers.
--Top 10 Large Report by Bytes
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY ByteCount DESC
--Top 10 Large Reports by RowCount
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY [RowCount] DESC
--Top 10 Longest Processing Reports
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY TimeProcessing DESC
--Top 10 Longest Rendering Reports
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY TimeRendering DESC
December 14, 2011 at 4:48 pm
JeremyE (12/13/2011)
If you know when the CPU spikes occurred, you can use the view ReportServer.dbo.ExecutionLog2 to go back and see which reports were running during that time. I use the queries below to find problem reports. The last 2 queries would give you info on reports that might cause CPU spikes on the front end report servers.
--Top 10 Large Report by Bytes
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY ByteCount DESC
--Top 10 Large Reports by RowCount
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY [RowCount] DESC
--Top 10 Longest Processing Reports
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY TimeProcessing DESC
--Top 10 Longest Rendering Reports
SELECT TOP 10 * FROM ExecutionLog2
ORDER BY TimeRendering DESC
This is great, thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply