July 27, 2018 at 9:20 am
Hi All,
I need some advice. My SSRS reports are running extremely slow. I have ran execution plans on SP's, added indexes to tables, rebuild stats and indexes, looked for long running queries. I have plenty of memory on the SQL Server, 256GB. I have plenty of space on all my drives, tempdb is not full, ran DBCC FREEPROCCACHE. Any ideas of what else I can look at to solve this mystery?
MCSE SQL Server 2012\2014\2016
July 27, 2018 at 10:51 am
lkennedy76 - Friday, July 27, 2018 9:20 AMHi All,I need some advice. My SSRS reports are running extremely slow. I have ran execution plans on SP's, added indexes to tables, rebuild stats and indexes, looked for long running queries. I have plenty of memory on the SQL Server, 256GB. I have plenty of space on all my drives, tempdb is not full, ran DBCC FREEPROCCACHE. Any ideas of what else I can look at to solve this mystery?
Did you check the ExecutionLog3 view? That breaks down time for Retrieval, processing and rendering for the report. The additionalInfo column has information on memory usage and pagination.
You would also want to check the Reporting Services log file for any issues, errors, system problems reported.
Sue
July 27, 2018 at 12:09 pm
Not seeing any error in the log files. What should I look for in the executionlog3?
MCSE SQL Server 2012\2014\2016
July 27, 2018 at 12:31 pm
lkennedy76 - Friday, July 27, 2018 12:09 PMNot seeing any error in the log files. What should I look for in the executionlog3?
The majority of the time there are errors or warnings in the Reporting Services log.
You would look at the columns mentioned - TimeDataRetrieval, TimeProcessing, TimeRendering, AdditionalInfo and work on identifying which piece of the process is taking the most time.
You can also write other queries against a view to analyze things such as which reports are executed the most and check stats on that report specifically, finding what the total times are for the reports using datediff and the start and end times, if it goes back far enough you can query the trends if any of the pieces are increasing or have a sudden increase. If 's data retrieval for a few reports that is taking the most time, are they using the same data sources. There are a lot of different queries you can write to analyze the data. I don't think there is an exhaustive list anywhere as so much depends on what you find along the way. This post should give you more ideas and explains a bit more on the columns in the view. It is for an earlier version of the Execution log but everything in there applies to ExecutionLog3:
ExecutionLog2 View – Analyzing and Optimizing Reports
Sue
July 28, 2018 at 12:26 pm
I ran this little nugget and found what reports where still running. the server was overloaded with agent jobs and other people running long queries. Once everything settled down and I rebuilt the indexes again, I started up SSRS services and reports started screaming out. Thanks for all the help, it was very useful.
USE ReportServer;DECLARE @cutoff DATETIME;SET @cutoff = DATEADD(minute, -5, GETDATE()); --Five minutes agoSELECT JobID, JobType, JobStatus, RequestName, StartDate FROM RunningJobsWHERE StartDate < @cutoff --prunes records that haven't run for five minutes yetORDER BY StartDate --oldest first
MCSE SQL Server 2012\2014\2016
July 28, 2018 at 12:38 pm
and I had some heap fragmentation, I had to add a clustered index drop it and run this query;
Alter table tablename rebuild
and ran the query below to check for any tables fragmented;
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;
MCSE SQL Server 2012\2014\2016
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply