forked from BrentOzarULTD/SQL-Server-First-Responder-Kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsp_BlitzQueryStore.sql
3793 lines (3210 loc) · 142 KB
/
sp_BlitzQueryStore.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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
DECLARE @msg NVARCHAR(MAX) = N'';
-- Must be a compatible, on-prem version of SQL (2016+)
IF ( (SELECT SERVERPROPERTY ('EDITION')) <> 'SQL Azure'
AND (SELECT PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4)) < 13
)
-- or Azure Database (not Azure Data Warehouse), running at database compat level 130+
OR ( (SELECT SERVERPROPERTY ('EDITION')) = 'SQL Azure'
AND (SELECT SERVERPROPERTY ('ENGINEEDITION')) <> 5
AND (SELECT [compatibility_level] FROM sys.databases WHERE [name] = DB_NAME()) < 130
)
BEGIN
SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on versions of SQL prior to 2016, or Azure Database compatibility < 130.' + REPLICATE(CHAR(13), 7933);
PRINT @msg;
RETURN;
END;
IF OBJECT_ID('dbo.sp_BlitzQueryStore') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_BlitzQueryStore AS RETURN 0;');
GO
ALTER PROCEDURE dbo.sp_BlitzQueryStore
@Help BIT = 0,
@DatabaseName NVARCHAR(128) = NULL ,
@Top INT = 3,
@StartDate DATETIME2 = NULL,
@EndDate DATETIME2 = NULL,
@MinimumExecutionCount INT = NULL,
@DurationFilter DECIMAL(38,4) = NULL ,
@StoredProcName NVARCHAR(128) = NULL,
@Failed BIT = 0,
@PlanIdFilter INT = NULL,
@QueryIdFilter INT = NULL,
@ExportToExcel BIT = 0,
@HideSummary BIT = 0 ,
@SkipXML BIT = 0,
@Debug BIT = 0,
@VersionDate DATETIME = NULL OUTPUT
WITH RECOMPILE
AS
BEGIN /*First BEGIN*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @Version NVARCHAR(30);
SET @Version = N'1.5';
SET @VersionDate = N'20170701';
DECLARE /*Variables for the variable Gods*/
@msg NVARCHAR(MAX) = N'', --Used to format RAISERROR messages in some places
@sql_select NVARCHAR(MAX) = N'', --Used to hold SELECT statements for dynamic SQL
@sql_where NVARCHAR(MAX) = N'', -- Used to hold WHERE clause for dynamic SQL
@duration_filter_ms DECIMAL(38,4) = (@DurationFilter * 1000.), --We accept Duration in seconds, but we filter in milliseconds (this is grandfathered from sp_BlitzCache)
@execution_threshold INT = 1000, --Threshold at which we consider a query to be frequently executed
@ctp_threshold_pct TINYINT = 10, --Percentage of CTFP at which we consider a query to be near parallel
@long_running_query_warning_seconds BIGINT = 300 * 1000 ,--Number of seconds (converted to milliseconds) at which a query is considered long running
@memory_grant_warning_percent INT = 10,--Percent of memory grant used compared to what's granted; used to trigger unused memory grant warning
@ctp INT,--Holds the CTFP value for the server
@min_memory_per_query INT,--Holds the server configuration value for min memory per query
@cr NVARCHAR(1) = NCHAR(13),--Special character
@lf NVARCHAR(1) = NCHAR(10),--Special character
@tab NVARCHAR(1) = NCHAR(9),--Special character
@error_severity INT,--Holds error info for try/catch blocks
@error_state INT,--Holds error info for try/catch blocks
@sp_params NVARCHAR(MAX) = N'@sp_Top INT, @sp_StartDate DATETIME2, @sp_EndDate DATETIME2, @sp_MinimumExecutionCount INT, @sp_MinDuration INT, @sp_StoredProcName NVARCHAR(128), @sp_PlanIdFilter INT, @sp_QueryIdFilter INT',--Holds parameters used in dynamic SQL
@is_azure_db BIT = 0, --Are we using Azure? I'm not. You might be. That's cool.
@compatibility_level TINYINT = 0 --Some functionality (T-SQL) isn't available in lower compat levels. We can use this to weed out those issues as we go.
/*Grabs CTFP setting*/
SELECT @ctp = NULLIF(CAST(value AS INT), 0)
FROM sys.configurations
WHERE name = N'cost threshold for parallelism'
OPTION (RECOMPILE);
/*Grabs min query memory setting*/
SELECT @min_memory_per_query = CONVERT(INT, c.value)
FROM sys.configurations AS c
WHERE c.name = N'min memory per query (KB)'
OPTION (RECOMPILE);
/*Help section*/
IF @Help = 1
BEGIN
SELECT N'You have requested assistance. It will arrive as soon as humanly possible.' AS [Take four red capsules, help is on the way];
PRINT N'
sp_BlitzQueryStore from http://FirstResponderKit.org
This script displays your most resource-intensive queries from the Query Store,
and points to ways you can tune these queries to make them faster.
To learn more, visit http://FirstResponderKit.org where you can download new
versions for free, watch training videos on how it works, get more info on
the findings, contribute your own code, and more.
Known limitations of this version:
- This query will not run on SQL Server versions less than 2016.
- This query will not run on Azure Databases with compatibility less than 130.
- This query will not run on Azure Data Warehouse.
Unknown limitations of this version:
- Could be tickling
Changes - for the full list of improvements and fixes in this version, see:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
MIT License
Copyright (c) 2016 Brent Ozar Unlimited
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
';
RETURN;
END;
/*Making sure your version is copasetic*/
IF ( (SELECT SERVERPROPERTY ('EDITION')) = 'SQL Azure' )
BEGIN
SET @is_azure_db = 1;
IF ( (SELECT SERVERPROPERTY ('ENGINEEDITION')) <> 5
OR (SELECT [compatibility_level] FROM sys.databases WHERE [name] = DB_NAME()) < 130
)
BEGIN
SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on Azure Data Warehouse, or Azure Databases with DB compatibility < 130.' + REPLICATE(CHAR(13), 7933);
PRINT @msg;
RETURN;
END
END
ELSE IF ( (SELECT PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')), 4) ) < 13 )
BEGIN
SELECT @msg = N'Sorry, sp_BlitzQueryStore doesn''t work on versions of SQL prior to 2016.' + REPLICATE(CHAR(13), 7933);
PRINT @msg;
RETURN;
END;
/*Making sure at least one database uses QS*/
IF ( SELECT COUNT(*)
FROM sys.databases AS d
WHERE d.is_query_store_on = 1
AND d.user_access_desc='MULTI_USER'
AND d.state_desc = 'ONLINE'
AND d.name NOT IN ('master', 'model', 'msdb', 'tempdb', '32767')
AND d.is_distributor = 0 ) = 0
BEGIN
SELECT @msg = N'You don''t currently have any databases with Query Store enabled.' + REPLICATE(CHAR(13), 7933);
PRINT @msg;
RETURN;
END;
/*Making sure your databases are using QDS.*/
RAISERROR('Checking database validity', 0, 1) WITH NOWAIT;
IF (@is_azure_db = 1)
SET @DatabaseName = DB_NAME()
ELSE
BEGIN
/*If we're on Azure we don't need to check all this @DatabaseName stuff...*/
SET @DatabaseName = LTRIM(RTRIM(@DatabaseName));
/*Did you set @DatabaseName?*/
RAISERROR('Making sure [%s] isn''t NULL', 0, 1, @DatabaseName) WITH NOWAIT;
IF (@DatabaseName IS NULL)
BEGIN
RAISERROR('@DatabaseName cannot be NULL', 0, 1) WITH NOWAIT;
RETURN;
END;
/*Does the database exist?*/
RAISERROR('Making sure [%s] exists', 0, 1, @DatabaseName) WITH NOWAIT;
IF ((DB_ID(@DatabaseName)) IS NULL)
BEGIN
RAISERROR('The @DatabaseName you specified ([%s]) does not exist. Please check the name and try again.', 0, 1, @DatabaseName) WITH NOWAIT;
RETURN;
END;
/*Is it online?*/
RAISERROR('Making sure [%s] is online', 0, 1, @DatabaseName) WITH NOWAIT;
IF (DATABASEPROPERTYEX(@DatabaseName, 'Status')) <> 'ONLINE'
BEGIN
RAISERROR('The @DatabaseName you specified ([%s]) is not readable. Please check the name and try again. Better yet, check your server.', 0, 1, @DatabaseName);
RETURN;
END;
END;
/*Does it have Query Store enabled?*/
RAISERROR('Making sure [%s] has Query Store enabled', 0, 1, @DatabaseName) WITH NOWAIT;
IF
((DB_ID(@DatabaseName)) IS NOT NULL AND @DatabaseName <> '')
AND
( SELECT DB_NAME(d.database_id)
FROM sys.databases AS d
WHERE d.is_query_store_on = 1
AND d.user_access_desc='MULTI_USER'
AND d.state_desc = 'ONLINE'
AND DB_NAME(d.database_id) = @DatabaseName ) IS NULL
BEGIN
RAISERROR('The @DatabaseName you specified ([%s]) does not have the Query Store enabled. Please check the name or settings, and try again.', 0, 1, @DatabaseName) WITH NOWAIT;
RETURN;
END;
/*Check database compat level*/
RAISERROR('Checking database compatibility level', 0, 1) WITH NOWAIT
SELECT @compatibility_level = d.compatibility_level
FROM sys.databases AS d
WHERE d.name = @DatabaseName
RAISERROR('The @DatabaseName you specified ([%s])is running in compatibility level ([%d]).', 0, 1, @DatabaseName, @compatibility_level) WITH NOWAIT;
/*Making sure top is set to something if NULL*/
IF ( @Top IS NULL )
BEGIN
SET @Top = 3;
END;
/*
This section determines if you have the Query Store wait stats DMV
*/
RAISERROR('Checking for query_store_wait_stats', 0, 1) WITH NOWAIT;
DECLARE @out INT,
@waitstats BIT,
@sql NVARCHAR(MAX) = N'SELECT @i_out = COUNT(*) FROM ' + QUOTENAME(@DatabaseName) + N'.sys.all_objects WHERE name = ''query_store_wait_stats'' OPTION (RECOMPILE);',
@ws_params NVARCHAR(MAX) = N'@i_out INT OUTPUT';
EXEC sys.sp_executesql @sql, @ws_params, @i_out = @out OUTPUT;
SELECT @waitstats = CASE @out WHEN 0 THEN 0 ELSE 1 END;
SET @msg = N'Wait stats DMV ' + CASE @waitstats
WHEN 0 THEN N' does not exist, skipping.'
WHEN 1 THEN N' exists, will analyze.'
END;
RAISERROR(@msg, 0, 1) WITH NOWAIT;
/*
These are the temp tables we use
*/
/*
This one holds the grouped data that helps use figure out which periods to examine
*/
RAISERROR(N'Creating temp tables', 0, 1) WITH NOWAIT;
DROP TABLE IF EXISTS #grouped_interval;
CREATE TABLE #grouped_interval
(
flat_date DATE NULL,
start_range DATETIME NULL,
end_range DATETIME NULL,
total_avg_duration_ms DECIMAL(38, 2) NULL,
total_avg_cpu_time_ms DECIMAL(38, 2) NULL,
total_avg_logical_io_reads_mb DECIMAL(38, 2) NULL,
total_avg_physical_io_reads_mb DECIMAL(38, 2) NULL,
total_avg_logical_io_writes_mb DECIMAL(38, 2) NULL,
total_avg_query_max_used_memory_mb DECIMAL(38, 2) NULL,
total_rowcount DECIMAL(38, 2) NULL,
total_count_executions BIGINT NULL,
INDEX gi_ix_dates CLUSTERED (start_range, end_range)
);
/*
These are the plans we focus on based on what we find in the grouped intervals
*/
DROP TABLE IF EXISTS #working_plans;
CREATE TABLE #working_plans
(
plan_id BIGINT,
query_id BIGINT,
pattern NVARCHAR(256),
INDEX wp_ix_ids CLUSTERED (plan_id, query_id)
);
/*
These are the gathered metrics we get from query store to generate some warnings and help you find your worst offenders
*/
DROP TABLE IF EXISTS #working_metrics;
CREATE TABLE #working_metrics
(
database_name NVARCHAR(256),
plan_id BIGINT,
query_id BIGINT,
/*these columns are from query_store_query*/
proc_or_function_name NVARCHAR(256),
batch_sql_handle VARBINARY(64),
query_hash BINARY(8),
query_parameterization_type_desc NVARCHAR(256),
parameter_sniffing_symptoms NVARCHAR(4000),
count_compiles BIGINT,
avg_compile_duration DECIMAL(38,2),
last_compile_duration DECIMAL(38,2),
avg_bind_duration DECIMAL(38,2),
last_bind_duration DECIMAL(38,2),
avg_bind_cpu_time DECIMAL(38,2),
last_bind_cpu_time DECIMAL(38,2),
avg_optimize_duration DECIMAL(38,2),
last_optimize_duration DECIMAL(38,2),
avg_optimize_cpu_time DECIMAL(38,2),
last_optimize_cpu_time DECIMAL(38,2),
avg_compile_memory_kb DECIMAL(38,2),
last_compile_memory_kb DECIMAL(38,2),
/*These come from query_store_runtime_stats*/
execution_type_desc NVARCHAR(128),
first_execution_time DATETIME2,
last_execution_time DATETIME2,
count_executions BIGINT,
avg_duration DECIMAL(38,2) ,
last_duration DECIMAL(38,2),
min_duration DECIMAL(38,2),
max_duration DECIMAL(38,2),
avg_cpu_time DECIMAL(38,2),
last_cpu_time DECIMAL(38,2),
min_cpu_time DECIMAL(38,2),
max_cpu_time DECIMAL(38,2),
avg_logical_io_reads DECIMAL(38,2),
last_logical_io_reads DECIMAL(38,2),
min_logical_io_reads DECIMAL(38,2),
max_logical_io_reads DECIMAL(38,2),
avg_logical_io_writes DECIMAL(38,2),
last_logical_io_writes DECIMAL(38,2),
min_logical_io_writes DECIMAL(38,2),
max_logical_io_writes DECIMAL(38,2),
avg_physical_io_reads DECIMAL(38,2),
last_physical_io_reads DECIMAL(38,2),
min_physical_io_reads DECIMAL(38,2),
max_physical_io_reads DECIMAL(38,2),
avg_clr_time DECIMAL(38,2),
last_clr_time DECIMAL(38,2),
min_clr_time DECIMAL(38,2),
max_clr_time DECIMAL(38,2),
avg_dop BIGINT,
last_dop BIGINT,
min_dop BIGINT,
max_dop BIGINT,
avg_query_max_used_memory DECIMAL(38,2),
last_query_max_used_memory DECIMAL(38,2),
min_query_max_used_memory DECIMAL(38,2),
max_query_max_used_memory DECIMAL(38,2),
avg_rowcount DECIMAL(38,2),
last_rowcount DECIMAL(38,2),
min_rowcount DECIMAL(38,2),
max_rowcount DECIMAL(38,2),
total_compile_duration AS avg_compile_duration * count_compiles,
total_bind_duration AS avg_bind_duration * count_compiles,
total_bind_cpu_time AS avg_bind_cpu_time * count_compiles,
total_optimize_duration AS avg_optimize_duration * count_compiles,
total_optimize_cpu_time AS avg_optimize_cpu_time * count_compiles,
total_compile_memory_kb AS avg_compile_memory_kb * count_compiles,
total_duration AS avg_duration * count_executions,
total_cpu_time AS avg_cpu_time * count_executions,
total_logical_io_reads AS avg_logical_io_reads * count_executions,
total_logical_io_writes AS avg_logical_io_writes * count_executions,
total_physical_io_reads AS avg_physical_io_reads * count_executions,
total_clr_time AS avg_clr_time * count_executions,
total_query_max_used_memory AS avg_query_max_used_memory * count_executions,
total_rowcount AS avg_rowcount * count_executions,
xpm AS NULLIF(count_executions, 0) / NULLIF(DATEDIFF(MINUTE, first_execution_time, last_execution_time), 0),
INDEX wm_ix_ids CLUSTERED (plan_id, query_id, query_hash)
);
/*
This is where we store some additional metrics, along with the query plan and text
*/
DROP TABLE IF EXISTS #working_plan_text;
CREATE TABLE #working_plan_text
(
database_name NVARCHAR(256),
plan_id BIGINT,
query_id BIGINT,
/*These are from query_store_plan*/
plan_group_id BIGINT,
engine_version NVARCHAR(64),
compatibility_level INT,
query_plan_hash BINARY(8),
query_plan_xml XML,
is_online_index_plan BIT,
is_trivial_plan BIT,
is_parallel_plan BIT,
is_forced_plan BIT,
is_natively_compiled BIT,
force_failure_count BIGINT,
last_force_failure_reason_desc NVARCHAR(256),
count_compiles BIGINT,
initial_compile_start_time DATETIME2,
last_compile_start_time DATETIME2,
last_execution_time DATETIME2,
avg_compile_duration DECIMAL(38,2),
last_compile_duration BIGINT,
min_grant_kb DECIMAL(38,2), --This column is updated from dm_exec_query_stats when sql_handle for query exists there
max_used_grant_kb DECIMAL(38,2), --This column is updated from dm_exec_query_stats when sql_handle for query exists there
percent_memory_grant_used AS CONVERT(MONEY, ISNULL(NULLIF(( max_used_grant_kb * 1.00 ), 0) / NULLIF(min_grant_kb, 0), 0) * 100.),
/*These are from query_store_query*/
query_sql_text NVARCHAR(MAX),
statement_sql_handle VARBINARY(64),
is_part_of_encrypted_module BIT,
has_restricted_text BIT,
/*This is from query_context_settings*/
context_settings NVARCHAR(512),
/*This is from #working_plans*/
pattern NVARCHAR(512),
top_three_waits NVARCHAR(MAX),
INDEX wpt_ix_ids CLUSTERED (plan_id, query_id, query_plan_hash)
);
/*
This is where we store warnings that we generate from the XML and metrics
*/
DROP TABLE IF EXISTS #working_warnings;
CREATE TABLE #working_warnings
(
plan_id BIGINT,
query_id BIGINT,
query_hash BINARY(8),
sql_handle VARBINARY(64),
proc_or_function_name NVARCHAR(256),
plan_multiple_plans BIT,
is_forced_plan BIT,
is_forced_parameterized BIT,
is_cursor BIT,
is_optimistic_cursor BIT,
is_forward_only_cursor BIT,
is_parallel BIT,
is_forced_serial BIT,
is_key_lookup_expensive BIT,
key_lookup_cost FLOAT,
is_remote_query_expensive BIT,
remote_query_cost FLOAT,
frequent_execution BIT,
parameter_sniffing BIT,
unparameterized_query BIT,
near_parallel BIT,
plan_warnings BIT,
long_running BIT,
downlevel_estimator BIT,
implicit_conversions BIT,
tvf_estimate BIT,
compile_timeout BIT,
compile_memory_limit_exceeded BIT,
warning_no_join_predicate BIT,
query_cost FLOAT,
missing_index_count INT,
unmatched_index_count INT,
is_trivial BIT,
trace_flags_session NVARCHAR(1000),
is_unused_grant BIT,
function_count INT,
clr_function_count INT,
is_table_variable BIT,
no_stats_warning BIT,
relop_warnings BIT,
is_table_scan BIT,
backwards_scan BIT,
forced_index BIT,
forced_seek BIT,
forced_scan BIT,
columnstore_row_mode BIT,
is_computed_scalar BIT ,
is_sort_expensive BIT,
sort_cost FLOAT,
is_computed_filter BIT,
op_name NVARCHAR(100) NULL,
index_insert_count INT NULL,
index_update_count INT NULL,
index_delete_count INT NULL,
cx_insert_count INT NULL,
cx_update_count INT NULL,
cx_delete_count INT NULL,
table_insert_count INT NULL,
table_update_count INT NULL,
table_delete_count INT NULL,
index_ops AS (index_insert_count + index_update_count + index_delete_count +
cx_insert_count + cx_update_count + cx_delete_count +
table_insert_count + table_update_count + table_delete_count),
is_row_level BIT,
is_spatial BIT,
index_dml BIT,
table_dml BIT,
long_running_low_cpu BIT,
low_cost_high_cpu BIT,
stale_stats BIT,
is_adaptive BIT,
is_slow_plan BIT,
is_compile_more BIT,
index_spool_cost FLOAT,
index_spool_rows FLOAT,
is_spool_expensive BIT,
is_spool_more_rows BIT,
warnings NVARCHAR(4000)
INDEX ww_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);
DROP TABLE IF EXISTS #working_wait_stats;
CREATE TABLE #working_wait_stats
(
plan_id BIGINT,
wait_category TINYINT,
wait_category_desc NVARCHAR(256),
total_query_wait_time_ms BIGINT,
avg_query_wait_time_ms DECIMAL(38, 2),
last_query_wait_time_ms BIGINT,
min_query_wait_time_ms BIGINT,
max_query_wait_time_ms BIGINT,
wait_category_mapped AS CASE wait_category
WHEN 0 THEN N'UNKNOWN'
WHEN 1 THEN N'SOS_SCHEDULER_YIELD'
WHEN 2 THEN N'THREADPOOL'
WHEN 3 THEN N'LCK_M_%'
WHEN 4 THEN N'LATCH_%'
WHEN 5 THEN N'PAGELATCH_%'
WHEN 6 THEN N'PAGEIOLATCH_%'
WHEN 7 THEN N'RESOURCE_SEMAPHORE_QUERY_COMPILE'
WHEN 8 THEN N'CLR%, SQLCLR%'
WHEN 9 THEN N'DBMIRROR%'
WHEN 10 THEN N'XACT%, DTC%, TRAN_MARKLATCH_%, MSQL_XACT_%, TRANSACTION_MUTEX'
WHEN 11 THEN N'SLEEP_%, LAZYWRITER_SLEEP, SQLTRACE_BUFFER_FLUSH, SQLTRACE_INCREMENTAL_FLUSH_SLEEP, SQLTRACE_WAIT_ENTRIES, FT_IFTS_SCHEDULER_IDLE_WAIT, XE_DISPATCHER_WAIT, REQUEST_FOR_DEADLOCK_SEARCH, LOGMGR_QUEUE, ONDEMAND_TASK_QUEUE, CHECKPOINT_QUEUE, XE_TIMER_EVENT'
WHEN 12 THEN N'PREEMPTIVE_%'
WHEN 13 THEN N'BROKER_% (but not BROKER_RECEIVE_WAITFOR)'
WHEN 14 THEN N'LOGMGR, LOGBUFFER, LOGMGR_RESERVE_APPEND, LOGMGR_FLUSH, LOGMGR_PMM_LOG, CHKPT, WRITELOG'
WHEN 15 THEN N'ASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF'
WHEN 16 THEN N'CXPACKET, EXCHANGE'
WHEN 17 THEN N'RESOURCE_SEMAPHORE, CMEMTHREAD, CMEMPARTITIONED, EE_PMOLOCK, MEMORY_ALLOCATION_EXT, RESERVED_MEMORY_ALLOCATION_EXT, MEMORY_GRANT_UPDATE'
WHEN 18 THEN N'WAITFOR, WAIT_FOR_RESULTS, BROKER_RECEIVE_WAITFOR'
WHEN 19 THEN N'TRACEWRITE, SQLTRACE_LOCK, SQLTRACE_FILE_BUFFER, SQLTRACE_FILE_WRITE_IO_COMPLETION, SQLTRACE_FILE_READ_IO_COMPLETION, SQLTRACE_PENDING_BUFFER_WRITERS, SQLTRACE_SHUTDOWN, QUERY_TRACEOUT, TRACE_EVTNOTIFF'
WHEN 20 THEN N'FT_RESTART_CRAWL, FULLTEXT GATHERER, MSSEARCH, FT_METADATA_MUTEX, FT_IFTSHC_MUTEX, FT_IFTSISM_MUTEX, FT_IFTS_RWLOCK, FT_COMPROWSET_RWLOCK, FT_MASTER_MERGE, FT_PROPERTYLIST_CACHE, FT_MASTER_MERGE_COORDINATOR, PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC'
WHEN 21 THEN N'ASYNC_IO_COMPLETION, IO_COMPLETION, BACKUPIO, WRITE_COMPLETION, IO_QUEUE_LIMIT, IO_RETRY'
WHEN 22 THEN N'SE_REPL_%, REPL_%, HADR_% (but not HADR_THROTTLE_LOG_RATE_GOVERNOR), PWAIT_HADR_%, REPLICA_WRITES, FCB_REPLICA_WRITE, FCB_REPLICA_READ, PWAIT_HADRSIM'
WHEN 23 THEN N'LOG_RATE_GOVERNOR, POOL_LOG_RATE_GOVERNOR, HADR_THROTTLE_LOG_RATE_GOVERNOR, INSTANCE_LOG_RATE_GOVERNOR'
END,
INDEX wws_ix_ids CLUSTERED ( plan_id)
);
/*
The next three tables hold plan XML parsed out to different degrees
*/
DROP TABLE IF EXISTS #statements;
CREATE TABLE #statements
(
plan_id BIGINT,
query_id BIGINT,
query_hash BINARY(8),
sql_handle VARBINARY(64),
statement XML,
INDEX s_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);
DROP TABLE IF EXISTS #query_plan;
CREATE TABLE #query_plan
(
plan_id BIGINT,
query_id BIGINT,
query_hash BINARY(8),
sql_handle VARBINARY(64),
query_plan XML,
INDEX qp_ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);
DROP TABLE IF EXISTS #relop;
CREATE TABLE #relop
(
plan_id BIGINT,
query_id BIGINT,
query_hash BINARY(8),
sql_handle VARBINARY(64),
relop XML,
INDEX ix_ids CLUSTERED (plan_id, query_id, query_hash, sql_handle)
);
DROP TABLE IF EXISTS #plan_cost;
CREATE TABLE #plan_cost
(
query_plan_cost DECIMAL(38,2),
sql_handle VARBINARY(64),
INDEX px_ix_ids CLUSTERED (sql_handle)
);
DROP TABLE IF EXISTS #stats_agg;
CREATE TABLE #stats_agg
(
sql_handle VARBINARY(64),
last_update DATETIME2,
modification_count DECIMAL(38, 2),
sampling_percent DECIMAL(38, 2),
[statistics] NVARCHAR(256),
[table] NVARCHAR(256),
[schema] NVARCHAR(256),
[database] NVARCHAR(256),
INDEX sa_ix_ids CLUSTERED (sql_handle)
);
DROP TABLE IF EXISTS #trace_flags;
CREATE TABLE #trace_flags
(
sql_handle VARBINARY(54),
global_trace_flags NVARCHAR(4000),
session_trace_flags NVARCHAR(4000),
INDEX tf_ix_ids CLUSTERED (sql_handle)
);
DROP TABLE IF EXISTS #warning_results;
CREATE TABLE #warning_results
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CheckID INT,
Priority TINYINT,
FindingsGroup NVARCHAR(50),
Finding NVARCHAR(200),
URL NVARCHAR(200),
Details NVARCHAR(4000)
);
/*Sets up WHERE clause that gets used quite a bit*/
--Date stuff
--If they're both NULL, we'll just look at the last 7 days
IF (@StartDate IS NULL AND @EndDate IS NULL)
BEGIN
RAISERROR(N'@StartDate and @EndDate are NULL, checking last 7 days', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.last_execution_time >= DATEADD(DAY, -7, DATEDIFF(DAY, 0, SYSDATETIME() ))
';
END;
--Hey, that's nice of me
IF @StartDate IS NOT NULL
BEGIN
RAISERROR(N'Setting start date filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.last_execution_time >= @sp_StartDate
';
END;
--Alright, sensible
IF @EndDate IS NOT NULL
BEGIN
RAISERROR(N'Setting end date filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.last_execution_time < @sp_EndDate
';
END;
--C'mon, why would you do that?
IF (@StartDate IS NULL AND @EndDate IS NOT NULL)
BEGIN
RAISERROR(N'Setting reasonable start date filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.last_execution_time >= DATEADD(DAY, -7, @sp_EndDate)
';
END;
--Jeez, abusive
IF (@StartDate IS NOT NULL AND @EndDate IS NULL)
BEGIN
RAISERROR(N'Setting reasonable end date filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.last_execution_time < DATEADD(DAY, 7, @sp_StartDate)
';
END;
--I care about minimum execution counts
IF @MinimumExecutionCount IS NOT NULL
BEGIN
RAISERROR(N'Setting execution filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.count_executions >= @sp_MinimumExecutionCount
';
END;
--You care about stored proc names
IF @StoredProcName IS NOT NULL
BEGIN
RAISERROR(N'Setting stored proc filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND object_name(qsq.object_id, DB_ID(' + QUOTENAME(@DatabaseName, '''') + N')) = @sp_StoredProcName
';
END;
--I will always love you, but hopefully this query will eventually end
IF @DurationFilter IS NOT NULL
BEGIN
RAISERROR(N'Setting duration filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND (qsrs.avg_duration / 1000.) >= @sp_MinDuration
';
END;
--I don't know why you'd go looking for failed queries, but hey
IF (@Failed = 0 OR @Failed IS NULL)
BEGIN
RAISERROR(N'Setting failed query filter to 0', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.execution_type = 0
';
END;
IF (@Failed = 1)
BEGIN
RAISERROR(N'Setting failed query filter to 3, 4', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsrs.execution_type IN (3, 4)
';
END;
/*Filtering for plan_id or query_id*/
IF (@PlanIdFilter IS NOT NULL)
BEGIN
RAISERROR(N'Setting plan_id filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsp.plan_id = @sp_PlanIdFilter
';
END;
IF (@QueryIdFilter IS NOT NULL)
BEGIN
RAISERROR(N'Setting query_id filter', 0, 1) WITH NOWAIT;
SET @sql_where += N' AND qsq.query_id = @sp_QueryIdFilter
';
END;
IF @Debug = 1
RAISERROR(N'Starting WHERE clause:', 0, 1) WITH NOWAIT;
PRINT @sql_where;
IF @sql_where IS NULL
BEGIN
RAISERROR(N'@sql_where is NULL', 0, 1) WITH NOWAIT;
RETURN;
END;
IF (@ExportToExcel = 1 OR @SkipXML = 1)
BEGIN
RAISERROR(N'Exporting to Excel or skipping XML, hiding summary', 0, 1) WITH NOWAIT;
SET @HideSummary = 1;
END;
IF @StoredProcName IS NOT NULL
BEGIN
DECLARE @proc_params NVARCHAR(MAX) = N'@sp_StartDate DATETIME2, @sp_EndDate DATETIME2, @sp_MinimumExecutionCount INT, @sp_MinDuration INT, @sp_StoredProcName NVARCHAR(128), @sp_PlanIdFilter INT, @sp_QueryIdFilter INT, @i_out INT OUTPUT';
SET @sql = N'SELECT @i_out = COUNT(*)
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = qsrs.plan_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
WHERE 1 = 1
AND qsq.is_internal_query = 0
AND qsp.query_plan IS NOT NULL
';
SET @sql += @sql_where;
EXEC sys.sp_executesql @sql,
@proc_params,
@sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter, @i_out = @out OUTPUT;
IF @out = 0
BEGIN
SET @msg = N'We couldn''t find the Stored Procedure ' + QUOTENAME(@StoredProcName) + N' in the Query Store views for ' + QUOTENAME(@DatabaseName) + N' between ' + CONVERT(NVARCHAR(30), ISNULL(@StartDate, DATEADD(DAY, -7, DATEDIFF(DAY, 0, SYSDATETIME() ))) ) + N' and ' + CONVERT(NVARCHAR(30), ISNULL(@EndDate, SYSDATETIME())) +
'. Try removing schema prefixes or adjusting dates. If it was executed from a different database context, try searching there instead.'
RAISERROR(@msg, 0, 1) WITH NOWAIT;
SELECT @msg AS [Blue Flowers, Blue Flowers, Blue Flowers]
RETURN;
END
END
/*
This is our grouped interval query.
By default, it looks at queries:
In the last 7 days
That aren't system queries
That have a query plan (some won't, if nested level is > 128, along with other reasons)
And haven't failed
This stuff, along with some other options, will be configurable in the stored proc
*/
IF @sql_where IS NOT NULL
BEGIN TRY
BEGIN
RAISERROR(N'Populating temp tables', 0, 1) WITH NOWAIT;
RAISERROR(N'Gathering intervals', 0, 1) WITH NOWAIT;
SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
SELECT CONVERT(DATE, qsrs.last_execution_time) AS flat_date,
MIN(DATEADD(HOUR, DATEDIFF(HOUR, 0, qsrs.last_execution_time), 0)) AS start_range,
MAX(DATEADD(HOUR, DATEDIFF(HOUR, 0, qsrs.last_execution_time) + 1, 0)) AS end_range,
SUM(qsrs.avg_duration / 1000.) / SUM(qsrs.count_executions) AS total_avg_duration_ms,
SUM(qsrs.avg_cpu_time / 1000.) / SUM(qsrs.count_executions) AS total_avg_cpu_time_ms,
SUM((qsrs.avg_logical_io_reads * 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_logical_io_reads_mb,
SUM((qsrs.avg_physical_io_reads* 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_physical_io_reads_mb,
SUM((qsrs.avg_logical_io_writes* 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_logical_io_writes_mb,
SUM(( qsrs.avg_query_max_used_memory * 8 ) / 1024.) / SUM(qsrs.count_executions) AS total_avg_query_max_used_memory_mb,
SUM(qsrs.avg_rowcount) AS total_rowcount,
SUM(qsrs.count_executions) AS total_count_executions
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
ON qsp.plan_id = qsrs.plan_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
WHERE 1 = 1
AND qsq.is_internal_query = 0
AND qsp.query_plan IS NOT NULL
';
SET @sql_select += @sql_where;
SET @sql_select +=
N'GROUP BY CONVERT(DATE, qsrs.last_execution_time)
OPTION (RECOMPILE);
';
IF @Debug = 1
PRINT @sql_select;
IF @sql_select IS NULL
BEGIN
RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
RETURN;
END;
INSERT #grouped_interval WITH (TABLOCK)
( flat_date, start_range, end_range, total_avg_duration_ms,
total_avg_cpu_time_ms, total_avg_logical_io_reads_mb, total_avg_physical_io_reads_mb,
total_avg_logical_io_writes_mb, total_avg_query_max_used_memory_mb, total_rowcount, total_count_executions )
EXEC sys.sp_executesql @stmt = @sql_select,
@params = @sp_params,
@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;
/*
The next group of queries looks at plans in the ranges we found in the grouped interval query
We take the highest value from each metric (duration, cpu, etc) and find the top plans by that metric in the range
They insert into the #working_plans table
*/
/*Get longest duration plans*/
RAISERROR(N'Gathering longest duration plans', 0, 1) WITH NOWAIT;
SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH duration_max
AS ( SELECT TOP 1
gi.start_range,
gi.end_range
FROM #grouped_interval AS gi
ORDER BY gi.total_avg_duration_ms DESC )
INSERT #working_plans WITH (TABLOCK)
( plan_id, query_id, pattern )
SELECT TOP ( @sp_Top )
qsp.plan_id, qsp.query_id, ''duration''
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN duration_max AS dm
ON qsp.last_execution_time >= dm.start_range
AND qsp.last_execution_time < dm.end_range
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE 1 = 1
AND qsq.is_internal_query = 0
AND qsp.query_plan IS NOT NULL
';
SET @sql_select += @sql_where;
SET @sql_select += N'ORDER BY qsrs.avg_duration DESC
OPTION (RECOMPILE);
';
IF @Debug = 1
PRINT @sql_select;
IF @sql_select IS NULL
BEGIN
RAISERROR(N'@sql_select is NULL', 0, 1) WITH NOWAIT;
RETURN;
END;
EXEC sys.sp_executesql @stmt = @sql_select,
@params = @sp_params,
@sp_Top = @Top, @sp_StartDate = @StartDate, @sp_EndDate = @EndDate, @sp_MinimumExecutionCount = @MinimumExecutionCount, @sp_MinDuration = @duration_filter_ms, @sp_StoredProcName = @StoredProcName, @sp_PlanIdFilter = @PlanIdFilter, @sp_QueryIdFilter = @QueryIdFilter;
/*Get longest cpu plans*/
RAISERROR(N'Gathering highest cpu plans', 0, 1) WITH NOWAIT;
SET @sql_select = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;';
SET @sql_select += N'
WITH cpu_max
AS ( SELECT TOP 1
gi.start_range,
gi.end_range
FROM #grouped_interval AS gi
ORDER BY gi.total_avg_cpu_time_ms DESC )
INSERT #working_plans WITH (TABLOCK)
( plan_id, query_id, pattern )
SELECT TOP ( @sp_Top )
qsp.plan_id, qsp.query_id, ''cpu''
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_plan AS qsp
JOIN cpu_max AS dm
ON qsp.last_execution_time >= dm.start_range
AND qsp.last_execution_time < dm.end_range
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_runtime_stats AS qsrs
ON qsrs.plan_id = qsp.plan_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query AS qsq
ON qsq.query_id = qsp.query_id
JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id
WHERE 1 = 1
AND qsq.is_internal_query = 0
AND qsp.query_plan IS NOT NULL
';
SET @sql_select += @sql_where;
SET @sql_select += N'ORDER BY qsrs.avg_cpu_time DESC