-
Notifications
You must be signed in to change notification settings - Fork 4
/
SSASQueryAnalyzerDatabaseBatchCalculateCommonAggregationsRead.sql
71 lines (70 loc) · 2.8 KB
/
SSASQueryAnalyzerDatabaseBatchCalculateCommonAggregationsRead.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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
BEGIN
SET NOCOUNT ON;
WITH [cte_aggregations] AS
(
SELECT
[ObjectPath],
[AggregationID] = LEFT(CONVERT(NVARCHAR(MAX), [TextData]), CHARINDEX(CHAR(10), CONVERT(NVARCHAR(MAX), [TextData])) - 1),
[AggregationObjectPath] = [ObjectPath] + '.' + LEFT(CONVERT(NVARCHAR(MAX), [TextData]), CHARINDEX(CHAR(10), CONVERT(NVARCHAR(MAX), [TextData])) - 1)
FROM
[asqa].[Trace]
WHERE
[ExecutionID] = @executionID AND
[EventClass] = N'GetDataFromAggregation'
),
[cte_events] AS
(
SELECT
t.[ObjectName],
t.[ObjectPath],
t.[Duration]
FROM
[asqa].[Trace] AS t
INNER JOIN
[cte_aggregations] AS a
ON
t.[ObjectID] = a.[AggregationID] AND
t.[ObjectPath] = a.[AggregationObjectPath]
WHERE
t.[ExecutionID] = @executionID AND
t.[EventClass] = N'ProgressReportEnd' AND
t.[EventSubclass] = N'Query'
),
[cte_duration_total] AS
(
SELECT
[DurationTotal] = SUM(t.[Duration])
FROM
[cte_events] AS t
)
INSERT INTO [asqa].[CommonAggregationsRead]
(
[ExecutionID]
,[CubeID]
,[MeasureGroupID]
,[PartitionID]
,[AggregationID]
,[AggregationName]
,[AggregationHit]
,[AggregationDuration]
,[AggregationDurationRatio]
)
SELECT
[ExecutionID] = @executionID,
[CubeID] = PARSENAME(SUBSTRING(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])), CHARINDEX('.', SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath]))) + 1, LEN(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])))), 4),
[MeasureGroupID] = PARSENAME(SUBSTRING(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])), CHARINDEX('.', SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath]))) + 1, LEN(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])))), 3),
[PartitionID] = PARSENAME(SUBSTRING(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])), CHARINDEX('.', SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath]))) + 1, LEN(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])))), 2),
[AggregationID] = PARSENAME(SUBSTRING(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])), CHARINDEX('.', SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath]))) + 1, LEN(SUBSTRING([ObjectPath], CHARINDEX('.', [ObjectPath]) + 1, LEN([ObjectPath])))), 1),
[AggregationName] = [ObjectName],
[AggregationHit] = COUNT(*),
[AggregationDuration] = CONVERT(TIME, DATEADD(MS, SUM([Duration]), 0)),
[AggregationDurationRatio] = ROUND(IIF([DurationTotal] = 0, CONVERT(FLOAT, 0), SUM([Duration]) * 100 / CONVERT(FLOAT, [DurationTotal])), 2)
FROM
[cte_events]
CROSS JOIN
[cte_duration_total] -- TODO: sistemare
GROUP BY
[ObjectName],
[ObjectPath],
[DurationTotal]
END