How can we check which report is currently in rendering state in SSRS 2008

Question

How can we check which report is currently in rendering state in SSRS 2008

anujkumar.mca

SSChasing Mays

Points: 621
More actions
April 25, 2017 at 11:31 am

#324891

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

anujkumar.mca - Tuesday, April 25, 2017 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.

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

anujkumar.mca SSChasing Mays Points: 621 More actions · Answer 2

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.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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

izhar-azati SSC Veteran Points: 214 More actions · Answer 4

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)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

izhar-azati - Wednesday, April 26, 2017 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)

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

anujkumar.mca SSChasing Mays Points: 621 More actions · Answer 6

Sue_H - Wednesday, April 26, 2017 12:09 PM
izhar-azati - Wednesday, April 26, 2017 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)
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

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 ?

Sue_H SSC Guru Points: 90860 More actions · Answer 7

No there is nothing that tells each stage of currently running reports. I've never heard of clearing a rendering queue in SSRS - didn't even know there was such a thing.
Subscriptions, scheduled reports are queued in a sense and having a large number of those at the same time can hit your CPU hard due to how they are processed but I don't know anything about the rendering queue or how you came up with that suspicion of the cause of the issues. So I would guess you have identified which reports are causing the most problems and checked the additional info column to eliminate any pagination issues which can cause slow rending. And then the size of the reports is not an issue as well. Scalability time can be high at the end of the month due to the load on the server but I would guess you ruled that out as well and there are no differences in overall execution counts being higher, no reports having higher executions counts, nothing in the log and nothing with the app domains cycling more often, the byte count is appropriate for the output type. Just a general issue with all reports having slow rendering times. How much higher are they now are the rendering times when compared to other times of the month? If you are using the defaults for the execution log you can compare those and even look at the same time frame last month. There are a lot of things to look at which can lead to some issues, especially end of month.
It might be good to look at things other than the rendering queue and that causing CPU issues. Sometimes when we assume what the problem is, we go about to prove how that is the problem. And never end up finding the problem.

Sue