SSRS Reports 2008 Performance Optimization case

One of our reporting service services has more SSRS reports deployed, one of which has a relatively long execution time after the SSRS report is deployed, and the vendor has nested reporting service reports inside the ASP. Make users very dissatisfied with the report response speed, so and several colleagues studied how to locate and optimize SSRS report performance.

Case Environment:

Operating System: Windows Server R2 Standard SP1

Database version: SQL Server R2 (SP2)-10.50.4000.0 (X64)

description of the phenomenon:

After synthesizing the feedback from users and developers, it was found that the reports of other systems deployed on the SSRS server responded very quickly and tested several of the reports found to be within 1-3 seconds, but the SSRS report for this system (module) was all slow and basically 8 seconds or more. And the first visit is very slow, if the refresh or second access is very fast, but it is also very slow if you modify the report URL parameter. So I took one of those reports as an example, to view the execution log information for the report, as shown below, we view the execution record of the report wf_markerroom_report by executionlog the Catalog Association. Please refer to reporting Services execution and trace logging for specific details.

 Use [ReportServer];

GO

SELECT  as ReportName

 as ReportID

 as UserName

 as Format

, E.Parameters as Parameters

 as Timestart

 as Timeend

 as Timedataretrieval

 as Timeprocessing

 as Timerendering

, DATEDIFF (SECOND, Timestart, Timeend)

 as Costtime

 from  with (NOLOCK)

INNER JOIN Reportserver.dbo. Catalog  with (NOLOCK)  on E.reportid = C.itemid

WHERE C.name =' Wf_markerroom_report '

 and CAST (' 2014-12-25 00:00 '  as DATETIME)

 and CAST (' 2014-12-25 '  as DATETIME)

ORDER  by Timestart DESC

Partial Execution Results

From the above you can see that the time of the report is consumed on Timedataretrieval, Timedataretrieval is the number of milliseconds that SSRS uses to retrieve data, process reports, and render reports (in SQL, I convert to seconds), So we first suspect that the report is the SQL statement performance problem, so the report involved in the SQL statements, stored procedures all take out the validation test, the results of the test found that all SQL statement execution time of hundreds of milliseconds, not more than 1 seconds, this idea and validation results are very large and Then again, whether because SSRS reports are incoming stored procedure parameters to get data, whether because "parameter sniffing" results in the test result is different, so modify, verify that the test results are still less than a second. You can then conclude that the problem is still on SSRS and that you have previously encountered cases where the report timed out because of security validation, but the SSRS report is still slow in addition to this module. Other module reports are very fast, and if it is a security verification issue, there should be other reporting speed problems. Is very puzzled, also checked a lot of settings, still no answer.

Where does the problem really go? After a careful comparison of the abuse of heart, actually found other modules of the report, in the data source settings using SQL authentication method to connect to the database, and this module using the Windows authentication method to access the database, so I tried to connect the data source of the report to a SQL Certified account, from the Windows Authentication using a domain account changed to SQL authentication

As shown above, the speed results of testing SSRS reports as well as the timedataretrieval time is surprising, and there are discussions at the official website forum: Performance Issue with Shared datasources using Windows vs SQL Authentication should be using Windows Authentication (Windows authentication using a domain account) that involves encryption, domain ID authentication, and so on. Of course, due to the fact that SSRS is not very deep, and can not be analyzed more deeply, in the official document "Performance comparison: Security design choices (build distributed applications)", we could see different authentication methods of response time is not the same. I think SSRS should be no exception.

Resources:

Http://msdn.microsoft.com/zh-cn/library/bb934330.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/43d09604-cc7a-479e-810f-a141f5f402f0/ Performance-issue-with-shared-datasources-using-windows-vs-sql-authentication?forum=sqlreportingservices

SSRS Reports 2008 Performance Optimization case