forked from BrentOzarULTD/SQL-Server-First-Responder-Kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsp_BlitzQueryStore.sql
6073 lines (5304 loc) · 230 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 CONVERT(NVARCHAR(128), 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 CONVERT(NVARCHAR(128), SERVERPROPERTY ('EDITION'))) = 'SQL Azure'
AND (SELECT SERVERPROPERTY ('ENGINEEDITION')) NOT IN (5,8)
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,
@ExpertMode BIT = 0,
@Version VARCHAR(30) = NULL OUTPUT,
@VersionDate DATETIME = NULL OUTPUT,
@VersionCheckMode BIT = 0
WITH RECOMPILE
AS
BEGIN /*First BEGIN*/
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @Version = '8.18', @VersionDate = '20231222';
IF(@VersionCheckMode = 1)
BEGIN
RETURN;
END;
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.
@log_size_mb DECIMAL(38,2) = 0,
@avg_tempdb_data_file DECIMAL(38,2) = 0;
/*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);
/*Check if this is Azure first*/
IF (SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY ('EDITION'))) <> 'SQL Azure'
BEGIN
/*Grabs log size for datbase*/
SELECT @log_size_mb = AVG(((mf.size * 8) / 1024.))
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID(@DatabaseName)
AND mf.type_desc = 'LOG';
/*Grab avg tempdb file size*/
SELECT @avg_tempdb_data_file = AVG(((mf.size * 8) / 1024.))
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID('tempdb')
AND mf.type_desc = 'ROWS';
END;
/*Help section*/
IF @Help = 1
BEGIN
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
MIT License
Copyright (c) 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.
';
/*Parameter info*/
SELECT N'@Help' AS [Parameter Name] ,
N'BIT' AS [Data Type] ,
N'0' AS [Default Value],
N'Displays this help message.' AS [Parameter Description]
UNION ALL
SELECT N'@DatabaseName',
N'NVARCHAR(128)',
N'NULL',
N'The name of the database you want to check the query store for.'
UNION ALL
SELECT N'@Top',
N'INT',
N'3',
N'The number of records to retrieve and analyze from the query store. The following system views are used: query_store_query, query_context_settings, query_store_wait_stats, query_store_runtime_stats,query_store_plan.'
UNION ALL
SELECT N'@StartDate',
N'DATETIME2(7)',
N'NULL',
N'Get query store info starting from this date. When not specified, sp_BlitzQueryStore gets info from the last 7 days'
UNION ALL
SELECT N'@EndDate',
N'DATETIME2(7)',
N'NULL',
N'Get query store info until this date. When not specified, sp_BlitzQueryStore gets info from the last 7 days'
UNION ALL
SELECT N'@MinimumExecutionCount',
N'INT',
N'NULL',
N'When a value is specified, sp_BlitzQueryStore gets info for queries where count_executions >= @MinimumExecutionCount'
UNION ALL
SELECT N'@DurationFilter',
N'DECIMAL(38,4)',
N'NULL',
N'Time unit - seconds. When a value is specified, sp_BlitzQueryStore gets info for queries where the average duration >= @DurationFilter'
UNION ALL
SELECT N'@StoredProcName',
N'NVARCHAR(128)',
N'NULL',
N'Get information for this specific stored procedure.'
UNION ALL
SELECT N'@Failed',
N'BIT',
N'0',
N'When set to 1, only information about failed queries is returned.'
UNION ALL
SELECT N'@PlanIdFilter',
N'INT',
N'NULL',
N'The ID of the plan you want to check for.'
UNION ALL
SELECT N'@QueryIdFilter',
N'INT',
N'NULL',
N'The ID of the query you want to check for.'
UNION ALL
SELECT N'@ExportToExcel',
N'BIT',
N'0',
N'When set to 1, prepares output for exporting to Excel. Newlines and additional whitespace are removed from query text and the execution plan is not displayed.'
UNION ALL
SELECT N'@HideSummary',
N'BIT',
N'0',
N'When set to 1, hides the findings summary result set.'
UNION ALL
SELECT N'@SkipXML',
N'BIT',
N'0',
N'When set to 1, missing_indexes, implicit_conversion_info, cached_execution_parameters, are not returned. Does not affect query_plan_xml'
UNION ALL
SELECT N'@Debug',
N'BIT',
N'0',
N'Setting this to 1 will print dynamic SQL and select data from all tables used.'
UNION ALL
SELECT N'@ExpertMode',
N'BIT',
N'0',
N'When set to 1, more checks are done. Examples: many to many merge joins, row goals, adaptive joins, stats info, bad scans and plan forcing, computed columns that reference scalar UDFs.'
UNION ALL
SELECT N'@VersionCheckMode',
N'BIT',
N'0',
N'Outputs the version number and date.'
/* Column definitions */
SELECT 'database_name' AS [Column Name],
'NVARCHAR(258)' AS [Data Type],
'The name of the database where the plan was encountered.' AS [Column Description]
UNION ALL
SELECT 'query_cost',
'FLOAT',
'The cost of the execution plan in query bucks.'
UNION ALL
SELECT 'plan_id',
'BIGINT',
'The ID of the plan from sys.query_store_plan.'
UNION ALL
SELECT 'query_id',
'BIGINT',
'The ID of the query from sys.query_store_query.'
UNION ALL
SELECT 'query_id_all_plan_ids',
'VARCHAR(8000)',
'Comma-separated list of all query plan IDs associated with this query.'
UNION ALL
SELECT 'query_sql_text',
'NVARCHAR(MAX)',
'The text of the query, as provided by the user/app. Includes whitespaces, hints and comments. Comments and spaces before and after the query text are ignored.'
UNION ALL
SELECT 'proc_or_function_name',
'NVARCHAR(258)',
'If the query is part of a function/stored procedure, you''ll see here the name of its parent object.'
UNION ALL
SELECT 'query_plan_xml',
' XML',
'The query plan. Click to display a graphical plan.'
UNION ALL
SELECT 'warnings',
'VARCHAR(MAX)',
'A list of individual warnings generated by this query.'
UNION ALL
SELECT 'pattern',
'NVARCHAR(512)',
'A list of performance related patterns identified for this query.'
UNION ALL
SELECT 'parameter_sniffing_symptoms',
'NVARCHAR(4000)',
'A list of all the identified symptoms that are usually indicators of parameter sniffing.'
UNION ALL
SELECT 'last_force_failure_reason_desc',
'NVARCHAR(258)',
'Reason why plan forcing failed. NONE if plan isn''t forced.'
UNION ALL
SELECT 'top_three_waits',
'NVARCHAR(MAX)',
'The top 3 wait types, and their times in milliseconds, recorded for this query.'
UNION ALL
SELECT 'missing_indexes',
'XML',
'Missing index recommendations retrieved from the query plan.'
UNION ALL
SELECT 'implicit_conversion_info',
'XML',
'Information about the implicit conversion warnings,if any, retrieved from the query plan.'
UNION ALL
SELECT 'cached_execution_parameters',
'XML',
'Names, data types, and values for the parameters used when the query plan was compiled.'
UNION ALL
SELECT 'count_executions ',
'BIGINT',
'The number of executions of this particular query.'
UNION ALL
SELECT 'count_compiles',
'BIGINT',
'The number of plan compilations for this particular query.'
UNION ALL
SELECT 'total_cpu_time',
'BIGINT',
'Total CPU time, reported in milliseconds, that was consumed by all executions of this query.'
UNION ALL
SELECT 'avg_cpu_time ',
'BIGINT',
'Average CPU time, reported in milliseconds, consumed by each execution of this query.'
UNION ALL
SELECT 'total_duration',
'BIGINT',
'Total elapsed time, reported in milliseconds, consumed by all executions of this query.'
UNION ALL
SELECT 'avg_duration',
'BIGINT',
'Average elapsed time, reported in milliseconds, consumed by each execution of this query.'
UNION ALL
SELECT 'total_logical_io_reads',
'BIGINT',
'Total logical reads, reported in MB, performed by this query.'
UNION ALL
SELECT 'avg_logical_io_reads',
'BIGINT',
'Average logical reads, reported in MB, performed by each execution of this query.'
UNION ALL
SELECT 'total_physical_io_reads',
'BIGINT',
'Total physical reads, reported in MB, performed by this query.'
UNION ALL
SELECT 'avg_physical_io_reads',
'BIGINT',
'Average physical reads, reported in MB, performed by each execution of this query.'
UNION ALL
SELECT 'total_logical_io_writes',
'BIGINT',
'Total logical writes, reported in MB, performed by this query.'
UNION ALL
SELECT 'avg_logical_io_writes',
'BIGINT',
'Average logical writes, reported in MB, performed by each execution of this query.'
UNION ALL
SELECT 'total_rowcount',
'BIGINT',
'Total number of rows returned for all executions of this query.'
UNION ALL
SELECT 'avg_rowcount',
'BIGINT',
'Average number of rows returned by each execution of this query.'
UNION ALL
SELECT 'total_query_max_used_memory',
'DECIMAL(38,2)',
'Total max memory grant, reported in MB, used by this query.'
UNION ALL
SELECT 'avg_query_max_used_memory',
'DECIMAL(38,2)',
'Average max memory grant, reported in MB, used by each execution of this query.'
UNION ALL
SELECT 'total_tempdb_space_used',
'DECIMAL(38,2)',
'Total tempdb space, reported in MB, used by this query.'
UNION ALL
SELECT 'avg_tempdb_space_used',
'DECIMAL(38,2)',
'Average tempdb space, reported in MB, used by each execution of this query.'
UNION ALL
SELECT 'total_log_bytes_used',
'DECIMAL(38,2)',
'Total number of bytes in the database log used by this query.'
UNION ALL
SELECT 'avg_log_bytes_used',
'DECIMAL(38,2)',
'Average number of bytes in the database log used by each execution of this query.'
UNION ALL
SELECT 'total_num_physical_io_reads',
'DECIMAL(38,2)',
'Total number of physical I/O reads performed by this query (expressed as a number of read I/O operations).'
UNION ALL
SELECT 'avg_num_physical_io_reads',
'DECIMAL(38,2)',
'Average number of physical I/O reads performed by each execution of this query (expressed as a number of read I/O operations).'
UNION ALL
SELECT 'first_execution_time',
'DATETIME2',
'First execution time for this query within the aggregation interval. This is the end time of the query execution.'
UNION ALL
SELECT 'last_execution_time',
'DATETIME2',
'Last execution time for this query within the aggregation interval. This is the end time of the query execution.'
UNION ALL
SELECT 'context_settings',
'NVARCHAR(512)',
'Contains information about context settings associated with this query.';
RETURN;
END;
/*Making sure your version is copasetic*/
IF ( (SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY ('EDITION'))) = 'SQL Azure' )
BEGIN
SET @is_azure_db = 1;
IF ( (SELECT SERVERPROPERTY ('ENGINEEDITION')) NOT IN (5,8)
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 AND SERVERPROPERTY ('ENGINEEDITION') <> 8)
SET @DatabaseName = DB_NAME();
ELSE
BEGIN
/*If we're on Azure SQL DB 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, 'Collation')) IS NULL
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
( SELECT [d].[name]
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].[database_id] = (SELECT database_id FROM sys.databases WHERE name = @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 @ws_out INT,
@waitstats BIT,
@ws_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 @ws_sql, @ws_params, @i_out = @ws_out OUTPUT;
SELECT @waitstats = CASE @ws_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;
/*
This section determines if you have some additional columns present in 2017, in case they get back ported.
*/
RAISERROR('Checking for new columns in query_store_runtime_stats', 0, 1) WITH NOWAIT;
DECLARE @nc_out INT,
@new_columns BIT,
@nc_sql NVARCHAR(MAX) = N'SELECT @i_out = COUNT(*)
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.all_columns AS ac
WHERE OBJECT_NAME(object_id) = ''query_store_runtime_stats''
AND ac.name IN (
''avg_num_physical_io_reads'',
''last_num_physical_io_reads'',
''min_num_physical_io_reads'',
''max_num_physical_io_reads'',
''avg_log_bytes_used'',
''last_log_bytes_used'',
''min_log_bytes_used'',
''max_log_bytes_used'',
''avg_tempdb_space_used'',
''last_tempdb_space_used'',
''min_tempdb_space_used'',
''max_tempdb_space_used''
) OPTION (RECOMPILE);',
@nc_params NVARCHAR(MAX) = N'@i_out INT OUTPUT';
EXEC sys.sp_executesql @nc_sql, @ws_params, @i_out = @nc_out OUTPUT;
SELECT @new_columns = CASE @nc_out WHEN 12 THEN 1 ELSE 0 END;
SET @msg = N'New query_store_runtime_stats columns ' + CASE @new_columns
WHEN 0 THEN N' do not exist, skipping.'
WHEN 1 THEN N' exist, will analyze.'
END;
RAISERROR(@msg, 0, 1) WITH NOWAIT;
/*
This section determines if Parameter Sensitive Plan Optimization is enabled on SQL Server 2022+.
*/
RAISERROR('Checking for Parameter Sensitive Plan Optimization ', 0, 1) WITH NOWAIT;
DECLARE @pspo_out BIT,
@pspo_enabled BIT,
@pspo_sql NVARCHAR(MAX) = N'SELECT @i_out = CONVERT(bit,dsc.value)
FROM ' + QUOTENAME(@DatabaseName) + N'.sys.database_scoped_configurations dsc
WHERE dsc.name = ''PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'';',
@pspo_params NVARCHAR(MAX) = N'@i_out INT OUTPUT';
EXEC sys.sp_executesql @pspo_sql, @pspo_params, @i_out = @pspo_out OUTPUT;
SET @pspo_enabled = CASE WHEN @pspo_out = 1 THEN 1 ELSE 0 END;
SET @msg = N'Parameter Sensitive Plan Optimization ' + CASE @pspo_enabled
WHEN 0 THEN N' not enabled, skipping.'
WHEN 1 THEN N' enabled, 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,
total_avg_log_bytes_mb DECIMAL(38, 2) NULL,
total_avg_tempdb_space DECIMAL(38, 2) NULL,
total_max_duration_ms DECIMAL(38, 2) NULL,
total_max_cpu_time_ms DECIMAL(38, 2) NULL,
total_max_logical_io_reads_mb DECIMAL(38, 2) NULL,
total_max_physical_io_reads_mb DECIMAL(38, 2) NULL,
total_max_logical_io_writes_mb DECIMAL(38, 2) NULL,
total_max_query_max_used_memory_mb DECIMAL(38, 2) NULL,
total_max_log_bytes_mb DECIMAL(38, 2) NULL,
total_max_tempdb_space DECIMAL(38, 2) 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(258),
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(258),
plan_id BIGINT,
query_id BIGINT,
query_id_all_plan_ids VARCHAR(8000),
/*these columns are from query_store_query*/
proc_or_function_name NVARCHAR(258),
batch_sql_handle VARBINARY(64),
query_hash BINARY(8),
query_parameterization_type_desc NVARCHAR(258),
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),
/*These are 2017 only, AFAIK*/
avg_num_physical_io_reads DECIMAL(38,2),
last_num_physical_io_reads DECIMAL(38,2),
min_num_physical_io_reads DECIMAL(38,2),
max_num_physical_io_reads DECIMAL(38,2),
avg_log_bytes_used DECIMAL(38,2),
last_log_bytes_used DECIMAL(38,2),
min_log_bytes_used DECIMAL(38,2),
max_log_bytes_used DECIMAL(38,2),
avg_tempdb_space_used DECIMAL(38,2),
last_tempdb_space_used DECIMAL(38,2),
min_tempdb_space_used DECIMAL(38,2),
max_tempdb_space_used DECIMAL(38,2),
/*These are computed columns to make some stuff easier down the line*/
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,
total_num_physical_io_reads AS avg_num_physical_io_reads * count_executions,
total_log_bytes_used AS avg_log_bytes_used * count_executions,
total_tempdb_space_used AS avg_tempdb_space_used * count_executions,
xpm AS NULLIF(count_executions, 0) / NULLIF(DATEDIFF(MINUTE, first_execution_time, last_execution_time), 0),
percent_memory_grant_used AS CONVERT(MONEY, ISNULL(NULLIF(( max_query_max_used_memory * 1.00 ), 0) / NULLIF(min_query_max_used_memory, 0), 0) * 100.),
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(258),
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(258),
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,
/*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(258),
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_fast_forward_cursor BIT,
is_cursor_dynamic 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,
estimated_rows FLOAT,
is_bad_estimate BIT,
is_big_log BIT,
is_big_tempdb BIT,
is_paul_white_electric BIT,
is_row_goal BIT,
is_mstvf BIT,
is_mm_join BIT,
is_nonsargable BIT,
busy_loops BIT,
tvf_join BIT,
implicit_conversion_info XML,
cached_execution_parameters XML,
missing_indexes XML,
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(258),
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, CXCONSUMER'
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,
is_cursor BIT
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;