forked from BrentOzarULTD/SQL-Server-First-Responder-Kit
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sp_BlitzCache.sql
7495 lines (6684 loc) · 322 KB
/
sp_BlitzCache.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
IF (
SELECT
CASE
WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
ELSE 1
END
) = 0
BEGIN
DECLARE @msg VARCHAR(8000);
SELECT @msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
PRINT @msg;
RETURN;
END;
IF OBJECT_ID('dbo.sp_BlitzCache') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_BlitzCache AS RETURN 0;');
GO
IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##BlitzCacheProcs', 'U') IS NOT NULL
EXEC ('DROP TABLE ##BlitzCacheProcs;');
GO
IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##BlitzCacheResults', 'U') IS NOT NULL
EXEC ('DROP TABLE ##BlitzCacheResults;');
GO
CREATE TABLE ##BlitzCacheResults (
SPID INT,
ID INT IDENTITY(1,1),
CheckID INT,
Priority TINYINT,
FindingsGroup VARCHAR(50),
Finding VARCHAR(500),
URL VARCHAR(200),
Details VARCHAR(4000)
);
CREATE TABLE ##BlitzCacheProcs (
SPID INT ,
QueryType NVARCHAR(258),
DatabaseName sysname,
AverageCPU DECIMAL(38,4),
AverageCPUPerMinute DECIMAL(38,4),
TotalCPU DECIMAL(38,4),
PercentCPUByType MONEY,
PercentCPU MONEY,
AverageDuration DECIMAL(38,4),
TotalDuration DECIMAL(38,4),
PercentDuration MONEY,
PercentDurationByType MONEY,
AverageReads BIGINT,
TotalReads BIGINT,
PercentReads MONEY,
PercentReadsByType MONEY,
ExecutionCount BIGINT,
PercentExecutions MONEY,
PercentExecutionsByType MONEY,
ExecutionsPerMinute MONEY,
TotalWrites BIGINT,
AverageWrites MONEY,
PercentWrites MONEY,
PercentWritesByType MONEY,
WritesPerMinute MONEY,
PlanCreationTime DATETIME,
PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
LastExecutionTime DATETIME,
LastCompletionTime DATETIME,
PlanHandle VARBINARY(64),
[Remove Plan Handle From Cache] AS
CASE WHEN [PlanHandle] IS NOT NULL
THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [PlanHandle], 1) + ');'
ELSE 'N/A' END,
SqlHandle VARBINARY(64),
[Remove SQL Handle From Cache] AS
CASE WHEN [SqlHandle] IS NOT NULL
THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ');'
ELSE 'N/A' END,
[SQL Handle More Info] AS
CASE WHEN [SqlHandle] IS NOT NULL
THEN 'EXEC sp_BlitzCache @OnlySqlHandles = ''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '''; '
ELSE 'N/A' END,
QueryHash BINARY(8),
[Query Hash More Info] AS
CASE WHEN [QueryHash] IS NOT NULL
THEN 'EXEC sp_BlitzCache @OnlyQueryHashes = ''' + CONVERT(VARCHAR(32), [QueryHash], 1) + '''; '
ELSE 'N/A' END,
QueryPlanHash BINARY(8),
StatementStartOffset INT,
StatementEndOffset INT,
PlanGenerationNum BIGINT,
MinReturnedRows BIGINT,
MaxReturnedRows BIGINT,
AverageReturnedRows MONEY,
TotalReturnedRows BIGINT,
LastReturnedRows BIGINT,
/*The Memory Grant columns are only supported
in certain versions, giggle giggle.
*/
MinGrantKB BIGINT,
MaxGrantKB BIGINT,
MinUsedGrantKB BIGINT,
MaxUsedGrantKB BIGINT,
PercentMemoryGrantUsed MONEY,
AvgMaxMemoryGrant MONEY,
MinSpills BIGINT,
MaxSpills BIGINT,
TotalSpills BIGINT,
AvgSpills MONEY,
QueryText NVARCHAR(MAX),
QueryPlan XML,
/* these next four columns are the total for the type of query.
don't actually use them for anything apart from math by type.
*/
TotalWorkerTimeForType BIGINT,
TotalElapsedTimeForType BIGINT,
TotalReadsForType BIGINT,
TotalExecutionCountForType BIGINT,
TotalWritesForType BIGINT,
NumberOfPlans INT,
NumberOfDistinctPlans INT,
SerialDesiredMemory FLOAT,
SerialRequiredMemory FLOAT,
CachedPlanSize FLOAT,
CompileTime FLOAT,
CompileCPU FLOAT ,
CompileMemory FLOAT ,
MaxCompileMemory FLOAT ,
min_worker_time BIGINT,
max_worker_time BIGINT,
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,
plan_multiple_plans INT,
long_running BIT,
downlevel_estimator BIT,
implicit_conversions BIT,
busy_loops BIT,
tvf_join BIT,
tvf_estimate BIT,
compile_timeout BIT,
compile_memory_limit_exceeded BIT,
warning_no_join_predicate BIT,
QueryPlanCost FLOAT,
missing_index_count INT,
unmatched_index_count INT,
min_elapsed_time BIGINT,
max_elapsed_time BIGINT,
age_minutes MONEY,
age_minutes_lifetime MONEY,
is_trivial BIT,
trace_flags_session VARCHAR(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 VARCHAR(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,
index_spool_cost FLOAT,
index_spool_rows FLOAT,
table_spool_cost FLOAT,
table_spool_rows FLOAT,
is_spool_expensive BIT,
is_spool_more_rows BIT,
is_table_spool_expensive BIT,
is_table_spool_more_rows BIT,
estimated_rows FLOAT,
is_bad_estimate BIT,
is_paul_white_electric BIT,
is_row_goal BIT,
is_big_spills BIT,
is_mstvf BIT,
is_mm_join BIT,
is_nonsargable BIT,
select_with_writes BIT,
implicit_conversion_info XML,
cached_execution_parameters XML,
missing_indexes XML,
SetOptions VARCHAR(MAX),
Warnings VARCHAR(MAX)
);
GO
ALTER PROCEDURE dbo.sp_BlitzCache
@Help BIT = 0,
@Top INT = NULL,
@SortOrder VARCHAR(50) = 'CPU',
@UseTriggersAnyway BIT = NULL,
@ExportToExcel BIT = 0,
@ExpertMode TINYINT = 0,
@OutputType VARCHAR(20) = 'TABLE' ,
@OutputServerName NVARCHAR(258) = NULL ,
@OutputDatabaseName NVARCHAR(258) = NULL ,
@OutputSchemaName NVARCHAR(258) = NULL ,
@OutputTableName NVARCHAR(258) = NULL , -- do NOT use ##BlitzCacheResults or ##BlitzCacheProcs as they are used as work tables in this procedure
@ConfigurationDatabaseName NVARCHAR(128) = NULL ,
@ConfigurationSchemaName NVARCHAR(258) = NULL ,
@ConfigurationTableName NVARCHAR(258) = NULL ,
@DurationFilter DECIMAL(38,4) = NULL ,
@HideSummary BIT = 0 ,
@IgnoreSystemDBs BIT = 1 ,
@OnlyQueryHashes VARCHAR(MAX) = NULL ,
@IgnoreQueryHashes VARCHAR(MAX) = NULL ,
@OnlySqlHandles VARCHAR(MAX) = NULL ,
@IgnoreSqlHandles VARCHAR(MAX) = NULL ,
@QueryFilter VARCHAR(10) = 'ALL' ,
@DatabaseName NVARCHAR(128) = NULL ,
@StoredProcName NVARCHAR(128) = NULL,
@SlowlySearchPlansFor NVARCHAR(4000) = NULL,
@Reanalyze BIT = 0 ,
@SkipAnalysis BIT = 0 ,
@BringThePain BIT = 0 ,
@MinimumExecutionCount INT = 0,
@Debug BIT = 0,
@CheckDateOverride DATETIMEOFFSET = NULL,
@MinutesBack INT = NULL,
@Version VARCHAR(30) = NULL OUTPUT,
@VersionDate DATETIME = NULL OUTPUT,
@VersionCheckMode BIT = 0
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @Version = '8.07', @VersionDate = '20211106';
SET @OutputType = UPPER(@OutputType);
IF(@VersionCheckMode = 1)
BEGIN
RETURN;
END;
DECLARE @nl NVARCHAR(2) = NCHAR(13) + NCHAR(10) ;
IF @Help = 1
BEGIN
PRINT '
sp_BlitzCache from http://FirstResponderKit.org
This script displays your most resource-intensive queries from the plan cache,
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 2005.
- SQL Server 2008 and 2008R2 have a bug in trigger stats, so that output is
excluded by default.
- @IgnoreQueryHashes and @OnlyQueryHashes require a CSV list of hashes
with no spaces between the hash values.
Unknown limitations of this version:
- May or may not be vulnerable to the wick effect.
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) 2021 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.
';
SELECT N'@Help' AS [Parameter Name] ,
N'BIT' AS [Data Type] ,
N'Displays this help message.' AS [Parameter Description]
UNION ALL
SELECT N'@Top',
N'INT',
N'The number of records to retrieve and analyze from the plan cache. The following DMVs are used as the plan cache: dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_trigger_stats.'
UNION ALL
SELECT N'@SortOrder',
N'VARCHAR(10)',
N'Data processing and display order. @SortOrder will still be used, even when preparing output for a table or for excel. Possible values are: "CPU", "Reads", "Writes", "Duration", "Executions", "Recent Compilations", "Memory Grant", "Unused Grant", "Spills", "Query Hash". Additionally, the word "Average" or "Avg" can be used to sort on averages rather than total. "Executions per minute" and "Executions / minute" can be used to sort by execution per minute. For the truly lazy, "xpm" can also be used. Note that when you use all or all avg, the only parameters you can use are @Top and @DatabaseName. All others will be ignored.'
UNION ALL
SELECT N'@UseTriggersAnyway',
N'BIT',
N'On SQL Server 2008R2 and earlier, trigger execution count is incorrect - trigger execution count is incremented once per execution of a SQL agent job. If you still want to see relative execution count of triggers, then you can force sp_BlitzCache to include this information.'
UNION ALL
SELECT N'@ExportToExcel',
N'BIT',
N'Prepare 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'@ExpertMode',
N'TINYINT',
N'Default 0. When set to 1, results include more columns. When 2, mode is optimized for Opserver, the open source dashboard.'
UNION ALL
SELECT N'@OutputType',
N'NVARCHAR(258)',
N'If set to "NONE", this will tell this procedure not to run any query leading to a results set thrown to caller.'
UNION ALL
SELECT N'@OutputDatabaseName',
N'NVARCHAR(128)',
N'The output database. If this does not exist SQL Server will divide by zero and everything will fall apart.'
UNION ALL
SELECT N'@OutputSchemaName',
N'NVARCHAR(258)',
N'The output schema. If this does not exist SQL Server will divide by zero and everything will fall apart.'
UNION ALL
SELECT N'@OutputTableName',
N'NVARCHAR(258)',
N'The output table. If this does not exist, it will be created for you.'
UNION ALL
SELECT N'@DurationFilter',
N'DECIMAL(38,4)',
N'Excludes queries with an average duration (in seconds) less than @DurationFilter.'
UNION ALL
SELECT N'@HideSummary',
N'BIT',
N'Hides the findings summary result set.'
UNION ALL
SELECT N'@IgnoreSystemDBs',
N'BIT',
N'Ignores plans found in the system databases (master, model, msdb, tempdb, dbmaintenance, dbadmin, dbatools and resourcedb)'
UNION ALL
SELECT N'@OnlyQueryHashes',
N'VARCHAR(MAX)',
N'A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored.'
UNION ALL
SELECT N'@IgnoreQueryHashes',
N'VARCHAR(MAX)',
N'A list of query hashes to ignore.'
UNION ALL
SELECT N'@OnlySqlHandles',
N'VARCHAR(MAX)',
N'One or more sql_handles to use for filtering results.'
UNION ALL
SELECT N'@IgnoreSqlHandles',
N'VARCHAR(MAX)',
N'One or more sql_handles to ignore.'
UNION ALL
SELECT N'@DatabaseName',
N'NVARCHAR(128)',
N'A database name which is used for filtering results.'
UNION ALL
SELECT N'@StoredProcName',
N'NVARCHAR(128)',
N'Name of stored procedure you want to find plans for.'
UNION ALL
SELECT N'@SlowlySearchPlansFor',
N'NVARCHAR(4000)',
N'String to search for in plan text. % wildcards allowed.'
UNION ALL
SELECT N'@BringThePain',
N'BIT',
N'When using @SortOrder = ''all'' and @Top > 10, we require you to set @BringThePain = 1 so you understand that sp_BlitzCache will take a while to run.'
UNION ALL
SELECT N'@QueryFilter',
N'VARCHAR(10)',
N'Filter out stored procedures or statements. The default value is ''ALL''. Allowed values are ''procedures'', ''statements'', ''functions'', or ''all'' (any variation in capitalization is acceptable).'
UNION ALL
SELECT N'@Reanalyze',
N'BIT',
N'The default is 0. When set to 0, sp_BlitzCache will re-evalute the plan cache. Set this to 1 to reanalyze existing results'
UNION ALL
SELECT N'@MinimumExecutionCount',
N'INT',
N'Queries with fewer than this number of executions will be omitted from results.'
UNION ALL
SELECT N'@Debug',
N'BIT',
N'Setting this to 1 will print dynamic SQL and select data from all tables used.'
UNION ALL
SELECT N'@MinutesBack',
N'INT',
N'How many minutes back to begin plan cache analysis. If you put in a positive number, we''ll flip it to negtive.';
/* Column definitions */
SELECT N'# Executions' AS [Column Name],
N'BIGINT' AS [Data Type],
N'The number of executions of this particular query. This is computed across statements, procedures, and triggers and aggregated by the SQL handle.' AS [Column Description]
UNION ALL
SELECT N'Executions / Minute',
N'MONEY',
N'Number of executions per minute - calculated for the life of the current plan. Plan life is the last execution time minus the plan creation time.'
UNION ALL
SELECT N'Execution Weight',
N'MONEY',
N'An arbitrary metric of total "execution-ness". A weight of 2 is "one more" than a weight of 1.'
UNION ALL
SELECT N'Database',
N'sysname',
N'The name of the database where the plan was encountered. If the database name cannot be determined for some reason, a value of NA will be substituted. A value of 32767 indicates the plan comes from ResourceDB.'
UNION ALL
SELECT N'Total CPU',
N'BIGINT',
N'Total CPU time, reported in milliseconds, that was consumed by all executions of this query since the last compilation.'
UNION ALL
SELECT N'Avg CPU',
N'BIGINT',
N'Average CPU time, reported in milliseconds, consumed by each execution of this query since the last compilation.'
UNION ALL
SELECT N'CPU Weight',
N'MONEY',
N'An arbitrary metric of total "CPU-ness". A weight of 2 is "one more" than a weight of 1.'
UNION ALL
SELECT N'Total Duration',
N'BIGINT',
N'Total elapsed time, reported in milliseconds, consumed by all executions of this query since last compilation.'
UNION ALL
SELECT N'Avg Duration',
N'BIGINT',
N'Average elapsed time, reported in milliseconds, consumed by each execution of this query since the last compilation.'
UNION ALL
SELECT N'Duration Weight',
N'MONEY',
N'An arbitrary metric of total "Duration-ness". A weight of 2 is "one more" than a weight of 1.'
UNION ALL
SELECT N'Total Reads',
N'BIGINT',
N'Total logical reads performed by this query since last compilation.'
UNION ALL
SELECT N'Average Reads',
N'BIGINT',
N'Average logical reads performed by each execution of this query since the last compilation.'
UNION ALL
SELECT N'Read Weight',
N'MONEY',
N'An arbitrary metric of "Read-ness". A weight of 2 is "one more" than a weight of 1.'
UNION ALL
SELECT N'Total Writes',
N'BIGINT',
N'Total logical writes performed by this query since last compilation.'
UNION ALL
SELECT N'Average Writes',
N'BIGINT',
N'Average logical writes performed by each execution this query since last compilation.'
UNION ALL
SELECT N'Write Weight',
N'MONEY',
N'An arbitrary metric of "Write-ness". A weight of 2 is "one more" than a weight of 1.'
UNION ALL
SELECT N'Query Type',
N'NVARCHAR(258)',
N'The type of query being examined. This can be "Procedure", "Statement", or "Trigger".'
UNION ALL
SELECT N'Query Text',
N'NVARCHAR(4000)',
N'The text of the query. This may be truncated by either SQL Server or by sp_BlitzCache(tm) for display purposes.'
UNION ALL
SELECT N'% Executions (Type)',
N'MONEY',
N'Percent of executions relative to the type of query - e.g. 17.2% of all stored procedure executions.'
UNION ALL
SELECT N'% CPU (Type)',
N'MONEY',
N'Percent of CPU time consumed by this query for a given type of query - e.g. 22% of CPU of all stored procedures executed.'
UNION ALL
SELECT N'% Duration (Type)',
N'MONEY',
N'Percent of elapsed time consumed by this query for a given type of query - e.g. 12% of all statements executed.'
UNION ALL
SELECT N'% Reads (Type)',
N'MONEY',
N'Percent of reads consumed by this query for a given type of query - e.g. 34.2% of all stored procedures executed.'
UNION ALL
SELECT N'% Writes (Type)',
N'MONEY',
N'Percent of writes performed by this query for a given type of query - e.g. 43.2% of all statements executed.'
UNION ALL
SELECT N'Total Rows',
N'BIGINT',
N'Total number of rows returned for all executions of this query. This only applies to query level stats, not stored procedures or triggers.'
UNION ALL
SELECT N'Average Rows',
N'MONEY',
N'Average number of rows returned by each execution of the query.'
UNION ALL
SELECT N'Min Rows',
N'BIGINT',
N'The minimum number of rows returned by any execution of this query.'
UNION ALL
SELECT N'Max Rows',
N'BIGINT',
N'The maximum number of rows returned by any execution of this query.'
UNION ALL
SELECT N'MinGrantKB',
N'BIGINT',
N'The minimum memory grant the query received in kb.'
UNION ALL
SELECT N'MaxGrantKB',
N'BIGINT',
N'The maximum memory grant the query received in kb.'
UNION ALL
SELECT N'MinUsedGrantKB',
N'BIGINT',
N'The minimum used memory grant the query received in kb.'
UNION ALL
SELECT N'MaxUsedGrantKB',
N'BIGINT',
N'The maximum used memory grant the query received in kb.'
UNION ALL
SELECT N'MinSpills',
N'BIGINT',
N'The minimum amount this query has spilled to tempdb in 8k pages.'
UNION ALL
SELECT N'MaxSpills',
N'BIGINT',
N'The maximum amount this query has spilled to tempdb in 8k pages.'
UNION ALL
SELECT N'TotalSpills',
N'BIGINT',
N'The total amount this query has spilled to tempdb in 8k pages.'
UNION ALL
SELECT N'AvgSpills',
N'BIGINT',
N'The average amount this query has spilled to tempdb in 8k pages.'
UNION ALL
SELECT N'PercentMemoryGrantUsed',
N'MONEY',
N'Result of dividing the maximum grant used by the minimum granted.'
UNION ALL
SELECT N'AvgMaxMemoryGrant',
N'MONEY',
N'The average maximum memory grant for a query.'
UNION ALL
SELECT N'# Plans',
N'INT',
N'The total number of execution plans found that match a given query.'
UNION ALL
SELECT N'# Distinct Plans',
N'INT',
N'The number of distinct execution plans that match a given query. '
+ NCHAR(13) + NCHAR(10)
+ N'This may be caused by running the same query across multiple databases or because of a lack of proper parameterization in the database.'
UNION ALL
SELECT N'Created At',
N'DATETIME',
N'Time that the execution plan was last compiled.'
UNION ALL
SELECT N'Last Execution',
N'DATETIME',
N'The last time that this query was executed.'
UNION ALL
SELECT N'Query Plan',
N'XML',
N'The query plan. Click to display a graphical plan or, if you need to patch SSMS, a pile of XML.'
UNION ALL
SELECT N'Plan Handle',
N'VARBINARY(64)',
N'An arbitrary identifier referring to the compiled plan this query is a part of.'
UNION ALL
SELECT N'SQL Handle',
N'VARBINARY(64)',
N'An arbitrary identifier referring to a batch or stored procedure that this query is a part of.'
UNION ALL
SELECT N'Query Hash',
N'BINARY(8)',
N'A hash of the query. Queries with the same query hash have similar logic but only differ by literal values or database.'
UNION ALL
SELECT N'Warnings',
N'VARCHAR(MAX)',
N'A list of individual warnings generated by this query.' ;
/* Configuration table description */
SELECT N'Frequent Execution Threshold' AS [Configuration Parameter] ,
N'100' AS [Default Value] ,
N'Executions / Minute' AS [Unit of Measure] ,
N'Executions / Minute before a "Frequent Execution Threshold" warning is triggered.' AS [Description]
UNION ALL
SELECT N'Parameter Sniffing Variance Percent' ,
N'30' ,
N'Percent' ,
N'Variance required between min/max values and average values before a "Parameter Sniffing" warning is triggered. Applies to worker time and returned rows.'
UNION ALL
SELECT N'Parameter Sniffing IO Threshold' ,
N'100,000' ,
N'Logical reads' ,
N'Minimum number of average logical reads before parameter sniffing checks are evaluated.'
UNION ALL
SELECT N'Cost Threshold for Parallelism Warning' AS [Configuration Parameter] ,
N'10' ,
N'Percent' ,
N'Trigger a "Nearly Parallel" warning when a query''s cost is within X percent of the cost threshold for parallelism.'
UNION ALL
SELECT N'Long Running Query Warning' AS [Configuration Parameter] ,
N'300' ,
N'Seconds' ,
N'Triggers a "Long Running Query Warning" when average duration, max CPU time, or max clock time is higher than this number.'
UNION ALL
SELECT N'Unused Memory Grant Warning' AS [Configuration Parameter] ,
N'10' ,
N'Percent' ,
N'Triggers an "Unused Memory Grant Warning" when a query uses >= X percent of its memory grant.';
RETURN;
END; /* IF @Help = 1 */
/*Validate version*/
IF (
SELECT
CASE
WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
ELSE 1
END
) = 0
BEGIN
DECLARE @version_msg VARCHAR(8000);
SELECT @version_msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
PRINT @version_msg;
RETURN;
END;
IF(@OutputType = 'NONE' AND (@OutputTableName IS NULL OR @OutputSchemaName IS NULL OR @OutputDatabaseName IS NULL))
BEGIN
RAISERROR('This procedure should be called with a value for all @Output* parameters, as @OutputType is set to NONE',12,1);
RETURN;
END;
IF(@OutputType = 'NONE')
BEGIN
SET @HideSummary = 1;
END;
/* Lets get @SortOrder set to lower case here for comparisons later */
SET @SortOrder = LOWER(@SortOrder);
/* If they want to sort by query hash, populate the @OnlyQueryHashes list for them */
IF @SortOrder LIKE 'query hash%'
BEGIN
RAISERROR('Beginning query hash sort', 0, 1) WITH NOWAIT;
SELECT qs.query_hash,
MAX(qs.max_worker_time) AS max_worker_time,
COUNT_BIG(*) AS records
INTO #query_hash_grouped
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY ( SELECT pa.value
FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE pa.attribute = 'dbid' ) AS ca
GROUP BY qs.query_hash, ca.value
HAVING COUNT_BIG(*) > 1
ORDER BY max_worker_time DESC,
records DESC;
SELECT TOP (1)
@OnlyQueryHashes = STUFF((SELECT DISTINCT N',' + CONVERT(NVARCHAR(MAX), qhg.query_hash, 1)
FROM #query_hash_grouped AS qhg
WHERE qhg.query_hash <> 0x00
FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
OPTION(RECOMPILE);
/* When they ran it, @SortOrder probably looked like 'query hash, cpu', so strip the first sort order out: */
SELECT @SortOrder = LTRIM(REPLACE(REPLACE(@SortOrder,'query hash', ''), ',', ''));
/* If they just called it with @SortOrder = 'query hash', set it to 'cpu' for backwards compatibility: */
IF @SortOrder = '' SET @SortOrder = 'cpu';
END
/* Set @Top based on sort */
IF (
@Top IS NULL
AND @SortOrder IN ( 'all', 'all sort' )
)
BEGIN
SET @Top = 5;
END;
IF (
@Top IS NULL
AND @SortOrder NOT IN ( 'all', 'all sort' )
)
BEGIN
SET @Top = 10;
END;
/* validate user inputs */
IF @Top IS NULL
OR @SortOrder IS NULL
OR @QueryFilter IS NULL
OR @Reanalyze IS NULL
BEGIN
RAISERROR(N'Several parameters (@Top, @SortOrder, @QueryFilter, @renalyze) are required. Do not set them to NULL. Please try again.', 16, 1) WITH NOWAIT;
RETURN;
END;
RAISERROR(N'Checking @MinutesBack validity.', 0, 1) WITH NOWAIT;
IF @MinutesBack IS NOT NULL
BEGIN
IF @MinutesBack > 0
BEGIN
RAISERROR(N'Setting @MinutesBack to a negative number', 0, 1) WITH NOWAIT;
SET @MinutesBack *=-1;
END;
IF @MinutesBack = 0
BEGIN
RAISERROR(N'@MinutesBack can''t be 0, setting to -1', 0, 1) WITH NOWAIT;
SET @MinutesBack = -1;
END;
END;
RAISERROR(N'Creating temp tables for results and warnings.', 0, 1) WITH NOWAIT;
IF OBJECT_ID('tempdb.dbo.##BlitzCacheResults') IS NULL
BEGIN
CREATE TABLE ##BlitzCacheResults (
SPID INT,
ID INT IDENTITY(1,1),
CheckID INT,
Priority TINYINT,
FindingsGroup VARCHAR(50),
Finding VARCHAR(500),
URL VARCHAR(200),
Details VARCHAR(4000)
);
END;
IF OBJECT_ID('tempdb.dbo.##BlitzCacheProcs') IS NULL
BEGIN
CREATE TABLE ##BlitzCacheProcs (
SPID INT ,
QueryType NVARCHAR(258),
DatabaseName sysname,
AverageCPU DECIMAL(38,4),
AverageCPUPerMinute DECIMAL(38,4),
TotalCPU DECIMAL(38,4),
PercentCPUByType MONEY,
PercentCPU MONEY,
AverageDuration DECIMAL(38,4),
TotalDuration DECIMAL(38,4),
PercentDuration MONEY,
PercentDurationByType MONEY,
AverageReads BIGINT,
TotalReads BIGINT,
PercentReads MONEY,
PercentReadsByType MONEY,
ExecutionCount BIGINT,
PercentExecutions MONEY,
PercentExecutionsByType MONEY,
ExecutionsPerMinute MONEY,
TotalWrites BIGINT,
AverageWrites MONEY,
PercentWrites MONEY,
PercentWritesByType MONEY,
WritesPerMinute MONEY,
PlanCreationTime DATETIME,
PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
LastExecutionTime DATETIME,
LastCompletionTime DATETIME,
PlanHandle VARBINARY(64),
[Remove Plan Handle From Cache] AS
CASE WHEN [PlanHandle] IS NOT NULL
THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [PlanHandle], 1) + ');'
ELSE 'N/A' END,
SqlHandle VARBINARY(64),
[Remove SQL Handle From Cache] AS
CASE WHEN [SqlHandle] IS NOT NULL
THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ');'
ELSE 'N/A' END,
[SQL Handle More Info] AS
CASE WHEN [SqlHandle] IS NOT NULL
THEN 'EXEC sp_BlitzCache @OnlySqlHandles = ''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '''; '
ELSE 'N/A' END,
QueryHash BINARY(8),
[Query Hash More Info] AS
CASE WHEN [QueryHash] IS NOT NULL
THEN 'EXEC sp_BlitzCache @OnlyQueryHashes = ''' + CONVERT(VARCHAR(32), [QueryHash], 1) + '''; '
ELSE 'N/A' END,
QueryPlanHash BINARY(8),
StatementStartOffset INT,
StatementEndOffset INT,
PlanGenerationNum BIGINT,
MinReturnedRows BIGINT,
MaxReturnedRows BIGINT,
AverageReturnedRows MONEY,
TotalReturnedRows BIGINT,
LastReturnedRows BIGINT,
MinGrantKB BIGINT,
MaxGrantKB BIGINT,
MinUsedGrantKB BIGINT,
MaxUsedGrantKB BIGINT,
PercentMemoryGrantUsed MONEY,
AvgMaxMemoryGrant MONEY,
MinSpills BIGINT,
MaxSpills BIGINT,
TotalSpills BIGINT,
AvgSpills MONEY,
QueryText NVARCHAR(MAX),
QueryPlan XML,
/* these next four columns are the total for the type of query.
don't actually use them for anything apart from math by type.
*/
TotalWorkerTimeForType BIGINT,
TotalElapsedTimeForType BIGINT,
TotalReadsForType BIGINT,
TotalExecutionCountForType BIGINT,
TotalWritesForType BIGINT,
NumberOfPlans INT,
NumberOfDistinctPlans INT,
SerialDesiredMemory FLOAT,
SerialRequiredMemory FLOAT,
CachedPlanSize FLOAT,
CompileTime FLOAT,
CompileCPU FLOAT ,
CompileMemory FLOAT ,
MaxCompileMemory FLOAT ,
min_worker_time BIGINT,
max_worker_time BIGINT,
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,
plan_multiple_plans INT,
long_running BIT,
downlevel_estimator BIT,
implicit_conversions BIT,
busy_loops BIT,
tvf_join BIT,
tvf_estimate BIT,
compile_timeout BIT,
compile_memory_limit_exceeded BIT,
warning_no_join_predicate BIT,
QueryPlanCost FLOAT,
missing_index_count INT,
unmatched_index_count INT,
min_elapsed_time BIGINT,
max_elapsed_time BIGINT,
age_minutes MONEY,
age_minutes_lifetime MONEY,
is_trivial BIT,
trace_flags_session VARCHAR(1000),