-
Notifications
You must be signed in to change notification settings - Fork 133
/
Examples.sql
158 lines (122 loc) · 5.55 KB
/
Examples.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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
/*
███████╗██╗ ██╗ █████╗ ███╗ ███╗██████╗ ██╗ ███████╗
██╔════╝╚██╗██╔╝██╔══██╗████╗ ████║██╔══██╗██║ ██╔════╝
█████╗ ╚███╔╝ ███████║██╔████╔██║██████╔╝██║ █████╗
██╔══╝ ██╔██╗ ██╔══██║██║╚██╔╝██║██╔═══╝ ██║ ██╔══╝
███████╗██╔╝ ██╗██║ ██║██║ ╚═╝ ██║██║ ███████╗███████╗
╚══════╝╚═╝ ╚═╝╚═╝ ╚═╝╚═╝ ╚═╝╚═╝ ╚══════╝╚══════╝
██████╗ █████╗ ██╗ ██╗ ███████╗
██╔════╝██╔══██╗██║ ██║ ██╔════╝
██║ ███████║██║ ██║ ███████╗
██║ ██╔══██║██║ ██║ ╚════██║
╚██████╗██║ ██║███████╗███████╗███████║
╚═════╝╚═╝ ╚═╝╚══════╝╚══════╝╚══════╝
Copyright 2024 Darling Data, LLC
https://www.erikdarling.com/
For support, head over to GitHub:
https://github.com/erikdarlingdata/DarlingData
*/
/*Get help!*/
EXEC dbo.sp_QuickieStore
@help = 1;
/*Find top 10 sorted by memory*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10;
/*Search for specific query_ids*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10,
@include_query_ids = '13977, 13978';
/*Search for specific plan_ids*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10,
@start_date = '20210320',
@include_plan_ids = '1896, 1897';
/*Ignore for specific query_ids*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10,
@ignore_query_ids = '13977, 13978';
/*Ignore for specific plan_ids*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10,
@start_date = '20210320',
@ignore_plan_ids = '1896, 1897';
/*Search for queries within a date range*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10,
@start_date = '20210320',
@end_date = '20210321';
/*Search for queries with a minimum execution count*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10,
@execution_count = 10;
/*Search for queries over a specific duration*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10,
@duration_ms = 10000;
/*Search for queries with a specific execution type*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@top = 10,
@execution_type_desc = 'aborted';
/*Search for a specific stored procedure*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@procedure_name = 'top_percent_sniffer';
/*Search for specific query text*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@query_text_search = 'WITH Comment'
/*Only return queries with query hints (2022+)*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@only_query_with_hints = 1;
/*Use expert mode to return additional columns*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10,
@expert_mode = 1;
/*Use format output to add commas to larger numbers*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@sort_order = 'memory',
@top = 10,
@format_output = 1;
/*Use wait filter to search for queries responsible for high waits*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@wait_filter = 'memory',
@sort_order = 'memory';
/*Search by query hashes*/
EXEC dbo.sp_QuickieStore
@include_query_hashes = '0x1AB614B461F4D769,0x1CD777B461F4D769';
/*Search by plan hashes*/
EXEC dbo.sp_QuickieStore
@include_plan_hashes = '0x6B84B820B8B38564,0x6B84B999D7B38564';
/*Search by SQL Handles
Do you need to find if one Query Store is tracking the same query that is present in another database's Query Store? If so, use the statement_sql_handle to do that.
This helps with scenarios where you have multiple production databases which have the same schema and you want to compare performance across Query Stores.
*/
EXEC dbo.sp_QuickieStore
@include_sql_handles =
'0x0900F46AC89E66DF744C8A0AD4FD3D3306B90000000000000000000000000000000000000000000000000000,0x0200000AC89E66DF744C8A0AD4FD3D3306B90000000000000000000000000000000000000000000000000000';
/*Troubleshoot performance*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@troubleshoot_performance = 1;
/*Debug dynamic SQL and temp table contents*/
EXEC dbo.sp_QuickieStore
@database_name = 'StackOverflow2013',
@debug = 1;