April 25, 2017 at 11:31 am
I stuck in a very critical situation. Reporting services is consuming 90+CPU utilization. Few reports are taking so much time in rendering. I suspect, CPU is spiking high due to this.
Sometimes, We are not able to see any report request on server still CPU hits on 90+, 100.
So we need to identify the root cause of this. I suspect its happening due to high rendering time.
April 25, 2017 at 11:50 am
anujkumar.mca - Tuesday, April 25, 2017 11:31 AMI stuck in a very critical situation. Reporting services is consuming 90+CPU utilization. Few reports are taking so much time in rendering. I suspect, CPU is spiking high due to this.
Sometimes, We are not able to see any report request on server still CPU hits on 90+, 100.
So we need to identify the root cause of this. I suspect its happening due to high rendering time.
You could query the table runningjobs in the ReportServer database to get an idea of what is running now.
Rendering time isn't written until after the report has completed. You can find those and other statistics in the ExecutionLog views in the ReportServer database. Make sure to look at the size of the reports as well the execution stats. You also may want to see if you can use caching for any of the reports.
If you have a lot of on-demand report requests, the background reports can get a lower processing priority if you are experiencing memory pressure at the time of the interactive, on-demand reports.
You probably want to check the Report Server log for issues, errors as well.
Sue
April 25, 2017 at 12:15 pm
Thanks,
Checked same tables ad views(RunningJobs, ExecutionLogStorage, ExecutionLog2). but not getting any clear picture. not able to see any report request on transnational database.
If Reporting service is consuming high CPU, then how can I check below points.
1. Why its consuming high CPU ?
2. Currently which reports are running and in which state (data retrieval, rendering ).
3. Why internal proc ChecksessionLock is occurred automatically.
Thanks.
April 25, 2017 at 1:00 pm
Not sure what you mean by not able to see any "report requests on transnational database" - are you trying to view something related to a data source?
There nothing in any system to tell you exactly why something is acting a certain way - it's up to you to investigate.
Is all of SSRS installed on the same server? Are there any other applications on the server or other databases in the SQL Server?
How did you determine it was Reporting Services consuming the CPU? Do you know which component is consuming the CPU- report service, web service, database?
Have you used performance monitor to see some of the usage specific to Reporting Services?
How many and how often are the jobs executing and is there a large number of subscriptions? Do you have a lot of reports with subreports?
And you should always be checking the Report Server log when you have issues.
The ExecutionLog views do give you the information about the reports, statistics, rendering time, processing time, you can query to get the execution counts, etc. You will need to execute queries to get the information you need. If you just do a select * from ExecutionLog3, it's not going to tell you much but it has a wealth of information if you spend some time on understanding what you want from the view, what queries to write to get that information, etc.
CheckSessionLock is called by GetSessionData as it needs the session information for any processing, report requests because that's how Reporting Services was written.
Sue
April 26, 2017 at 10:39 am
Is it started around 2017-04-11?
In my system some of the reports take long long time and sometimes ended with timeout.
I suspect that some hidden updates of Microsoft are the root case.
(sql 2008 R2 SP3)
April 26, 2017 at 12:09 pm
izhar-azati - Wednesday, April 26, 2017 10:39 AMIs it started around 2017-04-11?
In my system some of the reports take long long time and sometimes ended with timeout.
I suspect that some hidden updates of Microsoft are the root case.
(sql 2008 R2 SP3)
Not likely related to updates - especially with it being on 2008 R2.
You really can't guess. You need to start by actually checking things like report statistics, Perf Mon counters, look at how all of Reporting Services is installed as you never did answer anything about that. You can get a general idea of overall report statistics since 4/11/2017 by executing the following in the Report Server database: SELECT ItemPath,
COUNT(TimeStart) Executions,
AVG([RowCount]) NumberOfRows,
AVG(ByteCount) Bytes,
AVG(TimeDataRetrieval)Retrieval,
AVG(TimeProcessing)Processing,
AVG(TimeRendering)Rendering
FROM ExecutionLog3
WHERE TimeStart >= '4/11/2017'
GROUP BY ItemPath
Times are in milliseconds
Null item paths are usually adhoc executions (i.e dynamically generated from drillthrough report) or a Report Builder report that is previewed on in the Report Builder on the client.
Sue
April 26, 2017 at 11:25 pm
Sue_H - Wednesday, April 26, 2017 12:09 PMizhar-azati - Wednesday, April 26, 2017 10:39 AMIs it started around 2017-04-11?
In my system some of the reports take long long time and sometimes ended with timeout.
I suspect that some hidden updates of Microsoft are the root case.
(sql 2008 R2 SP3)Not likely related to updates - especially with it being on 2008 R2.
You really can't guess. You need to start by actually checking things like report statistics, Perf Mon counters, look at how all of Reporting Services is installed as you never did answer anything about that. You can get a general idea of overall report statistics since 4/11/2017 by executing the following in the Report Server database:
SELECT ItemPath,
COUNT(TimeStart) Executions,
AVG([RowCount]) NumberOfRows,
AVG(ByteCount) Bytes,
AVG(TimeDataRetrieval)Retrieval,
AVG(TimeProcessing)Processing,
AVG(TimeRendering)Rendering
FROM ExecutionLog3
WHERE TimeStart >= '4/11/2017'
GROUP BY ItemPathTimes are in milliseconds
Null item paths are usually adhoc executions (i.e dynamically generated from drillthrough report) or a Report Builder report that is previewed on in the Report Builder on the client.Sue
Sue_H,
This is a established environment. but we are facing issue in this month-end only.
Server A - has reporting services, Report DB and ReportTempDB,
Server B - has source database (transnational), where reports hit to get the data (both server has high end configuration - 128 GB RAM, 32 core CPU).
1. Reports are retrieving the data in Sec. but rendering time is high. We retrieving the information from below query
SELECT c.Name,ByteCount/1024/1024 as ByteCount_MB, [RowCount],TimeStart,TimeEnd,TimeDataRetrieval/1000/60 TimeDataRetrieval_MIN
,TimeProcessing/1000/60 TimeProcessing_MIN
,TimeRendering/1000/60 TimeRendering_MIN
FROM dbo.ExecutionLog el
INNER JOIN dbo.Catalog c
ON c.ItemID = el.ReportID
WHERE TimeStart BETWEEN '2017-04-27 9:30:48.887' AND '2017-04-27 18:50:48.887'
AND ((TimeProcessing/1000/60)>1 OR (TimeRendering/1000/60) >1)
----AND Name LIKE '%INBEV101%'
ORDER BY
(TimeRendering/1000/60) desc
2. Most of the time, we don't see any request from report server (Server A) at Server B (Source data server). but still reporting services consume high CPU approx (80-90 +)
3.
We suspect that reports are caught in rendering at Server A and making queue. That's why Reporting service consuming high CPU
."
So Is it possible, we can get information which reports are currently rendering ? and Can we clear rendering queue ?
April 27, 2017 at 9:56 pm
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply