Sunday, February 16, 2014

AX 2012 SSRS Improve Performance - PART I

Hi

By using the execution log tables in the ReportServer database, an administrator can determine the run times of reports.

The following query provides the total time, data retrieval time, processing time, rendering time, Report Definition Customization Extension (RDCE) snapshot generation time, and more for every time that a report has run.

SELECT
e.timestart,
DATEDIFF(ms, e.[timestart], e.[timeend]) AS [TotalTime],
e.[timedataretrieval] AS [DataRetrievalTime],
e.[timeprocessing] AS [ProcessingTime],
e.[timerendering] AS [RenderingTime],
COALESCE ([AdditionalInfo].value('(/AdditionalInfo/RdceSnapshotGenerationTime//node())[1]', 'int'), 0) AS [RDCESnapshotGenTime],
e.*
FROM
[executionlog2] AS e
--WHERE e.[timestart] BETWEEN @basedate AND DATEADD(hh,25,@basedate)
ORDER BY 1 DESC;

The column of interest would be TimeDataRetrieval, TimeProcessing, TimeRendering.
Here is some site where you can analyse your results:

ExecutionLog2 View - Analyzing and Optimizing Reports

Report Server Execution Log and the ExecutionLog3 View

SQL 2008 R2 RTM! Time to look at some new Execution Log Reports
 


That's it!

 


No comments: