-
Notifications
You must be signed in to change notification settings - Fork 4
/
SSASQueryAnalyzerDatabaseBatchCalculateCommonPartitionsRead.sql
47 lines (44 loc) · 1.45 KB
/
SSASQueryAnalyzerDatabaseBatchCalculateCommonPartitionsRead.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
BEGIN
SET NOCOUNT ON;
DECLARE @durationTotal BIGINT;
SELECT
@durationTotal = SUM([Duration])
FROM
[asqa].[Trace]
WHERE
[ExecutionID] = @executionID AND
[EventClass] = N'ProgressReportEnd' AND
[EventSubclass] = N'Query' AND
[ObjectReference] LIKE '%<PartitionID>%'-- [ObjectType] = N'Partition' -- TOFIX
INSERT INTO [asqa].[CommonPartitionsRead]
(
[ExecutionID]
,[CubeID]
,[MeasureGroupID]
,[PartitionID]
,[PartitionName]
,[PartitionHit]
,[PartitionDuration]
,[PartitionDurationRatio]
)
SELECT
[ExecutionID] = @executionID,
[CubeID] = CONVERT(XML, [ObjectReference]).value(N'(/Object/CubeID)[1]', N'NVARCHAR(256)'),
[MeasureGroupID] = CONVERT(XML, [ObjectReference]).value(N'(/Object/MeasureGroupID)[1]', N'NVARCHAR(256)'),
[PartitionID] = CONVERT(XML, [ObjectReference]).value(N'(/Object/PartitionID)[1]', N'NVARCHAR(256)'),
[PartitionName] = [ObjectName],
[PartitionHit] = COUNT(*),
[PartitionDuration] = CONVERT(TIME, DATEADD(MS, SUM([Duration]), 0)),
[PartitionDurationRatio] = ROUND(IIF(@durationTotal = 0, CONVERT(FLOAT, 0), SUM([Duration]) * 100 / CONVERT(FLOAT, @durationTotal)), 2)
FROM
[asqa].[Trace]
WHERE
[ExecutionID] = @executionID AND
[EventClass] = N'ProgressReportEnd' AND
[EventSubclass] = N'Query' AND
[ObjectReference] LIKE '%<PartitionID>%'-- [ObjectType] = N'Partition' -- TOFIX
GROUP BY
[ObjectID],
[ObjectName],
[ObjectReference]
END