-
Notifications
You must be signed in to change notification settings - Fork 0
/
sp_exec_stats.sql
104 lines (100 loc) · 2.95 KB
/
sp_exec_stats.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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[sp_dependencies] Script Date: 13/06/2017 00:59:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_exec_stats] @ObjectName sysname,
@Depth int = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ObjectID int = OBJECT_ID(@ObjectName)
;WITH DependenciesCTE
(
fromName,
fromID,
fromDesc,
[From Last Execution Time],
[From Last Execution Total Duration],
[From CPU time (μs)],
[From Execs since compile],
toName,
toID,
toDesc,
[To Last Execution Time],
[To Last Execution Total Duration],
[To CPU time (μs)],
[To Execs since compile],
[Level]
)
AS
(
SELECT @ObjectName referencing_name,
referencing_id,
O1.type_desc,
S1.last_execution_time,
S1.last_elapsed_time,
S1.last_worker_time,
S1.execution_count,
referenced_entity_name,
OBJECT_ID(referenced_entity_name) referenced_id,
O2.type_desc,
S2.last_execution_time [Last Execution Time],
S2.last_elapsed_time [Last Execution Total Duration],
S2.last_worker_time [CPU time (μs)],
S2.execution_count [Execs since compile],
0
FROM sys.sql_expression_dependencies D
LEFT JOIN sys.objects O1 ON O1.object_id = D.referencing_id
LEFT JOIN sys.objects O2 ON O2.object_id = OBJECT_ID(D.referenced_entity_name)
LEFT JOIN sys.dm_exec_procedure_stats S1 ON O1.object_id = S1.object_id
LEFT JOIN sys.dm_exec_procedure_stats S2 ON O2.object_id = S2.object_id
WHERE D.referencing_id = @ObjectID
AND D.referenced_class = 1
AND D.referencing_minor_id = 0
AND O2.type IN ('P', 'FN', 'IF')
UNION ALL
SELECT C.toName,
C.toID,
O1.type_desc,
S1.last_execution_time [Last Execution Time],
S1.last_elapsed_time [Last Execution Total Duration],
S1.last_worker_time [CPU time (μs)],
S1.execution_count [Execs since compile],
D.referenced_entity_name,
OBJECT_ID(D.referenced_entity_name) referenced_id,
O2.type_desc,
S2.last_execution_time [Last Execution Time],
S2.last_elapsed_time [Last Execution Total Duration],
S2.last_worker_time [CPU time (μs)],
S2.execution_count [Execs since compile],
C.Level + 1
FROM sys.sql_expression_dependencies D
JOIN DependenciesCTE C ON D.referencing_id = C.toID
INNER JOIN sys.objects O1 ON O1.object_id = D.referencing_id
INNER JOIN sys.objects O2 ON O2.object_id = OBJECT_ID(D.referenced_entity_name)
INNER JOIN sys.dm_exec_procedure_stats S1 ON O1.object_id = S1.object_id
INNER JOIN sys.dm_exec_procedure_stats S2 ON O2.object_id = S2.object_id
WHERE C.Level < ISNULL(@Depth, C.Level+1)
AND D.referenced_class = 1
AND D.referencing_minor_id = 0
AND O2.type IN ('P', 'FN', 'IF')
)
SELECT fromName,
fromID,
fromDesc,
[From Last Execution Time],
[From Last Execution Total Duration],
[From CPU time (μs)],
[From Execs since compile],
toName,
toID,
toDesc,
[To Last Execution Time],
[To Last Execution Total Duration],
[To CPU time (μs)],
[To Execs since compile],
[Level]
FROM DependenciesCTE