Too high TimeDataRetrieval(15 mins) of a report is making us restart the reporting services

Hi,

We have an SSRS report which is taking less than a minute to open on development server. Same report on production server is taking more than 15 minutes to retrieve the data; rendering and processing time is very small comparatively which is not an issue. When we restart the reporting services on the production server, retrieval time is reducing from 15 minutes to less than a minute.

Please help us in rectifying this critical issue.

Thanks in advance.

Re

November 14th, 2013 10:33am

Hi,

Waiting for a reply from any moderator or an SSRS expert.

Regards,

RamukNarik

Free Windows Admin Tool Kit Click here and download it now
November 15th, 2013 6:34am

Hi RamukNarik,

Sorry for my delay.

In SQL Server Reporting Services (SSRS), TimeDataRetrieval is includes time spent opening connections to the data source and time spent reading data rows from the data extension.

If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations.  If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

If the TimeDataRetrieval is high, the data source could be a bottleneck, or the queries retrieve lots of data.  If the there is a high RowCount, lots of data is being retrieved - we might want to review the dataset queries.  If high volumes of data are grouped, sorted, and aggregated, high EstimatedMemoryUsageKB values for Processing are very likely. More detail information you can refer to these articles about Analyzing and Optimizing Reports and Troubleshooting Report Performance.

Regards,
Alisa Tang

November 21st, 2013 5:35am

Hi Alisa,

Thanks a lot for your response.

Primarily, Too high TimeDataRetrieval issue is getting reduced/resolved by restarting the SSRS services. I would like to understand how the issue is getting resolved by restarting the services or what is causing the issue so that manual re-start of the service can be avoided.

Same report is displaying the data in less than a minute sometimes and taking around 15 mins sometimes.

Please provide your inputs to avoid the re-start of the SSRS service.

Dataset queries are getting executed in the SSMS very fast, there is no issue in retrieval of data on SSMS.

Sorry, I cannot post the queries due to security policies as these are business confidential.

Thanks,

RamukNarik

Free Windows Admin Tool Kit Click here and download it now
November 21st, 2013 12:46pm

Did you find any solution? We are having the same issue.

Thanks,

Ashwani

April 20th, 2015 3:12pm

I am experiencing exactly the same problem.  I have ruled out bad execution plans and parameter sniffing.  It is very strange that stopping and starting the report server resolves the issue.  There are several other threads on here with people reporting the s
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2015 9:03pm

Is this with MS SQL 2012? 
April 20th, 2015 9:03pm

Hi Ashwani,

I did not find any solution for it.

@LucasF - Yes, it is with MS SQL 2012.

Regards,

RamukNarik

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 12:37am

If you need to configure the amount of memory available to an instance of SSRS you have to get your hands dirty and edit the RsReportServer.config file. The RsReportServer.config file stores settings that are used by Report Manager, the Report Server Web service, and background processing.

The location of the rsconfile file is generally:

\Program Files\Microsoft SQL Server\MSRS1111.MSSQLSERVER\Reporting Services\ReportServer

Within the RsReportServer.config file, configuration settings that control memory allocation for the report server include WorkingSetMaximumWorkingSetMinimumMemorySafetyMargin, and MemoryThreshold. If you were to open the file you will see that the WorkingSetMinimum and WorkingSetMaximum are not present by default.

	<Service>
		<IsSchedulingService>True</IsSchedulingService>
		<IsNotificationService>True</IsNotificationService>
		<IsEventService>True</IsEventService>
		<PollingInterval>10</PollingInterval>
		<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
		<MemorySafetyMargin>80</MemorySafetyMargin>
		<MemoryThreshold>90</MemoryThreshold>
		<RecycleTime>720</RecycleTime>
		<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
		<MaxQueueThreads>0</MaxQueueThreads>

This is because you have to enter them yourself, as if you are hosting multiple applications on the same computer and you determine that the report server is using a disproportionate amount of system resources relative to other applications on the same computer.

	<Service>
		<IsSchedulingService>True</IsSchedulingService>
		<IsNotificationService>True</IsNotificationService>
		<IsEventService>True</IsEventService>
		<PollingInterval>10</PollingInterval>
		<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
		<MemorySafetyMargin>80</MemorySafetyMargin>
		<MemoryThreshold>90</MemoryThreshold>
		<RecycleTime>720</RecycleTime>
		<WorkingSetMaximum>786432</WorkingSetMaximum>
		<WorkingSetMinimum>524288</WorkingSetMinimum>
		<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
		<MaxQueueThreads>0</MaxQueueThreads>

The values that these two take are in KB. Once you have made your change save the file. Once you have saved, the service will restart and the changes will take place.

What is odd about this is that the two configs that are present by default in the file,  MemorySafetyMargin and MemoryThreshold, are percentages based on the two that we have added. Whether this means that they are ineffective until we add these two, or whether they specify in percentage the total amount of memory available to the server I dont know. However, MSDN states that:

MemoryThreshold: Specifies a percentage of WorkingSetMaximum that defines the boundary between high and medium pressure scenarios. If report server memory use reaches this value, the report server slows down request processing and changes the amount of memory allocated to different server applications. The default value is 90. This value should be greater than the value set forMemorySafetyMargin.

MemorySafetyMargin: Specifies a percentage of WorkingSetMaximum that defines the boundary between medium and low pressure scenarios. This value is the percentage of available memory that is reserved for the system and cannot be used for report server operations. The default value is 80.

May 5th, 2015 7:59am

Hi, 

I have been made aware of a possible solution to this issue.  We haven't tested this in our environment yet but will be doing so 26/7th of June.

Firstly Install Cumulative Update 2 for SQL 2012 SP2 (if thats the version you are running). 

Secondly,  Disable Watson logging in SSRS config as follows.

Modify the rsreportserver.config file as follows:

1. Open the rsreportserver.config file with Notepad (run as administrator) and find the following settings: <!-- <Add Key="ProcessTimeout" Value="150" /> --> <!-- <Add Key="ProcessTimeoutGcExtension" Value="30" /> --> <!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump--> <!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump --> <!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump--> <Add Key="WatsonFlags" Value="0x0428"/>

2. Modify the setting with the Value=0x0002 to not take a crash dump; new settings will be as follows: <!-- <Add Key="ProcessTimeout" Value="150" /> --> <!-- <Add Key="ProcessTimeoutGcExtension" Value="30" /> --> <!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump--> <!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump --> <!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump--> <Add Key="WatsonFlags" Value="0x0002 />

3. Restart Reporting services 

Free Windows Admin Tool Kit Click here and download it now
June 17th, 2015 9:39pm

Hi, 

I have been made aware of a possible solution to this issue.  We haven't tested this in our environment yet but will be doing so 26/7th of June.

Firstly Install Cumulative Update 2 for SQL 2012 SP2 (if thats the version you are running). 

Secondly,  Disable Watson logging in SSRS config as follows.

Modify the rsreportserver.config file as follows:

1. Open the rsreportserver.config file with Notepad (run as administrator) and find the following settings: <!-- <Add Key="ProcessTimeout" Value="150" /> --> <!-- <Add Key="ProcessTimeoutGcExtension" Value="30" /> --> <!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump--> <!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump --> <!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump--> <Add Key="WatsonFlags" Value="0x0428"/>

2. Modify the setting with the Value=0x0002 to not take a crash dump; new settings will be as follows: <!-- <Add Key="ProcessTimeout" Value="150" /> --> <!-- <Add Key="ProcessTimeoutGcExtension" Value="30" /> --> <!-- <Add Key="WatsonFlags" Value="0x0430" /> full dump--> <!-- <Add Key="WatsonFlags" Value="0x0428" /> minidump --> <!-- <Add Key="WatsonFlags" Value="0x0002" /> no dump--> <Add Key="WatsonFlags" Value="0x0002 />

3. Restart Reporting services 

June 17th, 2015 10:30pm

Parameter sniffing was the first thing I eliminated.  Rebooting the SQL Server is not the fix. All you have to do is stop and start the reporting services service and it goes away - which strongly suggests it is a report server issue and nothing to do with query plans.  It also doesn't make sense that at random times the SQL Server would suddenly start coming up with bad plans for a query that was running perfectly well before.  I understand that data and statistics change over time and this can cause plans to become stale but that wouldn't explain the consistency with which this problem occurs.  Not that I consider stopping and starting reporting services in our production environment an acceptable fix either.  I went so far as deleting the specific plans from the cache and it made no difference, it recompiled with the same plans each time.  The same plans which then run quickly after stopping and starting the report server!

Free Windows Admin Tool Kit Click here and download it now
June 18th, 2015 12:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics