Created
December 21, 2015 07:37
-
-
Save ghotz/341d6b470cb438e58dc7 to your computer and use it in GitHub Desktop.
Reporting Services execution log queries
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH cte AS | |
| ( | |
| SELECT * | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Pagination)[1]', 'bigint') AS pagination_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]', 'bigint') AS pagination_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Rendering)[1]', 'bigint') AS rendering_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]', 'bigint') AS rendering_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Processing)[1]', 'bigint') AS processing_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'bigint') AS processing_estimated_kb | |
| FROM ExecutionLog2 | |
| ) | |
| SELECT | |
| CONVERT(varchar(8), TimeStart, 112) AS [start_date] | |
| , DATEPART(hour, TimeStart) AS start_hour | |
| , COUNT(*) as num_executions | |
| , CAST(MIN(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS min_TotalTime_mins | |
| , CAST(AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS avg_TotalTime_mins | |
| , CAST(MAX(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS max_TotalTime_mins | |
| , CAST(MIN(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS min_MByteCount | |
| , CAST(AVG(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS avg_MByteCount | |
| , CAST(MAX(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS max_MByteCount | |
| , CAST(SUM(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS sum_MByteCount | |
| , MIN(TimeDataRetrieval) AS min_TimeDataRetrieval_ms | |
| , AVG(TimeDataRetrieval) AS avg_TimeDataRetrieval_ms | |
| , MAX(TimeDataRetrieval) AS max_TimeDataRetrieval_ms | |
| , MIN(TimeProcessing) AS min_TimeProcessing_ms | |
| , AVG(TimeProcessing) AS avg_TimeProcessing_ms | |
| , MAX(TimeProcessing) AS max_TimeProcessing_ms | |
| , MIN(TimeRendering) AS min_TimeRendering_ms | |
| , AVG(TimeRendering) AS avg_TimeRendering_ms | |
| , MAX(TimeRendering) AS max_TimeRendering_ms | |
| , MIN(TimeDataRetrieval + TimeProcessing + TimeRendering) AS min_TotalTime_ms | |
| , AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) AS avg_TotalTime_ms | |
| , MAX(TimeDataRetrieval + TimeProcessing + TimeRendering) AS max_TotalTime_ms | |
| , MIN(ByteCount) AS min_ByteCount | |
| , AVG(ByteCount) AS avg_ByteCount | |
| , MAX(ByteCount) AS max_ByteCount | |
| , SUM(ByteCount) AS sum_ByteCount | |
| , MIN([RowCount]) AS min_RowCount | |
| , AVG([RowCount]) AS avg_RowCount | |
| , MAX([RowCount]) AS max_RowCount | |
| , SUM([RowCount]) AS sum_RowCount | |
| , MIN(pagination_scalability_ms) AS min_pagination_scalability_ms | |
| , AVG(pagination_scalability_ms) AS avg_pagination_scalability_ms | |
| , MAX(pagination_scalability_ms) AS max_pagination_scalability_ms | |
| , MIN(rendering_scalability_ms) AS min_rendering_scalability_ms | |
| , AVG(rendering_scalability_ms) AS avg_rendering_scalability_ms | |
| , MAX(rendering_scalability_ms) AS max_rendering_scalability_ms | |
| , MIN(processing_scalability_ms) AS min_processing_scalability_ms | |
| , AVG(processing_scalability_ms) AS avg_processing_scalability_ms | |
| , MAX(processing_scalability_ms) AS max_processing_scalability_ms | |
| , MIN(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS min_total_scalability_ms | |
| , AVG(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS avg_total_scalability_ms | |
| , MAX(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS max_total_scalability_ms | |
| , MIN(pagination_estimated_kb) AS min_pagination_estimated_kb | |
| , AVG(pagination_estimated_kb) AS avg_pagination_estimated_kb | |
| , MAX(pagination_estimated_kb) AS max_pagination_estimated_kb | |
| , SUM(pagination_estimated_kb) AS sum_pagination_estimated_kb | |
| , MIN(rendering_estimated_kb) AS min_rendering_estimated_kb | |
| , AVG(rendering_estimated_kb) AS avg_rendering_estimated_kb | |
| , MAX(rendering_estimated_kb) AS max_rendering_estimated_kb | |
| , SUM(rendering_estimated_kb) AS sum_rendering_estimated_kb | |
| , MIN(processing_estimated_kb) AS min_processing_estimated_kb | |
| , AVG(processing_estimated_kb) AS avg_processing_estimated_kb | |
| , MAX(processing_estimated_kb) AS max_processing_estimated_kb | |
| , SUM(processing_estimated_kb) AS sum_processing_estimated_kb | |
| , CAST(MIN(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. AS decimal(15, 2)) AS min_total_estimated_mb | |
| , CAST(AVG(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. AS decimal(15, 2)) AS avg_total_estimated_mb | |
| , CAST(MAX(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. AS decimal(15, 2)) AS max_total_estimated_mb | |
| , CAST(SUM(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. AS decimal(15, 2)) AS sum_total_estimated_mb | |
| , CAST(MIN(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS min_total_scalability_mins | |
| , CAST(AVG(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS avg_total_scalability_mins | |
| , CAST(MAX(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS max_total_scalability_mins | |
| FROM cte | |
| WHERE [Status] = 'rsSuccess' | |
| GROUP BY | |
| CONVERT(varchar(8), TimeStart, 112) | |
| , DATEPART(hour, TimeStart) | |
| ORDER BY avg_TotalTime_ms DESC |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH cte AS | |
| ( | |
| SELECT InstanceName, ReportPath, UserName, ExecutionId, RequestType, [Format], [Parameters], ReportAction, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing, TimeRendering, [Source], [Status], ByteCount, [RowCount] | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Pagination)[1]', 'bigint') AS pagination_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]', 'bigint') AS pagination_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Rendering)[1]', 'bigint') AS rendering_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]', 'bigint') AS rendering_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Processing)[1]', 'bigint') AS processing_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'bigint') AS processing_estimated_kb | |
| FROM ExecutionLog2 | |
| ) | |
| SELECT * | |
| FROM cte | |
| WHERE [Status] <> 'rsSuccess' |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH cte AS | |
| ( | |
| SELECT * | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Pagination)[1]', 'bigint') AS pagination_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]', 'bigint') AS pagination_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Rendering)[1]', 'bigint') AS rendering_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]', 'bigint') AS rendering_estimated_kb | |
| , AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Processing)[1]', 'bigint') AS processing_scalability_ms | |
| , AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'bigint') AS processing_estimated_kb | |
| FROM ExecutionLog2 | |
| ) | |
| SELECT | |
| ReportPath | |
| , RequestType | |
| , [Format] AS ReportFormat | |
| , ReportAction | |
| , COUNT(*) as num_executions | |
| , CAST(MIN(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS min_TotalTime_mins | |
| , CAST(AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS avg_TotalTime_mins | |
| , CAST(MAX(TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000. / 60. AS decimal(15, 2)) AS max_TotalTime_mins | |
| , CAST(MIN(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS min_MByteCount | |
| , CAST(AVG(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS avg_MByteCount | |
| , CAST(MAX(ByteCount) / 1024. / 1024. AS decimal(15, 2)) AS max_MByteCount | |
| , MIN(TimeDataRetrieval) AS min_TimeDataRetrieval_ms | |
| , AVG(TimeDataRetrieval) AS avg_TimeDataRetrieval_ms | |
| , MAX(TimeDataRetrieval) AS max_TimeDataRetrieval_ms | |
| , MIN(TimeProcessing) AS min_TimeProcessing_ms | |
| , AVG(TimeProcessing) AS avg_TimeProcessing_ms | |
| , MAX(TimeProcessing) AS max_TimeProcessing_ms | |
| , MIN(TimeRendering) AS min_TimeRendering_ms | |
| , AVG(TimeRendering) AS avg_TimeRendering_ms | |
| , MAX(TimeRendering) AS max_TimeRendering_ms | |
| , MIN(TimeDataRetrieval + TimeProcessing + TimeRendering) AS min_TotalTime_ms | |
| , AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) AS avg_TotalTime_ms | |
| , MAX(TimeDataRetrieval + TimeProcessing + TimeRendering) AS max_TotalTime_ms | |
| , MIN(ByteCount) AS min_ByteCount | |
| , AVG(ByteCount) AS avg_ByteCount | |
| , MAX(ByteCount) AS max_ByteCount | |
| , MIN([RowCount]) AS min_RowCount | |
| , AVG([RowCount]) AS avg_RowCount | |
| , MAX([RowCount]) AS max_RowCount | |
| , MIN(pagination_scalability_ms) AS min_pagination_scalability_ms | |
| , AVG(pagination_scalability_ms) AS avg_pagination_scalability_ms | |
| , MAX(pagination_scalability_ms) AS max_pagination_scalability_ms | |
| , MIN(rendering_scalability_ms) AS min_rendering_scalability_ms | |
| , AVG(rendering_scalability_ms) AS avg_rendering_scalability_ms | |
| , MAX(rendering_scalability_ms) AS max_rendering_scalability_ms | |
| , MIN(processing_scalability_ms) AS min_processing_scalability_ms | |
| , AVG(processing_scalability_ms) AS avg_processing_scalability_ms | |
| , MAX(processing_scalability_ms) AS max_processing_scalability_ms | |
| , MIN(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS min_total_scalability_ms | |
| , AVG(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS avg_total_scalability_ms | |
| , MAX(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) AS max_total_scalability_ms | |
| , MIN(pagination_estimated_kb) AS min_pagination_estimated_kb | |
| , AVG(pagination_estimated_kb) AS avg_pagination_estimated_kb | |
| , MAX(pagination_estimated_kb) AS max_pagination_estimated_kb | |
| , MIN(rendering_estimated_kb) AS min_rendering_estimated_kb | |
| , AVG(rendering_estimated_kb) AS avg_rendering_estimated_kb | |
| , MAX(rendering_estimated_kb) AS max_rendering_estimated_kb | |
| , MIN(processing_estimated_kb) AS min_processing_estimated_kb | |
| , AVG(processing_estimated_kb) AS avg_processing_estimated_kb | |
| , MAX(processing_estimated_kb) AS max_processing_estimated_kb | |
| , CAST(MIN(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. / 1024. AS decimal(15, 2)) AS min_total_estimated_mb | |
| , CAST(AVG(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. / 1024. AS decimal(15, 2)) AS avg_total_estimated_mb | |
| , CAST(MAX(pagination_estimated_kb + rendering_estimated_kb + processing_estimated_kb) / 1024. / 1024. AS decimal(15, 2)) AS max_total_estimated_mb | |
| , CAST(MIN(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS min_total_scalability_mins | |
| , CAST(AVG(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS avg_total_scalability_mins | |
| , CAST(MAX(pagination_scalability_ms + rendering_scalability_ms + processing_scalability_ms) / 1000. / 60. AS decimal(15, 2)) AS max_total_scalability_mins | |
| FROM cte | |
| WHERE [Status] = 'rsSuccess' | |
| GROUP BY | |
| ReportPath | |
| , RequestType | |
| , [Format] | |
| , ReportAction | |
| ORDER BY avg_TotalTime_ms DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment