· 8 years ago · Apr 19, 2018, 04:02 PM
1SET ANSI_NULLS ON;
2SET ANSI_PADDING ON;
3SET ANSI_WARNINGS ON;
4SET ARITHABORT ON;
5SET CONCAT_NULL_YIELDS_NULL ON;
6SET QUOTED_IDENTIFIER ON;
7SET STATISTICS IO OFF;
8SET STATISTICS TIME OFF;
9GO
10
11IF (
12SELECT
13 CASE
14 WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
15 WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
16 ELSE 1
17 END
18) = 0
19BEGIN
20 DECLARE @msg VARCHAR(8000);
21 SELECT @msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
22 PRINT @msg;
23 RETURN;
24END;
25
26IF OBJECT_ID('dbo.sp_BlitzCache') IS NULL
27 EXEC ('CREATE PROCEDURE dbo.sp_BlitzCache AS RETURN 0;');
28GO
29
30IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##bou_BlitzCacheProcs', 'U') IS NOT NULL
31 EXEC ('DROP TABLE ##bou_BlitzCacheProcs;');
32GO
33
34IF OBJECT_ID('dbo.sp_BlitzCache') IS NOT NULL AND OBJECT_ID('tempdb.dbo.##bou_BlitzCacheResults', 'U') IS NOT NULL
35 EXEC ('DROP TABLE ##bou_BlitzCacheResults;');
36GO
37
38CREATE TABLE ##bou_BlitzCacheResults (
39 SPID INT,
40 ID INT IDENTITY(1,1),
41 CheckID INT,
42 Priority TINYINT,
43 FindingsGroup VARCHAR(50),
44 Finding VARCHAR(200),
45 URL VARCHAR(200),
46 Details VARCHAR(4000)
47);
48
49CREATE TABLE ##bou_BlitzCacheProcs (
50 SPID INT ,
51 QueryType NVARCHAR(258),
52 DatabaseName sysname,
53 AverageCPU DECIMAL(38,4),
54 AverageCPUPerMinute DECIMAL(38,4),
55 TotalCPU DECIMAL(38,4),
56 PercentCPUByType MONEY,
57 PercentCPU MONEY,
58 AverageDuration DECIMAL(38,4),
59 TotalDuration DECIMAL(38,4),
60 PercentDuration MONEY,
61 PercentDurationByType MONEY,
62 AverageReads BIGINT,
63 TotalReads BIGINT,
64 PercentReads MONEY,
65 PercentReadsByType MONEY,
66 ExecutionCount BIGINT,
67 PercentExecutions MONEY,
68 PercentExecutionsByType MONEY,
69 ExecutionsPerMinute MONEY,
70 TotalWrites BIGINT,
71 AverageWrites MONEY,
72 PercentWrites MONEY,
73 PercentWritesByType MONEY,
74 WritesPerMinute MONEY,
75 PlanCreationTime DATETIME,
76 PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
77 LastExecutionTime DATETIME,
78 PlanHandle VARBINARY(64),
79 [Remove Plan Handle From Cache] AS
80 CASE WHEN [PlanHandle] IS NOT NULL
81 THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [PlanHandle], 1) + ');'
82 ELSE 'N/A' END,
83 SqlHandle VARBINARY(64),
84 [Remove SQL Handle From Cache] AS
85 CASE WHEN [SqlHandle] IS NOT NULL
86 THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ');'
87 ELSE 'N/A' END,
88 [SQL Handle More Info] AS
89 CASE WHEN [SqlHandle] IS NOT NULL
90 THEN 'EXEC sp_BlitzCache @OnlySqlHandles = ''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '''; '
91 ELSE 'N/A' END,
92 QueryHash BINARY(8),
93 [Query Hash More Info] AS
94 CASE WHEN [QueryHash] IS NOT NULL
95 THEN 'EXEC sp_BlitzCache @OnlyQueryHashes = ''' + CONVERT(VARCHAR(32), [QueryHash], 1) + '''; '
96 ELSE 'N/A' END,
97 QueryPlanHash BINARY(8),
98 StatementStartOffset INT,
99 StatementEndOffset INT,
100 MinReturnedRows BIGINT,
101 MaxReturnedRows BIGINT,
102 AverageReturnedRows MONEY,
103 TotalReturnedRows BIGINT,
104 LastReturnedRows BIGINT,
105 /*The Memory Grant columns are only supported
106 in certain versions, giggle giggle.
107 */
108 MinGrantKB BIGINT,
109 MaxGrantKB BIGINT,
110 MinUsedGrantKB BIGINT,
111 MaxUsedGrantKB BIGINT,
112 PercentMemoryGrantUsed MONEY,
113 AvgMaxMemoryGrant MONEY,
114 MinSpills BIGINT,
115 MaxSpills BIGINT,
116 TotalSpills BIGINT,
117 AvgSpills MONEY,
118 QueryText NVARCHAR(MAX),
119 QueryPlan XML,
120 /* these next four columns are the total for the type of query.
121 don't actually use them for anything apart from math by type.
122 */
123 TotalWorkerTimeForType BIGINT,
124 TotalElapsedTimeForType BIGINT,
125 TotalReadsForType BIGINT,
126 TotalExecutionCountForType BIGINT,
127 TotalWritesForType BIGINT,
128 NumberOfPlans INT,
129 NumberOfDistinctPlans INT,
130 SerialDesiredMemory FLOAT,
131 SerialRequiredMemory FLOAT,
132 CachedPlanSize FLOAT,
133 CompileTime FLOAT,
134 CompileCPU FLOAT ,
135 CompileMemory FLOAT ,
136 min_worker_time BIGINT,
137 max_worker_time BIGINT,
138 is_forced_plan BIT,
139 is_forced_parameterized BIT,
140 is_cursor BIT,
141 is_optimistic_cursor BIT,
142 is_forward_only_cursor BIT,
143 is_fast_forward_cursor BIT,
144 is_cursor_dynamic BIT,
145 is_parallel BIT,
146 is_forced_serial BIT,
147 is_key_lookup_expensive BIT,
148 key_lookup_cost FLOAT,
149 is_remote_query_expensive BIT,
150 remote_query_cost FLOAT,
151 frequent_execution BIT,
152 parameter_sniffing BIT,
153 unparameterized_query BIT,
154 near_parallel BIT,
155 plan_warnings BIT,
156 plan_multiple_plans BIT,
157 long_running BIT,
158 downlevel_estimator BIT,
159 implicit_conversions BIT,
160 busy_loops BIT,
161 tvf_join BIT,
162 tvf_estimate BIT,
163 compile_timeout BIT,
164 compile_memory_limit_exceeded BIT,
165 warning_no_join_predicate BIT,
166 QueryPlanCost FLOAT,
167 missing_index_count INT,
168 unmatched_index_count INT,
169 min_elapsed_time BIGINT,
170 max_elapsed_time BIGINT,
171 age_minutes MONEY,
172 age_minutes_lifetime MONEY,
173 is_trivial BIT,
174 trace_flags_session VARCHAR(1000),
175 is_unused_grant BIT,
176 function_count INT,
177 clr_function_count INT,
178 is_table_variable BIT,
179 no_stats_warning BIT,
180 relop_warnings BIT,
181 is_table_scan BIT,
182 backwards_scan BIT,
183 forced_index BIT,
184 forced_seek BIT,
185 forced_scan BIT,
186 columnstore_row_mode BIT,
187 is_computed_scalar BIT ,
188 is_sort_expensive BIT,
189 sort_cost FLOAT,
190 is_computed_filter BIT,
191 op_name VARCHAR(100) NULL,
192 index_insert_count INT NULL,
193 index_update_count INT NULL,
194 index_delete_count INT NULL,
195 cx_insert_count INT NULL,
196 cx_update_count INT NULL,
197 cx_delete_count INT NULL,
198 table_insert_count INT NULL,
199 table_update_count INT NULL,
200 table_delete_count INT NULL,
201 index_ops AS (index_insert_count + index_update_count + index_delete_count +
202 cx_insert_count + cx_update_count + cx_delete_count +
203 table_insert_count + table_update_count + table_delete_count),
204 is_row_level BIT,
205 is_spatial BIT,
206 index_dml BIT,
207 table_dml BIT,
208 long_running_low_cpu BIT,
209 low_cost_high_cpu BIT,
210 stale_stats BIT,
211 is_adaptive BIT,
212 index_spool_cost FLOAT,
213 index_spool_rows FLOAT,
214 is_spool_expensive BIT,
215 is_spool_more_rows BIT,
216 estimated_rows FLOAT,
217 is_bad_estimate BIT,
218 is_paul_white_electric BIT,
219 is_row_goal BIT,
220 is_big_spills BIT,
221 is_mstvf BIT,
222 is_mm_join BIT,
223 is_nonsargable BIT,
224 implicit_conversion_info XML,
225 cached_execution_parameters XML,
226 missing_indexes XML,
227 SetOptions VARCHAR(MAX),
228 Warnings VARCHAR(MAX)
229 );
230GO
231
232ALTER PROCEDURE dbo.sp_BlitzCache
233 @Help BIT = 0,
234 @Top INT = NULL,
235 @SortOrder VARCHAR(50) = 'CPU',
236 @UseTriggersAnyway BIT = NULL,
237 @ExportToExcel BIT = 0,
238 @ExpertMode TINYINT = 0,
239 @OutputServerName NVARCHAR(258) = NULL ,
240 @OutputDatabaseName NVARCHAR(258) = NULL ,
241 @OutputSchemaName NVARCHAR(258) = NULL ,
242 @OutputTableName NVARCHAR(258) = NULL ,
243 @ConfigurationDatabaseName NVARCHAR(128) = NULL ,
244 @ConfigurationSchemaName NVARCHAR(258) = NULL ,
245 @ConfigurationTableName NVARCHAR(258) = NULL ,
246 @DurationFilter DECIMAL(38,4) = NULL ,
247 @HideSummary BIT = 0 ,
248 @IgnoreSystemDBs BIT = 1 ,
249 @OnlyQueryHashes VARCHAR(MAX) = NULL ,
250 @IgnoreQueryHashes VARCHAR(MAX) = NULL ,
251 @OnlySqlHandles VARCHAR(MAX) = NULL ,
252 @IgnoreSqlHandles VARCHAR(MAX) = NULL ,
253 @QueryFilter VARCHAR(10) = 'ALL' ,
254 @DatabaseName NVARCHAR(128) = NULL ,
255 @StoredProcName NVARCHAR(128) = NULL,
256 @Reanalyze BIT = 0 ,
257 @SkipAnalysis BIT = 0 ,
258 @BringThePain BIT = 0, /* This will forcibly set @Top to 2,147,483,647 */
259 @MinimumExecutionCount INT = 0,
260 @Debug BIT = 0,
261 @CheckDateOverride DATETIMEOFFSET = NULL,
262 @MinutesBack INT = NULL,
263 @VersionDate DATETIME = NULL OUTPUT
264WITH RECOMPILE
265AS
266BEGIN
267SET NOCOUNT ON;
268SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
269
270DECLARE @Version VARCHAR(30);
271SET @Version = '6.4';
272SET @VersionDate = '20180401';
273
274IF @Help = 1 PRINT '
275sp_BlitzCache from http://FirstResponderKit.org
276
277This script displays your most resource-intensive queries from the plan cache,
278and points to ways you can tune these queries to make them faster.
279
280
281To learn more, visit http://FirstResponderKit.org where you can download new
282versions for free, watch training videos on how it works, get more info on
283the findings, contribute your own code, and more.
284
285Known limitations of this version:
286 - This query will not run on SQL Server 2005.
287 - SQL Server 2008 and 2008R2 have a bug in trigger stats, so that output is
288 excluded by default.
289 - @IgnoreQueryHashes and @OnlyQueryHashes require a CSV list of hashes
290 with no spaces between the hash values.
291 - @OutputServerName is not functional yet.
292
293Unknown limitations of this version:
294 - May or may not be vulnerable to the wick effect.
295
296Changes - for the full list of improvements and fixes in this version, see:
297https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
298
299
300
301MIT License
302
303Copyright (c) 2016 Brent Ozar Unlimited
304
305Permission is hereby granted, free of charge, to any person obtaining a copy
306of this software and associated documentation files (the "Software"), to deal
307in the Software without restriction, including without limitation the rights
308to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
309copies of the Software, and to permit persons to whom the Software is
310furnished to do so, subject to the following conditions:
311
312The above copyright notice and this permission notice shall be included in all
313copies or substantial portions of the Software.
314
315THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
316IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
317FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
318AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
319LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
320OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
321SOFTWARE.
322';
323
324DECLARE @nl NVARCHAR(2) = NCHAR(13) + NCHAR(10) ;
325
326IF @Help = 1
327BEGIN
328 SELECT N'@Help' AS [Parameter Name] ,
329 N'BIT' AS [Data Type] ,
330 N'Displays this help message.' AS [Parameter Description]
331
332 UNION ALL
333 SELECT N'@Top',
334 N'INT',
335 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.'
336
337 UNION ALL
338 SELECT N'@SortOrder',
339 N'VARCHAR(10)',
340 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", "Spills". 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.'
341
342 UNION ALL
343 SELECT N'@UseTriggersAnyway',
344 N'BIT',
345 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.'
346
347 UNION ALL
348 SELECT N'@ExportToExcel',
349 N'BIT',
350 N'Prepare output for exporting to Excel. Newlines and additional whitespace are removed from query text and the execution plan is not displayed.'
351
352 UNION ALL
353 SELECT N'@ExpertMode',
354 N'TINYINT',
355 N'Default 0. When set to 1, results include more columns. When 2, mode is optimized for Opserver, the open source dashboard.'
356
357 UNION ALL
358 SELECT N'@OutputDatabaseName',
359 N'NVARCHAR(128)',
360 N'The output database. If this does not exist SQL Server will divide by zero and everything will fall apart.'
361
362 UNION ALL
363 SELECT N'@OutputSchemaName',
364 N'NVARCHAR(258)',
365 N'The output schema. If this does not exist SQL Server will divide by zero and everything will fall apart.'
366
367 UNION ALL
368 SELECT N'@OutputTableName',
369 N'NVARCHAR(258)',
370 N'The output table. If this does not exist, it will be created for you.'
371
372 UNION ALL
373 SELECT N'@DurationFilter',
374 N'DECIMAL(38,4)',
375 N'Excludes queries with an average duration (in seconds) less than @DurationFilter.'
376
377 UNION ALL
378 SELECT N'@HideSummary',
379 N'BIT',
380 N'Hides the findings summary result set.'
381
382 UNION ALL
383 SELECT N'@IgnoreSystemDBs',
384 N'BIT',
385 N'Ignores plans found in the system databases (master, model, msdb, tempdb, and resourcedb)'
386
387 UNION ALL
388 SELECT N'@OnlyQueryHashes',
389 N'VARCHAR(MAX)',
390 N'A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored.'
391
392 UNION ALL
393 SELECT N'@IgnoreQueryHashes',
394 N'VARCHAR(MAX)',
395 N'A list of query hashes to ignore.'
396
397 UNION ALL
398 SELECT N'@OnlySqlHandles',
399 N'VARCHAR(MAX)',
400 N'One or more sql_handles to use for filtering results.'
401
402 UNION ALL
403 SELECT N'@IgnoreSqlHandles',
404 N'VARCHAR(MAX)',
405 N'One or more sql_handles to ignore.'
406
407 UNION ALL
408 SELECT N'@DatabaseName',
409 N'NVARCHAR(128)',
410 N'A database name which is used for filtering results.'
411
412 UNION ALL
413 SELECT N'@StoredProcName',
414 N'NVARCHAR(128)',
415 N'Name of stored procedure you want to find plans for.'
416
417 UNION ALL
418 SELECT N'@BringThePain',
419 N'BIT',
420 N'This forces sp_BlitzCache to examine the entire plan cache. Be careful running this on servers with a lot of memory or a large execution plan cache.'
421
422 UNION ALL
423 SELECT N'@QueryFilter',
424 N'VARCHAR(10)',
425 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).'
426
427 UNION ALL
428 SELECT N'@Reanalyze',
429 N'BIT',
430 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'
431
432 UNION ALL
433 SELECT N'@MinimumExecutionCount',
434 N'INT',
435 N'Queries with fewer than this number of executions will be omitted from results.'
436
437 UNION ALL
438 SELECT N'@Debug',
439 N'BIT',
440 N'Setting this to 1 will print dynamic SQL and select data from all tables used.'
441
442 UNION ALL
443 SELECT N'@MinutesBack',
444 N'INT',
445 N'How many minutes back to begin plan cache analysis. If you put in a positive number, we''ll flip it to negtive.';
446
447
448 /* Column definitions */
449 SELECT N'# Executions' AS [Column Name],
450 N'BIGINT' AS [Data Type],
451 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]
452
453 UNION ALL
454 SELECT N'Executions / Minute',
455 N'MONEY',
456 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.'
457
458 UNION ALL
459 SELECT N'Execution Weight',
460 N'MONEY',
461 N'An arbitrary metric of total "execution-ness". A weight of 2 is "one more" than a weight of 1.'
462
463 UNION ALL
464 SELECT N'Database',
465 N'sysname',
466 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.'
467
468 UNION ALL
469 SELECT N'Total CPU',
470 N'BIGINT',
471 N'Total CPU time, reported in milliseconds, that was consumed by all executions of this query since the last compilation.'
472
473 UNION ALL
474 SELECT N'Avg CPU',
475 N'BIGINT',
476 N'Average CPU time, reported in milliseconds, consumed by each execution of this query since the last compilation.'
477
478 UNION ALL
479 SELECT N'CPU Weight',
480 N'MONEY',
481 N'An arbitrary metric of total "CPU-ness". A weight of 2 is "one more" than a weight of 1.'
482
483 UNION ALL
484 SELECT N'Total Duration',
485 N'BIGINT',
486 N'Total elapsed time, reported in milliseconds, consumed by all executions of this query since last compilation.'
487
488 UNION ALL
489 SELECT N'Avg Duration',
490 N'BIGINT',
491 N'Average elapsed time, reported in milliseconds, consumed by each execution of this query since the last compilation.'
492
493 UNION ALL
494 SELECT N'Duration Weight',
495 N'MONEY',
496 N'An arbitrary metric of total "Duration-ness". A weight of 2 is "one more" than a weight of 1.'
497
498 UNION ALL
499 SELECT N'Total Reads',
500 N'BIGINT',
501 N'Total logical reads performed by this query since last compilation.'
502
503 UNION ALL
504 SELECT N'Average Reads',
505 N'BIGINT',
506 N'Average logical reads performed by each execution of this query since the last compilation.'
507
508 UNION ALL
509 SELECT N'Read Weight',
510 N'MONEY',
511 N'An arbitrary metric of "Read-ness". A weight of 2 is "one more" than a weight of 1.'
512
513 UNION ALL
514 SELECT N'Total Writes',
515 N'BIGINT',
516 N'Total logical writes performed by this query since last compilation.'
517
518 UNION ALL
519 SELECT N'Average Writes',
520 N'BIGINT',
521 N'Average logical writes performed by each execution this query since last compilation.'
522
523 UNION ALL
524 SELECT N'Write Weight',
525 N'MONEY',
526 N'An arbitrary metric of "Write-ness". A weight of 2 is "one more" than a weight of 1.'
527
528 UNION ALL
529 SELECT N'Query Type',
530 N'NVARCHAR(258)',
531 N'The type of query being examined. This can be "Procedure", "Statement", or "Trigger".'
532
533 UNION ALL
534 SELECT N'Query Text',
535 N'NVARCHAR(4000)',
536 N'The text of the query. This may be truncated by either SQL Server or by sp_BlitzCache(tm) for display purposes.'
537
538 UNION ALL
539 SELECT N'% Executions (Type)',
540 N'MONEY',
541 N'Percent of executions relative to the type of query - e.g. 17.2% of all stored procedure executions.'
542
543 UNION ALL
544 SELECT N'% CPU (Type)',
545 N'MONEY',
546 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.'
547
548 UNION ALL
549 SELECT N'% Duration (Type)',
550 N'MONEY',
551 N'Percent of elapsed time consumed by this query for a given type of query - e.g. 12% of all statements executed.'
552
553 UNION ALL
554 SELECT N'% Reads (Type)',
555 N'MONEY',
556 N'Percent of reads consumed by this query for a given type of query - e.g. 34.2% of all stored procedures executed.'
557
558 UNION ALL
559 SELECT N'% Writes (Type)',
560 N'MONEY',
561 N'Percent of writes performed by this query for a given type of query - e.g. 43.2% of all statements executed.'
562
563 UNION ALL
564 SELECT N'Total Rows',
565 N'BIGINT',
566 N'Total number of rows returned for all executions of this query. This only applies to query level stats, not stored procedures or triggers.'
567
568 UNION ALL
569 SELECT N'Average Rows',
570 N'MONEY',
571 N'Average number of rows returned by each execution of the query.'
572
573 UNION ALL
574 SELECT N'Min Rows',
575 N'BIGINT',
576 N'The minimum number of rows returned by any execution of this query.'
577
578 UNION ALL
579 SELECT N'Max Rows',
580 N'BIGINT',
581 N'The maximum number of rows returned by any execution of this query.'
582
583 UNION ALL
584 SELECT N'MinGrantKB',
585 N'BIGINT',
586 N'The minimum memory grant the query received in kb.'
587
588 UNION ALL
589 SELECT N'MaxGrantKB',
590 N'BIGINT',
591 N'The maximum memory grant the query received in kb.'
592
593 UNION ALL
594 SELECT N'MinUsedGrantKB',
595 N'BIGINT',
596 N'The minimum used memory grant the query received in kb.'
597
598 UNION ALL
599 SELECT N'MaxUsedGrantKB',
600 N'BIGINT',
601 N'The maximum used memory grant the query received in kb.';
602
603 SELECT N'MinSpills',
604 N'BIGINT',
605 N'The minimum amount this query has spilled to tempdb in 8k pages.'
606
607 UNION ALL
608 SELECT N'MaxSpills',
609 N'BIGINT',
610 N'The maximum amount this query has spilled to tempdb in 8k pages.'
611
612 UNION ALL
613 SELECT N'TotalSpills',
614 N'BIGINT',
615 N'The total amount this query has spilled to tempdb in 8k pages.'
616
617 UNION ALL
618 SELECT N'AvgSpills',
619 N'BIGINT',
620 N'The average amount this query has spilled to tempdb in 8k pages.'
621
622 UNION ALL
623 SELECT N'PercentMemoryGrantUsed',
624 N'MONEY',
625 N'Result of dividing the maximum grant used by the minimum granted.'
626
627 UNION ALL
628 SELECT N'AvgMaxMemoryGrant',
629 N'MONEY',
630 N'The average maximum memory grant for a query.'
631
632 UNION ALL
633 SELECT N'# Plans',
634 N'INT',
635 N'The total number of execution plans found that match a given query.'
636
637 UNION ALL
638 SELECT N'# Distinct Plans',
639 N'INT',
640 N'The number of distinct execution plans that match a given query. '
641 + NCHAR(13) + NCHAR(10)
642 + N'This may be caused by running the same query across multiple databases or because of a lack of proper parameterization in the database.'
643
644 UNION ALL
645 SELECT N'Created At',
646 N'DATETIME',
647 N'Time that the execution plan was last compiled.'
648
649 UNION ALL
650 SELECT N'Last Execution',
651 N'DATETIME',
652 N'The last time that this query was executed.'
653
654 UNION ALL
655 SELECT N'Query Plan',
656 N'XML',
657 N'The query plan. Click to display a graphical plan or, if you need to patch SSMS, a pile of XML.'
658
659 UNION ALL
660 SELECT N'Plan Handle',
661 N'VARBINARY(64)',
662 N'An arbitrary identifier referring to the compiled plan this query is a part of.'
663
664 UNION ALL
665 SELECT N'SQL Handle',
666 N'VARBINARY(64)',
667 N'An arbitrary identifier referring to a batch or stored procedure that this query is a part of.'
668
669 UNION ALL
670 SELECT N'Query Hash',
671 N'BINARY(8)',
672 N'A hash of the query. Queries with the same query hash have similar logic but only differ by literal values or database.'
673
674 UNION ALL
675 SELECT N'Warnings',
676 N'VARCHAR(MAX)',
677 N'A list of individual warnings generated by this query.' ;
678
679
680
681 /* Configuration table description */
682 SELECT N'Frequent Execution Threshold' AS [Configuration Parameter] ,
683 N'100' AS [Default Value] ,
684 N'Executions / Minute' AS [Unit of Measure] ,
685 N'Executions / Minute before a "Frequent Execution Threshold" warning is triggered.' AS [Description]
686
687 UNION ALL
688 SELECT N'Parameter Sniffing Variance Percent' ,
689 N'30' ,
690 N'Percent' ,
691 N'Variance required between min/max values and average values before a "Parameter Sniffing" warning is triggered. Applies to worker time and returned rows.'
692
693 UNION ALL
694 SELECT N'Parameter Sniffing IO Threshold' ,
695 N'100,000' ,
696 N'Logical reads' ,
697 N'Minimum number of average logical reads before parameter sniffing checks are evaluated.'
698
699 UNION ALL
700 SELECT N'Cost Threshold for Parallelism Warning' AS [Configuration Parameter] ,
701 N'10' ,
702 N'Percent' ,
703 N'Trigger a "Nearly Parallel" warning when a query''s cost is within X percent of the cost threshold for parallelism.'
704
705 UNION ALL
706 SELECT N'Long Running Query Warning' AS [Configuration Parameter] ,
707 N'300' ,
708 N'Seconds' ,
709 N'Triggers a "Long Running Query Warning" when average duration, max CPU time, or max clock time is higher than this number.'
710
711 UNION ALL
712 SELECT N'Unused Memory Grant Warning' AS [Configuration Parameter] ,
713 N'10' ,
714 N'Percent' ,
715 N'Triggers an "Unused Memory Grant Warning" when a query uses >= X percent of its memory grant.';
716 RETURN;
717END;
718
719/*Validate version*/
720IF (
721SELECT
722 CASE
723 WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '8%' THEN 0
724 WHEN CONVERT(NVARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) LIKE '9%' THEN 0
725 ELSE 1
726 END
727) = 0
728BEGIN
729 DECLARE @version_msg VARCHAR(8000);
730 SELECT @version_msg = 'Sorry, sp_BlitzCache doesn''t work on versions of SQL prior to 2008.' + REPLICATE(CHAR(13), 7933);
731 PRINT @version_msg;
732 RETURN;
733END;
734
735/* Set @Top based on sort */
736IF (
737 @Top IS NULL
738 AND LOWER(@SortOrder) IN ( 'all', 'all sort' )
739 )
740 BEGIN
741 SET @Top = 5;
742 END;
743
744IF (
745 @Top IS NULL
746 AND LOWER(@SortOrder) NOT IN ( 'all', 'all sort' )
747 )
748 BEGIN
749 SET @Top = 10;
750 END;
751
752/* validate user inputs */
753IF @Top IS NULL
754 OR @SortOrder IS NULL
755 OR @QueryFilter IS NULL
756 OR @Reanalyze IS NULL
757BEGIN
758 RAISERROR(N'Several parameters (@Top, @SortOrder, @QueryFilter, @renalyze) are required. Do not set them to NULL. Please try again.', 16, 1) WITH NOWAIT;
759 RETURN;
760END;
761
762RAISERROR(N'Checking @MinutesBack validity.', 0, 1) WITH NOWAIT;
763IF @MinutesBack IS NOT NULL
764 BEGIN
765 IF @MinutesBack > 0
766 BEGIN
767 RAISERROR(N'Setting @MinutesBack to a negative number', 0, 1) WITH NOWAIT;
768 SET @MinutesBack *=-1;
769 END;
770 IF @MinutesBack = 0
771 BEGIN
772 RAISERROR(N'@MinutesBack can''t be 0, setting to -1', 0, 1) WITH NOWAIT;
773 SET @MinutesBack = -1;
774 END;
775 END;
776
777
778RAISERROR(N'Creating temp tables for results and warnings.', 0, 1) WITH NOWAIT;
779
780IF OBJECT_ID('tempdb.dbo.##bou_BlitzCacheResults') IS NULL
781BEGIN
782 CREATE TABLE ##bou_BlitzCacheResults (
783 SPID INT,
784 ID INT IDENTITY(1,1),
785 CheckID INT,
786 Priority TINYINT,
787 FindingsGroup VARCHAR(50),
788 Finding VARCHAR(200),
789 URL VARCHAR(200),
790 Details VARCHAR(4000)
791 );
792END;
793
794IF OBJECT_ID('tempdb.dbo.##bou_BlitzCacheProcs') IS NULL
795BEGIN
796 CREATE TABLE ##bou_BlitzCacheProcs (
797 SPID INT ,
798 QueryType NVARCHAR(258),
799 DatabaseName sysname,
800 AverageCPU DECIMAL(38,4),
801 AverageCPUPerMinute DECIMAL(38,4),
802 TotalCPU DECIMAL(38,4),
803 PercentCPUByType MONEY,
804 PercentCPU MONEY,
805 AverageDuration DECIMAL(38,4),
806 TotalDuration DECIMAL(38,4),
807 PercentDuration MONEY,
808 PercentDurationByType MONEY,
809 AverageReads BIGINT,
810 TotalReads BIGINT,
811 PercentReads MONEY,
812 PercentReadsByType MONEY,
813 ExecutionCount BIGINT,
814 PercentExecutions MONEY,
815 PercentExecutionsByType MONEY,
816 ExecutionsPerMinute MONEY,
817 TotalWrites BIGINT,
818 AverageWrites MONEY,
819 PercentWrites MONEY,
820 PercentWritesByType MONEY,
821 WritesPerMinute MONEY,
822 PlanCreationTime DATETIME,
823 PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
824 LastExecutionTime DATETIME,
825 PlanHandle VARBINARY(64),
826 [Remove Plan Handle From Cache] AS
827 CASE WHEN [PlanHandle] IS NOT NULL
828 THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [PlanHandle], 1) + ');'
829 ELSE 'N/A' END,
830 SqlHandle VARBINARY(64),
831 [Remove SQL Handle From Cache] AS
832 CASE WHEN [SqlHandle] IS NOT NULL
833 THEN 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ');'
834 ELSE 'N/A' END,
835 [SQL Handle More Info] AS
836 CASE WHEN [SqlHandle] IS NOT NULL
837 THEN 'EXEC sp_BlitzCache @OnlySqlHandles = ''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '''; '
838 ELSE 'N/A' END,
839 QueryHash BINARY(8),
840 [Query Hash More Info] AS
841 CASE WHEN [QueryHash] IS NOT NULL
842 THEN 'EXEC sp_BlitzCache @OnlyQueryHashes = ''' + CONVERT(VARCHAR(32), [QueryHash], 1) + '''; '
843 ELSE 'N/A' END,
844 QueryPlanHash BINARY(8),
845 StatementStartOffset INT,
846 StatementEndOffset INT,
847 MinReturnedRows BIGINT,
848 MaxReturnedRows BIGINT,
849 AverageReturnedRows MONEY,
850 TotalReturnedRows BIGINT,
851 LastReturnedRows BIGINT,
852 MinGrantKB BIGINT,
853 MaxGrantKB BIGINT,
854 MinUsedGrantKB BIGINT,
855 MaxUsedGrantKB BIGINT,
856 PercentMemoryGrantUsed MONEY,
857 AvgMaxMemoryGrant MONEY,
858 MinSpills BIGINT,
859 MaxSpills BIGINT,
860 TotalSpills BIGINT,
861 AvgSpills MONEY,
862 QueryText NVARCHAR(MAX),
863 QueryPlan XML,
864 /* these next four columns are the total for the type of query.
865 don't actually use them for anything apart from math by type.
866 */
867 TotalWorkerTimeForType BIGINT,
868 TotalElapsedTimeForType BIGINT,
869 TotalReadsForType BIGINT,
870 TotalExecutionCountForType BIGINT,
871 TotalWritesForType BIGINT,
872 NumberOfPlans INT,
873 NumberOfDistinctPlans INT,
874 SerialDesiredMemory FLOAT,
875 SerialRequiredMemory FLOAT,
876 CachedPlanSize FLOAT,
877 CompileTime FLOAT,
878 CompileCPU FLOAT ,
879 CompileMemory FLOAT ,
880 min_worker_time BIGINT,
881 max_worker_time BIGINT,
882 is_forced_plan BIT,
883 is_forced_parameterized BIT,
884 is_cursor BIT,
885 is_optimistic_cursor BIT,
886 is_forward_only_cursor BIT,
887 is_fast_forward_cursor BIT,
888 is_cursor_dynamic BIT,
889 is_parallel BIT,
890 is_forced_serial BIT,
891 is_key_lookup_expensive BIT,
892 key_lookup_cost FLOAT,
893 is_remote_query_expensive BIT,
894 remote_query_cost FLOAT,
895 frequent_execution BIT,
896 parameter_sniffing BIT,
897 unparameterized_query BIT,
898 near_parallel BIT,
899 plan_warnings BIT,
900 plan_multiple_plans BIT,
901 long_running BIT,
902 downlevel_estimator BIT,
903 implicit_conversions BIT,
904 busy_loops BIT,
905 tvf_join BIT,
906 tvf_estimate BIT,
907 compile_timeout BIT,
908 compile_memory_limit_exceeded BIT,
909 warning_no_join_predicate BIT,
910 QueryPlanCost FLOAT,
911 missing_index_count INT,
912 unmatched_index_count INT,
913 min_elapsed_time BIGINT,
914 max_elapsed_time BIGINT,
915 age_minutes MONEY,
916 age_minutes_lifetime MONEY,
917 is_trivial BIT,
918 trace_flags_session VARCHAR(1000),
919 is_unused_grant BIT,
920 function_count INT,
921 clr_function_count INT,
922 is_table_variable BIT,
923 no_stats_warning BIT,
924 relop_warnings BIT,
925 is_table_scan BIT,
926 backwards_scan BIT,
927 forced_index BIT,
928 forced_seek BIT,
929 forced_scan BIT,
930 columnstore_row_mode BIT,
931 is_computed_scalar BIT ,
932 is_sort_expensive BIT,
933 sort_cost FLOAT,
934 is_computed_filter BIT,
935 op_name VARCHAR(100) NULL,
936 index_insert_count INT NULL,
937 index_update_count INT NULL,
938 index_delete_count INT NULL,
939 cx_insert_count INT NULL,
940 cx_update_count INT NULL,
941 cx_delete_count INT NULL,
942 table_insert_count INT NULL,
943 table_update_count INT NULL,
944 table_delete_count INT NULL,
945 index_ops AS (index_insert_count + index_update_count + index_delete_count +
946 cx_insert_count + cx_update_count + cx_delete_count +
947 table_insert_count + table_update_count + table_delete_count),
948 is_row_level BIT,
949 is_spatial BIT,
950 index_dml BIT,
951 table_dml BIT,
952 long_running_low_cpu BIT,
953 low_cost_high_cpu BIT,
954 stale_stats BIT,
955 is_adaptive BIT,
956 index_spool_cost FLOAT,
957 index_spool_rows FLOAT,
958 is_spool_expensive BIT,
959 is_spool_more_rows BIT,
960 estimated_rows FLOAT,
961 is_bad_estimate BIT,
962 is_paul_white_electric BIT,
963 is_row_goal BIT,
964 is_big_spills BIT,
965 is_mstvf BIT,
966 is_mm_join BIT,
967 is_nonsargable BIT,
968 implicit_conversion_info XML,
969 cached_execution_parameters XML,
970 missing_indexes XML,
971 SetOptions VARCHAR(MAX),
972 Warnings VARCHAR(MAX)
973 );
974END;
975
976DECLARE @DurationFilter_i INT,
977 @MinMemoryPerQuery INT,
978 @msg NVARCHAR(4000) ;
979
980
981IF @BringThePain = 1
982 BEGIN
983 RAISERROR(N'You have chosen to bring the pain. Setting top to 2147483647.', 0, 1) WITH NOWAIT;
984 SET @Top = 2147483647;
985 END;
986
987/* Change duration from seconds to milliseconds */
988IF @DurationFilter IS NOT NULL
989 BEGIN
990 RAISERROR(N'Converting Duration Filter to milliseconds', 0, 1) WITH NOWAIT;
991 SET @DurationFilter_i = CAST((@DurationFilter * 1000.0) AS INT);
992 END;
993
994RAISERROR(N'Checking database validity', 0, 1) WITH NOWAIT;
995SET @DatabaseName = LTRIM(RTRIM(@DatabaseName)) ;
996IF (DB_ID(@DatabaseName)) IS NULL AND @DatabaseName <> N''
997BEGIN
998 RAISERROR('The database you specified does not exist. Please check the name and try again.', 16, 1);
999 RETURN;
1000END;
1001IF (SELECT DATABASEPROPERTYEX(@DatabaseName, 'Status')) <> 'ONLINE'
1002BEGIN
1003 RAISERROR('The database you specified is not readable. Please check the name and try again. Better yet, check your server.', 16, 1);
1004 RETURN;
1005END;
1006
1007SELECT @MinMemoryPerQuery = CONVERT(INT, c.value) FROM sys.configurations AS c WHERE c.name = 'min memory per query (KB)';
1008
1009SET @SortOrder = LOWER(@SortOrder);
1010SET @SortOrder = REPLACE(REPLACE(@SortOrder, 'average', 'avg'), '.', '');
1011SET @SortOrder = REPLACE(@SortOrder, 'executions per minute', 'avg executions');
1012SET @SortOrder = REPLACE(@SortOrder, 'executions / minute', 'avg executions');
1013SET @SortOrder = REPLACE(@SortOrder, 'xpm', 'avg executions');
1014SET @SortOrder = REPLACE(@SortOrder, 'recent compilations', 'compiles');
1015
1016RAISERROR(N'Checking sort order', 0, 1) WITH NOWAIT;
1017IF @SortOrder NOT IN ('cpu', 'avg cpu', 'reads', 'avg reads', 'writes', 'avg writes',
1018 'duration', 'avg duration', 'executions', 'avg executions',
1019 'compiles', 'memory grant', 'avg memory grant',
1020 'spills', 'avg spills', 'all', 'all avg')
1021 BEGIN
1022 RAISERROR(N'Invalid sort order chosen, reverting to cpu', 0, 1) WITH NOWAIT;
1023 SET @SortOrder = 'cpu';
1024 END;
1025
1026SELECT @OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
1027 @OutputSchemaName = QUOTENAME(@OutputSchemaName),
1028 @OutputTableName = QUOTENAME(@OutputTableName);
1029
1030SET @QueryFilter = LOWER(@QueryFilter);
1031
1032IF LEFT(@QueryFilter, 3) NOT IN ('all', 'sta', 'pro', 'fun')
1033 BEGIN
1034 RAISERROR(N'Invalid query filter chosen. Reverting to all.', 0, 1) WITH NOWAIT;
1035 SET @QueryFilter = 'all';
1036 END;
1037
1038IF @SkipAnalysis = 1
1039 BEGIN
1040 RAISERROR(N'Skip Analysis set to 1, hiding Summary', 0, 1) WITH NOWAIT;
1041 SET @HideSummary = 1;
1042 END;
1043
1044IF @Reanalyze = 1 AND OBJECT_ID('tempdb..##bou_BlitzCacheResults') IS NULL
1045 BEGIN
1046 RAISERROR(N'##bou_BlitzCacheResults does not exist, can''t reanalyze', 0, 1) WITH NOWAIT;
1047 SET @Reanalyze = 0;
1048 END;
1049
1050IF @Reanalyze = 0
1051 BEGIN
1052 RAISERROR(N'Cleaning up old warnings for your SPID', 0, 1) WITH NOWAIT;
1053 DELETE ##bou_BlitzCacheResults
1054 WHERE SPID = @@SPID
1055 OPTION (RECOMPILE) ;
1056 RAISERROR(N'Cleaning up old plans for your SPID', 0, 1) WITH NOWAIT;
1057 DELETE ##bou_BlitzCacheProcs
1058 WHERE SPID = @@SPID
1059 OPTION (RECOMPILE) ;
1060 END;
1061
1062IF @Reanalyze = 1
1063 BEGIN
1064 RAISERROR(N'Reanalyzing current data, skipping to results', 0, 1) WITH NOWAIT;
1065 GOTO Results;
1066 END;
1067
1068IF @SortOrder IN ('all', 'all avg')
1069 BEGIN
1070 RAISERROR(N'Checking all sort orders, please be patient', 0, 1) WITH NOWAIT;
1071 GOTO AllSorts;
1072 END;
1073
1074
1075RAISERROR(N'Creating temp tables for internal processing', 0, 1) WITH NOWAIT;
1076IF OBJECT_ID('tempdb..#only_query_hashes') IS NOT NULL
1077 DROP TABLE #only_query_hashes ;
1078
1079IF OBJECT_ID('tempdb..#ignore_query_hashes') IS NOT NULL
1080 DROP TABLE #ignore_query_hashes ;
1081
1082IF OBJECT_ID('tempdb..#only_sql_handles') IS NOT NULL
1083 DROP TABLE #only_sql_handles ;
1084
1085IF OBJECT_ID('tempdb..#ignore_sql_handles') IS NOT NULL
1086 DROP TABLE #ignore_sql_handles ;
1087
1088IF OBJECT_ID('tempdb..#p') IS NOT NULL
1089 DROP TABLE #p;
1090
1091IF OBJECT_ID ('tempdb..#checkversion') IS NOT NULL
1092 DROP TABLE #checkversion;
1093
1094IF OBJECT_ID ('tempdb..#configuration') IS NOT NULL
1095 DROP TABLE #configuration;
1096
1097IF OBJECT_ID ('tempdb..#stored_proc_info') IS NOT NULL
1098 DROP TABLE #stored_proc_info;
1099
1100IF OBJECT_ID ('tempdb..#plan_creation') IS NOT NULL
1101 DROP TABLE #plan_creation;
1102
1103IF OBJECT_ID ('tempdb..#est_rows') IS NOT NULL
1104 DROP TABLE #est_rows;
1105
1106IF OBJECT_ID ('tempdb..#plan_cost') IS NOT NULL
1107 DROP TABLE #plan_cost;
1108
1109IF OBJECT_ID ('tempdb..#proc_costs') IS NOT NULL
1110 DROP TABLE #proc_costs;
1111
1112IF OBJECT_ID ('tempdb..#stats_agg') IS NOT NULL
1113 DROP TABLE #stats_agg;
1114
1115IF OBJECT_ID ('tempdb..#trace_flags') IS NOT NULL
1116 DROP TABLE #trace_flags;
1117
1118IF OBJECT_ID('tempdb..#variable_info') IS NOT NULL
1119 DROP TABLE #variable_info;
1120
1121IF OBJECT_ID('tempdb..#conversion_info') IS NOT NULL
1122 DROP TABLE #conversion_info;
1123
1124
1125IF OBJECT_ID('tempdb..#missing_index_xml') IS NOT NULL
1126 DROP TABLE #missing_index_xml;
1127
1128IF OBJECT_ID('tempdb..#missing_index_schema') IS NOT NULL
1129 DROP TABLE #missing_index_schema;
1130
1131IF OBJECT_ID('tempdb..#missing_index_usage') IS NOT NULL
1132 DROP TABLE #missing_index_usage;
1133
1134IF OBJECT_ID('tempdb..#missing_index_detail') IS NOT NULL
1135 DROP TABLE #missing_index_detail;
1136
1137IF OBJECT_ID('tempdb..#missing_index_pretty') IS NOT NULL
1138 DROP TABLE #missing_index_pretty;
1139
1140
1141CREATE TABLE #only_query_hashes (
1142 query_hash BINARY(8)
1143);
1144
1145CREATE TABLE #ignore_query_hashes (
1146 query_hash BINARY(8)
1147);
1148
1149CREATE TABLE #only_sql_handles (
1150 sql_handle VARBINARY(64)
1151);
1152
1153CREATE TABLE #ignore_sql_handles (
1154 sql_handle VARBINARY(64)
1155);
1156
1157CREATE TABLE #p (
1158 SqlHandle VARBINARY(64),
1159 TotalCPU BIGINT,
1160 TotalDuration BIGINT,
1161 TotalReads BIGINT,
1162 TotalWrites BIGINT,
1163 ExecutionCount BIGINT
1164);
1165
1166CREATE TABLE #checkversion (
1167 version NVARCHAR(128),
1168 common_version AS SUBSTRING(version, 1, CHARINDEX('.', version) + 1 ),
1169 major AS PARSENAME(CONVERT(VARCHAR(32), version), 4),
1170 minor AS PARSENAME(CONVERT(VARCHAR(32), version), 3),
1171 build AS PARSENAME(CONVERT(VARCHAR(32), version), 2),
1172 revision AS PARSENAME(CONVERT(VARCHAR(32), version), 1)
1173);
1174
1175CREATE TABLE #configuration (
1176 parameter_name VARCHAR(100),
1177 value DECIMAL(38,0)
1178);
1179
1180CREATE TABLE #plan_creation
1181(
1182 percent_24 DECIMAL(5, 2),
1183 percent_4 DECIMAL(5, 2),
1184 percent_1 DECIMAL(5, 2),
1185 total_plans INT,
1186 SPID INT
1187);
1188
1189CREATE TABLE #est_rows
1190(
1191 QueryHash BINARY(8),
1192 estimated_rows FLOAT
1193);
1194
1195CREATE TABLE #plan_cost
1196(
1197 QueryPlanCost FLOAT,
1198 SqlHandle VARBINARY(64),
1199 QueryHash BINARY(8),
1200 QueryPlanHash BINARY(8)
1201);
1202
1203CREATE TABLE #proc_costs
1204(
1205 PlanTotalQuery FLOAT,
1206 PlanHandle VARBINARY(64),
1207 SqlHandle VARBINARY(64)
1208);
1209
1210CREATE TABLE #stats_agg
1211(
1212 SqlHandle VARBINARY(64),
1213 LastUpdate DATETIME2(7),
1214 ModificationCount INT,
1215 SamplingPercent FLOAT,
1216 [Statistics] NVARCHAR(258),
1217 [Table] NVARCHAR(258),
1218 [Schema] NVARCHAR(258),
1219 [Database] NVARCHAR(258),
1220);
1221
1222CREATE TABLE #trace_flags
1223(
1224 SqlHandle VARBINARY(64),
1225 QueryHash BINARY(8),
1226 global_trace_flags VARCHAR(1000),
1227 session_trace_flags VARCHAR(1000)
1228);
1229
1230CREATE TABLE #stored_proc_info
1231(
1232 SPID INT,
1233 SqlHandle VARBINARY(64),
1234 QueryHash BINARY(8),
1235 variable_name NVARCHAR(258),
1236 variable_datatype NVARCHAR(258),
1237 converted_column_name NVARCHAR(258),
1238 compile_time_value NVARCHAR(258),
1239 proc_name NVARCHAR(1000),
1240 column_name NVARCHAR(258),
1241 converted_to NVARCHAR(258)
1242);
1243
1244CREATE TABLE #variable_info
1245(
1246 SPID INT,
1247 QueryHash BINARY(8),
1248 SqlHandle VARBINARY(64),
1249 proc_name NVARCHAR(1000),
1250 variable_name NVARCHAR(258),
1251 variable_datatype NVARCHAR(258),
1252 compile_time_value NVARCHAR(258)
1253);
1254
1255CREATE TABLE #conversion_info
1256(
1257 SPID INT,
1258 QueryHash BINARY(8),
1259 SqlHandle VARBINARY(64),
1260 proc_name NVARCHAR(258),
1261 expression NVARCHAR(4000),
1262 at_charindex AS CHARINDEX('@', expression),
1263 bracket_charindex AS CHARINDEX(']', expression, CHARINDEX('@', expression)) - CHARINDEX('@', expression),
1264 comma_charindex AS CHARINDEX(',', expression) + 1,
1265 second_comma_charindex AS
1266 CHARINDEX(',', expression, CHARINDEX(',', expression) + 1) - CHARINDEX(',', expression) - 1,
1267 equal_charindex AS CHARINDEX('=', expression) + 1,
1268 paren_charindex AS CHARINDEX('(', expression) + 1,
1269 comma_paren_charindex AS
1270 CHARINDEX(',', expression, CHARINDEX('(', expression) + 1) - CHARINDEX('(', expression) - 1,
1271 convert_implicit_charindex AS CHARINDEX('=CONVERT_IMPLICIT', expression)
1272);
1273
1274
1275CREATE TABLE #missing_index_xml
1276(
1277 QueryHash BINARY(8),
1278 SqlHandle VARBINARY(64),
1279 impact FLOAT,
1280 index_xml XML
1281);
1282
1283
1284CREATE TABLE #missing_index_schema
1285(
1286 QueryHash BINARY(8),
1287 SqlHandle VARBINARY(64),
1288 impact FLOAT,
1289 database_name NVARCHAR(128),
1290 schema_name NVARCHAR(128),
1291 table_name NVARCHAR(128),
1292 index_xml XML
1293);
1294
1295
1296CREATE TABLE #missing_index_usage
1297(
1298 QueryHash BINARY(8),
1299 SqlHandle VARBINARY(64),
1300 impact FLOAT,
1301 database_name NVARCHAR(128),
1302 schema_name NVARCHAR(128),
1303 table_name NVARCHAR(128),
1304 usage NVARCHAR(128),
1305 index_xml XML
1306);
1307
1308
1309CREATE TABLE #missing_index_detail
1310(
1311 QueryHash BINARY(8),
1312 SqlHandle VARBINARY(64),
1313 impact FLOAT,
1314 database_name NVARCHAR(128),
1315 schema_name NVARCHAR(128),
1316 table_name NVARCHAR(128),
1317 usage NVARCHAR(128),
1318 column_name NVARCHAR(128)
1319);
1320
1321
1322CREATE TABLE #missing_index_pretty
1323(
1324 QueryHash BINARY(8),
1325 SqlHandle VARBINARY(64),
1326 impact FLOAT,
1327 database_name NVARCHAR(128),
1328 schema_name NVARCHAR(128),
1329 table_name NVARCHAR(128),
1330 equality NVARCHAR(MAX),
1331 inequality NVARCHAR(MAX),
1332 [include] NVARCHAR(MAX),
1333 details AS N'/* '
1334 + CHAR(10)
1335 + N'The Query Processor estimates that implementing the following index could improve the query cost by '
1336 + CONVERT(NVARCHAR(30), impact)
1337 + '%.'
1338 + CHAR(10)
1339 + N'*/'
1340 + CHAR(10) + CHAR(13)
1341 + N'/* '
1342 + CHAR(10)
1343 + N'USE '
1344 + database_name
1345 + CHAR(10)
1346 + N'GO'
1347 + CHAR(10) + CHAR(13)
1348 + N'CREATE NONCLUSTERED INDEX ix_'
1349 + ISNULL(REPLACE(REPLACE(REPLACE(equality,'[', ''), ']', ''), ', ', '_'), '')
1350 + ISNULL(REPLACE(REPLACE(REPLACE(inequality,'[', ''), ']', ''), ', ', '_'), '')
1351 + CASE WHEN [include] IS NOT NULL THEN + N'Includes' ELSE N'' END
1352 + CHAR(10)
1353 + N' ON '
1354 + schema_name
1355 + N'.'
1356 + table_name
1357 + N' (' +
1358 + CASE WHEN equality IS NOT NULL
1359 THEN equality
1360 + CASE WHEN inequality IS NOT NULL
1361 THEN N', ' + inequality
1362 ELSE N''
1363 END
1364 ELSE inequality
1365 END
1366 + N')'
1367 + CHAR(10)
1368 + CASE WHEN include IS NOT NULL
1369 THEN N'INCLUDE (' + include + N')WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);'
1370 ELSE N'WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);'
1371 END
1372 + CHAR(10)
1373 + N'GO'
1374 + CHAR(10)
1375 + N'*/'
1376);
1377
1378RAISERROR(N'Checking plan cache age', 0, 1) WITH NOWAIT;
1379WITH x AS (
1380SELECT SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 24 THEN 1 ELSE 0 END) AS [plans_24],
1381 SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 4 THEN 1 ELSE 0 END) AS [plans_4],
1382 SUM(CASE WHEN DATEDIFF(HOUR, deqs.creation_time, SYSDATETIME()) <= 1 THEN 1 ELSE 0 END) AS [plans_1],
1383 COUNT(deqs.creation_time) AS [total_plans]
1384FROM sys.dm_exec_query_stats AS deqs
1385)
1386INSERT INTO #plan_creation ( percent_24, percent_4, percent_1, total_plans, SPID )
1387SELECT CONVERT(DECIMAL(3,2), NULLIF(x.plans_24, 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_24],
1388 CONVERT(DECIMAL(3,2), NULLIF(x.plans_4 , 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_4],
1389 CONVERT(DECIMAL(3,2), NULLIF(x.plans_1 , 0) / (1. * NULLIF(x.total_plans, 0))) * 100 AS [percent_1],
1390 x.total_plans,
1391 @@SPID AS SPID
1392FROM x
1393OPTION (RECOMPILE) ;
1394
1395
1396SET @OnlySqlHandles = LTRIM(RTRIM(@OnlySqlHandles)) ;
1397SET @OnlyQueryHashes = LTRIM(RTRIM(@OnlyQueryHashes)) ;
1398SET @IgnoreQueryHashes = LTRIM(RTRIM(@IgnoreQueryHashes)) ;
1399
1400DECLARE @individual VARCHAR(100) ;
1401
1402IF (@OnlySqlHandles IS NOT NULL AND @IgnoreSqlHandles IS NOT NULL)
1403BEGIN
1404RAISERROR('You shouldn''t need to ignore and filter on SqlHandle at the same time.', 0, 1) WITH NOWAIT;
1405RETURN;
1406END;
1407
1408IF (@StoredProcName IS NOT NULL AND (@OnlySqlHandles IS NOT NULL OR @IgnoreSqlHandles IS NOT NULL))
1409BEGIN
1410RAISERROR('You can''t filter on stored procedure name and SQL Handle.', 0, 1) WITH NOWAIT;
1411RETURN;
1412END;
1413
1414IF @OnlySqlHandles IS NOT NULL
1415 AND LEN(@OnlySqlHandles) > 0
1416BEGIN
1417 RAISERROR(N'Processing SQL Handles', 0, 1) WITH NOWAIT;
1418 SET @individual = '';
1419
1420 WHILE LEN(@OnlySqlHandles) > 0
1421 BEGIN
1422 IF PATINDEX('%,%', @OnlySqlHandles) > 0
1423 BEGIN
1424 SET @individual = SUBSTRING(@OnlySqlHandles, 0, PATINDEX('%,%',@OnlySqlHandles)) ;
1425
1426 INSERT INTO #only_sql_handles
1427 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1428 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1429 OPTION (RECOMPILE) ;
1430
1431 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS BINARY(8));
1432
1433 SET @OnlySqlHandles = SUBSTRING(@OnlySqlHandles, LEN(@individual + ',') + 1, LEN(@OnlySqlHandles)) ;
1434 END;
1435 ELSE
1436 BEGIN
1437 SET @individual = @OnlySqlHandles;
1438 SET @OnlySqlHandles = NULL;
1439
1440 INSERT INTO #only_sql_handles
1441 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1442 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1443 OPTION (RECOMPILE) ;
1444
1445 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS VARBINARY(MAX)) ;
1446 END;
1447 END;
1448END;
1449
1450IF @IgnoreSqlHandles IS NOT NULL
1451 AND LEN(@IgnoreSqlHandles) > 0
1452BEGIN
1453 RAISERROR(N'Processing SQL Handles To Ignore', 0, 1) WITH NOWAIT;
1454 SET @individual = '';
1455
1456 WHILE LEN(@IgnoreSqlHandles) > 0
1457 BEGIN
1458 IF PATINDEX('%,%', @IgnoreSqlHandles) > 0
1459 BEGIN
1460 SET @individual = SUBSTRING(@IgnoreSqlHandles, 0, PATINDEX('%,%',@IgnoreSqlHandles)) ;
1461
1462 INSERT INTO #ignore_sql_handles
1463 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1464 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1465 OPTION (RECOMPILE) ;
1466
1467 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS BINARY(8));
1468
1469 SET @IgnoreSqlHandles = SUBSTRING(@IgnoreSqlHandles, LEN(@individual + ',') + 1, LEN(@IgnoreSqlHandles)) ;
1470 END;
1471 ELSE
1472 BEGIN
1473 SET @individual = @IgnoreSqlHandles;
1474 SET @IgnoreSqlHandles = NULL;
1475
1476 INSERT INTO #ignore_sql_handles
1477 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1478 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1479 OPTION (RECOMPILE) ;
1480
1481 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS VARBINARY(MAX)) ;
1482 END;
1483 END;
1484END;
1485
1486IF @StoredProcName IS NOT NULL AND @StoredProcName <> N''
1487
1488BEGIN
1489 RAISERROR(N'Setting up filter for stored procedure name', 0, 1) WITH NOWAIT;
1490 INSERT #only_sql_handles
1491 ( sql_handle )
1492 SELECT ISNULL(deps.sql_handle, CONVERT(VARBINARY(64),'0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'))
1493 FROM sys.dm_exec_procedure_stats AS deps
1494 WHERE OBJECT_NAME(deps.object_id, deps.database_id) = @StoredProcName
1495 OPTION (RECOMPILE) ;
1496
1497 IF (SELECT COUNT(*) FROM #only_sql_handles) = 0
1498 BEGIN
1499 RAISERROR(N'No information for that stored procedure was found.', 0, 1) WITH NOWAIT;
1500 RETURN;
1501 END;
1502
1503END;
1504
1505
1506
1507IF ((@OnlyQueryHashes IS NOT NULL AND LEN(@OnlyQueryHashes) > 0)
1508 OR (@IgnoreQueryHashes IS NOT NULL AND LEN(@IgnoreQueryHashes) > 0))
1509 AND LEFT(@QueryFilter, 3) IN ('pro', 'fun')
1510BEGIN
1511 RAISERROR('You cannot limit by query hash and filter by stored procedure', 16, 1);
1512 RETURN;
1513END;
1514
1515/* If the user is attempting to limit by query hash, set up the
1516 #only_query_hashes temp table. This will be used to narrow down
1517 results.
1518
1519 Just a reminder: Using @OnlyQueryHashes will ignore stored
1520 procedures and triggers.
1521 */
1522IF @OnlyQueryHashes IS NOT NULL
1523 AND LEN(@OnlyQueryHashes) > 0
1524BEGIN
1525 RAISERROR(N'Setting up filter for Query Hashes', 0, 1) WITH NOWAIT;
1526 SET @individual = '';
1527
1528 WHILE LEN(@OnlyQueryHashes) > 0
1529 BEGIN
1530 IF PATINDEX('%,%', @OnlyQueryHashes) > 0
1531 BEGIN
1532 SET @individual = SUBSTRING(@OnlyQueryHashes, 0, PATINDEX('%,%',@OnlyQueryHashes)) ;
1533
1534 INSERT INTO #only_query_hashes
1535 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1536 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1537 OPTION (RECOMPILE) ;
1538
1539 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS BINARY(8));
1540
1541 SET @OnlyQueryHashes = SUBSTRING(@OnlyQueryHashes, LEN(@individual + ',') + 1, LEN(@OnlyQueryHashes)) ;
1542 END;
1543 ELSE
1544 BEGIN
1545 SET @individual = @OnlyQueryHashes;
1546 SET @OnlyQueryHashes = NULL;
1547
1548 INSERT INTO #only_query_hashes
1549 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1550 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1551 OPTION (RECOMPILE) ;
1552
1553 --SELECT CAST(SUBSTRING(@individual, 1, 2) AS VARBINARY(MAX)) ;
1554 END;
1555 END;
1556END;
1557
1558/* If the user is setting up a list of query hashes to ignore, those
1559 values will be inserted into #ignore_query_hashes. This is used to
1560 exclude values from query results.
1561
1562 Just a reminder: Using @IgnoreQueryHashes will ignore stored
1563 procedures and triggers.
1564 */
1565IF @IgnoreQueryHashes IS NOT NULL
1566 AND LEN(@IgnoreQueryHashes) > 0
1567BEGIN
1568 RAISERROR(N'Setting up filter to ignore query hashes', 0, 1) WITH NOWAIT;
1569 SET @individual = '' ;
1570
1571 WHILE LEN(@IgnoreQueryHashes) > 0
1572 BEGIN
1573 IF PATINDEX('%,%', @IgnoreQueryHashes) > 0
1574 BEGIN
1575 SET @individual = SUBSTRING(@IgnoreQueryHashes, 0, PATINDEX('%,%',@IgnoreQueryHashes)) ;
1576
1577 INSERT INTO #ignore_query_hashes
1578 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1579 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1580 OPTION (RECOMPILE) ;
1581
1582 SET @IgnoreQueryHashes = SUBSTRING(@IgnoreQueryHashes, LEN(@individual + ',') + 1, LEN(@IgnoreQueryHashes)) ;
1583 END;
1584 ELSE
1585 BEGIN
1586 SET @individual = @IgnoreQueryHashes ;
1587 SET @IgnoreQueryHashes = NULL ;
1588
1589 INSERT INTO #ignore_query_hashes
1590 SELECT CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@individual"), sql:column("t.pos")) )', 'varbinary(max)')
1591 FROM (SELECT CASE SUBSTRING(@individual, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
1592 OPTION (RECOMPILE) ;
1593 END;
1594 END;
1595END;
1596
1597IF @ConfigurationDatabaseName IS NOT NULL
1598BEGIN
1599 RAISERROR(N'Reading values from Configuration Database', 0, 1) WITH NOWAIT;
1600 DECLARE @config_sql NVARCHAR(MAX) = N'INSERT INTO #configuration SELECT parameter_name, value FROM '
1601 + QUOTENAME(@ConfigurationDatabaseName)
1602 + '.' + QUOTENAME(@ConfigurationSchemaName)
1603 + '.' + QUOTENAME(@ConfigurationTableName)
1604 + ' ; ' ;
1605 EXEC(@config_sql);
1606END;
1607
1608RAISERROR(N'Setting up variables', 0, 1) WITH NOWAIT;
1609DECLARE @sql NVARCHAR(MAX) = N'',
1610 @insert_list NVARCHAR(MAX) = N'',
1611 @plans_triggers_select_list NVARCHAR(MAX) = N'',
1612 @body NVARCHAR(MAX) = N'',
1613 @body_where NVARCHAR(MAX) = N'WHERE 1 = 1 ' + @nl,
1614 @body_order NVARCHAR(MAX) = N'ORDER BY #sortable# DESC OPTION (RECOMPILE) ',
1615
1616 @q NVARCHAR(1) = N'''',
1617 @pv VARCHAR(20),
1618 @pos TINYINT,
1619 @v DECIMAL(6,2),
1620 @build INT;
1621
1622
1623RAISERROR (N'Determining SQL Server version.',0,1) WITH NOWAIT;
1624
1625INSERT INTO #checkversion (version)
1626SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
1627OPTION (RECOMPILE);
1628
1629
1630SELECT @v = common_version ,
1631 @build = build
1632FROM #checkversion
1633OPTION (RECOMPILE);
1634
1635IF (@SortOrder IN ('memory grant', 'avg memory grant'))
1636AND ((@v < 11)
1637OR (@v = 11 AND @build < 6020)
1638OR (@v = 12 AND @build < 5000)
1639OR (@v = 13 AND @build < 1601))
1640BEGIN
1641 RAISERROR('Your version of SQL does not support sorting by memory grant or average memory grant. Please use another sort order.', 16, 1);
1642 RETURN;
1643END;
1644
1645IF (@SortOrder IN ('spills', 'avg spills'))
1646AND (@v < 14)
1647BEGIN
1648 RAISERROR('Your version of SQL does not support sorting by spills or average spills. Please use another sort order.', 16, 1);
1649 RETURN;
1650END;
1651
1652IF ((LEFT(@QueryFilter, 3) = 'fun') AND (@v < 13))
1653BEGIN
1654 RAISERROR('Your version of SQL does not support filtering by functions. Please use another filter.', 16, 1);
1655 RETURN;
1656END;
1657
1658RAISERROR (N'Creating dynamic SQL based on SQL Server version.',0,1) WITH NOWAIT;
1659
1660SET @insert_list += N'
1661INSERT INTO ##bou_BlitzCacheProcs (SPID, QueryType, DatabaseName, AverageCPU, TotalCPU, AverageCPUPerMinute, PercentCPUByType, PercentDurationByType,
1662 PercentReadsByType, PercentExecutionsByType, AverageDuration, TotalDuration, AverageReads, TotalReads, ExecutionCount,
1663 ExecutionsPerMinute, TotalWrites, AverageWrites, PercentWritesByType, WritesPerMinute, PlanCreationTime,
1664 LastExecutionTime, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows,
1665 LastReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills,
1666 QueryText, QueryPlan, TotalWorkerTimeForType, TotalElapsedTimeForType, TotalReadsForType,
1667 TotalExecutionCountForType, TotalWritesForType, SqlHandle, PlanHandle, QueryHash, QueryPlanHash,
1668 min_worker_time, max_worker_time, is_parallel, min_elapsed_time, max_elapsed_time, age_minutes, age_minutes_lifetime) ' ;
1669
1670SET @body += N'
1671FROM (SELECT TOP (@Top) x.*, xpa.*,
1672 CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
1673 THEN DATEDIFF(mi, cached_time, GETDATE())
1674 ELSE NULL END) as MONEY) as age_minutes,
1675 CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
1676 THEN DATEDIFF(mi, cached_time, last_execution_time)
1677 ELSE Null END) as MONEY) as age_minutes_lifetime
1678 FROM sys.#view# x
1679 CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa
1680 WHERE ixpa.attribute = ''dbid'') AS xpa ' + @nl ;
1681
1682SET @body += N' WHERE 1 = 1 ' + @nl ;
1683
1684
1685IF @IgnoreSystemDBs = 1
1686 BEGIN
1687 RAISERROR(N'Ignoring system databases by default', 0, 1) WITH NOWAIT;
1688 SET @body += N' AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'') AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)' + @nl ;
1689 END;
1690
1691IF @DatabaseName IS NOT NULL OR @DatabaseName <> N''
1692 BEGIN
1693 RAISERROR(N'Filtering database name chosen', 0, 1) WITH NOWAIT;
1694 SET @body += N' AND CAST(xpa.value AS BIGINT) = DB_ID(N'
1695 + QUOTENAME(@DatabaseName, N'''')
1696 + N') ' + @nl;
1697 END;
1698
1699IF (SELECT COUNT(*) FROM #only_sql_handles) > 0
1700BEGIN
1701 RAISERROR(N'Including only chosen SQL Handles', 0, 1) WITH NOWAIT;
1702 SET @body += N' AND EXISTS(SELECT 1/0 FROM #only_sql_handles q WHERE q.sql_handle = x.sql_handle) ' + @nl ;
1703END;
1704
1705IF (SELECT COUNT(*) FROM #ignore_sql_handles) > 0
1706BEGIN
1707 RAISERROR(N'Including only chosen SQL Handles', 0, 1) WITH NOWAIT;
1708 SET @body += N' AND NOT EXISTS(SELECT 1/0 FROM #ignore_sql_handles q WHERE q.sql_handle = x.sql_handle) ' + @nl ;
1709END;
1710
1711IF (SELECT COUNT(*) FROM #only_query_hashes) > 0
1712 AND (SELECT COUNT(*) FROM #ignore_query_hashes) = 0
1713 AND (SELECT COUNT(*) FROM #only_sql_handles) = 0
1714 AND (SELECT COUNT(*) FROM #ignore_sql_handles) = 0
1715BEGIN
1716 RAISERROR(N'Including only chosen Query Hashes', 0, 1) WITH NOWAIT;
1717 SET @body += N' AND EXISTS(SELECT 1/0 FROM #only_query_hashes q WHERE q.query_hash = x.query_hash) ' + @nl ;
1718END;
1719
1720/* filtering for query hashes */
1721IF (SELECT COUNT(*) FROM #ignore_query_hashes) > 0
1722 AND (SELECT COUNT(*) FROM #only_query_hashes) = 0
1723BEGIN
1724 RAISERROR(N'Excluding chosen Query Hashes', 0, 1) WITH NOWAIT;
1725 SET @body += N' AND NOT EXISTS(SELECT 1/0 FROM #ignore_query_hashes iq WHERE iq.query_hash = x.query_hash) ' + @nl ;
1726END;
1727/* end filtering for query hashes */
1728
1729
1730IF @DurationFilter IS NOT NULL
1731 BEGIN
1732 RAISERROR(N'Setting duration filter', 0, 1) WITH NOWAIT;
1733 SET @body += N' AND (total_elapsed_time / 1000.0) / execution_count > @min_duration ' + @nl ;
1734 END;
1735
1736IF @MinutesBack IS NOT NULL
1737 BEGIN
1738 RAISERROR(N'Setting minutes back filter', 0, 1) WITH NOWAIT;
1739 SET @body += N' AND x.last_execution_time >= DATEADD(MINUTE, @min_back, GETDATE()) ' + @nl ;
1740 END;
1741
1742/* Apply the sort order here to only grab relevant plans.
1743 This should make it faster to process since we'll be pulling back fewer
1744 plans for processing.
1745 */
1746RAISERROR(N'Applying chosen sort order', 0, 1) WITH NOWAIT;
1747SELECT @body += N' ORDER BY ' +
1748 CASE @SortOrder WHEN N'cpu' THEN N'total_worker_time'
1749 WHEN N'reads' THEN N'total_logical_reads'
1750 WHEN N'writes' THEN N'total_logical_writes'
1751 WHEN N'duration' THEN N'total_elapsed_time'
1752 WHEN N'executions' THEN N'execution_count'
1753 WHEN N'compiles' THEN N'cached_time'
1754 WHEN N'memory grant' THEN N'max_grant_kb'
1755 WHEN N'spills' THEN N'max_spills'
1756 /* And now the averages */
1757 WHEN N'avg cpu' THEN N'total_worker_time / execution_count'
1758 WHEN N'avg reads' THEN N'total_logical_reads / execution_count'
1759 WHEN N'avg writes' THEN N'total_logical_writes / execution_count'
1760 WHEN N'avg duration' THEN N'total_elapsed_time / execution_count'
1761 WHEN N'avg memory grant' THEN N'CASE WHEN max_grant_kb = 0 THEN 0 ELSE max_grant_kb / execution_count END'
1762 WHEN N'avg spills' THEN N'CASE WHEN total_spills = 0 THEN 0 ELSE total_spills / execution_count END'
1763 WHEN N'avg executions' THEN 'CASE WHEN execution_count = 0 THEN 0
1764 WHEN COALESCE(CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
1765 THEN DATEDIFF(mi, cached_time, GETDATE())
1766 ELSE NULL END) as MONEY), CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
1767 THEN DATEDIFF(mi, cached_time, last_execution_time)
1768 ELSE Null END) as MONEY), 0) = 0 THEN 0
1769 ELSE CAST((1.00 * execution_count / COALESCE(CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
1770 THEN DATEDIFF(mi, cached_time, GETDATE())
1771 ELSE NULL END) as MONEY), CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
1772 THEN DATEDIFF(mi, cached_time, last_execution_time)
1773 ELSE Null END) as MONEY))) AS money)
1774 END '
1775 END + N' DESC ' + @nl ;
1776
1777
1778
1779SET @body += N') AS qs
1780 CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
1781 SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
1782 SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
1783 SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
1784 SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
1785 FROM sys.#view#) AS t
1786 CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
1787 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
1788 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp ' + @nl ;
1789
1790SET @body_where += N' AND pa.attribute = ' + QUOTENAME('dbid', @q ) + @nl ;
1791
1792
1793
1794SET @plans_triggers_select_list += N'
1795SELECT TOP (@Top)
1796 @@SPID ,
1797 ''Procedure or Function: ''
1798 + QUOTENAME(COALESCE(OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id),''''))
1799 + ''.''
1800 + QUOTENAME(COALESCE(OBJECT_NAME(qs.object_id, qs.database_id),'''')) AS QueryType,
1801 COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), N''-- N/A --'') AS DatabaseName,
1802 (total_worker_time / 1000.0) / execution_count AS AvgCPU ,
1803 (total_worker_time / 1000.0) AS TotalCPU ,
1804 CASE WHEN total_worker_time = 0 THEN 0
1805 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
1806 ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time)) AS MONEY)
1807 END AS AverageCPUPerMinute ,
1808 CASE WHEN t.t_TotalWorker = 0 THEN 0
1809 ELSE CAST(ROUND(100.00 * (total_worker_time / 1000.0) / t.t_TotalWorker, 2) AS MONEY)
1810 END AS PercentCPUByType,
1811 CASE WHEN t.t_TotalElapsed = 0 THEN 0
1812 ELSE CAST(ROUND(100.00 * (total_elapsed_time / 1000.0) / t.t_TotalElapsed, 2) AS MONEY)
1813 END AS PercentDurationByType,
1814 CASE WHEN t.t_TotalReads = 0 THEN 0
1815 ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
1816 END AS PercentReadsByType,
1817 CASE WHEN t.t_TotalExecs = 0 THEN 0
1818 ELSE CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY)
1819 END AS PercentExecutionsByType,
1820 (total_elapsed_time / 1000.0) / execution_count AS AvgDuration ,
1821 (total_elapsed_time / 1000.0) AS TotalDuration ,
1822 total_logical_reads / execution_count AS AvgReads ,
1823 total_logical_reads AS TotalReads ,
1824 execution_count AS ExecutionCount ,
1825 CASE WHEN execution_count = 0 THEN 0
1826 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
1827 ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time))) AS money)
1828 END AS ExecutionsPerMinute ,
1829 total_logical_writes AS TotalWrites ,
1830 total_logical_writes / execution_count AS AverageWrites ,
1831 CASE WHEN t.t_TotalWrites = 0 THEN 0
1832 ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
1833 END AS PercentWritesByType,
1834 CASE WHEN total_logical_writes = 0 THEN 0
1835 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
1836 ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0)) AS money)
1837 END AS WritesPerMinute,
1838 qs.cached_time AS PlanCreationTime,
1839 qs.last_execution_time AS LastExecutionTime,
1840 NULL AS StatementStartOffset,
1841 NULL AS StatementEndOffset,
1842 NULL AS MinReturnedRows,
1843 NULL AS MaxReturnedRows,
1844 NULL AS AvgReturnedRows,
1845 NULL AS TotalReturnedRows,
1846 NULL AS LastReturnedRows,
1847 NULL AS MinGrantKB,
1848 NULL AS MaxGrantKB,
1849 NULL AS MinUsedGrantKB,
1850 NULL AS MaxUsedGrantKB,
1851 NULL AS PercentMemoryGrantUsed,
1852 NULL AS AvgMaxMemoryGrant,';
1853
1854 IF @v >=14
1855 BEGIN
1856 RAISERROR(N'Getting spill information for newer versions of SQL', 0, 1) WITH NOWAIT;
1857 SET @plans_triggers_select_list += N'
1858 min_spills AS MinSpills,
1859 max_spills AS MaxSpills,
1860 total_spills AS TotalSpills,
1861 CAST(ISNULL(NULLIF(( total_spills * 1. ), 0) / NULLIF(execution_count, 0), 0) AS MONEY) AS AvgSpills, ';
1862 END;
1863 ELSE
1864 BEGIN
1865 RAISERROR(N'Substituting NULLs for spill columns in older versions of SQL', 0, 1) WITH NOWAIT;
1866 SET @plans_triggers_select_list += N'
1867 NULL AS MinSpills,
1868 NULL AS MaxSpills,
1869 NULL AS TotalSpills,
1870 NULL AS AvgSpills, ' ;
1871 END;
1872
1873 SET @plans_triggers_select_list +=
1874 N'st.text AS QueryText ,
1875 query_plan AS QueryPlan,
1876 t.t_TotalWorker,
1877 t.t_TotalElapsed,
1878 t.t_TotalReads,
1879 t.t_TotalExecs,
1880 t.t_TotalWrites,
1881 qs.sql_handle AS SqlHandle,
1882 qs.plan_handle AS PlanHandle,
1883 NULL AS QueryHash,
1884 NULL AS QueryPlanHash,
1885 qs.min_worker_time / 1000.0,
1886 qs.max_worker_time / 1000.0,
1887 CASE WHEN qp.query_plan.value(''declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)'', ''float'') > 0 THEN 1 ELSE 0 END,
1888 qs.min_elapsed_time / 1000.0,
1889 qs.max_elapsed_time / 1000.0,
1890 age_minutes,
1891 age_minutes_lifetime ';
1892
1893
1894IF LEFT(@QueryFilter, 3) IN ('all', 'sta')
1895BEGIN
1896 SET @sql += @insert_list;
1897
1898 SET @sql += N'
1899 SELECT TOP (@Top)
1900 @@SPID ,
1901 ''Statement'' AS QueryType,
1902 COALESCE(DB_NAME(CAST(pa.value AS INT)), N''-- N/A --'') AS DatabaseName,
1903 (total_worker_time / 1000.0) / execution_count AS AvgCPU ,
1904 (total_worker_time / 1000.0) AS TotalCPU ,
1905 CASE WHEN total_worker_time = 0 THEN 0
1906 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
1907 ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time)) AS MONEY)
1908 END AS AverageCPUPerMinute ,
1909 CASE WHEN t.t_TotalWorker = 0 THEN 0
1910 ELSE CAST(ROUND(100.00 * total_worker_time / t.t_TotalWorker, 2) AS MONEY)
1911 END AS PercentCPUByType,
1912 CASE WHEN t.t_TotalElapsed = 0 THEN 0
1913 ELSE CAST(ROUND(100.00 * total_elapsed_time / t.t_TotalElapsed, 2) AS MONEY)
1914 END AS PercentDurationByType,
1915 CASE WHEN t.t_TotalReads = 0 THEN 0
1916 ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
1917 END AS PercentReadsByType,
1918 CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY) AS PercentExecutionsByType,
1919 (total_elapsed_time / 1000.0) / execution_count AS AvgDuration ,
1920 (total_elapsed_time / 1000.0) AS TotalDuration ,
1921 total_logical_reads / execution_count AS AvgReads ,
1922 total_logical_reads AS TotalReads ,
1923 execution_count AS ExecutionCount ,
1924 CASE WHEN execution_count = 0 THEN 0
1925 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
1926 ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time))) AS money)
1927 END AS ExecutionsPerMinute ,
1928 total_logical_writes AS TotalWrites ,
1929 total_logical_writes / execution_count AS AverageWrites ,
1930 CASE WHEN t.t_TotalWrites = 0 THEN 0
1931 ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
1932 END AS PercentWritesByType,
1933 CASE WHEN total_logical_writes = 0 THEN 0
1934 WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0) = 0 THEN 0
1935 ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.creation_time, qs.last_execution_time), 0)) AS money)
1936 END AS WritesPerMinute,
1937 qs.creation_time AS PlanCreationTime,
1938 qs.last_execution_time AS LastExecutionTime,
1939 qs.statement_start_offset AS StatementStartOffset,
1940 qs.statement_end_offset AS StatementEndOffset, ';
1941
1942 IF (@v >= 11) OR (@v >= 10.5 AND @build >= 2500)
1943 BEGIN
1944 RAISERROR(N'Adding additional info columns for newer versions of SQL', 0, 1) WITH NOWAIT;
1945 SET @sql += N'
1946 qs.min_rows AS MinReturnedRows,
1947 qs.max_rows AS MaxReturnedRows,
1948 CAST(qs.total_rows as MONEY) / execution_count AS AvgReturnedRows,
1949 qs.total_rows AS TotalReturnedRows,
1950 qs.last_rows AS LastReturnedRows, ' ;
1951 END;
1952 ELSE
1953 BEGIN
1954 RAISERROR(N'Substituting NULLs for more info columns in older versions of SQL', 0, 1) WITH NOWAIT;
1955 SET @sql += N'
1956 NULL AS MinReturnedRows,
1957 NULL AS MaxReturnedRows,
1958 NULL AS AvgReturnedRows,
1959 NULL AS TotalReturnedRows,
1960 NULL AS LastReturnedRows, ' ;
1961 END;
1962
1963 IF (@v = 11 AND @build >= 6020) OR (@v = 12 AND @build >= 5000) OR (@v = 13 AND @build >= 1601) OR (@v >= 14)
1964
1965 BEGIN
1966 RAISERROR(N'Getting memory grant information for newer versions of SQL', 0, 1) WITH NOWAIT;
1967 SET @sql += N'
1968 min_grant_kb AS MinGrantKB,
1969 max_grant_kb AS MaxGrantKB,
1970 min_used_grant_kb AS MinUsedGrantKB,
1971 max_used_grant_kb AS MaxUsedGrantKB,
1972 CAST(ISNULL(NULLIF(( max_used_grant_kb * 1.00 ), 0) / NULLIF(min_grant_kb, 0), 0) * 100. AS MONEY) AS PercentMemoryGrantUsed,
1973 CAST(ISNULL(NULLIF(( max_grant_kb * 1. ), 0) / NULLIF(execution_count, 0), 0) AS MONEY) AS AvgMaxMemoryGrant, ';
1974 END;
1975 ELSE
1976 BEGIN
1977 RAISERROR(N'Substituting NULLs for memory grant columns in older versions of SQL', 0, 1) WITH NOWAIT;
1978 SET @sql += N'
1979 NULL AS MinGrantKB,
1980 NULL AS MaxGrantKB,
1981 NULL AS MinUsedGrantKB,
1982 NULL AS MaxUsedGrantKB,
1983 NULL AS PercentMemoryGrantUsed,
1984 NULL AS AvgMaxMemoryGrant, ' ;
1985 END;
1986
1987 IF @v >=14
1988 BEGIN
1989 RAISERROR(N'Getting spill information for newer versions of SQL', 0, 1) WITH NOWAIT;
1990 SET @sql += N'
1991 min_spills AS MinSpills,
1992 max_spills AS MaxSpills,
1993 total_spills AS TotalSpills,
1994 CAST(ISNULL(NULLIF(( total_spills * 1. ), 0) / NULLIF(execution_count, 0), 0) AS MONEY) AS AvgSpills,';
1995 END;
1996 ELSE
1997 BEGIN
1998 RAISERROR(N'Substituting NULLs for spill columns in older versions of SQL', 0, 1) WITH NOWAIT;
1999 SET @sql += N'
2000 NULL AS MinSpills,
2001 NULL AS MaxSpills,
2002 NULL AS TotalSpills,
2003 NULL AS AvgSpills, ' ;
2004 END;
2005
2006 SET @sql += N'
2007 SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset
2008 WHEN -1 THEN DATALENGTH(st.text)
2009 ELSE qs.statement_end_offset
2010 END - qs.statement_start_offset ) / 2 ) + 1) AS QueryText ,
2011 query_plan AS QueryPlan,
2012 t.t_TotalWorker,
2013 t.t_TotalElapsed,
2014 t.t_TotalReads,
2015 t.t_TotalExecs,
2016 t.t_TotalWrites,
2017 qs.sql_handle AS SqlHandle,
2018 qs.plan_handle AS PlanHandle,
2019 qs.query_hash AS QueryHash,
2020 qs.query_plan_hash AS QueryPlanHash,
2021 qs.min_worker_time / 1000.0,
2022 qs.max_worker_time / 1000.0,
2023 CASE WHEN qp.query_plan.value(''declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)'', ''float'') > 0 THEN 1 ELSE 0 END,
2024 qs.min_elapsed_time / 1000.0,
2025 qs.max_worker_time / 1000.0,
2026 age_minutes,
2027 age_minutes_lifetime ';
2028
2029 SET @sql += REPLACE(REPLACE(@body, '#view#', 'dm_exec_query_stats'), 'cached_time', 'creation_time') ;
2030
2031 SET @sql += REPLACE(@body_where, 'cached_time', 'creation_time') ;
2032
2033 SET @sql += @body_order + @nl + @nl + @nl;
2034
2035 IF @SortOrder = 'compiles'
2036 BEGIN
2037 RAISERROR(N'Sorting by compiles', 0, 1) WITH NOWAIT;
2038 SET @sql = REPLACE(@sql, '#sortable#', 'creation_time');
2039 END;
2040END;
2041
2042
2043IF (@QueryFilter = 'all'
2044 AND (SELECT COUNT(*) FROM #only_query_hashes) = 0
2045 AND (SELECT COUNT(*) FROM #ignore_query_hashes) = 0)
2046 AND (@SortOrder NOT IN ('memory grant', 'avg memory grant'))
2047 OR (LEFT(@QueryFilter, 3) = 'pro')
2048BEGIN
2049 SET @sql += @insert_list;
2050 SET @sql += REPLACE(@plans_triggers_select_list, '#query_type#', 'Stored Procedure') ;
2051
2052 SET @sql += REPLACE(@body, '#view#', 'dm_exec_procedure_stats') ;
2053 SET @sql += @body_where ;
2054
2055 IF @IgnoreSystemDBs = 1
2056 SET @sql += N' AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'') AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)' + @nl ;
2057
2058 SET @sql += @body_order + @nl + @nl + @nl ;
2059END;
2060
2061IF (@v >= 13
2062 AND @QueryFilter = 'all'
2063 AND (SELECT COUNT(*) FROM #only_query_hashes) = 0
2064 AND (SELECT COUNT(*) FROM #ignore_query_hashes) = 0)
2065 AND (@SortOrder NOT IN ('memory grant', 'avg memory grant'))
2066 AND (@SortOrder NOT IN ('spills', 'avg spills'))
2067 OR (LEFT(@QueryFilter, 3) = 'fun')
2068BEGIN
2069 SET @sql += @insert_list;
2070 SET @sql += REPLACE(REPLACE(@plans_triggers_select_list, '#query_type#', 'Function')
2071 , N'
2072 min_spills AS MinSpills,
2073 max_spills AS MaxSpills,
2074 total_spills AS TotalSpills,
2075 CAST(ISNULL(NULLIF(( total_spills * 1. ), 0) / NULLIF(execution_count, 0), 0) AS MONEY) AS AvgSpills, ',
2076 N'
2077 NULL AS MinSpills,
2078 NULL AS MaxSpills,
2079 NULL AS TotalSpills,
2080 NULL AS AvgSpills, ') ;
2081
2082 SET @sql += REPLACE(@body, '#view#', 'dm_exec_function_stats') ;
2083 SET @sql += @body_where ;
2084
2085 IF @IgnoreSystemDBs = 1
2086 SET @sql += N' AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'') AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)' + @nl ;
2087
2088 SET @sql += @body_order + @nl + @nl + @nl ;
2089END;
2090
2091
2092/*******************************************************************************
2093 *
2094 * Because the trigger execution count in SQL Server 2008R2 and earlier is not
2095 * correct, we ignore triggers for these versions of SQL Server. If you'd like
2096 * to include trigger numbers, just know that the ExecutionCount,
2097 * PercentExecutions, and ExecutionsPerMinute are wildly inaccurate for
2098 * triggers on these versions of SQL Server.
2099 *
2100 * This is why we can't have nice things.
2101 *
2102 ******************************************************************************/
2103IF (@UseTriggersAnyway = 1 OR @v >= 11)
2104 AND (SELECT COUNT(*) FROM #only_query_hashes) = 0
2105 AND (SELECT COUNT(*) FROM #ignore_query_hashes) = 0
2106 AND (@QueryFilter = 'all')
2107 AND (@SortOrder NOT IN ('memory grant', 'avg memory grant'))
2108BEGIN
2109 RAISERROR (N'Adding SQL to collect trigger stats.',0,1) WITH NOWAIT;
2110
2111 /* Trigger level information from the plan cache */
2112 SET @sql += @insert_list ;
2113
2114 SET @sql += REPLACE(@plans_triggers_select_list, '#query_type#', 'Trigger') ;
2115
2116 SET @sql += REPLACE(@body, '#view#', 'dm_exec_trigger_stats') ;
2117
2118 SET @sql += @body_where ;
2119
2120 IF @IgnoreSystemDBs = 1
2121 SET @sql += N' AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'') AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)' + @nl ;
2122
2123 SET @sql += @body_order + @nl + @nl + @nl ;
2124END;
2125
2126DECLARE @sort NVARCHAR(MAX);
2127
2128SELECT @sort = CASE @SortOrder WHEN N'cpu' THEN N'total_worker_time'
2129 WHEN N'reads' THEN N'total_logical_reads'
2130 WHEN N'writes' THEN N'total_logical_writes'
2131 WHEN N'duration' THEN N'total_elapsed_time'
2132 WHEN N'executions' THEN N'execution_count'
2133 WHEN N'compiles' THEN N'cached_time'
2134 WHEN N'memory grant' THEN N'max_grant_kb'
2135 WHEN N'spills' THEN N'max_spills'
2136 /* And now the averages */
2137 WHEN N'avg cpu' THEN N'total_worker_time / execution_count'
2138 WHEN N'avg reads' THEN N'total_logical_reads / execution_count'
2139 WHEN N'avg writes' THEN N'total_logical_writes / execution_count'
2140 WHEN N'avg duration' THEN N'total_elapsed_time / execution_count'
2141 WHEN N'avg memory grant' THEN N'CASE WHEN max_grant_kb = 0 THEN 0 ELSE max_grant_kb / execution_count END'
2142 WHEN N'avg spills' THEN N'CASE WHEN total_spills = 0 THEN 0 ELSE total_spills / execution_count END'
2143 WHEN N'avg executions' THEN N'CASE WHEN execution_count = 0 THEN 0
2144 WHEN COALESCE(age_minutes, age_minutes_lifetime, 0) = 0 THEN 0
2145 ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, age_minutes_lifetime)) AS money)
2146 END'
2147 END ;
2148
2149SELECT @sql = REPLACE(@sql, '#sortable#', @sort);
2150
2151SET @sql += N'
2152INSERT INTO #p (SqlHandle, TotalCPU, TotalReads, TotalDuration, TotalWrites, ExecutionCount)
2153SELECT SqlHandle,
2154 TotalCPU,
2155 TotalReads,
2156 TotalDuration,
2157 TotalWrites,
2158 ExecutionCount
2159FROM (SELECT SqlHandle,
2160 TotalCPU,
2161 TotalReads,
2162 TotalDuration,
2163 TotalWrites,
2164 ExecutionCount,
2165 ROW_NUMBER() OVER (PARTITION BY SqlHandle ORDER BY #sortable# DESC) AS rn
2166 FROM ##bou_BlitzCacheProcs) AS x
2167WHERE x.rn = 1
2168OPTION (RECOMPILE);
2169';
2170
2171SELECT @sort = CASE @SortOrder WHEN N'cpu' THEN N'TotalCPU'
2172 WHEN N'reads' THEN N'TotalReads'
2173 WHEN N'writes' THEN N'TotalWrites'
2174 WHEN N'duration' THEN N'TotalDuration'
2175 WHEN N'executions' THEN N'ExecutionCount'
2176 WHEN N'compiles' THEN N'PlanCreationTime'
2177 WHEN N'memory grant' THEN N'MaxGrantKB'
2178 WHEN N'spills' THEN N'MaxSpills'
2179 /* And now the averages */
2180 WHEN N'avg cpu' THEN N'TotalCPU / ExecutionCount'
2181 WHEN N'avg reads' THEN N'TotalReads / ExecutionCount'
2182 WHEN N'avg writes' THEN N'TotalWrites / ExecutionCount'
2183 WHEN N'avg duration' THEN N'TotalDuration / ExecutionCount'
2184 WHEN N'avg memory grant' THEN N'AvgMaxMemoryGrant'
2185 WHEN N'avg spills' THEN N'AvgSpills'
2186 WHEN N'avg executions' THEN N'CASE WHEN ExecutionCount = 0 THEN 0
2187 WHEN COALESCE(age_minutes, age_minutes_lifetime, 0) = 0 THEN 0
2188 ELSE CAST((1.00 * ExecutionCount / COALESCE(age_minutes, age_minutes_lifetime)) AS money)
2189 END'
2190 END ;
2191
2192SELECT @sql = REPLACE(@sql, '#sortable#', @sort);
2193
2194
2195IF @Debug = 1
2196 BEGIN
2197 PRINT SUBSTRING(@sql, 0, 4000);
2198 PRINT SUBSTRING(@sql, 4000, 8000);
2199 PRINT SUBSTRING(@sql, 8000, 12000);
2200 PRINT SUBSTRING(@sql, 12000, 16000);
2201 PRINT SUBSTRING(@sql, 16000, 20000);
2202 PRINT SUBSTRING(@sql, 20000, 24000);
2203 PRINT SUBSTRING(@sql, 24000, 28000);
2204 PRINT SUBSTRING(@sql, 28000, 32000);
2205 PRINT SUBSTRING(@sql, 32000, 36000);
2206 PRINT SUBSTRING(@sql, 36000, 40000);
2207 END;
2208
2209IF @Reanalyze = 0
2210BEGIN
2211 RAISERROR('Collecting execution plan information.', 0, 1) WITH NOWAIT;
2212
2213 EXEC sp_executesql @sql, N'@Top INT, @min_duration INT, @min_back INT', @Top, @DurationFilter_i, @MinutesBack;
2214END;
2215
2216
2217/* Update ##bou_BlitzCacheProcs to get Stored Proc info
2218 * This should get totals for all statements in a Stored Proc
2219 */
2220RAISERROR(N'Attempting to aggregate stored proc info from separate statements', 0, 1) WITH NOWAIT;
2221;WITH agg AS (
2222 SELECT
2223 b.SqlHandle,
2224 SUM(b.MinReturnedRows) AS MinReturnedRows,
2225 SUM(b.MaxReturnedRows) AS MaxReturnedRows,
2226 SUM(b.AverageReturnedRows) AS AverageReturnedRows,
2227 SUM(b.TotalReturnedRows) AS TotalReturnedRows,
2228 SUM(b.LastReturnedRows) AS LastReturnedRows,
2229 SUM(b.MinGrantKB) AS MinGrantKB,
2230 SUM(b.MaxGrantKB) AS MaxGrantKB,
2231 SUM(b.MinUsedGrantKB) AS MinUsedGrantKB,
2232 SUM(b.MaxUsedGrantKB) AS MaxUsedGrantKB,
2233 SUM(b.MinSpills) AS MinSpills,
2234 SUM(b.MaxSpills) AS MaxSpills,
2235 SUM(b.TotalSpills) AS TotalSpills
2236 FROM ##bou_BlitzCacheProcs b
2237 WHERE b.SPID = @@SPID
2238 AND b.QueryHash IS NOT NULL
2239 GROUP BY b.SqlHandle
2240)
2241UPDATE b
2242 SET
2243 b.MinReturnedRows = b2.MinReturnedRows,
2244 b.MaxReturnedRows = b2.MaxReturnedRows,
2245 b.AverageReturnedRows = b2.AverageReturnedRows,
2246 b.TotalReturnedRows = b2.TotalReturnedRows,
2247 b.LastReturnedRows = b2.LastReturnedRows,
2248 b.MinGrantKB = b2.MinGrantKB,
2249 b.MaxGrantKB = b2.MaxGrantKB,
2250 b.MinUsedGrantKB = b2.MinUsedGrantKB,
2251 b.MaxUsedGrantKB = b2.MaxUsedGrantKB,
2252 b.MinSpills = b2.MinSpills,
2253 b.MaxSpills = b2.MaxSpills,
2254 b.TotalSpills = b2.TotalSpills
2255FROM ##bou_BlitzCacheProcs b
2256JOIN agg b2
2257ON b2.SqlHandle = b.SqlHandle
2258WHERE b.QueryHash IS NULL
2259AND b.SPID = @@SPID
2260OPTION (RECOMPILE) ;
2261
2262/* Compute the total CPU, etc across our active set of the plan cache.
2263 * Yes, there's a flaw - this doesn't include anything outside of our @Top
2264 * metric.
2265 */
2266RAISERROR('Computing CPU, duration, read, and write metrics', 0, 1) WITH NOWAIT;
2267DECLARE @total_duration BIGINT,
2268 @total_cpu BIGINT,
2269 @total_reads BIGINT,
2270 @total_writes BIGINT,
2271 @total_execution_count BIGINT;
2272
2273SELECT @total_cpu = SUM(TotalCPU),
2274 @total_duration = SUM(TotalDuration),
2275 @total_reads = SUM(TotalReads),
2276 @total_writes = SUM(TotalWrites),
2277 @total_execution_count = SUM(ExecutionCount)
2278FROM #p
2279OPTION (RECOMPILE) ;
2280
2281DECLARE @cr NVARCHAR(1) = NCHAR(13);
2282DECLARE @lf NVARCHAR(1) = NCHAR(10);
2283DECLARE @tab NVARCHAR(1) = NCHAR(9);
2284
2285/* Update CPU percentage for stored procedures */
2286RAISERROR(N'Update CPU percentage for stored procedures', 0, 1) WITH NOWAIT;
2287UPDATE ##bou_BlitzCacheProcs
2288SET PercentCPU = y.PercentCPU,
2289 PercentDuration = y.PercentDuration,
2290 PercentReads = y.PercentReads,
2291 PercentWrites = y.PercentWrites,
2292 PercentExecutions = y.PercentExecutions,
2293 ExecutionsPerMinute = y.ExecutionsPerMinute,
2294 /* Strip newlines and tabs. Tabs are replaced with multiple spaces
2295 so that the later whitespace trim will completely eliminate them
2296 */
2297 QueryText = REPLACE(REPLACE(REPLACE(QueryText, @cr, ' '), @lf, ' '), @tab, ' ')
2298FROM (
2299 SELECT PlanHandle,
2300 CASE @total_cpu WHEN 0 THEN 0
2301 ELSE CAST((100. * TotalCPU) / @total_cpu AS MONEY) END AS PercentCPU,
2302 CASE @total_duration WHEN 0 THEN 0
2303 ELSE CAST((100. * TotalDuration) / @total_duration AS MONEY) END AS PercentDuration,
2304 CASE @total_reads WHEN 0 THEN 0
2305 ELSE CAST((100. * TotalReads) / @total_reads AS MONEY) END AS PercentReads,
2306 CASE @total_writes WHEN 0 THEN 0
2307 ELSE CAST((100. * TotalWrites) / @total_writes AS MONEY) END AS PercentWrites,
2308 CASE @total_execution_count WHEN 0 THEN 0
2309 ELSE CAST((100. * ExecutionCount) / @total_execution_count AS MONEY) END AS PercentExecutions,
2310 CASE DATEDIFF(mi, PlanCreationTime, LastExecutionTime)
2311 WHEN 0 THEN 0
2312 ELSE CAST((1.00 * ExecutionCount / DATEDIFF(mi, PlanCreationTime, LastExecutionTime)) AS MONEY)
2313 END AS ExecutionsPerMinute
2314 FROM (
2315 SELECT PlanHandle,
2316 TotalCPU,
2317 TotalDuration,
2318 TotalReads,
2319 TotalWrites,
2320 ExecutionCount,
2321 PlanCreationTime,
2322 LastExecutionTime
2323 FROM ##bou_BlitzCacheProcs
2324 WHERE PlanHandle IS NOT NULL
2325 AND SPID = @@SPID
2326 GROUP BY PlanHandle,
2327 TotalCPU,
2328 TotalDuration,
2329 TotalReads,
2330 TotalWrites,
2331 ExecutionCount,
2332 PlanCreationTime,
2333 LastExecutionTime
2334 ) AS x
2335) AS y
2336WHERE ##bou_BlitzCacheProcs.PlanHandle = y.PlanHandle
2337 AND ##bou_BlitzCacheProcs.PlanHandle IS NOT NULL
2338 AND ##bou_BlitzCacheProcs.SPID = @@SPID
2339OPTION (RECOMPILE) ;
2340
2341
2342RAISERROR(N'Gather percentage information from grouped results', 0, 1) WITH NOWAIT;
2343UPDATE ##bou_BlitzCacheProcs
2344SET PercentCPU = y.PercentCPU,
2345 PercentDuration = y.PercentDuration,
2346 PercentReads = y.PercentReads,
2347 PercentWrites = y.PercentWrites,
2348 PercentExecutions = y.PercentExecutions,
2349 ExecutionsPerMinute = y.ExecutionsPerMinute,
2350 /* Strip newlines and tabs. Tabs are replaced with multiple spaces
2351 so that the later whitespace trim will completely eliminate them
2352 */
2353 QueryText = REPLACE(REPLACE(REPLACE(QueryText, @cr, ' '), @lf, ' '), @tab, ' ')
2354FROM (
2355 SELECT DatabaseName,
2356 SqlHandle,
2357 QueryHash,
2358 CASE @total_cpu WHEN 0 THEN 0
2359 ELSE CAST((100. * TotalCPU) / @total_cpu AS MONEY) END AS PercentCPU,
2360 CASE @total_duration WHEN 0 THEN 0
2361 ELSE CAST((100. * TotalDuration) / @total_duration AS MONEY) END AS PercentDuration,
2362 CASE @total_reads WHEN 0 THEN 0
2363 ELSE CAST((100. * TotalReads) / @total_reads AS MONEY) END AS PercentReads,
2364 CASE @total_writes WHEN 0 THEN 0
2365 ELSE CAST((100. * TotalWrites) / @total_writes AS MONEY) END AS PercentWrites,
2366 CASE @total_execution_count WHEN 0 THEN 0
2367 ELSE CAST((100. * ExecutionCount) / @total_execution_count AS MONEY) END AS PercentExecutions,
2368 CASE DATEDIFF(mi, PlanCreationTime, LastExecutionTime)
2369 WHEN 0 THEN 0
2370 ELSE CAST((1.00 * ExecutionCount / DATEDIFF(mi, PlanCreationTime, LastExecutionTime)) AS MONEY)
2371 END AS ExecutionsPerMinute
2372 FROM (
2373 SELECT DatabaseName,
2374 SqlHandle,
2375 QueryHash,
2376 TotalCPU,
2377 TotalDuration,
2378 TotalReads,
2379 TotalWrites,
2380 ExecutionCount,
2381 PlanCreationTime,
2382 LastExecutionTime
2383 FROM ##bou_BlitzCacheProcs
2384 WHERE SPID = @@SPID
2385 GROUP BY DatabaseName,
2386 SqlHandle,
2387 QueryHash,
2388 TotalCPU,
2389 TotalDuration,
2390 TotalReads,
2391 TotalWrites,
2392 ExecutionCount,
2393 PlanCreationTime,
2394 LastExecutionTime
2395 ) AS x
2396) AS y
2397WHERE ##bou_BlitzCacheProcs.SqlHandle = y.SqlHandle
2398 AND ##bou_BlitzCacheProcs.QueryHash = y.QueryHash
2399 AND ##bou_BlitzCacheProcs.DatabaseName = y.DatabaseName
2400 AND ##bou_BlitzCacheProcs.PlanHandle IS NULL
2401OPTION (RECOMPILE) ;
2402
2403
2404
2405/* Testing using XML nodes to speed up processing */
2406RAISERROR(N'Begin XML nodes processing', 0, 1) WITH NOWAIT;
2407WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2408SELECT QueryHash ,
2409 SqlHandle ,
2410 PlanHandle,
2411 q.n.query('.') AS statement
2412INTO #statements
2413FROM ##bou_BlitzCacheProcs p
2414 CROSS APPLY p.QueryPlan.nodes('//p:StmtSimple') AS q(n)
2415WHERE p.SPID = @@SPID
2416OPTION (RECOMPILE) ;
2417
2418WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2419INSERT #statements
2420SELECT QueryHash ,
2421 SqlHandle ,
2422 PlanHandle,
2423 q.n.query('.') AS statement
2424FROM ##bou_BlitzCacheProcs p
2425 CROSS APPLY p.QueryPlan.nodes('//p:StmtCursor') AS q(n)
2426WHERE p.SPID = @@SPID
2427OPTION (RECOMPILE) ;
2428
2429WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2430SELECT QueryHash ,
2431 SqlHandle ,
2432 q.n.query('.') AS query_plan
2433INTO #query_plan
2434FROM #statements p
2435 CROSS APPLY p.statement.nodes('//p:QueryPlan') AS q(n)
2436OPTION (RECOMPILE) ;
2437
2438WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2439SELECT QueryHash ,
2440 SqlHandle ,
2441 q.n.query('.') AS relop
2442INTO #relop
2443FROM #query_plan p
2444 CROSS APPLY p.query_plan.nodes('//p:RelOp') AS q(n)
2445OPTION (RECOMPILE) ;
2446
2447
2448
2449-- high level plan stuff
2450RAISERROR(N'Gathering high level plan information', 0, 1) WITH NOWAIT;
2451UPDATE ##bou_BlitzCacheProcs
2452SET NumberOfDistinctPlans = distinct_plan_count,
2453 NumberOfPlans = number_of_plans ,
2454 plan_multiple_plans = CASE WHEN distinct_plan_count < number_of_plans THEN 1 END
2455FROM (
2456 SELECT COUNT(DISTINCT QueryHash) AS distinct_plan_count,
2457 COUNT(QueryHash) AS number_of_plans,
2458 QueryHash
2459 FROM ##bou_BlitzCacheProcs
2460 WHERE SPID = @@SPID
2461 GROUP BY QueryHash
2462) AS x
2463WHERE ##bou_BlitzCacheProcs.QueryHash = x.QueryHash
2464OPTION (RECOMPILE) ;
2465
2466-- statement level checks
2467RAISERROR(N'Performing compile timeout checks', 0, 1) WITH NOWAIT;
2468WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2469UPDATE b
2470SET compile_timeout = 1
2471FROM #statements s
2472JOIN ##bou_BlitzCacheProcs b
2473ON s.QueryHash = b.QueryHash
2474AND SPID = @@SPID
2475WHERE statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1
2476OPTION (RECOMPILE);
2477
2478RAISERROR(N'Performing compile memory limit exceeded checks', 0, 1) WITH NOWAIT;
2479WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2480UPDATE b
2481SET compile_memory_limit_exceeded = 1
2482FROM #statements s
2483JOIN ##bou_BlitzCacheProcs b
2484ON s.QueryHash = b.QueryHash
2485AND SPID = @@SPID
2486WHERE statement.exist('/p:StmtSimple/@StatementOptmEarlyAbortReason[.="MemoryLimitExceeded"]') = 1
2487OPTION (RECOMPILE);
2488
2489IF @ExpertMode > 0
2490BEGIN
2491RAISERROR(N'Performing unparameterized query checks', 0, 1) WITH NOWAIT;
2492WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
2493unparameterized_query AS (
2494 SELECT s.QueryHash,
2495 unparameterized_query = CASE WHEN statement.exist('//p:StmtSimple[@StatementOptmLevel[.="FULL"]]/p:QueryPlan/p:ParameterList') = 1 AND
2496 statement.exist('//p:StmtSimple[@StatementOptmLevel[.="FULL"]]/p:QueryPlan/p:ParameterList/p:ColumnReference') = 0 THEN 1
2497 WHEN statement.exist('//p:StmtSimple[@StatementOptmLevel[.="FULL"]]/p:QueryPlan/p:ParameterList') = 0 AND
2498 statement.exist('//p:StmtSimple[@StatementOptmLevel[.="FULL"]]/*/p:RelOp/descendant::p:ScalarOperator/p:Identifier/p:ColumnReference[contains(@Column, "@")]') = 1 THEN 1
2499 END
2500 FROM #statements AS s
2501 )
2502UPDATE b
2503SET b.unparameterized_query = u.unparameterized_query
2504FROM ##bou_BlitzCacheProcs b
2505JOIN unparameterized_query u
2506ON u.QueryHash = b.QueryHash
2507AND SPID = @@SPID
2508WHERE u.unparameterized_query = 1
2509OPTION (RECOMPILE);
2510END;
2511
2512
2513IF @ExpertMode > 0
2514BEGIN
2515RAISERROR(N'Performing index DML checks', 0, 1) WITH NOWAIT;
2516WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
2517index_dml AS (
2518 SELECT s.QueryHash,
2519 index_dml = CASE WHEN statement.exist('//p:StmtSimple/@StatementType[.="CREATE INDEX"]') = 1 THEN 1
2520 WHEN statement.exist('//p:StmtSimple/@StatementType[.="DROP INDEX"]') = 1 THEN 1
2521 END
2522 FROM #statements s
2523 )
2524 UPDATE b
2525 SET b.index_dml = i.index_dml
2526 FROM ##bou_BlitzCacheProcs AS b
2527 JOIN index_dml i
2528 ON i.QueryHash = b.QueryHash
2529 WHERE i.index_dml = 1
2530 AND b.SPID = @@SPID
2531 OPTION (RECOMPILE);
2532END;
2533
2534
2535IF @ExpertMode > 0
2536BEGIN
2537RAISERROR(N'Performing table DML checks', 0, 1) WITH NOWAIT;
2538WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
2539table_dml AS (
2540 SELECT s.QueryHash,
2541 table_dml = CASE WHEN statement.exist('//p:StmtSimple/@StatementType[.="CREATE TABLE"]') = 1 THEN 1
2542 WHEN statement.exist('//p:StmtSimple/@StatementType[.="DROP OBJECT"]') = 1 THEN 1
2543 END
2544 FROM #statements AS s
2545 )
2546 UPDATE b
2547 SET b.table_dml = t.table_dml
2548 FROM ##bou_BlitzCacheProcs AS b
2549 JOIN table_dml t
2550 ON t.QueryHash = b.QueryHash
2551 WHERE t.table_dml = 1
2552 AND b.SPID = @@SPID
2553 OPTION (RECOMPILE);
2554END;
2555
2556
2557IF @ExpertMode > 0
2558BEGIN
2559RAISERROR(N'Gathering row estimates', 0, 1) WITH NOWAIT;
2560WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
2561INSERT INTO #est_rows
2562SELECT DISTINCT
2563 CONVERT(BINARY(8), RIGHT('0000000000000000' + SUBSTRING(c.n.value('@QueryHash', 'VARCHAR(18)'), 3, 18), 16), 2) AS QueryHash,
2564 c.n.value('(/p:StmtSimple/@StatementEstRows)[1]', 'FLOAT') AS estimated_rows
2565FROM #statements AS s
2566CROSS APPLY s.statement.nodes('/p:StmtSimple') AS c(n)
2567WHERE c.n.exist('/p:StmtSimple[@StatementEstRows > 0]') = 1;
2568
2569 UPDATE b
2570 SET b.estimated_rows = er.estimated_rows
2571 FROM ##bou_BlitzCacheProcs AS b
2572 JOIN #est_rows er
2573 ON er.QueryHash = b.QueryHash
2574 WHERE b.SPID = @@SPID
2575 AND b.QueryType = 'Statement'
2576 OPTION (RECOMPILE);
2577END;
2578
2579RAISERROR(N'Gathering trivial plans', 0, 1) WITH NOWAIT;
2580WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
2581UPDATE b
2582SET b.is_trivial = 1
2583FROM ##bou_BlitzCacheProcs AS b
2584JOIN (
2585SELECT s.SqlHandle
2586FROM #statements AS s
2587JOIN ( SELECT r.SqlHandle
2588 FROM #relop AS r
2589 WHERE r.relop.exist('//p:RelOp[contains(@LogicalOp, "Scan")]') = 1 ) AS r
2590 ON r.SqlHandle = s.SqlHandle
2591WHERE s.statement.exist('//p:StmtSimple[@StatementOptmLevel[.="TRIVIAL"]]/p:QueryPlan/p:ParameterList') = 1
2592) AS s
2593ON b.SqlHandle = s.SqlHandle
2594OPTION (RECOMPILE);
2595
2596
2597--Gather costs
2598RAISERROR(N'Gathering statement costs', 0, 1) WITH NOWAIT;
2599WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2600INSERT INTO #plan_cost
2601SELECT DISTINCT
2602 statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float') QueryPlanCost,
2603 s.SqlHandle,
2604 CONVERT(BINARY(8), RIGHT('0000000000000000' + SUBSTRING(q.n.value('@QueryHash', 'VARCHAR(18)'), 3, 18), 16), 2) AS QueryHash,
2605 CONVERT(BINARY(8), RIGHT('0000000000000000' + SUBSTRING(q.n.value('@QueryPlanHash', 'VARCHAR(18)'), 3, 18), 16), 2) AS QueryPlanHash
2606FROM #statements s
2607CROSS APPLY s.statement.nodes('/p:StmtSimple') AS q(n)
2608WHERE statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float') > 0
2609OPTION (RECOMPILE);
2610
2611RAISERROR(N'Updating statement costs', 0, 1) WITH NOWAIT;
2612WITH pc AS (
2613 SELECT SUM(DISTINCT pc.QueryPlanCost) AS QueryPlanCostSum, pc.QueryHash, pc.QueryPlanHash
2614 FROM #plan_cost AS pc
2615 GROUP BY pc.QueryHash, pc.QueryPlanHash
2616)
2617 UPDATE b
2618 SET b.QueryPlanCost = ISNULL(pc.QueryPlanCostSum, 0)
2619 FROM pc
2620 JOIN ##bou_BlitzCacheProcs b
2621 ON b.QueryPlanHash = pc.QueryPlanHash
2622 OR b.QueryHash = pc.QueryHash
2623 WHERE b.QueryType NOT LIKE '%Procedure%'
2624 OPTION (RECOMPILE);
2625
2626IF EXISTS (
2627SELECT 1
2628FROM ##bou_BlitzCacheProcs AS b
2629WHERE b.QueryType LIKE 'Procedure%'
2630)
2631
2632BEGIN
2633
2634RAISERROR(N'Gathering stored procedure costs', 0, 1) WITH NOWAIT;
2635;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2636, QueryCost AS (
2637 SELECT
2638 DISTINCT
2639 statement.value('sum(/p:StmtSimple/@StatementSubTreeCost)', 'float') AS SubTreeCost,
2640 s.PlanHandle,
2641 s.SqlHandle
2642 FROM #statements AS s
2643 WHERE PlanHandle IS NOT NULL
2644)
2645, QueryCostUpdate AS (
2646 SELECT
2647 SUM(qc.SubTreeCost) OVER (PARTITION BY SqlHandle, PlanHandle) PlanTotalQuery,
2648 qc.PlanHandle,
2649 qc.SqlHandle
2650 FROM QueryCost qc
2651)
2652INSERT INTO #proc_costs
2653SELECT qcu.PlanTotalQuery, PlanHandle, SqlHandle
2654FROM QueryCostUpdate AS qcu
2655OPTION (RECOMPILE);
2656
2657
2658UPDATE b
2659 SET b.QueryPlanCost = ca.PlanTotalQuery
2660FROM ##bou_BlitzCacheProcs AS b
2661CROSS APPLY (
2662 SELECT TOP 1 PlanTotalQuery
2663 FROM #proc_costs qcu
2664 WHERE qcu.PlanHandle = b.PlanHandle
2665 ORDER BY PlanTotalQuery DESC
2666) ca
2667WHERE b.QueryType LIKE 'Procedure%'
2668AND b.SPID = @@SPID
2669OPTION (RECOMPILE);
2670
2671END;
2672
2673UPDATE b
2674SET b.QueryPlanCost = 0.0
2675FROM ##bou_BlitzCacheProcs b
2676WHERE b.QueryPlanCost IS NULL
2677AND b.SPID = @@SPID
2678OPTION (RECOMPILE);
2679
2680RAISERROR(N'Checking for plan warnings', 0, 1) WITH NOWAIT;
2681WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2682UPDATE ##bou_BlitzCacheProcs
2683SET plan_warnings = 1
2684FROM #query_plan qp
2685WHERE qp.SqlHandle = ##bou_BlitzCacheProcs.SqlHandle
2686AND SPID = @@SPID
2687AND query_plan.exist('/p:QueryPlan/p:Warnings') = 1
2688OPTION (RECOMPILE);
2689
2690RAISERROR(N'Checking for implicit conversion', 0, 1) WITH NOWAIT;
2691WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2692UPDATE ##bou_BlitzCacheProcs
2693SET implicit_conversions = 1
2694FROM #query_plan qp
2695WHERE qp.SqlHandle = ##bou_BlitzCacheProcs.SqlHandle
2696AND SPID = @@SPID
2697AND query_plan.exist('/p:QueryPlan/p:Warnings/p:PlanAffectingConvert/@Expression[contains(., "CONVERT_IMPLICIT")]') = 1
2698OPTION (RECOMPILE);
2699
2700-- operator level checks
2701IF @ExpertMode > 0
2702BEGIN
2703RAISERROR(N'Performing busy loops checks', 0, 1) WITH NOWAIT;
2704WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2705UPDATE p
2706SET busy_loops = CASE WHEN (x.estimated_executions / 100.0) > x.estimated_rows THEN 1 END
2707FROM ##bou_BlitzCacheProcs p
2708 JOIN (
2709 SELECT qs.SqlHandle,
2710 relop.value('sum(/p:RelOp/@EstimateRows)', 'float') AS estimated_rows ,
2711 relop.value('sum(/p:RelOp/@EstimateRewinds)', 'float') + relop.value('sum(/p:RelOp/@EstimateRebinds)', 'float') + 1.0 AS estimated_executions
2712 FROM #relop qs
2713 ) AS x ON p.SqlHandle = x.SqlHandle
2714WHERE SPID = @@SPID
2715OPTION (RECOMPILE);
2716END;
2717
2718
2719RAISERROR(N'Performing TVF join check', 0, 1) WITH NOWAIT;
2720WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2721UPDATE p
2722SET p.tvf_join = CASE WHEN x.tvf_join = 1 THEN 1 END
2723FROM ##bou_BlitzCacheProcs p
2724 JOIN (
2725 SELECT r.SqlHandle,
2726 1 AS tvf_join
2727 FROM #relop AS r
2728 WHERE r.relop.exist('//p:RelOp[(@LogicalOp[.="Table-valued function"])]') = 1
2729 AND r.relop.exist('//p:RelOp[contains(@LogicalOp, "Join")]') = 1
2730 ) AS x ON p.SqlHandle = x.SqlHandle
2731WHERE SPID = @@SPID
2732OPTION (RECOMPILE);
2733
2734
2735RAISERROR(N'Checking for operator warnings', 0, 1) WITH NOWAIT;
2736WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2737, x AS (
2738SELECT r.SqlHandle,
2739 c.n.exist('//p:Warnings[(@NoJoinPredicate[.="1"])]') AS warning_no_join_predicate,
2740 c.n.exist('//p:ColumnsWithNoStatistics') AS no_stats_warning ,
2741 c.n.exist('//p:Warnings') AS relop_warnings
2742FROM #relop AS r
2743CROSS APPLY r.relop.nodes('/p:RelOp/p:Warnings') AS c(n)
2744)
2745UPDATE p
2746SET p.warning_no_join_predicate = x.warning_no_join_predicate,
2747 p.no_stats_warning = x.no_stats_warning,
2748 p.relop_warnings = x.relop_warnings
2749FROM ##bou_BlitzCacheProcs AS p
2750JOIN x ON x.SqlHandle = p.SqlHandle
2751AND SPID = @@SPID
2752OPTION (RECOMPILE);
2753
2754
2755RAISERROR(N'Checking for table variables', 0, 1) WITH NOWAIT;
2756WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2757, x AS (
2758SELECT r.SqlHandle,
2759 c.n.value('substring(@Table, 2, 1)','VARCHAR(100)') AS first_char
2760FROM #relop r
2761CROSS APPLY r.relop.nodes('//p:Object') AS c(n)
2762)
2763UPDATE p
2764SET is_table_variable = 1
2765FROM ##bou_BlitzCacheProcs AS p
2766JOIN x ON x.SqlHandle = p.SqlHandle
2767AND SPID = @@SPID
2768WHERE x.first_char = '@'
2769OPTION (RECOMPILE);
2770
2771IF @ExpertMode > 0
2772BEGIN
2773RAISERROR(N'Checking for functions', 0, 1) WITH NOWAIT;
2774WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2775, x AS (
2776SELECT qs.SqlHandle,
2777 n.fn.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS function_count,
2778 n.fn.value('count(distinct-values(//p:UserDefinedFunction[@IsClrFunction = "1"]))', 'INT') AS clr_function_count
2779FROM #relop qs
2780CROSS APPLY relop.nodes('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ScalarOperator') n(fn)
2781)
2782UPDATE p
2783SET p.function_count = x.function_count,
2784 p.clr_function_count = x.clr_function_count
2785FROM ##bou_BlitzCacheProcs AS p
2786JOIN x ON x.SqlHandle = p.SqlHandle
2787AND SPID = @@SPID
2788OPTION (RECOMPILE);
2789END;
2790
2791
2792RAISERROR(N'Checking for expensive key lookups', 0, 1) WITH NOWAIT;
2793WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2794UPDATE ##bou_BlitzCacheProcs
2795SET key_lookup_cost = x.key_lookup_cost
2796FROM (
2797SELECT
2798 qs.SqlHandle,
2799 MAX(relop.value('sum(/p:RelOp/@EstimatedTotalSubtreeCost)', 'float')) AS key_lookup_cost
2800FROM #relop qs
2801WHERE [relop].exist('/p:RelOp/p:IndexScan[(@Lookup[.="1"])]') = 1
2802GROUP BY qs.SqlHandle
2803) AS x
2804WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
2805AND SPID = @@SPID
2806OPTION (RECOMPILE);
2807
2808
2809RAISERROR(N'Checking for expensive remote queries', 0, 1) WITH NOWAIT;
2810WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2811UPDATE ##bou_BlitzCacheProcs
2812SET remote_query_cost = x.remote_query_cost
2813FROM (
2814SELECT
2815 qs.SqlHandle,
2816 MAX(relop.value('sum(/p:RelOp/@EstimatedTotalSubtreeCost)', 'float')) AS remote_query_cost
2817FROM #relop qs
2818WHERE [relop].exist('/p:RelOp[(@PhysicalOp[contains(., "Remote")])]') = 1
2819GROUP BY qs.SqlHandle
2820) AS x
2821WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
2822AND SPID = @@SPID
2823OPTION (RECOMPILE);
2824
2825RAISERROR(N'Checking for expensive sorts', 0, 1) WITH NOWAIT;
2826WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2827UPDATE ##bou_BlitzCacheProcs
2828SET sort_cost = y.max_sort_cost
2829FROM (
2830 SELECT x.SqlHandle, MAX((x.sort_io + x.sort_cpu)) AS max_sort_cost
2831 FROM (
2832 SELECT
2833 qs.SqlHandle,
2834 relop.value('sum(/p:RelOp/@EstimateIO)', 'float') AS sort_io,
2835 relop.value('sum(/p:RelOp/@EstimateCPU)', 'float') AS sort_cpu
2836 FROM #relop qs
2837 WHERE [relop].exist('/p:RelOp[(@PhysicalOp[.="Sort"])]') = 1
2838 ) AS x
2839 GROUP BY x.SqlHandle
2840 ) AS y
2841WHERE ##bou_BlitzCacheProcs.SqlHandle = y.SqlHandle
2842AND SPID = @@SPID
2843OPTION (RECOMPILE);
2844
2845RAISERROR(N'Checking for Optimistic cursors', 0, 1) WITH NOWAIT;
2846WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2847UPDATE b
2848SET b.is_optimistic_cursor = 1
2849FROM ##bou_BlitzCacheProcs b
2850JOIN #statements AS qs
2851ON b.SqlHandle = qs.SqlHandle
2852CROSS APPLY qs.statement.nodes('/p:StmtCursor') AS n1(fn)
2853WHERE SPID = @@SPID
2854AND n1.fn.exist('//p:CursorPlan/@CursorConcurrency[.="Optimistic"]') = 1
2855OPTION (RECOMPILE);
2856
2857
2858RAISERROR(N'Checking if cursor is Forward Only', 0, 1) WITH NOWAIT;
2859WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2860UPDATE b
2861SET b.is_forward_only_cursor = 1
2862FROM ##bou_BlitzCacheProcs b
2863JOIN #statements AS qs
2864ON b.SqlHandle = qs.SqlHandle
2865CROSS APPLY qs.statement.nodes('/p:StmtCursor') AS n1(fn)
2866WHERE SPID = @@SPID
2867AND n1.fn.exist('//p:CursorPlan/@ForwardOnly[.="true"]') = 1
2868OPTION (RECOMPILE);
2869
2870RAISERROR(N'Checking if cursor is Fast Forward', 0, 1) WITH NOWAIT;
2871WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2872UPDATE b
2873SET b.is_fast_forward_cursor = 1
2874FROM ##bou_BlitzCacheProcs b
2875JOIN #statements AS qs
2876ON b.SqlHandle = qs.SqlHandle
2877CROSS APPLY qs.statement.nodes('/p:StmtCursor') AS n1(fn)
2878WHERE SPID = @@SPID
2879AND n1.fn.exist('//p:CursorPlan/@CursorActualType[.="FastForward"]') = 1
2880OPTION (RECOMPILE);
2881
2882
2883RAISERROR(N'Checking for Dynamic cursors', 0, 1) WITH NOWAIT;
2884WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2885UPDATE b
2886SET b.is_cursor_dynamic = 1
2887FROM ##bou_BlitzCacheProcs b
2888JOIN #statements AS qs
2889ON b.SqlHandle = qs.SqlHandle
2890CROSS APPLY qs.statement.nodes('/p:StmtCursor') AS n1(fn)
2891WHERE SPID = @@SPID
2892AND n1.fn.exist('//p:CursorPlan/@CursorActualType[.="Dynamic"]') = 1
2893OPTION (RECOMPILE);
2894
2895IF @ExpertMode > 0
2896BEGIN
2897RAISERROR(N'Checking for bad scans and plan forcing', 0, 1) WITH NOWAIT;
2898;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2899UPDATE b
2900SET
2901b.is_table_scan = x.is_table_scan,
2902b.backwards_scan = x.backwards_scan,
2903b.forced_index = x.forced_index,
2904b.forced_seek = x.forced_seek,
2905b.forced_scan = x.forced_scan
2906FROM ##bou_BlitzCacheProcs b
2907JOIN (
2908SELECT
2909 qs.SqlHandle,
2910 0 AS is_table_scan,
2911 q.n.exist('@ScanDirection[.="BACKWARD"]') AS backwards_scan,
2912 q.n.value('@ForcedIndex', 'bit') AS forced_index,
2913 q.n.value('@ForceSeek', 'bit') AS forced_seek,
2914 q.n.value('@ForceScan', 'bit') AS forced_scan
2915FROM #relop qs
2916CROSS APPLY qs.relop.nodes('//p:IndexScan') AS q(n)
2917UNION ALL
2918SELECT
2919 qs.SqlHandle,
2920 1 AS is_table_scan,
2921 q.n.exist('@ScanDirection[.="BACKWARD"]') AS backwards_scan,
2922 q.n.value('@ForcedIndex', 'bit') AS forced_index,
2923 q.n.value('@ForceSeek', 'bit') AS forced_seek,
2924 q.n.value('@ForceScan', 'bit') AS forced_scan
2925FROM #relop qs
2926CROSS APPLY qs.relop.nodes('//p:TableScan') AS q(n)
2927) AS x ON b.SqlHandle = x.SqlHandle
2928WHERE SPID = @@SPID
2929OPTION (RECOMPILE);
2930END
2931
2932
2933IF @ExpertMode > 0
2934BEGIN
2935RAISERROR(N'Checking for computed columns that reference scalar UDFs', 0, 1) WITH NOWAIT;
2936WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2937UPDATE ##bou_BlitzCacheProcs
2938SET is_computed_scalar = x.computed_column_function
2939FROM (
2940SELECT qs.SqlHandle,
2941 n.fn.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS computed_column_function
2942FROM #relop qs
2943CROSS APPLY relop.nodes('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ScalarOperator') n(fn)
2944WHERE n.fn.exist('/p:RelOp/p:ComputeScalar/p:DefinedValues/p:DefinedValue/p:ColumnReference[(@ComputedColumn[.="1"])]') = 1
2945) AS x
2946WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
2947AND SPID = @@SPID
2948OPTION (RECOMPILE);
2949END;
2950
2951
2952RAISERROR(N'Checking for filters that reference scalar UDFs', 0, 1) WITH NOWAIT;
2953WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
2954UPDATE ##bou_BlitzCacheProcs
2955SET is_computed_filter = x.filter_function
2956FROM (
2957SELECT
2958r.SqlHandle,
2959c.n.value('count(distinct-values(//p:UserDefinedFunction[not(@IsClrFunction)]))', 'INT') AS filter_function
2960FROM #relop AS r
2961CROSS APPLY r.relop.nodes('/p:RelOp/p:Filter/p:Predicate/p:ScalarOperator/p:Compare/p:ScalarOperator/p:UserDefinedFunction') c(n)
2962) x
2963WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
2964AND SPID = @@SPID
2965OPTION (RECOMPILE);
2966
2967IF @ExpertMode > 0
2968BEGIN
2969RAISERROR(N'Checking modification queries that hit lots of indexes', 0, 1) WITH NOWAIT;
2970WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
2971IndexOps AS
2972(
2973 SELECT
2974 r.QueryHash,
2975 c.n.value('@PhysicalOp', 'VARCHAR(100)') AS op_name,
2976 c.n.exist('@PhysicalOp[.="Index Insert"]') AS ii,
2977 c.n.exist('@PhysicalOp[.="Index Update"]') AS iu,
2978 c.n.exist('@PhysicalOp[.="Index Delete"]') AS id,
2979 c.n.exist('@PhysicalOp[.="Clustered Index Insert"]') AS cii,
2980 c.n.exist('@PhysicalOp[.="Clustered Index Update"]') AS ciu,
2981 c.n.exist('@PhysicalOp[.="Clustered Index Delete"]') AS cid,
2982 c.n.exist('@PhysicalOp[.="Table Insert"]') AS ti,
2983 c.n.exist('@PhysicalOp[.="Table Update"]') AS tu,
2984 c.n.exist('@PhysicalOp[.="Table Delete"]') AS td
2985 FROM #relop AS r
2986 CROSS APPLY r.relop.nodes('/p:RelOp') c(n)
2987 OUTER APPLY r.relop.nodes('/p:RelOp/p:ScalarInsert/p:Object') q(n)
2988 OUTER APPLY r.relop.nodes('/p:RelOp/p:Update/p:Object') o2(n)
2989 OUTER APPLY r.relop.nodes('/p:RelOp/p:SimpleUpdate/p:Object') o3(n)
2990), iops AS
2991(
2992 SELECT ios.QueryHash,
2993 SUM(CONVERT(TINYINT, ios.ii)) AS index_insert_count,
2994 SUM(CONVERT(TINYINT, ios.iu)) AS index_update_count,
2995 SUM(CONVERT(TINYINT, ios.id)) AS index_delete_count,
2996 SUM(CONVERT(TINYINT, ios.cii)) AS cx_insert_count,
2997 SUM(CONVERT(TINYINT, ios.ciu)) AS cx_update_count,
2998 SUM(CONVERT(TINYINT, ios.cid)) AS cx_delete_count,
2999 SUM(CONVERT(TINYINT, ios.ti)) AS table_insert_count,
3000 SUM(CONVERT(TINYINT, ios.tu)) AS table_update_count,
3001 SUM(CONVERT(TINYINT, ios.td)) AS table_delete_count
3002 FROM IndexOps AS ios
3003 WHERE ios.op_name IN ('Index Insert', 'Index Delete', 'Index Update',
3004 'Clustered Index Insert', 'Clustered Index Delete', 'Clustered Index Update',
3005 'Table Insert', 'Table Delete', 'Table Update')
3006 GROUP BY ios.QueryHash)
3007UPDATE b
3008SET b.index_insert_count = iops.index_insert_count,
3009 b.index_update_count = iops.index_update_count,
3010 b.index_delete_count = iops.index_delete_count,
3011 b.cx_insert_count = iops.cx_insert_count,
3012 b.cx_update_count = iops.cx_update_count,
3013 b.cx_delete_count = iops.cx_delete_count,
3014 b.table_insert_count = iops.table_insert_count,
3015 b.table_update_count = iops.table_update_count,
3016 b.table_delete_count = iops.table_delete_count
3017FROM ##bou_BlitzCacheProcs AS b
3018JOIN iops ON iops.QueryHash = b.QueryHash
3019WHERE SPID = @@SPID
3020OPTION (RECOMPILE);
3021END;
3022
3023
3024IF @ExpertMode > 0
3025BEGIN
3026RAISERROR(N'Checking for Spatial index use', 0, 1) WITH NOWAIT;
3027WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3028UPDATE ##bou_BlitzCacheProcs
3029SET is_spatial = x.is_spatial
3030FROM (
3031SELECT qs.SqlHandle,
3032 1 AS is_spatial
3033FROM #relop qs
3034CROSS APPLY relop.nodes('/p:RelOp//p:Object') n(fn)
3035WHERE n.fn.exist('(@IndexKind[.="Spatial"])') = 1
3036) AS x
3037WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
3038AND SPID = @@SPID
3039OPTION (RECOMPILE);
3040END;
3041
3042
3043IF @ExpertMode > 0
3044BEGIN
3045RAISERROR('Checking for wonky Index Spools', 0, 1) WITH NOWAIT;
3046WITH XMLNAMESPACES (
3047 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3048, selects
3049AS ( SELECT s.QueryHash
3050 FROM #statements AS s
3051 WHERE s.statement.exist('/p:StmtSimple/@StatementType[.="SELECT"]') = 1 )
3052, spools
3053AS ( SELECT DISTINCT r.QueryHash,
3054 c.n.value('@EstimateRows', 'FLOAT') AS estimated_rows,
3055 c.n.value('@EstimateIO', 'FLOAT') AS estimated_io,
3056 c.n.value('@EstimateCPU', 'FLOAT') AS estimated_cpu,
3057 c.n.value('@EstimateRewinds', 'FLOAT') AS estimated_rewinds
3058FROM #relop AS r
3059JOIN selects AS s
3060ON s.QueryHash = r.QueryHash
3061CROSS APPLY r.relop.nodes('/p:RelOp') AS c(n)
3062WHERE r.relop.exist('/p:RelOp[@PhysicalOp="Index Spool" and @LogicalOp="Eager Spool"]') = 1
3063)
3064UPDATE b
3065 SET b.index_spool_rows = sp.estimated_rows,
3066 b.index_spool_cost = ((sp.estimated_io * sp.estimated_cpu) * CASE WHEN sp.estimated_rewinds < 1 THEN 1 ELSE sp.estimated_rewinds END)
3067FROM ##bou_BlitzCacheProcs b
3068JOIN spools sp
3069ON sp.QueryHash = b.QueryHash
3070OPTION (RECOMPILE);
3071END;
3072
3073
3074/* 2012+ only */
3075IF @v >= 11
3076BEGIN
3077
3078 RAISERROR(N'Checking for forced serialization', 0, 1) WITH NOWAIT;
3079 WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3080 UPDATE ##bou_BlitzCacheProcs
3081 SET is_forced_serial = 1
3082 FROM #query_plan qp
3083 WHERE qp.SqlHandle = ##bou_BlitzCacheProcs.SqlHandle
3084 AND SPID = @@SPID
3085 AND query_plan.exist('/p:QueryPlan/@NonParallelPlanReason') = 1
3086 AND (##bou_BlitzCacheProcs.is_parallel = 0 OR ##bou_BlitzCacheProcs.is_parallel IS NULL)
3087 OPTION (RECOMPILE);
3088
3089 IF @ExpertMode > 0
3090 BEGIN
3091 RAISERROR(N'Checking for ColumnStore queries operating in Row Mode instead of Batch Mode', 0, 1) WITH NOWAIT;
3092 WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3093 UPDATE ##bou_BlitzCacheProcs
3094 SET columnstore_row_mode = x.is_row_mode
3095 FROM (
3096 SELECT
3097 qs.SqlHandle,
3098 relop.exist('/p:RelOp[(@EstimatedExecutionMode[.="Row"])]') AS is_row_mode
3099 FROM #relop qs
3100 WHERE [relop].exist('/p:RelOp/p:IndexScan[(@Storage[.="ColumnStore"])]') = 1
3101 ) AS x
3102 WHERE ##bou_BlitzCacheProcs.SqlHandle = x.SqlHandle
3103 AND SPID = @@SPID
3104 OPTION (RECOMPILE);
3105 END;
3106
3107END;
3108
3109/* 2014+ only */
3110IF @v >= 12
3111BEGIN
3112 RAISERROR('Checking for downlevel cardinality estimators being used on SQL Server 2014.', 0, 1) WITH NOWAIT;
3113
3114 WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3115 UPDATE p
3116 SET downlevel_estimator = CASE WHEN statement.value('min(//p:StmtSimple/@CardinalityEstimationModelVersion)', 'int') < (@v * 10) THEN 1 END
3117 FROM ##bou_BlitzCacheProcs p
3118 JOIN #statements s ON p.QueryHash = s.QueryHash
3119 WHERE SPID = @@SPID
3120 OPTION (RECOMPILE);
3121END ;
3122
3123/* 2016+ only */
3124IF @v >= 13 AND @ExpertMode > 0
3125BEGIN
3126 RAISERROR('Checking for row level security in 2016 only', 0, 1) WITH NOWAIT;
3127
3128 WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3129 UPDATE p
3130 SET p.is_row_level = 1
3131 FROM ##bou_BlitzCacheProcs p
3132 JOIN #statements s ON p.QueryHash = s.QueryHash
3133 WHERE SPID = @@SPID
3134 AND statement.exist('/p:StmtSimple/@SecurityPolicyApplied[.="true"]') = 1
3135 OPTION (RECOMPILE);
3136END ;
3137
3138/* 2017+ only */
3139IF @v >= 14
3140BEGIN
3141
3142RAISERROR('Gathering stats information', 0, 1) WITH NOWAIT;
3143WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3144INSERT INTO #stats_agg
3145SELECT qp.SqlHandle,
3146 x.c.value('@LastUpdate', 'DATETIME2(7)') AS LastUpdate,
3147 x.c.value('@ModificationCount', 'INT') AS ModificationCount,
3148 x.c.value('@SamplingPercent', 'FLOAT') AS SamplingPercent,
3149 x.c.value('@Statistics', 'NVARCHAR(258)') AS [Statistics],
3150 x.c.value('@Table', 'NVARCHAR(258)') AS [Table],
3151 x.c.value('@Schema', 'NVARCHAR(258)') AS [Schema],
3152 x.c.value('@Database', 'NVARCHAR(258)') AS [Database]
3153FROM #query_plan AS qp
3154CROSS APPLY qp.query_plan.nodes('//p:OptimizerStatsUsage/p:StatisticsInfo') x (c)
3155OPTION (RECOMPILE);
3156
3157
3158RAISERROR('Checking for stale stats', 0, 1) WITH NOWAIT;
3159WITH stale_stats AS (
3160 SELECT sa.SqlHandle
3161 FROM #stats_agg AS sa
3162 GROUP BY sa.SqlHandle
3163 HAVING MAX(sa.LastUpdate) <= DATEADD(DAY, -7, SYSDATETIME())
3164 AND AVG(sa.ModificationCount) >= 100000
3165)
3166UPDATE b
3167SET stale_stats = 1
3168FROM ##bou_BlitzCacheProcs b
3169JOIN stale_stats os
3170ON b.SqlHandle = os.SqlHandle
3171AND b.SPID = @@SPID
3172OPTION (RECOMPILE);
3173
3174
3175RAISERROR('Checking for adaptive joins', 0, 1) WITH NOWAIT;
3176WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
3177aj AS (
3178 SELECT
3179 SqlHandle
3180 FROM #relop AS r
3181 CROSS APPLY r.relop.nodes('//p:RelOp') x(c)
3182 WHERE x.c.exist('@IsAdaptive[.=1]') = 1
3183)
3184UPDATE b
3185SET b.is_adaptive = 1
3186FROM ##bou_BlitzCacheProcs b
3187JOIN aj
3188ON b.SqlHandle = aj.SqlHandle
3189AND b.SPID = @@SPID
3190OPTION (RECOMPILE);
3191
3192
3193IF @ExpertMode > 0
3194BEGIN;
3195WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
3196row_goals AS(
3197SELECT qs.QueryHash
3198FROM #relop qs
3199WHERE relop.value('sum(/p:RelOp/@EstimateRowsWithoutRowGoal)', 'float') > 0
3200)
3201UPDATE b
3202SET b.is_row_goal = 1
3203FROM ##bou_BlitzCacheProcs b
3204JOIN row_goals
3205ON b.QueryHash = row_goals.QueryHash
3206AND b.SPID = @@SPID
3207OPTION (RECOMPILE);
3208END ;
3209
3210END;
3211
3212-- query level checks
3213RAISERROR(N'Performing query level checks', 0, 1) WITH NOWAIT;
3214WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3215UPDATE ##bou_BlitzCacheProcs
3216SET missing_index_count = query_plan.value('count(//p:QueryPlan/p:MissingIndexes/p:MissingIndexGroup)', 'int') ,
3217 unmatched_index_count = query_plan.value('count(//p:QueryPlan/p:UnmatchedIndexes/p:Parameterization/p:Object)', 'int') ,
3218 SerialDesiredMemory = query_plan.value('sum(//p:QueryPlan/p:MemoryGrantInfo/@SerialDesiredMemory)', 'float') ,
3219 SerialRequiredMemory = query_plan.value('sum(//p:QueryPlan/p:MemoryGrantInfo/@SerialRequiredMemory)', 'float'),
3220 CachedPlanSize = query_plan.value('sum(//p:QueryPlan/@CachedPlanSize)', 'float') ,
3221 CompileTime = query_plan.value('sum(//p:QueryPlan/@CompileTime)', 'float') ,
3222 CompileCPU = query_plan.value('sum(//p:QueryPlan/@CompileCPU)', 'float') ,
3223 CompileMemory = query_plan.value('sum(//p:QueryPlan/@CompileMemory)', 'float')
3224FROM #query_plan qp
3225WHERE qp.QueryHash = ##bou_BlitzCacheProcs.QueryHash
3226AND SPID = @@SPID
3227OPTION (RECOMPILE);
3228
3229
3230/* END Testing using XML nodes to speed up processing */
3231RAISERROR(N'Gathering additional plan level information', 0, 1) WITH NOWAIT;
3232WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3233UPDATE ##bou_BlitzCacheProcs
3234SET NumberOfDistinctPlans = distinct_plan_count,
3235 NumberOfPlans = number_of_plans,
3236 plan_multiple_plans = CASE WHEN distinct_plan_count < number_of_plans THEN 1 END
3237FROM (
3238SELECT COUNT(DISTINCT QueryHash) AS distinct_plan_count,
3239 COUNT(QueryHash) AS number_of_plans,
3240 QueryHash
3241FROM ##bou_BlitzCacheProcs
3242WHERE SPID = @@SPID
3243GROUP BY QueryHash
3244) AS x
3245WHERE ##bou_BlitzCacheProcs.QueryHash = x.QueryHash
3246OPTION (RECOMPILE);
3247
3248/* Update to grab stored procedure name for individual statements */
3249RAISERROR(N'Attempting to get stored procedure name for individual statements', 0, 1) WITH NOWAIT;
3250UPDATE p
3251SET QueryType = QueryType + ' (parent ' +
3252 + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id, s.database_id))
3253 + '.'
3254 + QUOTENAME(OBJECT_NAME(s.object_id, s.database_id)) + ')'
3255FROM ##bou_BlitzCacheProcs p
3256 JOIN sys.dm_exec_procedure_stats s ON p.SqlHandle = s.sql_handle
3257WHERE QueryType = 'Statement'
3258AND SPID = @@SPID
3259OPTION (RECOMPILE);
3260
3261/* Trace Flag Checks 2014 SP2 and 2016 SP1 only)*/
3262IF @v >= 11
3263BEGIN
3264
3265RAISERROR(N'Trace flag checks', 0, 1) WITH NOWAIT;
3266;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3267, tf_pretty AS (
3268SELECT qp.QueryHash,
3269 qp.SqlHandle,
3270 q.n.value('@Value', 'INT') AS trace_flag,
3271 q.n.value('@Scope', 'VARCHAR(10)') AS scope
3272FROM #query_plan qp
3273CROSS APPLY qp.query_plan.nodes('/p:QueryPlan/p:TraceFlags/p:TraceFlag') AS q(n)
3274)
3275INSERT INTO #trace_flags
3276SELECT DISTINCT tf1.SqlHandle , tf1.QueryHash,
3277 STUFF((
3278 SELECT DISTINCT ', ' + CONVERT(VARCHAR(5), tf2.trace_flag)
3279 FROM tf_pretty AS tf2
3280 WHERE tf1.SqlHandle = tf2.SqlHandle
3281 AND tf1.QueryHash = tf2.QueryHash
3282 AND tf2.scope = 'Global'
3283 FOR XML PATH(N'')), 1, 2, N''
3284 ) AS global_trace_flags,
3285 STUFF((
3286 SELECT DISTINCT ', ' + CONVERT(VARCHAR(5), tf2.trace_flag)
3287 FROM tf_pretty AS tf2
3288 WHERE tf1.SqlHandle = tf2.SqlHandle
3289 AND tf1.QueryHash = tf2.QueryHash
3290 AND tf2.scope = 'Session'
3291 FOR XML PATH(N'')), 1, 2, N''
3292 ) AS session_trace_flags
3293FROM tf_pretty AS tf1
3294OPTION (RECOMPILE);
3295
3296UPDATE p
3297SET p.trace_flags_session = tf.session_trace_flags
3298FROM ##bou_BlitzCacheProcs p
3299JOIN #trace_flags tf ON tf.QueryHash = p.QueryHash
3300WHERE SPID = @@SPID
3301OPTION (RECOMPILE);
3302
3303END;
3304
3305
3306RAISERROR(N'Checking for MSTVFs', 0, 1) WITH NOWAIT;
3307WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3308UPDATE b
3309SET b.is_mstvf = 1
3310FROM #relop AS r
3311JOIN ##bou_BlitzCacheProcs AS b
3312ON b.SqlHandle = r.SqlHandle
3313WHERE r.relop.exist('/p:RelOp[(@EstimateRows="100" or @EstimateRows="1") and @LogicalOp="Table-valued function"]') = 1
3314OPTION (RECOMPILE);
3315
3316
3317IF @ExpertMode > 0
3318BEGIN
3319RAISERROR(N'Checking for many to many merge joins', 0, 1) WITH NOWAIT;
3320WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3321UPDATE b
3322SET b.is_mm_join = 1
3323FROM #relop AS r
3324JOIN ##bou_BlitzCacheProcs AS b
3325ON b.SqlHandle = r.SqlHandle
3326WHERE r.relop.exist('/p:RelOp/p:Merge/@ManyToMany[.="1"]') = 1
3327OPTION (RECOMPILE);
3328END ;
3329
3330
3331IF @ExpertMode > 0
3332BEGIN
3333RAISERROR(N'Is Paul White Electric?', 0, 1) WITH NOWAIT;
3334WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p),
3335is_paul_white_electric AS (
3336SELECT 1 AS [is_paul_white_electric],
3337r.SqlHandle
3338FROM #relop AS r
3339CROSS APPLY r.relop.nodes('//p:RelOp') c(n)
3340WHERE c.n.exist('@PhysicalOp[.="Switch"]') = 1
3341)
3342UPDATE b
3343SET b.is_paul_white_electric = ipwe.is_paul_white_electric
3344FROM ##bou_BlitzCacheProcs AS b
3345JOIN is_paul_white_electric ipwe
3346ON ipwe.SqlHandle = b.SqlHandle
3347WHERE b.SPID = @@SPID
3348OPTION (RECOMPILE);
3349END ;
3350
3351
3352RAISERROR(N'Checking for non-sargable predicates', 0, 1) WITH NOWAIT;
3353WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3354, nsarg
3355 AS ( SELECT r.QueryHash, 1 AS fn, 0 AS jo, 0 AS lk
3356 FROM #relop AS r
3357 CROSS APPLY r.relop.nodes('/p:RelOp/p:IndexScan/p:Predicate/p:ScalarOperator/p:Compare/p:ScalarOperator') AS ca(x)
3358 WHERE ( ca.x.exist('//p:ScalarOperator/p:Intrinsic/@FunctionName') = 1
3359 OR ca.x.exist('//p:ScalarOperator/p:IF') = 1 )
3360 UNION ALL
3361 SELECT r.QueryHash, 0 AS fn, 1 AS jo, 0 AS lk
3362 FROM #relop AS r
3363 CROSS APPLY r.relop.nodes('/p:RelOp//p:ScalarOperator') AS ca(x)
3364 WHERE r.relop.exist('/p:RelOp[contains(@LogicalOp, "Join")]') = 1
3365 AND ca.x.exist('//p:ScalarOperator[contains(@ScalarString, "Expr")]') = 1
3366 UNION ALL
3367 SELECT r.QueryHash, 0 AS fn, 0 AS jo, 1 AS lk
3368 FROM #relop AS r
3369 CROSS APPLY r.relop.nodes('/p:RelOp/p:IndexScan/p:Predicate/p:ScalarOperator') AS ca(x)
3370 CROSS APPLY ca.x.nodes('//p:Const') AS co(x)
3371 WHERE ca.x.exist('//p:ScalarOperator/p:Intrinsic/@FunctionName[.="like"]') = 1
3372 AND ( ( co.x.value('substring(@ConstValue, 1, 1)', 'VARCHAR(100)') <> 'N'
3373 AND co.x.value('substring(@ConstValue, 2, 1)', 'VARCHAR(100)') = '%' )
3374 OR ( co.x.value('substring(@ConstValue, 1, 1)', 'VARCHAR(100)') = 'N'
3375 AND co.x.value('substring(@ConstValue, 3, 1)', 'VARCHAR(100)') = '%' ))),
3376 d_nsarg
3377 AS ( SELECT DISTINCT
3378 nsarg.QueryHash
3379 FROM nsarg
3380 WHERE nsarg.fn = 1
3381 OR nsarg.jo = 1
3382 OR nsarg.lk = 1 )
3383UPDATE b
3384SET b.is_nonsargable = 1
3385FROM d_nsarg AS d
3386JOIN ##bou_BlitzCacheProcs AS b
3387 ON b.QueryHash = d.QueryHash
3388WHERE b.SPID = @@SPID
3389OPTION ( RECOMPILE );
3390
3391IF EXISTS ( SELECT 1
3392 FROM ##bou_BlitzCacheProcs AS bbcp
3393 WHERE bbcp.implicit_conversions = 1
3394 OR bbcp.QueryType LIKE '%Procedure or Function: %')
3395BEGIN
3396
3397RAISERROR(N'Getting information about implicit conversions and stored proc parameters', 0, 1) WITH NOWAIT;
3398
3399RAISERROR(N'Getting variable info', 0, 1) WITH NOWAIT;
3400WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3401INSERT #variable_info ( SPID, QueryHash, SqlHandle, proc_name, variable_name, variable_datatype, compile_time_value )
3402SELECT DISTINCT @@SPID,
3403 qp.QueryHash,
3404 qp.SqlHandle,
3405 b.QueryType AS proc_name,
3406 q.n.value('@Column', 'NVARCHAR(258)') AS variable_name,
3407 q.n.value('@ParameterDataType', 'NVARCHAR(258)') AS variable_datatype,
3408 q.n.value('@ParameterCompiledValue', 'NVARCHAR(258)') AS compile_time_value
3409FROM #query_plan AS qp
3410JOIN ##bou_BlitzCacheProcs AS b
3411ON (b.QueryType = 'adhoc' AND b.QueryHash = qp.QueryHash)
3412OR (b.QueryType <> 'adhoc' AND b.SqlHandle = qp.SqlHandle)
3413CROSS APPLY qp.query_plan.nodes('//p:QueryPlan/p:ParameterList/p:ColumnReference') AS q(n)
3414WHERE b.SPID = @@SPID
3415OPTION (RECOMPILE);
3416
3417RAISERROR(N'Getting conversion info', 0, 1) WITH NOWAIT;
3418WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3419INSERT #conversion_info ( SPID, QueryHash, SqlHandle, proc_name, expression )
3420SELECT DISTINCT @@SPID,
3421 qp.QueryHash,
3422 qp.SqlHandle,
3423 b.QueryType AS proc_name,
3424 qq.c.value('@Expression', 'NVARCHAR(4000)') AS expression
3425FROM #query_plan AS qp
3426JOIN ##bou_BlitzCacheProcs AS b
3427ON (b.QueryType = 'adhoc' AND b.QueryHash = qp.QueryHash)
3428OR (b.QueryType <> 'adhoc' AND b.SqlHandle = qp.SqlHandle)
3429CROSS APPLY qp.query_plan.nodes('//p:QueryPlan/p:Warnings/p:PlanAffectingConvert') AS qq(c)
3430WHERE qq.c.exist('@ConvertIssue[.="Seek Plan"]') = 1
3431 AND qp.QueryHash IS NOT NULL
3432 AND b.implicit_conversions = 1
3433AND b.SPID = @@SPID
3434OPTION (RECOMPILE);
3435
3436RAISERROR(N'Parsing conversion info', 0, 1) WITH NOWAIT;
3437INSERT #stored_proc_info ( SPID, SqlHandle, QueryHash, proc_name, variable_name, variable_datatype, converted_column_name, column_name, converted_to, compile_time_value )
3438SELECT @@SPID AS SPID,
3439 ci.SqlHandle,
3440 ci.QueryHash,
3441 REPLACE(REPLACE(REPLACE(ci.proc_name, ')', ''), 'Statement (parent ', ''), 'Procedure or Function: ', '') AS proc_name,
3442 CASE WHEN ci.at_charindex > 0
3443 AND ci.bracket_charindex > 0
3444 THEN SUBSTRING(ci.expression, ci.at_charindex, ci.bracket_charindex)
3445 ELSE N'**no_variable**'
3446 END AS variable_name,
3447 N'**no_variable**' AS variable_datatype,
3448 CASE WHEN ci.at_charindex = 0
3449 AND ci.comma_charindex > 0
3450 AND ci.second_comma_charindex > 0
3451 THEN SUBSTRING(ci.expression, ci.comma_charindex, ci.second_comma_charindex)
3452 ELSE N'**no_column**'
3453 END AS converted_column_name,
3454 CASE WHEN ci.at_charindex = 0
3455 AND ci.equal_charindex > 0
3456 AND ci.convert_implicit_charindex = 0
3457 THEN SUBSTRING(ci.expression, ci.equal_charindex, 4000)
3458 WHEN ci.at_charindex = 0
3459 AND ci.equal_charindex > 0
3460 AND ci.convert_implicit_charindex > 0
3461 THEN SUBSTRING(ci.expression, 0, ci.equal_charindex -1)
3462 WHEN ci.at_charindex > 0
3463 AND ci.comma_charindex > 0
3464 AND ci.second_comma_charindex > 0
3465 THEN SUBSTRING(ci.expression, ci.comma_charindex, ci.second_comma_charindex)
3466 ELSE N'**no_column **'
3467 END AS column_name,
3468 CASE WHEN ci.paren_charindex > 0
3469 AND ci.comma_paren_charindex > 0
3470 THEN SUBSTRING(ci.expression, ci.paren_charindex, ci.comma_paren_charindex)
3471 END AS converted_to,
3472 CASE WHEN ci.at_charindex = 0
3473 AND ci.convert_implicit_charindex = 0
3474 AND ci.proc_name = 'Statement'
3475 THEN SUBSTRING(ci.expression, ci.equal_charindex, 4000)
3476 ELSE '**idk_man**'
3477 END AS compile_time_value
3478FROM #conversion_info AS ci
3479OPTION (RECOMPILE);
3480
3481
3482
3483RAISERROR(N'Updating variables inserted procs', 0, 1) WITH NOWAIT;
3484UPDATE sp
3485SET sp.variable_datatype = vi.variable_datatype,
3486 sp.compile_time_value = vi.compile_time_value
3487FROM #stored_proc_info AS sp
3488JOIN #variable_info AS vi
3489ON (sp.proc_name = 'adhoc' AND sp.QueryHash = vi.QueryHash)
3490OR (sp.proc_name <> 'adhoc' AND sp.SqlHandle = vi.SqlHandle)
3491AND sp.variable_name = vi.variable_name
3492OPTION (RECOMPILE);
3493
3494
3495RAISERROR(N'Inserting variables for other procs', 0, 1) WITH NOWAIT;
3496INSERT #stored_proc_info
3497 ( SPID, SqlHandle, QueryHash, variable_name, variable_datatype, compile_time_value, proc_name )
3498SELECT vi.SPID, vi.SqlHandle, vi.QueryHash, vi.variable_name, vi.variable_datatype, vi.compile_time_value, REPLACE(REPLACE(REPLACE(vi.proc_name, ')', ''), 'Statement (parent ', ''), 'Procedure or Function: ', '') AS proc_name
3499FROM #variable_info AS vi
3500WHERE NOT EXISTS
3501(
3502 SELECT *
3503 FROM #stored_proc_info AS sp
3504 WHERE (sp.proc_name = 'adhoc' AND sp.QueryHash = vi.QueryHash)
3505 OR (sp.proc_name <> 'adhoc' AND sp.SqlHandle = vi.SqlHandle)
3506)
3507OPTION (RECOMPILE);
3508
3509
3510RAISERROR(N'Updating procs', 0, 1) WITH NOWAIT;
3511UPDATE s
3512SET s.variable_datatype = CASE WHEN s.variable_datatype LIKE '%(%)%' THEN
3513 LEFT(s.variable_datatype, CHARINDEX('(', s.variable_datatype) - 1)
3514 ELSE s.variable_datatype
3515 END,
3516 s.converted_to = CASE WHEN s.converted_to LIKE '%(%)%' THEN
3517 LEFT(s.converted_to, CHARINDEX('(', s.converted_to) - 1)
3518 ELSE s.converted_to
3519 END,
3520 s.compile_time_value = CASE WHEN s.compile_time_value LIKE '%(%)%' THEN
3521 SUBSTRING(s.compile_time_value,
3522 CHARINDEX('(', s.compile_time_value) + 1,
3523 CHARINDEX(')', s.compile_time_value) - 1
3524 - CHARINDEX('(', s.compile_time_value)
3525 )
3526 WHEN variable_datatype NOT IN ('bit', 'tinyint', 'smallint', 'int', 'bigint')
3527 AND s.variable_datatype NOT LIKE '%binary%'
3528 AND s.compile_time_value NOT LIKE 'N''%'''
3529 AND s.compile_time_value NOT LIKE '''%''' THEN
3530 QUOTENAME(compile_time_value, '''')
3531 ELSE s.compile_time_value
3532 END
3533FROM #stored_proc_info AS s
3534OPTION (RECOMPILE);
3535
3536RAISERROR(N'Updating conversion XML', 0, 1) WITH NOWAIT;
3537WITH precheck AS (
3538SELECT spi.SPID,
3539 spi.SqlHandle,
3540 spi.proc_name,
3541 CONVERT(XML,
3542 N'<ClickMe><![CDATA['
3543 + @nl
3544 + CASE WHEN spi.proc_name <> 'Statement'
3545 THEN N'The stored procedure ' + spi.proc_name
3546 ELSE N'This ad hoc statement'
3547 END
3548 + N' had the following implicit conversions: '
3549 + CHAR(10)
3550 + STUFF((
3551 SELECT DISTINCT
3552 @nl
3553 + CASE WHEN spi2.variable_name <> N'**no_variable**'
3554 THEN N'The variable '
3555 WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
3556 THEN N'The compiled value '
3557 WHEN spi2.column_name LIKE '%Expr%'
3558 THEN 'The expression '
3559 ELSE N'The column '
3560 END
3561 + CASE WHEN spi2.variable_name <> N'**no_variable**'
3562 THEN spi2.variable_name
3563 WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
3564 THEN spi2.compile_time_value
3565
3566 ELSE spi2.column_name
3567 END
3568 + N' has a data type of '
3569 + CASE WHEN spi2.variable_datatype = N'**no_variable**' THEN spi2.converted_to
3570 ELSE spi2.variable_datatype
3571 END
3572 + N' which caused implicit conversion on the column '
3573 + CASE WHEN spi2.column_name LIKE N'%CONVERT_IMPLICIT%'
3574 THEN spi2.converted_column_name
3575 WHEN spi2.column_name = N'**no_column**'
3576 THEN spi2.converted_column_name
3577 WHEN spi2.converted_column_name = N'**no_column**'
3578 THEN spi2.column_name
3579 WHEN spi2.column_name <> spi2.converted_column_name
3580 THEN spi2.converted_column_name
3581 ELSE spi2.column_name
3582 END
3583 + CASE WHEN spi2.variable_name = N'**no_variable**' AND (spi2.column_name = spi2.converted_column_name OR spi2.column_name LIKE '%CONVERT_IMPLICIT%')
3584 THEN N''
3585 WHEN spi2.column_name LIKE '%Expr%'
3586 THEN N''
3587 WHEN spi2.compile_time_value NOT IN ('**declared in proc**', '**idk_man**')
3588 AND spi2.compile_time_value <> spi2.column_name
3589 THEN ' with the value ' + RTRIM(spi2.compile_time_value)
3590 ELSE N''
3591 END
3592 + '.'
3593 FROM #stored_proc_info AS spi2
3594 WHERE spi.SqlHandle = spi2.SqlHandle
3595 FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
3596 + CHAR(10)
3597 + N']]></ClickMe>'
3598 ) AS implicit_conversion_info
3599FROM #stored_proc_info AS spi
3600GROUP BY spi.SPID, spi.SqlHandle, spi.proc_name
3601)
3602UPDATE b
3603SET b.implicit_conversion_info = pk.implicit_conversion_info
3604FROM ##bou_BlitzCacheProcs AS b
3605JOIN precheck pk
3606ON pk.SqlHandle = b.SqlHandle
3607AND pk.SPID = b.SPID
3608OPTION (RECOMPILE);
3609
3610RAISERROR(N'Updating cached parameter XML', 0, 1) WITH NOWAIT;
3611WITH precheck AS (
3612SELECT spi.SPID,
3613 spi.SqlHandle,
3614 spi.proc_name,
3615CONVERT(XML,
3616 N'<ClickMe><![CDATA['
3617 + @nl
3618 + N'EXEC '
3619 + spi.proc_name
3620 + N' '
3621 + STUFF((
3622 SELECT DISTINCT N', '
3623 + CASE WHEN spi2.variable_name <> N'**no_variable**' AND spi2.compile_time_value <> N'**idk_man**'
3624 THEN spi2.variable_name + N' = '
3625 ELSE @nl + N' We could not find any cached parameter values for this stored proc. '
3626 END
3627 + CASE WHEN spi2.variable_name = N'**no_variable**' OR spi2.compile_time_value = N'**idk_man**'
3628 THEN @nl + N' Possible reasons include declared variables inside the procedure, recompile hints, etc. '
3629 WHEN spi2.compile_time_value = N'NULL'
3630 THEN spi2.compile_time_value
3631 ELSE RTRIM(spi2.compile_time_value)
3632 END
3633 FROM #stored_proc_info AS spi2
3634 WHERE spi.SqlHandle = spi2.SqlHandle
3635 AND spi2.proc_name <> N'Statement'
3636 FOR XML PATH(N''), TYPE).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 1, N'')
3637 + @nl
3638 + N']]></ClickMe>'
3639 ) AS cached_execution_parameters
3640FROM #stored_proc_info AS spi
3641GROUP BY spi.SPID, spi.SqlHandle, spi.proc_name
3642)
3643UPDATE b
3644SET b.cached_execution_parameters = pk.cached_execution_parameters
3645FROM ##bou_BlitzCacheProcs AS b
3646JOIN precheck pk
3647ON pk.SqlHandle = b.SqlHandle
3648AND pk.SPID = b.SPID
3649OPTION (RECOMPILE);
3650
3651
3652END; --End implicit conversion information gathering
3653
3654UPDATE b
3655SET b.implicit_conversion_info = CASE WHEN b.implicit_conversion_info IS NULL THEN '<?NoNeedToClickMe -- N/A --?>' ELSE b.implicit_conversion_info END,
3656 b.cached_execution_parameters = CASE WHEN b.cached_execution_parameters IS NULL THEN '<?NoNeedToClickMe -- N/A --?>' ELSE b.cached_execution_parameters END
3657FROM ##bou_BlitzCacheProcs AS b
3658WHERE b.SPID = @@SPID
3659OPTION (RECOMPILE);
3660
3661/*Begin Missing Index*/
3662
3663IF EXISTS
3664 (SELECT 1 FROM ##bou_BlitzCacheProcs AS bbcp WHERE bbcp.missing_index_count > 0 AND bbcp.SPID = @@SPID)
3665 BEGIN;
3666
3667 WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3668 INSERT #missing_index_xml
3669 SELECT qp.QueryHash,
3670 qp.SqlHandle,
3671 c.mg.value('@Impact', 'FLOAT') AS Impact,
3672 c.mg.query('.') AS cmg
3673 FROM #query_plan AS qp
3674 CROSS APPLY qp.query_plan.nodes('//p:MissingIndexes/p:MissingIndexGroup') AS c(mg)
3675 WHERE qp.QueryHash IS NOT NULL
3676 AND c.mg.value('@Impact', 'FLOAT') > 70.0
3677 OPTION(RECOMPILE);
3678
3679 WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3680 INSERT #missing_index_schema
3681 SELECT mix.QueryHash, mix.SqlHandle, mix.impact,
3682 c.mi.value('@Database', 'NVARCHAR(128)') ,
3683 c.mi.value('@Schema', 'NVARCHAR(128)') ,
3684 c.mi.value('@Table', 'NVARCHAR(128)') ,
3685 c.mi.query('.')
3686 FROM #missing_index_xml AS mix
3687 CROSS APPLY mix.index_xml.nodes('//p:MissingIndex') AS c(mi)
3688 OPTION(RECOMPILE);
3689
3690 WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3691 INSERT #missing_index_usage
3692 SELECT ms.QueryHash, ms.SqlHandle, ms.impact, ms.database_name, ms.schema_name, ms.table_name,
3693 c.cg.value('@Usage', 'NVARCHAR(128)'),
3694 c.cg.query('.')
3695 FROM #missing_index_schema ms
3696 CROSS APPLY ms.index_xml.nodes('//p:ColumnGroup') AS c(cg)
3697 OPTION(RECOMPILE);
3698
3699 WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
3700 INSERT #missing_index_detail
3701 SELECT miu.QueryHash,
3702 miu.SqlHandle,
3703 miu.impact,
3704 miu.database_name,
3705 miu.schema_name,
3706 miu.table_name,
3707 miu.usage,
3708 c.c.value('@Name', 'NVARCHAR(128)')
3709 FROM #missing_index_usage AS miu
3710 CROSS APPLY miu.index_xml.nodes('//p:Column') AS c(c)
3711 OPTION (RECOMPILE);
3712
3713 INSERT #missing_index_pretty
3714 SELECT m.QueryHash, m.SqlHandle, m.impact, m.database_name, m.schema_name, m.table_name
3715 , STUFF(( SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
3716 FROM #missing_index_detail AS m2
3717 WHERE m2.usage = 'EQUALITY'
3718 AND m.QueryHash = m2.QueryHash
3719 AND m.SqlHandle = m2.SqlHandle
3720 AND m.impact = m2.impact
3721 AND m.database_name = m2.database_name
3722 AND m.schema_name = m2.schema_name
3723 AND m.table_name = m2.table_name
3724 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS equality
3725 , STUFF(( SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
3726 FROM #missing_index_detail AS m2
3727 WHERE m2.usage = 'INEQUALITY'
3728 AND m.QueryHash = m2.QueryHash
3729 AND m.SqlHandle = m2.SqlHandle
3730 AND m.impact = m2.impact
3731 AND m.database_name = m2.database_name
3732 AND m.schema_name = m2.schema_name
3733 AND m.table_name = m2.table_name
3734 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS inequality
3735 , STUFF(( SELECT DISTINCT N', ' + ISNULL(m2.column_name, '') AS column_name
3736 FROM #missing_index_detail AS m2
3737 WHERE m2.usage = 'INCLUDE'
3738 AND m.QueryHash = m2.QueryHash
3739 AND m.SqlHandle = m2.SqlHandle
3740 AND m.impact = m2.impact
3741 AND m.database_name = m2.database_name
3742 AND m.schema_name = m2.schema_name
3743 AND m.table_name = m2.table_name
3744 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') AS [include]
3745 FROM #missing_index_detail AS m
3746 GROUP BY m.QueryHash, m.SqlHandle, m.impact, m.database_name, m.schema_name, m.table_name
3747 OPTION (RECOMPILE);
3748
3749 WITH missing AS (
3750 SELECT mip.QueryHash,
3751 mip.SqlHandle,
3752 CONVERT(XML,
3753 N'<MissingIndexes><![CDATA['
3754 + CHAR(10) + CHAR(13)
3755 + STUFF(( SELECT CHAR(10) + CHAR(13) + ISNULL(mip2.details, '') AS details
3756 FROM #missing_index_pretty AS mip2
3757 WHERE mip.QueryHash = mip2.QueryHash
3758 AND mip.SqlHandle = mip2.SqlHandle
3759 GROUP BY mip2.details
3760 ORDER BY MAX(mip2.impact) DESC
3761 FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'')
3762 + CHAR(10) + CHAR(13)
3763 + N']]></MissingIndexes>'
3764 ) AS full_details
3765 FROM #missing_index_pretty AS mip
3766 GROUP BY mip.QueryHash, mip.SqlHandle, mip.impact
3767 )
3768 UPDATE bbcp
3769 SET bbcp.missing_indexes = m.full_details
3770 FROM ##bou_BlitzCacheProcs AS bbcp
3771 JOIN missing AS m
3772 ON m.SqlHandle = bbcp.SqlHandle
3773 AND SPID = @@SPID
3774 OPTION (RECOMPILE);
3775
3776
3777 END;
3778
3779 UPDATE b
3780 SET b.missing_indexes =
3781 CASE WHEN b.missing_indexes IS NULL
3782 THEN '<?NoNeedToClickMe -- N/A --?>'
3783 ELSE b.missing_indexes
3784 END
3785 FROM ##bou_BlitzCacheProcs AS b
3786 WHERE b.SPID = @@SPID
3787 OPTION (RECOMPILE);
3788
3789/*End Missing Index*/
3790
3791
3792
3793IF @SkipAnalysis = 1
3794 BEGIN
3795 RAISERROR(N'Skipping analysis, going to results', 0, 1) WITH NOWAIT;
3796 GOTO Results ;
3797 END;
3798
3799
3800/* Set configuration values */
3801RAISERROR(N'Setting configuration values', 0, 1) WITH NOWAIT;
3802DECLARE @execution_threshold INT = 1000 ,
3803 @parameter_sniffing_warning_pct TINYINT = 30,
3804 /* This is in average reads */
3805 @parameter_sniffing_io_threshold BIGINT = 100000 ,
3806 @ctp_threshold_pct TINYINT = 10,
3807 @long_running_query_warning_seconds BIGINT = 300 * 1000 ,
3808 @memory_grant_warning_percent INT = 10;
3809
3810IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'frequent execution threshold' = LOWER(parameter_name))
3811BEGIN
3812 SELECT @execution_threshold = CAST(value AS INT)
3813 FROM #configuration
3814 WHERE 'frequent execution threshold' = LOWER(parameter_name) ;
3815
3816 SET @msg = ' Setting "frequent execution threshold" to ' + CAST(@execution_threshold AS VARCHAR(10)) ;
3817
3818 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3819END;
3820
3821IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'parameter sniffing variance percent' = LOWER(parameter_name))
3822BEGIN
3823 SELECT @parameter_sniffing_warning_pct = CAST(value AS TINYINT)
3824 FROM #configuration
3825 WHERE 'parameter sniffing variance percent' = LOWER(parameter_name) ;
3826
3827 SET @msg = ' Setting "parameter sniffing variance percent" to ' + CAST(@parameter_sniffing_warning_pct AS VARCHAR(3)) ;
3828
3829 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3830END;
3831
3832IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'parameter sniffing io threshold' = LOWER(parameter_name))
3833BEGIN
3834 SELECT @parameter_sniffing_io_threshold = CAST(value AS BIGINT)
3835 FROM #configuration
3836 WHERE 'parameter sniffing io threshold' = LOWER(parameter_name) ;
3837
3838 SET @msg = ' Setting "parameter sniffing io threshold" to ' + CAST(@parameter_sniffing_io_threshold AS VARCHAR(10));
3839
3840 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3841END;
3842
3843IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'cost threshold for parallelism warning' = LOWER(parameter_name))
3844BEGIN
3845 SELECT @ctp_threshold_pct = CAST(value AS TINYINT)
3846 FROM #configuration
3847 WHERE 'cost threshold for parallelism warning' = LOWER(parameter_name) ;
3848
3849 SET @msg = ' Setting "cost threshold for parallelism warning" to ' + CAST(@ctp_threshold_pct AS VARCHAR(3));
3850
3851 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3852END;
3853
3854IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'long running query warning (seconds)' = LOWER(parameter_name))
3855BEGIN
3856 SELECT @long_running_query_warning_seconds = CAST(value * 1000 AS BIGINT)
3857 FROM #configuration
3858 WHERE 'long running query warning (seconds)' = LOWER(parameter_name) ;
3859
3860 SET @msg = ' Setting "long running query warning (seconds)" to ' + CAST(@long_running_query_warning_seconds AS VARCHAR(10));
3861
3862 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3863END;
3864
3865IF EXISTS (SELECT 1/0 FROM #configuration WHERE 'unused memory grant' = LOWER(parameter_name))
3866BEGIN
3867 SELECT @memory_grant_warning_percent = CAST(value AS INT)
3868 FROM #configuration
3869 WHERE 'unused memory grant' = LOWER(parameter_name) ;
3870
3871 SET @msg = ' Setting "unused memory grant" to ' + CAST(@memory_grant_warning_percent AS VARCHAR(10));
3872
3873 RAISERROR(@msg, 0, 1) WITH NOWAIT;
3874END;
3875
3876DECLARE @ctp INT ;
3877
3878SELECT @ctp = NULLIF(CAST(value AS INT), 0)
3879FROM sys.configurations
3880WHERE name = 'cost threshold for parallelism'
3881OPTION (RECOMPILE);
3882
3883
3884/* Update to populate checks columns */
3885RAISERROR('Checking for query level SQL Server issues.', 0, 1) WITH NOWAIT;
3886
3887WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
3888UPDATE ##bou_BlitzCacheProcs
3889SET frequent_execution = CASE WHEN ExecutionsPerMinute > @execution_threshold THEN 1 END ,
3890 parameter_sniffing = CASE WHEN AverageReads > @parameter_sniffing_io_threshold
3891 AND min_worker_time < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
3892 WHEN AverageReads > @parameter_sniffing_io_threshold
3893 AND max_worker_time > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageCPU) THEN 1
3894 WHEN AverageReads > @parameter_sniffing_io_threshold
3895 AND MinReturnedRows < ((1.0 - (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1
3896 WHEN AverageReads > @parameter_sniffing_io_threshold
3897 AND MaxReturnedRows > ((1.0 + (@parameter_sniffing_warning_pct / 100.0)) * AverageReturnedRows) THEN 1 END ,
3898 near_parallel = CASE WHEN QueryPlanCost BETWEEN @ctp * (1 - (@ctp_threshold_pct / 100.0)) AND @ctp THEN 1 END,
3899 long_running = CASE WHEN AverageDuration > @long_running_query_warning_seconds THEN 1
3900 WHEN max_worker_time > @long_running_query_warning_seconds THEN 1
3901 WHEN max_elapsed_time > @long_running_query_warning_seconds THEN 1 END,
3902 is_key_lookup_expensive = CASE WHEN QueryPlanCost >= (@ctp / 2) AND key_lookup_cost >= QueryPlanCost * .5 THEN 1 END,
3903 is_sort_expensive = CASE WHEN QueryPlanCost >= (@ctp / 2) AND sort_cost >= QueryPlanCost * .5 THEN 1 END,
3904 is_remote_query_expensive = CASE WHEN remote_query_cost >= QueryPlanCost * .05 THEN 1 END,
3905 is_forced_serial = CASE WHEN is_forced_serial = 1 THEN 1 END,
3906 is_unused_grant = CASE WHEN PercentMemoryGrantUsed <= @memory_grant_warning_percent AND MinGrantKB > @MinMemoryPerQuery THEN 1 END,
3907 long_running_low_cpu = CASE WHEN AverageDuration > AverageCPU * 4 AND AverageCPU < 500. THEN 1 END,
3908 low_cost_high_cpu = CASE WHEN QueryPlanCost <= 10 AND AverageCPU > 5000. THEN 1 END,
3909 is_spool_expensive = CASE WHEN QueryPlanCost > (@ctp / 2) AND index_spool_cost >= QueryPlanCost * .1 THEN 1 END,
3910 is_spool_more_rows = CASE WHEN index_spool_rows >= (AverageReturnedRows / ISNULL(NULLIF(ExecutionCount, 0), 1)) THEN 1 END,
3911 is_bad_estimate = CASE WHEN AverageReturnedRows > 0 AND (estimated_rows * 1000 < AverageReturnedRows OR estimated_rows > AverageReturnedRows * 1000) THEN 1 END,
3912 is_big_spills = CASE WHEN (AvgSpills / 128.) > 499. THEN 1 END
3913WHERE SPID = @@SPID
3914OPTION (RECOMPILE);
3915
3916
3917
3918RAISERROR('Checking for forced parameterization and cursors.', 0, 1) WITH NOWAIT;
3919
3920/* Set options checks */
3921UPDATE p
3922 SET is_forced_parameterized = CASE WHEN (CAST(pa.value AS INT) & 131072 = 131072) THEN 1 END ,
3923 is_forced_plan = CASE WHEN (CAST(pa.value AS INT) & 4 = 4) THEN 1 END ,
3924 SetOptions = SUBSTRING(
3925 CASE WHEN (CAST(pa.value AS INT) & 1 = 1) THEN ', ANSI_PADDING' ELSE '' END +
3926 CASE WHEN (CAST(pa.value AS INT) & 8 = 8) THEN ', CONCAT_NULL_YIELDS_NULL' ELSE '' END +
3927 CASE WHEN (CAST(pa.value AS INT) & 16 = 16) THEN ', ANSI_WARNINGS' ELSE '' END +
3928 CASE WHEN (CAST(pa.value AS INT) & 32 = 32) THEN ', ANSI_NULLS' ELSE '' END +
3929 CASE WHEN (CAST(pa.value AS INT) & 64 = 64) THEN ', QUOTED_IDENTIFIER' ELSE '' END +
3930 CASE WHEN (CAST(pa.value AS INT) & 4096 = 4096) THEN ', ARITH_ABORT' ELSE '' END +
3931 CASE WHEN (CAST(pa.value AS INT) & 8192 = 8191) THEN ', NUMERIC_ROUNDABORT' ELSE '' END
3932 , 2, 200000)
3933FROM ##bou_BlitzCacheProcs p
3934 CROSS APPLY sys.dm_exec_plan_attributes(p.PlanHandle) pa
3935WHERE pa.attribute = 'set_options'
3936AND SPID = @@SPID
3937OPTION (RECOMPILE);
3938
3939
3940/* Cursor checks */
3941UPDATE p
3942SET is_cursor = CASE WHEN CAST(pa.value AS INT) <> 0 THEN 1 END
3943FROM ##bou_BlitzCacheProcs p
3944 CROSS APPLY sys.dm_exec_plan_attributes(p.PlanHandle) pa
3945WHERE pa.attribute LIKE '%cursor%'
3946AND SPID = @@SPID
3947OPTION (RECOMPILE);
3948
3949UPDATE p
3950SET is_cursor = 1
3951FROM ##bou_BlitzCacheProcs p
3952WHERE QueryHash = 0x0000000000000000
3953OR QueryPlanHash = 0x0000000000000000
3954AND SPID = @@SPID
3955OPTION (RECOMPILE);
3956
3957
3958
3959RAISERROR('Populating Warnings column', 0, 1) WITH NOWAIT;
3960/* Populate warnings */
3961UPDATE ##bou_BlitzCacheProcs
3962SET Warnings = SUBSTRING(
3963 CASE WHEN warning_no_join_predicate = 1 THEN ', No Join Predicate' ELSE '' END +
3964 CASE WHEN compile_timeout = 1 THEN ', Compilation Timeout' ELSE '' END +
3965 CASE WHEN compile_memory_limit_exceeded = 1 THEN ', Compile Memory Limit Exceeded' ELSE '' END +
3966 CASE WHEN busy_loops = 1 THEN ', Busy Loops' ELSE '' END +
3967 CASE WHEN is_forced_plan = 1 THEN ', Forced Plan' ELSE '' END +
3968 CASE WHEN is_forced_parameterized = 1 THEN ', Forced Parameterization' ELSE '' END +
3969 CASE WHEN unparameterized_query = 1 THEN ', Unparameterized Query' ELSE '' END +
3970 CASE WHEN missing_index_count > 0 THEN ', Missing Indexes (' + CAST(missing_index_count AS VARCHAR(3)) + ')' ELSE '' END +
3971 CASE WHEN unmatched_index_count > 0 THEN ', Unmatched Indexes (' + CAST(unmatched_index_count AS VARCHAR(3)) + ')' ELSE '' END +
3972 CASE WHEN is_cursor = 1 THEN ', Cursor'
3973 + CASE WHEN is_optimistic_cursor = 1 THEN '; optimistic' ELSE '' END
3974 + CASE WHEN is_forward_only_cursor = 0 THEN '; not forward only' ELSE '' END
3975 + CASE WHEN is_cursor_dynamic = 1 THEN '; dynamic' ELSE '' END
3976 + CASE WHEN is_fast_forward_cursor = 1 THEN '; fast forward' ELSE '' END
3977 ELSE '' END +
3978 CASE WHEN is_parallel = 1 THEN ', Parallel' ELSE '' END +
3979 CASE WHEN near_parallel = 1 THEN ', Nearly Parallel' ELSE '' END +
3980 CASE WHEN frequent_execution = 1 THEN ', Frequent Execution' ELSE '' END +
3981 CASE WHEN plan_warnings = 1 THEN ', Plan Warnings' ELSE '' END +
3982 CASE WHEN parameter_sniffing = 1 THEN ', Parameter Sniffing' ELSE '' END +
3983 CASE WHEN long_running = 1 THEN ', Long Running Query' ELSE '' END +
3984 CASE WHEN downlevel_estimator = 1 THEN ', Downlevel CE' ELSE '' END +
3985 CASE WHEN implicit_conversions = 1 THEN ', Implicit Conversions' ELSE '' END +
3986 CASE WHEN tvf_join = 1 THEN ', Function Join' ELSE '' END +
3987 CASE WHEN plan_multiple_plans = 1 THEN ', Multiple Plans' ELSE '' END +
3988 CASE WHEN is_trivial = 1 THEN ', Trivial Plans' ELSE '' END +
3989 CASE WHEN is_forced_serial = 1 THEN ', Forced Serialization' ELSE '' END +
3990 CASE WHEN is_key_lookup_expensive = 1 THEN ', Expensive Key Lookup' ELSE '' END +
3991 CASE WHEN is_remote_query_expensive = 1 THEN ', Expensive Remote Query' ELSE '' END +
3992 CASE WHEN trace_flags_session IS NOT NULL THEN ', Session Level Trace Flag(s) Enabled: ' + trace_flags_session ELSE '' END +
3993 CASE WHEN is_unused_grant = 1 THEN ', Unused Memory Grant' ELSE '' END +
3994 CASE WHEN function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), function_count) + ' function(s)' ELSE '' END +
3995 CASE WHEN clr_function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), clr_function_count) + ' CLR function(s)' ELSE '' END +
3996 CASE WHEN PlanCreationTimeHours <= 4 THEN ', Plan created last 4hrs' ELSE '' END +
3997 CASE WHEN is_table_variable = 1 THEN ', Table Variables' ELSE '' END +
3998 CASE WHEN no_stats_warning = 1 THEN ', Columns With No Statistics' ELSE '' END +
3999 CASE WHEN relop_warnings = 1 THEN ', Operator Warnings' ELSE '' END +
4000 CASE WHEN is_table_scan = 1 THEN ', Table Scans' ELSE '' END +
4001 CASE WHEN backwards_scan = 1 THEN ', Backwards Scans' ELSE '' END +
4002 CASE WHEN forced_index = 1 THEN ', Forced Indexes' ELSE '' END +
4003 CASE WHEN forced_seek = 1 THEN ', Forced Seeks' ELSE '' END +
4004 CASE WHEN forced_scan = 1 THEN ', Forced Scans' ELSE '' END +
4005 CASE WHEN columnstore_row_mode = 1 THEN ', ColumnStore Row Mode ' ELSE '' END +
4006 CASE WHEN is_computed_scalar = 1 THEN ', Computed Column UDF ' ELSE '' END +
4007 CASE WHEN is_sort_expensive = 1 THEN ', Expensive Sort' ELSE '' END +
4008 CASE WHEN is_computed_filter = 1 THEN ', Filter UDF' ELSE '' END +
4009 CASE WHEN index_ops >= 5 THEN ', >= 5 Indexes Modified' ELSE '' END +
4010 CASE WHEN is_row_level = 1 THEN ', Row Level Security' ELSE '' END +
4011 CASE WHEN is_spatial = 1 THEN ', Spatial Index' ELSE '' END +
4012 CASE WHEN index_dml = 1 THEN ', Index DML' ELSE '' END +
4013 CASE WHEN table_dml = 1 THEN ', Table DML' ELSE '' END +
4014 CASE WHEN low_cost_high_cpu = 1 THEN ', Low Cost High CPU' ELSE '' END +
4015 CASE WHEN long_running_low_cpu = 1 THEN + ', Long Running With Low CPU' ELSE '' END +
4016 CASE WHEN stale_stats = 1 THEN + ', Statistics used have > 100k modifications in the last 7 days' ELSE '' END +
4017 CASE WHEN is_adaptive = 1 THEN + ', Adaptive Joins' ELSE '' END +
4018 CASE WHEN is_spool_expensive = 1 THEN + ', Expensive Index Spool' ELSE '' END +
4019 CASE WHEN is_spool_more_rows = 1 THEN + ', Large Index Row Spool' ELSE '' END +
4020 CASE WHEN is_bad_estimate = 1 THEN + ', Row estimate mismatch' ELSE '' END +
4021 CASE WHEN is_paul_white_electric = 1 THEN ', SWITCH!' ELSE '' END +
4022 CASE WHEN is_row_goal = 1 THEN ', Row Goals' ELSE '' END +
4023 CASE WHEN is_big_spills = 1 THEN ', >500mb spills' ELSE '' END +
4024 CASE WHEN is_mstvf = 1 THEN ', MSTVFs' ELSE '' END +
4025 CASE WHEN is_mm_join = 1 THEN ', Many to Many Merge' ELSE '' END +
4026 CASE WHEN is_nonsargable = 1 THEN ', non-SARGables' ELSE '' END
4027 , 2, 200000)
4028WHERE SPID = @@SPID
4029OPTION (RECOMPILE);
4030
4031
4032RAISERROR('Populating Warnings column for stored procedures', 0, 1) WITH NOWAIT;
4033WITH statement_warnings AS
4034 (
4035SELECT DISTINCT
4036 SqlHandle,
4037 Warnings = SUBSTRING(
4038 CASE WHEN warning_no_join_predicate = 1 THEN ', No Join Predicate' ELSE '' END +
4039 CASE WHEN compile_timeout = 1 THEN ', Compilation Timeout' ELSE '' END +
4040 CASE WHEN compile_memory_limit_exceeded = 1 THEN ', Compile Memory Limit Exceeded' ELSE '' END +
4041 CASE WHEN busy_loops = 1 THEN ', Busy Loops' ELSE '' END +
4042 CASE WHEN is_forced_plan = 1 THEN ', Forced Plan' ELSE '' END +
4043 CASE WHEN is_forced_parameterized = 1 THEN ', Forced Parameterization' ELSE '' END +
4044 --CASE WHEN unparameterized_query = 1 THEN ', Unparameterized Query' ELSE '' END +
4045 CASE WHEN missing_index_count > 0 THEN ', Missing Indexes (' + CONVERT(VARCHAR(10), (SELECT SUM(b2.missing_index_count) FROM ##bou_BlitzCacheProcs AS b2 WHERE b2.SqlHandle = b.SqlHandle AND b2.QueryHash IS NOT NULL) ) + ')' ELSE '' END +
4046 CASE WHEN unmatched_index_count > 0 THEN ', Unmatched Indexes (' + CONVERT(VARCHAR(10), (SELECT SUM(b2.unmatched_index_count) FROM ##bou_BlitzCacheProcs AS b2 WHERE b2.SqlHandle = b.SqlHandle AND b2.QueryHash IS NOT NULL) ) + ')' ELSE '' END +
4047 CASE WHEN is_cursor = 1 THEN ', Cursor'
4048 + CASE WHEN is_optimistic_cursor = 1 THEN '; optimistic' ELSE '' END
4049 + CASE WHEN is_forward_only_cursor = 0 THEN '; not forward only' ELSE '' END
4050 + CASE WHEN is_cursor_dynamic = 1 THEN '; dynamic' ELSE '' END
4051 + CASE WHEN is_fast_forward_cursor = 1 THEN '; fast forward' ELSE '' END
4052 ELSE '' END +
4053 CASE WHEN is_parallel = 1 THEN ', Parallel' ELSE '' END +
4054 CASE WHEN near_parallel = 1 THEN ', Nearly Parallel' ELSE '' END +
4055 CASE WHEN frequent_execution = 1 THEN ', Frequent Execution' ELSE '' END +
4056 CASE WHEN plan_warnings = 1 THEN ', Plan Warnings' ELSE '' END +
4057 CASE WHEN parameter_sniffing = 1 THEN ', Parameter Sniffing' ELSE '' END +
4058 CASE WHEN long_running = 1 THEN ', Long Running Query' ELSE '' END +
4059 CASE WHEN downlevel_estimator = 1 THEN ', Downlevel CE' ELSE '' END +
4060 CASE WHEN implicit_conversions = 1 THEN ', Implicit Conversions' ELSE '' END +
4061 CASE WHEN tvf_join = 1 THEN ', Function Join' ELSE '' END +
4062 CASE WHEN plan_multiple_plans = 1 THEN ', Multiple Plans' ELSE '' END +
4063 CASE WHEN is_trivial = 1 THEN ', Trivial Plans' ELSE '' END +
4064 CASE WHEN is_forced_serial = 1 THEN ', Forced Serialization' ELSE '' END +
4065 CASE WHEN is_key_lookup_expensive = 1 THEN ', Expensive Key Lookup' ELSE '' END +
4066 CASE WHEN is_remote_query_expensive = 1 THEN ', Expensive Remote Query' ELSE '' END +
4067 CASE WHEN trace_flags_session IS NOT NULL THEN ', Session Level Trace Flag(s) Enabled: ' + trace_flags_session ELSE '' END +
4068 CASE WHEN is_unused_grant = 1 THEN ', Unused Memory Grant' ELSE '' END +
4069 CASE WHEN function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), (SELECT SUM(b2.function_count) FROM ##bou_BlitzCacheProcs AS b2 WHERE b2.SqlHandle = b.SqlHandle AND b2.QueryHash IS NOT NULL) ) + ' function(s)' ELSE '' END +
4070 CASE WHEN clr_function_count > 0 THEN ', Calls ' + CONVERT(VARCHAR(10), (SELECT SUM(b2.clr_function_count) FROM ##bou_BlitzCacheProcs AS b2 WHERE b2.SqlHandle = b.SqlHandle AND b2.QueryHash IS NOT NULL) ) + ' CLR function(s)' ELSE '' END +
4071 CASE WHEN PlanCreationTimeHours <= 4 THEN ', Plan created last 4hrs' ELSE '' END +
4072 CASE WHEN is_table_variable = 1 THEN ', Table Variables' ELSE '' END +
4073 CASE WHEN no_stats_warning = 1 THEN ', Columns With No Statistics' ELSE '' END +
4074 CASE WHEN relop_warnings = 1 THEN ', Operator Warnings' ELSE '' END +
4075 CASE WHEN is_table_scan = 1 THEN ', Table Scans' ELSE '' END +
4076 CASE WHEN backwards_scan = 1 THEN ', Backwards Scans' ELSE '' END +
4077 CASE WHEN forced_index = 1 THEN ', Forced Indexes' ELSE '' END +
4078 CASE WHEN forced_seek = 1 THEN ', Forced Seeks' ELSE '' END +
4079 CASE WHEN forced_scan = 1 THEN ', Forced Scans' ELSE '' END +
4080 CASE WHEN columnstore_row_mode = 1 THEN ', ColumnStore Row Mode ' ELSE '' END +
4081 CASE WHEN is_computed_scalar = 1 THEN ', Computed Column UDF ' ELSE '' END +
4082 CASE WHEN is_sort_expensive = 1 THEN ', Expensive Sort' ELSE '' END +
4083 CASE WHEN is_computed_filter = 1 THEN ', Filter UDF' ELSE '' END +
4084 CASE WHEN index_ops >= 5 THEN ', >= 5 Indexes Modified' ELSE '' END +
4085 CASE WHEN is_row_level = 1 THEN ', Row Level Security' ELSE '' END +
4086 CASE WHEN is_spatial = 1 THEN ', Spatial Index' ELSE '' END +
4087 CASE WHEN index_dml = 1 THEN ', Index DML' ELSE '' END +
4088 CASE WHEN table_dml = 1 THEN ', Table DML' ELSE '' END +
4089 CASE WHEN low_cost_high_cpu = 1 THEN ', Low Cost High CPU' ELSE '' END +
4090 CASE WHEN long_running_low_cpu = 1 THEN + ', Long Running With Low CPU' ELSE '' END +
4091 CASE WHEN stale_stats = 1 THEN + ', Statistics used have > 100k modifications in the last 7 days' ELSE '' END +
4092 CASE WHEN is_adaptive = 1 THEN + ', Adaptive Joins' ELSE '' END +
4093 CASE WHEN is_spool_expensive = 1 THEN + ', Expensive Index Spool' ELSE '' END +
4094 CASE WHEN is_spool_more_rows = 1 THEN + ', Large Index Row Spool' ELSE '' END +
4095 CASE WHEN is_bad_estimate = 1 THEN + ', Row estimate mismatch' ELSE '' END +
4096 CASE WHEN is_paul_white_electric = 1 THEN ', SWITCH!' ELSE '' END +
4097 CASE WHEN is_row_goal = 1 THEN ', Row Goals' ELSE '' END +
4098 CASE WHEN is_big_spills = 1 THEN ', >500mb spills' ELSE '' END +
4099 CASE WHEN is_mstvf = 1 THEN ', MSTVFs' ELSE '' END +
4100 CASE WHEN is_mm_join = 1 THEN ', Many to Many Merge' ELSE '' END +
4101 CASE WHEN is_nonsargable = 1 THEN ', non-SARGables' ELSE '' END
4102 , 2, 200000)
4103FROM ##bou_BlitzCacheProcs b
4104WHERE SPID = @@SPID
4105AND QueryType LIKE 'Statement (parent%'
4106 )
4107UPDATE b
4108SET b.Warnings = s.Warnings
4109FROM ##bou_BlitzCacheProcs AS b
4110JOIN statement_warnings s
4111ON b.SqlHandle = s.SqlHandle
4112WHERE QueryType LIKE 'Procedure or Function%'
4113AND SPID = @@SPID
4114OPTION (RECOMPILE);
4115
4116RAISERROR('Checking for plans with >128 levels of nesting', 0, 1) WITH NOWAIT;
4117WITH plan_handle AS (
4118SELECT b.PlanHandle
4119FROM ##bou_BlitzCacheProcs b
4120 CROSS APPLY sys.dm_exec_text_query_plan(b.PlanHandle, 0, -1) tqp
4121 CROSS APPLY sys.dm_exec_query_plan(b.PlanHandle) qp
4122 WHERE tqp.encrypted = 0
4123 AND b.SPID = @@SPID
4124 AND (qp.query_plan IS NULL
4125 AND tqp.query_plan IS NOT NULL)
4126)
4127UPDATE b
4128SET Warnings = ISNULL('Your query plan is >128 levels of nested nodes, and can''t be converted to XML. Use SELECT * FROM sys.dm_exec_text_query_plan('+ CONVERT(VARCHAR(128), ph.PlanHandle, 1) + ', 0, -1) to get more information'
4129 , 'We couldn''t find a plan for this query. Possible reasons for this include dynamic SQL, RECOMPILE hints, and encrypted code.')
4130FROM ##bou_BlitzCacheProcs b
4131LEFT JOIN plan_handle ph ON
4132b.PlanHandle = ph.PlanHandle
4133WHERE b.QueryPlan IS NULL
4134AND b.SPID = @@SPID
4135OPTION (RECOMPILE);
4136
4137RAISERROR('Checking for plans with no warnings', 0, 1) WITH NOWAIT;
4138UPDATE ##bou_BlitzCacheProcs
4139SET Warnings = 'No warnings detected. ' + CASE @ExpertMode
4140 WHEN 0
4141 THEN ' Try running sp_BlitzCache with @ExpertMode = 1 to find more advanced problems.'
4142 ELSE ''
4143 END
4144WHERE Warnings = '' OR Warnings IS NULL
4145AND SPID = @@SPID
4146OPTION (RECOMPILE);
4147
4148
4149Results:
4150IF @OutputDatabaseName IS NOT NULL
4151 AND @OutputSchemaName IS NOT NULL
4152 AND @OutputTableName IS NOT NULL
4153BEGIN
4154 RAISERROR('Writing results to table.', 0, 1) WITH NOWAIT;
4155
4156 /* send results to a table */
4157 DECLARE @insert_sql NVARCHAR(MAX) = N'' ;
4158
4159 SET @insert_sql = 'USE '
4160 + @OutputDatabaseName
4161 + '; IF EXISTS(SELECT * FROM '
4162 + @OutputDatabaseName
4163 + '.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
4164 + @OutputSchemaName
4165 + ''') AND NOT EXISTS (SELECT * FROM '
4166 + @OutputDatabaseName
4167 + '.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = '''
4168 + @OutputSchemaName + ''' AND QUOTENAME(TABLE_NAME) = '''
4169 + @OutputTableName + ''') CREATE TABLE '
4170 + @OutputSchemaName + '.'
4171 + @OutputTableName
4172 + N'(ID bigint NOT NULL IDENTITY(1,1),
4173 ServerName NVARCHAR(258),
4174 CheckDate DATETIMEOFFSET,
4175 Version NVARCHAR(258),
4176 QueryType NVARCHAR(258),
4177 Warnings varchar(max),
4178 DatabaseName sysname,
4179 SerialDesiredMemory float,
4180 SerialRequiredMemory float,
4181 AverageCPU bigint,
4182 TotalCPU bigint,
4183 PercentCPUByType money,
4184 CPUWeight money,
4185 AverageDuration bigint,
4186 TotalDuration bigint,
4187 DurationWeight money,
4188 PercentDurationByType money,
4189 AverageReads bigint,
4190 TotalReads bigint,
4191 ReadWeight money,
4192 PercentReadsByType money,
4193 AverageWrites bigint,
4194 TotalWrites bigint,
4195 WriteWeight money,
4196 PercentWritesByType money,
4197 ExecutionCount bigint,
4198 ExecutionWeight money,
4199 PercentExecutionsByType money,' + N'
4200 ExecutionsPerMinute money,
4201 PlanCreationTime datetime,
4202 PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME()),
4203 LastExecutionTime datetime,
4204 PlanHandle varbinary(64),
4205 [Remove Plan Handle From Cache] AS
4206 CASE WHEN [PlanHandle] IS NOT NULL
4207 THEN ''DBCC FREEPROCCACHE ('' + CONVERT(VARCHAR(128), [PlanHandle], 1) + '');''
4208 ELSE ''N/A'' END,
4209 SqlHandle varbinary(64),
4210 [Remove SQL Handle From Cache] AS
4211 CASE WHEN [SqlHandle] IS NOT NULL
4212 THEN ''DBCC FREEPROCCACHE ('' + CONVERT(VARCHAR(128), [SqlHandle], 1) + '');''
4213 ELSE ''N/A'' END,
4214 [SQL Handle More Info] AS
4215 CASE WHEN [SqlHandle] IS NOT NULL
4216 THEN ''EXEC sp_BlitzCache @OnlySqlHandles = '''''' + CONVERT(VARCHAR(128), [SqlHandle], 1) + ''''''; ''
4217 ELSE ''N/A'' END,
4218 QueryHash binary(8),
4219 [Query Hash More Info] AS
4220 CASE WHEN [QueryHash] IS NOT NULL
4221 THEN ''EXEC sp_BlitzCache @OnlyQueryHashes = '''''' + CONVERT(VARCHAR(32), [QueryHash], 1) + ''''''; ''
4222 ELSE ''N/A'' END,
4223 QueryPlanHash binary(8),
4224 StatementStartOffset int,
4225 StatementEndOffset int,
4226 MinReturnedRows bigint,
4227 MaxReturnedRows bigint,
4228 AverageReturnedRows money,
4229 TotalReturnedRows bigint,
4230 QueryText nvarchar(max),
4231 QueryPlan xml,
4232 NumberOfPlans int,
4233 NumberOfDistinctPlans int,
4234 MinGrantKB BIGINT,
4235 MaxGrantKB BIGINT,
4236 MinUsedGrantKB BIGINT,
4237 MaxUsedGrantKB BIGINT,
4238 PercentMemoryGrantUsed MONEY,
4239 AvgMaxMemoryGrant MONEY,
4240 MinSpills BIGINT,
4241 MaxSpills BIGINT,
4242 TotalSpills BIGINT,
4243 AvgSpills MONEY,
4244 QueryPlanCost FLOAT,
4245 CONSTRAINT [PK_' +CAST(NEWID() AS NCHAR(36)) + '] PRIMARY KEY CLUSTERED(ID))';
4246
4247 IF @Debug = 1
4248 BEGIN
4249 PRINT SUBSTRING(@insert_sql, 0, 4000);
4250 PRINT SUBSTRING(@insert_sql, 4000, 8000);
4251 PRINT SUBSTRING(@insert_sql, 8000, 12000);
4252 PRINT SUBSTRING(@insert_sql, 12000, 16000);
4253 PRINT SUBSTRING(@insert_sql, 16000, 20000);
4254 PRINT SUBSTRING(@insert_sql, 20000, 24000);
4255 PRINT SUBSTRING(@insert_sql, 24000, 28000);
4256 PRINT SUBSTRING(@insert_sql, 28000, 32000);
4257 PRINT SUBSTRING(@insert_sql, 32000, 36000);
4258 PRINT SUBSTRING(@insert_sql, 36000, 40000);
4259 END;
4260
4261 EXEC sp_executesql @insert_sql ;
4262
4263 IF @CheckDateOverride IS NULL
4264 BEGIN
4265 SET @CheckDateOverride = SYSDATETIMEOFFSET();
4266 END;
4267
4268
4269 SET @insert_sql =N' IF EXISTS(SELECT * FROM '
4270 + @OutputDatabaseName
4271 + N'.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = '''
4272 + @OutputSchemaName + N''') '
4273 + 'INSERT '
4274 + @OutputDatabaseName + '.'
4275 + @OutputSchemaName + '.'
4276 + @OutputTableName
4277 + N' (ServerName, CheckDate, Version, QueryType, DatabaseName, AverageCPU, TotalCPU, PercentCPUByType, CPUWeight, AverageDuration, TotalDuration, DurationWeight, PercentDurationByType, AverageReads, TotalReads, ReadWeight, PercentReadsByType, '
4278 + N' AverageWrites, TotalWrites, WriteWeight, PercentWritesByType, ExecutionCount, ExecutionWeight, PercentExecutionsByType, '
4279 + N' ExecutionsPerMinute, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, QueryHash, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows, QueryText, QueryPlan, NumberOfPlans, NumberOfDistinctPlans, Warnings, '
4280 + N' SerialRequiredMemory, SerialDesiredMemory, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, QueryPlanCost ) '
4281 + N'SELECT TOP (@Top) '
4282 + QUOTENAME(CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)), N'''') + N', @CheckDateOverride, '
4283 + QUOTENAME(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), N'''') + ', '
4284 + N' QueryType, DatabaseName, AverageCPU, TotalCPU, PercentCPUByType, PercentCPU, AverageDuration, TotalDuration, PercentDuration, PercentDurationByType, AverageReads, TotalReads, PercentReads, PercentReadsByType, '
4285 + N' AverageWrites, TotalWrites, PercentWrites, PercentWritesByType, ExecutionCount, PercentExecutions, PercentExecutionsByType, '
4286 + N' ExecutionsPerMinute, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, QueryHash, StatementStartOffset, StatementEndOffset, MinReturnedRows, MaxReturnedRows, AverageReturnedRows, TotalReturnedRows, QueryText, QueryPlan, NumberOfPlans, NumberOfDistinctPlans, Warnings, '
4287 + N' SerialRequiredMemory, SerialDesiredMemory, MinGrantKB, MaxGrantKB, MinUsedGrantKB, MaxUsedGrantKB, PercentMemoryGrantUsed, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, QueryPlanCost '
4288 + N' FROM ##bou_BlitzCacheProcs '
4289 + N' WHERE 1=1 ';
4290
4291 IF @MinimumExecutionCount IS NOT NULL
4292 BEGIN
4293 SET @insert_sql += N' AND ExecutionCount >= @MinimumExecutionCount ';
4294 END;
4295
4296 IF @MinutesBack IS NOT NULL
4297 BEGIN
4298 SET @insert_sql += N' AND LastExecutionTime >= DATEADD(MINUTE, @min_back, GETDATE() ) ';
4299 END;
4300
4301 SET @insert_sql += N' AND SPID = @@SPID ';
4302
4303 SELECT @insert_sql += N' ORDER BY ' + CASE @SortOrder WHEN 'cpu' THEN N' TotalCPU '
4304 WHEN N'reads' THEN N' TotalReads '
4305 WHEN N'writes' THEN N' TotalWrites '
4306 WHEN N'duration' THEN N' TotalDuration '
4307 WHEN N'executions' THEN N' ExecutionCount '
4308 WHEN N'compiles' THEN N' PlanCreationTime '
4309 WHEN N'memory grant' THEN N' MaxGrantKB'
4310 WHEN N'spills' THEN N' MaxSpills'
4311 WHEN N'avg cpu' THEN N' AverageCPU'
4312 WHEN N'avg reads' THEN N' AverageReads'
4313 WHEN N'avg writes' THEN N' AverageWrites'
4314 WHEN N'avg duration' THEN N' AverageDuration'
4315 WHEN N'avg executions' THEN N' ExecutionsPerMinute'
4316 WHEN N'avg memory grant' THEN N' AvgMaxMemoryGrant'
4317 WHEN 'avg spills' THEN N' AvgSpills'
4318 END + N' DESC ';
4319
4320 SET @insert_sql += N' OPTION (RECOMPILE) ; ';
4321
4322 IF @Debug = 1
4323 BEGIN
4324 PRINT SUBSTRING(@insert_sql, 0, 4000);
4325 PRINT SUBSTRING(@insert_sql, 4000, 8000);
4326 PRINT SUBSTRING(@insert_sql, 8000, 12000);
4327 PRINT SUBSTRING(@insert_sql, 12000, 16000);
4328 PRINT SUBSTRING(@insert_sql, 16000, 20000);
4329 PRINT SUBSTRING(@insert_sql, 20000, 24000);
4330 PRINT SUBSTRING(@insert_sql, 24000, 28000);
4331 PRINT SUBSTRING(@insert_sql, 28000, 32000);
4332 PRINT SUBSTRING(@insert_sql, 32000, 36000);
4333 PRINT SUBSTRING(@insert_sql, 36000, 40000);
4334 END;
4335
4336 EXEC sp_executesql @insert_sql, N'@Top INT, @min_duration INT, @min_back INT, @CheckDateOverride DATETIMEOFFSET, @MinimumExecutionCount INT', @Top, @DurationFilter_i, @MinutesBack, @CheckDateOverride, @MinimumExecutionCount;
4337
4338 RETURN;
4339END;
4340ELSE IF @ExportToExcel = 1
4341BEGIN
4342 RAISERROR('Displaying results with Excel formatting (no plans).', 0, 1) WITH NOWAIT;
4343
4344 /* excel output */
4345 UPDATE ##bou_BlitzCacheProcs
4346 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),' ','<>'),'><',''),'<>',' '), 1, 32000)
4347 OPTION(RECOMPILE);
4348
4349 SET @sql = N'
4350 SELECT TOP (@Top)
4351 DatabaseName AS [Database Name],
4352 QueryPlanCost AS [Cost],
4353 QueryText,
4354 QueryType AS [Query Type],
4355 Warnings,
4356 ExecutionCount,
4357 ExecutionsPerMinute AS [Executions / Minute],
4358 PercentExecutions AS [Execution Weight],
4359 PercentExecutionsByType AS [% Executions (Type)],
4360 SerialDesiredMemory AS [Serial Desired Memory],
4361 SerialRequiredMemory AS [Serial Required Memory],
4362 TotalCPU AS [Total CPU (ms)],
4363 AverageCPU AS [Avg CPU (ms)],
4364 PercentCPU AS [CPU Weight],
4365 PercentCPUByType AS [% CPU (Type)],
4366 TotalDuration AS [Total Duration (ms)],
4367 AverageDuration AS [Avg Duration (ms)],
4368 PercentDuration AS [Duration Weight],
4369 PercentDurationByType AS [% Duration (Type)],
4370 TotalReads AS [Total Reads],
4371 AverageReads AS [Average Reads],
4372 PercentReads AS [Read Weight],
4373 PercentReadsByType AS [% Reads (Type)],
4374 TotalWrites AS [Total Writes],
4375 AverageWrites AS [Average Writes],
4376 PercentWrites AS [Write Weight],
4377 PercentWritesByType AS [% Writes (Type)],
4378 TotalReturnedRows,
4379 AverageReturnedRows,
4380 MinReturnedRows,
4381 MaxReturnedRows,
4382 MinGrantKB,
4383 MaxGrantKB,
4384 MinUsedGrantKB,
4385 MaxUsedGrantKB,
4386 PercentMemoryGrantUsed,
4387 AvgMaxMemoryGrant,
4388 MinSpills,
4389 MaxSpills,
4390 TotalSpills,
4391 AvgSpills,
4392 NumberOfPlans,
4393 NumberOfDistinctPlans,
4394 PlanCreationTime AS [Created At],
4395 LastExecutionTime AS [Last Execution],
4396 StatementStartOffset,
4397 StatementEndOffset,
4398 PlanHandle AS [Plan Handle],
4399 SqlHandle AS [SQL Handle],
4400 QueryHash,
4401 QueryPlanHash,
4402 COALESCE(SetOptions, '''') AS [SET Options]
4403 FROM ##bou_BlitzCacheProcs
4404 WHERE 1 = 1
4405 AND SPID = @@SPID ' + @nl;
4406
4407 IF @MinimumExecutionCount IS NOT NULL
4408 BEGIN
4409 SET @sql += N' AND ExecutionCount >= @minimumExecutionCount ';
4410 END;
4411
4412 IF @MinutesBack IS NOT NULL
4413 BEGIN
4414 SET @sql += N' AND LastExecutionTime >= DATEADD(MINUTE, @min_back, GETDATE() ) ';
4415 END;
4416
4417 SELECT @sql += N' ORDER BY ' + CASE @SortOrder WHEN N'cpu' THEN N' TotalCPU '
4418 WHEN N'reads' THEN N' TotalReads '
4419 WHEN N'writes' THEN N' TotalWrites '
4420 WHEN N'duration' THEN N' TotalDuration '
4421 WHEN N'executions' THEN N' ExecutionCount '
4422 WHEN N'compiles' THEN N' PlanCreationTime '
4423 WHEN N'memory grant' THEN N' MaxGrantKB'
4424 WHEN N'spills' THEN N' MaxSpills'
4425 WHEN N'avg cpu' THEN N' AverageCPU'
4426 WHEN N'avg reads' THEN N' AverageReads'
4427 WHEN N'avg writes' THEN N' AverageWrites'
4428 WHEN N'avg duration' THEN N' AverageDuration'
4429 WHEN N'avg executions' THEN N' ExecutionsPerMinute'
4430 WHEN N'avg memory grant' THEN N' AvgMaxMemoryGrant'
4431 WHEN N'avg spills' THEN N' AvgSpills'
4432 END + N' DESC ';
4433
4434 SET @sql += N' OPTION (RECOMPILE) ; ';
4435
4436 IF @Debug = 1
4437 BEGIN
4438 PRINT SUBSTRING(@sql, 0, 4000);
4439 PRINT SUBSTRING(@sql, 4000, 8000);
4440 PRINT SUBSTRING(@sql, 8000, 12000);
4441 PRINT SUBSTRING(@sql, 12000, 16000);
4442 PRINT SUBSTRING(@sql, 16000, 20000);
4443 PRINT SUBSTRING(@sql, 20000, 24000);
4444 PRINT SUBSTRING(@sql, 24000, 28000);
4445 PRINT SUBSTRING(@sql, 28000, 32000);
4446 PRINT SUBSTRING(@sql, 32000, 36000);
4447 PRINT SUBSTRING(@sql, 36000, 40000);
4448 END;
4449
4450 EXEC sp_executesql @sql, N'@Top INT, @min_duration INT, @min_back INT, @minimumExecutionCount INT', @Top, @DurationFilter_i, @MinutesBack, @MinimumExecutionCount;
4451END;
4452
4453
4454RAISERROR('Displaying analysis of plan cache.', 0, 1) WITH NOWAIT;
4455
4456DECLARE @columns NVARCHAR(MAX) = N'' ;
4457
4458IF @ExpertMode = 0
4459BEGIN
4460 RAISERROR(N'Returning ExpertMode = 0', 0, 1) WITH NOWAIT;
4461 SET @columns = N' DatabaseName AS [Database],
4462 QueryPlanCost AS [Cost],
4463 QueryText AS [Query Text],
4464 QueryType AS [Query Type],
4465 Warnings AS [Warnings],
4466 QueryPlan AS [Query Plan],
4467 missing_indexes AS [Missing Indexes],
4468 implicit_conversion_info AS [Implicit Conversion Info],
4469 cached_execution_parameters AS [Cached Execution Parameters],
4470 ExecutionCount AS [# Executions],
4471 ExecutionsPerMinute AS [Executions / Minute],
4472 PercentExecutions AS [Execution Weight],
4473 TotalCPU AS [Total CPU (ms)],
4474 AverageCPU AS [Avg CPU (ms)],
4475 PercentCPU AS [CPU Weight],
4476 TotalDuration AS [Total Duration (ms)],
4477 AverageDuration AS [Avg Duration (ms)],
4478 PercentDuration AS [Duration Weight],
4479 TotalReads AS [Total Reads],
4480 AverageReads AS [Avg Reads],
4481 PercentReads AS [Read Weight],
4482 TotalWrites AS [Total Writes],
4483 AverageWrites AS [Avg Writes],
4484 PercentWrites AS [Write Weight],
4485 AverageReturnedRows AS [Average Rows],
4486 MinGrantKB AS [Minimum Memory Grant KB],
4487 MaxGrantKB AS [Maximum Memory Grant KB],
4488 MinUsedGrantKB AS [Minimum Used Grant KB],
4489 MaxUsedGrantKB AS [Maximum Used Grant KB],
4490 AvgMaxMemoryGrant AS [Average Max Memory Grant],
4491 MinSpills AS [Min Spills],
4492 MaxSpills AS [Max Spills],
4493 TotalSpills AS [Total Spills],
4494 AvgSpills AS [Avg Spills],
4495 PlanCreationTime AS [Created At],
4496 LastExecutionTime AS [Last Execution],
4497 PlanHandle AS [Plan Handle],
4498 SqlHandle AS [SQL Handle],
4499 COALESCE(SetOptions, '''') AS [SET Options] ';
4500END;
4501ELSE
4502BEGIN
4503 SET @columns = N' DatabaseName AS [Database],
4504 QueryPlanCost AS [Cost],
4505 QueryText AS [Query Text],
4506 QueryType AS [Query Type],
4507 Warnings AS [Warnings],
4508 QueryPlan AS [Query Plan],
4509 missing_indexes AS [Missing Indexes],
4510 implicit_conversion_info AS [Implicit Conversion Info],
4511 cached_execution_parameters AS [Cached Execution Parameters], ' + @nl;
4512
4513 IF @ExpertMode = 2 /* Opserver */
4514 BEGIN
4515 RAISERROR(N'Returning Expert Mode = 2', 0, 1) WITH NOWAIT;
4516 SET @columns += N'
4517 SUBSTRING(
4518 CASE WHEN warning_no_join_predicate = 1 THEN '', 20'' ELSE '''' END +
4519 CASE WHEN compile_timeout = 1 THEN '', 18'' ELSE '''' END +
4520 CASE WHEN compile_memory_limit_exceeded = 1 THEN '', 19'' ELSE '''' END +
4521 CASE WHEN busy_loops = 1 THEN '', 16'' ELSE '''' END +
4522 CASE WHEN is_forced_plan = 1 THEN '', 3'' ELSE '''' END +
4523 CASE WHEN is_forced_parameterized > 0 THEN '', 5'' ELSE '''' END +
4524 CASE WHEN unparameterized_query = 1 THEN '', 23'' ELSE '''' END +
4525 CASE WHEN missing_index_count > 0 THEN '', 10'' ELSE '''' END +
4526 CASE WHEN unmatched_index_count > 0 THEN '', 22'' ELSE '''' END +
4527 CASE WHEN is_cursor = 1 THEN '', 4'' ELSE '''' END +
4528 CASE WHEN is_parallel = 1 THEN '', 6'' ELSE '''' END +
4529 CASE WHEN near_parallel = 1 THEN '', 7'' ELSE '''' END +
4530 CASE WHEN frequent_execution = 1 THEN '', 1'' ELSE '''' END +
4531 CASE WHEN plan_warnings = 1 THEN '', 8'' ELSE '''' END +
4532 CASE WHEN parameter_sniffing = 1 THEN '', 2'' ELSE '''' END +
4533 CASE WHEN long_running = 1 THEN '', 9'' ELSE '''' END +
4534 CASE WHEN downlevel_estimator = 1 THEN '', 13'' ELSE '''' END +
4535 CASE WHEN implicit_conversions = 1 THEN '', 14'' ELSE '''' END +
4536 CASE WHEN tvf_join = 1 THEN '', 17'' ELSE '''' END +
4537 CASE WHEN plan_multiple_plans = 1 THEN '', 21'' ELSE '''' END +
4538 CASE WHEN unmatched_index_count > 0 THEN '', 22'' ELSE '''' END +
4539 CASE WHEN is_trivial = 1 THEN '', 24'' ELSE '''' END +
4540 CASE WHEN is_forced_serial = 1 THEN '', 25'' ELSE '''' END +
4541 CASE WHEN is_key_lookup_expensive = 1 THEN '', 26'' ELSE '''' END +
4542 CASE WHEN is_remote_query_expensive = 1 THEN '', 28'' ELSE '''' END +
4543 CASE WHEN trace_flags_session IS NOT NULL THEN '', 29'' ELSE '''' END +
4544 CASE WHEN is_unused_grant = 1 THEN '', 30'' ELSE '''' END +
4545 CASE WHEN function_count > 0 THEN '', 31'' ELSE '''' END +
4546 CASE WHEN clr_function_count > 0 THEN '', 32'' ELSE '''' END +
4547 CASE WHEN PlanCreationTimeHours <= 4 THEN '', 33'' ELSE '''' END +
4548 CASE WHEN is_table_variable = 1 THEN '', 34'' ELSE '''' END +
4549 CASE WHEN no_stats_warning = 1 THEN '', 35'' ELSE '''' END +
4550 CASE WHEN relop_warnings = 1 THEN '', 36'' ELSE '''' END +
4551 CASE WHEN is_table_scan = 1 THEN '', 37'' ELSE '''' END +
4552 CASE WHEN backwards_scan = 1 THEN '', 38'' ELSE '''' END +
4553 CASE WHEN forced_index = 1 THEN '', 39'' ELSE '''' END +
4554 CASE WHEN forced_seek = 1 OR forced_scan = 1 THEN '', 40'' ELSE '''' END +
4555 CASE WHEN columnstore_row_mode = 1 THEN '', 41'' ELSE '''' END +
4556 CASE WHEN is_computed_scalar = 1 THEN '', 42'' ELSE '''' END +
4557 CASE WHEN is_sort_expensive = 1 THEN '', 43'' ELSE '''' END +
4558 CASE WHEN is_computed_filter = 1 THEN '', 44'' ELSE '''' END +
4559 CASE WHEN index_ops >= 5 THEN '', 45'' ELSE '''' END +
4560 CASE WHEN is_row_level = 1 THEN '', 46'' ELSE '''' END +
4561 CASE WHEN is_spatial = 1 THEN '', 47'' ELSE '''' END +
4562 CASE WHEN index_dml = 1 THEN '', 48'' ELSE '''' END +
4563 CASE WHEN table_dml = 1 THEN '', 49'' ELSE '''' END +
4564 CASE WHEN long_running_low_cpu = 1 THEN '', 50'' ELSE '''' END +
4565 CASE WHEN low_cost_high_cpu = 1 THEN '', 51'' ELSE '''' END +
4566 CASE WHEN stale_stats = 1 THEN '', 52'' ELSE '''' END +
4567 CASE WHEN is_adaptive = 1 THEN '', 53'' ELSE '''' END +
4568 CASE WHEN is_spool_expensive = 1 THEN + '', 54'' ELSE '''' END +
4569 CASE WHEN is_spool_more_rows = 1 THEN + '', 55'' ELSE '''' END +
4570 CASE WHEN is_bad_estimate = 1 THEN + '', 56'' ELSE '''' END +
4571 CASE WHEN is_paul_white_electric = 1 THEN '', 57'' ELSE '''' END +
4572 CASE WHEN is_row_goal = 1 THEN '', 58'' ELSE '''' END +
4573 CASE WHEN is_big_spills = 1 THEN '', 59'' ELSE '''' END +
4574 CASE WHEN is_mstvf = 1 THEN '', 60'' ELSE '''' END +
4575 CASE WHEN is_mm_join = 1 THEN '', 61'' ELSE '''' END +
4576 CASE WHEN is_nonsargable = 1 THEN '', 62'' ELSE '''' END
4577 , 2, 200000) AS opserver_warning , ' + @nl ;
4578 END;
4579
4580 SET @columns += N' ExecutionCount AS [# Executions],
4581 ExecutionsPerMinute AS [Executions / Minute],
4582 PercentExecutions AS [Execution Weight],
4583 SerialDesiredMemory AS [Serial Desired Memory],
4584 SerialRequiredMemory AS [Serial Required Memory],
4585 TotalCPU AS [Total CPU (ms)],
4586 AverageCPU AS [Avg CPU (ms)],
4587 PercentCPU AS [CPU Weight],
4588 TotalDuration AS [Total Duration (ms)],
4589 AverageDuration AS [Avg Duration (ms)],
4590 PercentDuration AS [Duration Weight],
4591 TotalReads AS [Total Reads],
4592 AverageReads AS [Average Reads],
4593 PercentReads AS [Read Weight],
4594 TotalWrites AS [Total Writes],
4595 AverageWrites AS [Average Writes],
4596 PercentWrites AS [Write Weight],
4597 PercentExecutionsByType AS [% Executions (Type)],
4598 PercentCPUByType AS [% CPU (Type)],
4599 PercentDurationByType AS [% Duration (Type)],
4600 PercentReadsByType AS [% Reads (Type)],
4601 PercentWritesByType AS [% Writes (Type)],
4602 TotalReturnedRows AS [Total Rows],
4603 AverageReturnedRows AS [Avg Rows],
4604 MinReturnedRows AS [Min Rows],
4605 MaxReturnedRows AS [Max Rows],
4606 MinGrantKB AS [Minimum Memory Grant KB],
4607 MaxGrantKB AS [Maximum Memory Grant KB],
4608 MinUsedGrantKB AS [Minimum Used Grant KB],
4609 MaxUsedGrantKB AS [Maximum Used Grant KB],
4610 AvgMaxMemoryGrant AS [Average Max Memory Grant],
4611 MinSpills AS [Min Spills],
4612 MaxSpills AS [Max Spills],
4613 TotalSpills AS [Total Spills],
4614 AvgSpills AS [Avg Spills],
4615 NumberOfPlans AS [# Plans],
4616 NumberOfDistinctPlans AS [# Distinct Plans],
4617 PlanCreationTime AS [Created At],
4618 LastExecutionTime AS [Last Execution],
4619 CachedPlanSize AS [Cached Plan Size (KB)],
4620 CompileTime AS [Compile Time (ms)],
4621 CompileCPU AS [Compile CPU (ms)],
4622 CompileMemory AS [Compile memory (KB)],
4623 COALESCE(SetOptions, '''') AS [SET Options],
4624 PlanHandle AS [Plan Handle],
4625 SqlHandle AS [SQL Handle],
4626 [SQL Handle More Info],
4627 QueryHash AS [Query Hash],
4628 [Query Hash More Info],
4629 QueryPlanHash AS [Query Plan Hash],
4630 StatementStartOffset,
4631 StatementEndOffset,
4632 [Remove Plan Handle From Cache],
4633 [Remove SQL Handle From Cache]';
4634END;
4635
4636
4637
4638SET @sql = N'
4639SELECT TOP (@Top) ' + @columns + @nl + N'
4640FROM ##bou_BlitzCacheProcs
4641WHERE SPID = @spid ' + @nl;
4642
4643IF @MinimumExecutionCount IS NOT NULL
4644 BEGIN
4645 SET @sql += N' AND ExecutionCount >= @minimumExecutionCount ' + @nl;
4646 END;
4647
4648IF @MinutesBack IS NOT NULL
4649 BEGIN
4650 SET @sql += N' AND LastExecutionTime >= DATEADD(MINUTE, @min_back, GETDATE() ) ' + @nl;
4651 END;
4652
4653SELECT @sql += N' ORDER BY ' + CASE @SortOrder WHEN N'cpu' THEN N' TotalCPU '
4654 WHEN N'reads' THEN N' TotalReads '
4655 WHEN N'writes' THEN N' TotalWrites '
4656 WHEN N'duration' THEN N' TotalDuration '
4657 WHEN N'executions' THEN N' ExecutionCount '
4658 WHEN N'compiles' THEN N' PlanCreationTime '
4659 WHEN N'memory grant' THEN N' MaxGrantKB'
4660 WHEN N'spills' THEN N' MaxSpills'
4661 WHEN N'avg cpu' THEN N' AverageCPU'
4662 WHEN N'avg reads' THEN N' AverageReads'
4663 WHEN N'avg writes' THEN N' AverageWrites'
4664 WHEN N'avg duration' THEN N' AverageDuration'
4665 WHEN N'avg executions' THEN N' ExecutionsPerMinute'
4666 WHEN N'avg memory grant' THEN N' AvgMaxMemoryGrant'
4667 WHEN N'avg spills' THEN N' AvgSpills'
4668 END + N' DESC ';
4669SET @sql += N' OPTION (RECOMPILE) ; ';
4670
4671IF @Debug = 1
4672 BEGIN
4673 PRINT SUBSTRING(@sql, 0, 4000);
4674 PRINT SUBSTRING(@sql, 4000, 8000);
4675 PRINT SUBSTRING(@sql, 8000, 12000);
4676 PRINT SUBSTRING(@sql, 12000, 16000);
4677 PRINT SUBSTRING(@sql, 16000, 20000);
4678 PRINT SUBSTRING(@sql, 20000, 24000);
4679 PRINT SUBSTRING(@sql, 24000, 28000);
4680 PRINT SUBSTRING(@sql, 28000, 32000);
4681 PRINT SUBSTRING(@sql, 32000, 36000);
4682 PRINT SUBSTRING(@sql, 36000, 40000);
4683 END;
4684
4685EXEC sp_executesql @sql, N'@Top INT, @spid INT, @minimumExecutionCount INT, @min_back INT', @Top, @@SPID, @MinimumExecutionCount, @MinutesBack;
4686
4687IF @HideSummary = 0 AND @ExportToExcel = 0
4688BEGIN
4689 IF @Reanalyze = 0
4690 BEGIN
4691 RAISERROR('Building query plan summary data.', 0, 1) WITH NOWAIT;
4692
4693 /* Build summary data */
4694 IF EXISTS (SELECT 1/0
4695 FROM ##bou_BlitzCacheProcs
4696 WHERE frequent_execution = 1
4697 AND SPID = @@SPID)
4698 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4699 VALUES (@@SPID,
4700 1,
4701 100,
4702 'Execution Pattern',
4703 'Frequently Executed Queries',
4704 'http://brentozar.com/blitzcache/frequently-executed-queries/',
4705 'Queries are being executed more than '
4706 + CAST (@execution_threshold AS VARCHAR(5))
4707 + ' times per minute. This can put additional load on the server, even when queries are lightweight.') ;
4708
4709 IF EXISTS (SELECT 1/0
4710 FROM ##bou_BlitzCacheProcs
4711 WHERE parameter_sniffing = 1
4712 AND SPID = @@SPID)
4713 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4714 VALUES (@@SPID,
4715 2,
4716 50,
4717 'Parameterization',
4718 'Parameter Sniffing',
4719 'http://brentozar.com/blitzcache/parameter-sniffing/',
4720 'There are signs of parameter sniffing (wide variance in rows return or time to execute). Investigate query patterns and tune code appropriately.') ;
4721
4722 /* Forced execution plans */
4723 IF EXISTS (SELECT 1/0
4724 FROM ##bou_BlitzCacheProcs
4725 WHERE is_forced_plan = 1
4726 AND SPID = @@SPID)
4727 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4728 VALUES (@@SPID,
4729 3,
4730 50,
4731 'Parameterization',
4732 'Forced Plans',
4733 'http://brentozar.com/blitzcache/forced-plans/',
4734 'Execution plans have been compiled with forced plans, either through FORCEPLAN, plan guides, or forced parameterization. This will make general tuning efforts less effective.');
4735
4736 IF EXISTS (SELECT 1/0
4737 FROM ##bou_BlitzCacheProcs
4738 WHERE is_cursor = 1
4739 AND SPID = @@SPID)
4740 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4741 VALUES (@@SPID,
4742 4,
4743 200,
4744 'Cursors',
4745 'Cursors',
4746 'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
4747 'There are cursors in the plan cache. This is neither good nor bad, but it is a thing. Cursors are weird in SQL Server.');
4748
4749 IF EXISTS (SELECT 1/0
4750 FROM ##bou_BlitzCacheProcs
4751 WHERE is_cursor = 1
4752 AND is_optimistic_cursor = 1
4753 AND SPID = @@SPID)
4754 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4755 VALUES (@@SPID,
4756 4,
4757 200,
4758 'Cursors',
4759 'Optimistic Cursors',
4760 'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
4761 'There are optimistic cursors in the plan cache, which can harm performance.');
4762
4763 IF EXISTS (SELECT 1/0
4764 FROM ##bou_BlitzCacheProcs
4765 WHERE is_cursor = 1
4766 AND is_forward_only_cursor = 0
4767 AND SPID = @@SPID)
4768 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4769 VALUES (@@SPID,
4770 4,
4771 200,
4772 'Cursors',
4773 'Non-forward Only Cursors',
4774 'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
4775 'There are non-forward only cursors in the plan cache, which can harm performance.');
4776
4777 IF EXISTS (SELECT 1/0
4778 FROM ##bou_BlitzCacheProcs
4779 WHERE is_cursor = 1
4780 AND is_cursor_dynamic = 1
4781 AND SPID = @@SPID)
4782 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4783 VALUES (@@SPID,
4784 4,
4785 200,
4786 'Cursors',
4787 'Dynamic Cursors',
4788 'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
4789 'Dynamic Cursors inhibit parallelism!.');
4790
4791 IF EXISTS (SELECT 1/0
4792 FROM ##bou_BlitzCacheProcs
4793 WHERE is_cursor = 1
4794 AND is_fast_forward_cursor = 1
4795 AND SPID = @@SPID)
4796 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4797 VALUES (@@SPID,
4798 4,
4799 200,
4800 'Cursors',
4801 'Fast Forward Cursors',
4802 'http://brentozar.com/blitzcache/cursors-found-slow-queries/',
4803 'Fast forward cursors inhibit parallelism!.');
4804
4805 IF EXISTS (SELECT 1/0
4806 FROM ##bou_BlitzCacheProcs
4807 WHERE is_forced_parameterized = 1
4808 AND SPID = @@SPID)
4809 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4810 VALUES (@@SPID,
4811 5,
4812 50,
4813 'Parameterization',
4814 'Forced Parameterization',
4815 'http://brentozar.com/blitzcache/forced-parameterization/',
4816 'Execution plans have been compiled with forced parameterization.') ;
4817
4818 IF EXISTS (SELECT 1/0
4819 FROM ##bou_BlitzCacheProcs p
4820 WHERE p.is_parallel = 1
4821 AND SPID = @@SPID)
4822 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4823 VALUES (@@SPID,
4824 6,
4825 200,
4826 'Execution Plans',
4827 'Parallelism',
4828 'http://brentozar.com/blitzcache/parallel-plans-detected/',
4829 'Parallel plans detected. These warrant investigation, but are neither good nor bad.') ;
4830
4831 IF EXISTS (SELECT 1/0
4832 FROM ##bou_BlitzCacheProcs p
4833 WHERE near_parallel = 1
4834 AND SPID = @@SPID)
4835 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4836 VALUES (@@SPID,
4837 7,
4838 200,
4839 'Execution Plans',
4840 'Nearly Parallel',
4841 'http://brentozar.com/blitzcache/query-cost-near-cost-threshold-parallelism/',
4842 'Queries near the cost threshold for parallelism. These may go parallel when you least expect it.') ;
4843
4844 IF EXISTS (SELECT 1/0
4845 FROM ##bou_BlitzCacheProcs p
4846 WHERE plan_warnings = 1
4847 AND SPID = @@SPID)
4848 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4849 VALUES (@@SPID,
4850 8,
4851 50,
4852 'Execution Plans',
4853 'Query Plan Warnings',
4854 'http://brentozar.com/blitzcache/query-plan-warnings/',
4855 'Warnings detected in execution plans. SQL Server is telling you that something bad is going on that requires your attention.') ;
4856
4857 IF EXISTS (SELECT 1/0
4858 FROM ##bou_BlitzCacheProcs p
4859 WHERE long_running = 1
4860 AND SPID = @@SPID)
4861 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4862 VALUES (@@SPID,
4863 9,
4864 50,
4865 'Performance',
4866 'Long Running Queries',
4867 'http://brentozar.com/blitzcache/long-running-queries/',
4868 'Long running queries have been found. These are queries with an average duration longer than '
4869 + CAST(@long_running_query_warning_seconds / 1000 / 1000 AS VARCHAR(5))
4870 + ' second(s). These queries should be investigated for additional tuning options.') ;
4871
4872 IF EXISTS (SELECT 1/0
4873 FROM ##bou_BlitzCacheProcs p
4874 WHERE p.missing_index_count > 0
4875 AND SPID = @@SPID)
4876 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4877 VALUES (@@SPID,
4878 10,
4879 50,
4880 'Performance',
4881 'Missing Index Request',
4882 'http://brentozar.com/blitzcache/missing-index-request/',
4883 'Queries found with missing indexes.');
4884
4885 IF EXISTS (SELECT 1/0
4886 FROM ##bou_BlitzCacheProcs p
4887 WHERE p.downlevel_estimator = 1
4888 AND SPID = @@SPID)
4889 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4890 VALUES (@@SPID,
4891 13,
4892 200,
4893 'Cardinality',
4894 'Legacy Cardinality Estimator in Use',
4895 'http://brentozar.com/blitzcache/legacy-cardinality-estimator/',
4896 'A legacy cardinality estimator is being used by one or more queries. Investigate whether you need to be using this cardinality estimator. This may be caused by compatibility levels, global trace flags, or query level trace flags.');
4897
4898 IF EXISTS (SELECT 1/0
4899 FROM ##bou_BlitzCacheProcs p
4900 WHERE implicit_conversions = 1
4901 AND SPID = @@SPID)
4902 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4903 VALUES (@@SPID,
4904 14,
4905 50,
4906 'Performance',
4907 'Implicit Conversions',
4908 'http://brentozar.com/go/implicit',
4909 'One or more queries are comparing two fields that are not of the same data type.') ;
4910
4911 IF EXISTS (SELECT 1/0
4912 FROM ##bou_BlitzCacheProcs
4913 WHERE busy_loops = 1
4914 AND SPID = @@SPID)
4915 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4916 VALUES (@@SPID,
4917 16,
4918 100,
4919 'Performance',
4920 'Frequently executed operators',
4921 'http://brentozar.com/blitzcache/busy-loops/',
4922 'Operations have been found that are executed 100 times more often than the number of rows returned by each iteration. This is an indicator that something is off in query execution.');
4923
4924 IF EXISTS (SELECT 1/0
4925 FROM ##bou_BlitzCacheProcs
4926 WHERE tvf_join = 1
4927 AND SPID = @@SPID)
4928 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4929 VALUES (@@SPID,
4930 17,
4931 50,
4932 'Performance',
4933 'Joining to table valued functions',
4934 'http://brentozar.com/blitzcache/tvf-join/',
4935 'Execution plans have been found that join to table valued functions (TVFs). TVFs produce inaccurate estimates of the number of rows returned and can lead to any number of query plan problems.');
4936
4937 IF EXISTS (SELECT 1/0
4938 FROM ##bou_BlitzCacheProcs
4939 WHERE compile_timeout = 1
4940 AND SPID = @@SPID)
4941 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4942 VALUES (@@SPID,
4943 18,
4944 50,
4945 'Execution Plans',
4946 'Compilation timeout',
4947 'http://brentozar.com/blitzcache/compilation-timeout/',
4948 'Query compilation timed out for one or more queries. SQL Server did not find a plan that meets acceptable performance criteria in the time allotted so the best guess was returned. There is a very good chance that this plan isn''t even below average - it''s probably terrible.');
4949
4950 IF EXISTS (SELECT 1/0
4951 FROM ##bou_BlitzCacheProcs
4952 WHERE compile_memory_limit_exceeded = 1
4953 AND SPID = @@SPID)
4954 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4955 VALUES (@@SPID,
4956 19,
4957 50,
4958 'Execution Plans',
4959 'Compilation memory limit exceeded',
4960 'http://brentozar.com/blitzcache/compile-memory-limit-exceeded/',
4961 'The optimizer has a limited amount of memory available. One or more queries are complex enough that SQL Server was unable to allocate enough memory to fully optimize the query. A best fit plan was found, and it''s probably terrible.');
4962
4963 IF EXISTS (SELECT 1/0
4964 FROM ##bou_BlitzCacheProcs
4965 WHERE warning_no_join_predicate = 1
4966 AND SPID = @@SPID)
4967 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4968 VALUES (@@SPID,
4969 20,
4970 50,
4971 'Execution Plans',
4972 'No join predicate',
4973 'http://brentozar.com/blitzcache/no-join-predicate/',
4974 'Operators in a query have no join predicate. This means that all rows from one table will be matched with all rows from anther table producing a Cartesian product. That''s a whole lot of rows. This may be your goal, but it''s important to investigate why this is happening.');
4975
4976 IF EXISTS (SELECT 1/0
4977 FROM ##bou_BlitzCacheProcs
4978 WHERE plan_multiple_plans = 1
4979 AND SPID = @@SPID)
4980 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4981 VALUES (@@SPID,
4982 21,
4983 200,
4984 'Execution Plans',
4985 'Multiple execution plans',
4986 'http://brentozar.com/blitzcache/multiple-plans/',
4987 'Queries exist with multiple execution plans (as determined by query_plan_hash). Investigate possible ways to parameterize these queries or otherwise reduce the plan count.');
4988
4989 IF EXISTS (SELECT 1/0
4990 FROM ##bou_BlitzCacheProcs
4991 WHERE unmatched_index_count > 0
4992 AND SPID = @@SPID)
4993 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
4994 VALUES (@@SPID,
4995 22,
4996 100,
4997 'Performance',
4998 'Unmatched indexes',
4999 'http://brentozar.com/blitzcache/unmatched-indexes',
5000 'An index could have been used, but SQL Server chose not to use it - likely due to parameterization and filtered indexes.');
5001
5002 IF EXISTS (SELECT 1/0
5003 FROM ##bou_BlitzCacheProcs
5004 WHERE unparameterized_query = 1
5005 AND SPID = @@SPID)
5006 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5007 VALUES (@@SPID,
5008 23,
5009 100,
5010 'Parameterization',
5011 'Unparameterized queries',
5012 'http://brentozar.com/blitzcache/unparameterized-queries',
5013 'Unparameterized queries found. These could be ad hoc queries, data exploration, or queries using "OPTIMIZE FOR UNKNOWN".');
5014
5015 IF EXISTS (SELECT 1/0
5016 FROM ##bou_BlitzCacheProcs
5017 WHERE is_trivial = 1
5018 AND SPID = @@SPID)
5019 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5020 VALUES (@@SPID,
5021 24,
5022 100,
5023 'Execution Plans',
5024 'Trivial Plans',
5025 'http://brentozar.com/blitzcache/trivial-plans',
5026 'Trivial plans get almost no optimization. If you''re finding these in the top worst queries, something may be going wrong.');
5027
5028 IF EXISTS (SELECT 1/0
5029 FROM ##bou_BlitzCacheProcs p
5030 WHERE p.is_forced_serial= 1
5031 AND SPID = @@SPID)
5032 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5033 VALUES (@@SPID,
5034 25,
5035 10,
5036 'Execution Plans',
5037 'Forced Serialization',
5038 'http://www.brentozar.com/blitzcache/forced-serialization/',
5039 'Something in your plan is forcing a serial query. Further investigation is needed if this is not by design.') ;
5040
5041 IF EXISTS (SELECT 1/0
5042 FROM ##bou_BlitzCacheProcs p
5043 WHERE p.is_key_lookup_expensive= 1
5044 AND SPID = @@SPID)
5045 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5046 VALUES (@@SPID,
5047 26,
5048 100,
5049 'Execution Plans',
5050 'Expensive Key Lookups',
5051 'http://www.brentozar.com/blitzcache/expensive-key-lookups/',
5052 'There''s a key lookup in your plan that costs >=50% of the total plan cost.') ;
5053
5054 IF EXISTS (SELECT 1/0
5055 FROM ##bou_BlitzCacheProcs p
5056 WHERE p.is_remote_query_expensive= 1
5057 AND SPID = @@SPID)
5058 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5059 VALUES (@@SPID,
5060 28,
5061 100,
5062 'Execution Plans',
5063 'Expensive Remote Query',
5064 'http://www.brentozar.com/blitzcache/expensive-remote-query/',
5065 'There''s a remote query in your plan that costs >=50% of the total plan cost.') ;
5066
5067 IF EXISTS (SELECT 1/0
5068 FROM ##bou_BlitzCacheProcs p
5069 WHERE p.trace_flags_session IS NOT NULL
5070 AND SPID = @@SPID)
5071 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5072 VALUES (@@SPID,
5073 29,
5074 200,
5075 'Trace Flags',
5076 'Session Level Trace Flags Enabled',
5077 'https://www.brentozar.com/blitz/trace-flags-enabled-globally/',
5078 'Someone is enabling session level Trace Flags in a query.') ;
5079
5080 IF EXISTS (SELECT 1/0
5081 FROM ##bou_BlitzCacheProcs p
5082 WHERE p.is_unused_grant IS NOT NULL
5083 AND SPID = @@SPID)
5084 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5085 VALUES (@@SPID,
5086 30,
5087 100,
5088 'Unused memory grants',
5089 'Queries are asking for more memory than they''re using',
5090 'https://www.brentozar.com/blitzcache/unused-memory-grants/',
5091 'Queries have large unused memory grants. This can cause concurrency issues, if queries are waiting a long time to get memory to run.') ;
5092
5093 IF EXISTS (SELECT 1/0
5094 FROM ##bou_BlitzCacheProcs p
5095 WHERE p.function_count > 0
5096 AND SPID = @@SPID)
5097 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5098 VALUES (@@SPID,
5099 31,
5100 100,
5101 'Compute Scalar That References A Function',
5102 'This could be trouble if you''re using Scalar Functions or MSTVFs',
5103 'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
5104 'Both of these will force queries to run serially, run at least once per row, and may result in poor cardinality estimates.') ;
5105
5106 IF EXISTS (SELECT 1/0
5107 FROM ##bou_BlitzCacheProcs p
5108 WHERE p.clr_function_count > 0
5109 AND SPID = @@SPID)
5110 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5111 VALUES (@@SPID,
5112 32,
5113 100,
5114 'Compute Scalar That References A CLR Function',
5115 'This could be trouble if your CLR functions perform data access',
5116 'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
5117 'May force queries to run serially, run at least once per row, and may result in poor cardinlity estimates.') ;
5118
5119
5120 IF EXISTS (SELECT 1/0
5121 FROM ##bou_BlitzCacheProcs p
5122 WHERE p.is_table_variable = 1
5123 AND SPID = @@SPID)
5124 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5125 VALUES (@@SPID,
5126 33,
5127 100,
5128 'Table Variables detected',
5129 'Beware nasty side effects',
5130 'https://www.brentozar.com/blitzcache/table-variables/',
5131 'All modifications are single threaded, and selects have really low row estimates.') ;
5132
5133 IF EXISTS (SELECT 1/0
5134 FROM ##bou_BlitzCacheProcs p
5135 WHERE p.no_stats_warning = 1
5136 AND SPID = @@SPID)
5137 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5138 VALUES (@@SPID,
5139 35,
5140 100,
5141 'Columns with no statistics',
5142 'Poor cardinality estimates may ensue',
5143 'https://www.brentozar.com/blitzcache/columns-no-statistics/',
5144 'Sometimes this happens with indexed views, other times because auto create stats is turned off.') ;
5145
5146 IF EXISTS (SELECT 1/0
5147 FROM ##bou_BlitzCacheProcs p
5148 WHERE p.relop_warnings = 1
5149 AND SPID = @@SPID)
5150 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5151 VALUES (@@SPID,
5152 36,
5153 100,
5154 'Operator Warnings',
5155 'SQL is throwing operator level plan warnings',
5156 'http://brentozar.com/blitzcache/query-plan-warnings/',
5157 'Check the plan for more details.') ;
5158
5159 IF EXISTS (SELECT 1/0
5160 FROM ##bou_BlitzCacheProcs p
5161 WHERE p.is_table_scan = 1
5162 AND SPID = @@SPID)
5163 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5164 VALUES (@@SPID,
5165 37,
5166 100,
5167 'Table Scans',
5168 'Your database has HEAPs',
5169 'https://www.brentozar.com/archive/2012/05/video-heaps/',
5170 'This may not be a problem. Run sp_BlitzIndex for more information.') ;
5171
5172 IF EXISTS (SELECT 1/0
5173 FROM ##bou_BlitzCacheProcs p
5174 WHERE p.backwards_scan = 1
5175 AND SPID = @@SPID)
5176 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5177 VALUES (@@SPID,
5178 38,
5179 200,
5180 'Backwards Scans',
5181 'Indexes are being read backwards',
5182 'https://www.brentozar.com/blitzcache/backwards-scans/',
5183 'This isn''t always a problem. They can cause serial zones in plans, and may need an index to match sort order.') ;
5184
5185 IF EXISTS (SELECT 1/0
5186 FROM ##bou_BlitzCacheProcs p
5187 WHERE p.forced_index = 1
5188 AND SPID = @@SPID)
5189 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5190 VALUES (@@SPID,
5191 39,
5192 100,
5193 'Index forcing',
5194 'Someone is using hints to force index usage',
5195 'https://www.brentozar.com/blitzcache/optimizer-forcing/',
5196 'This can cause inefficient plans, and will prevent missing index requests.') ;
5197
5198 IF EXISTS (SELECT 1/0
5199 FROM ##bou_BlitzCacheProcs p
5200 WHERE p.forced_seek = 1
5201 OR p.forced_scan = 1
5202 AND SPID = @@SPID)
5203 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5204 VALUES (@@SPID,
5205 40,
5206 100,
5207 'Seek/Scan forcing',
5208 'Someone is using hints to force index seeks/scans',
5209 'https://www.brentozar.com/blitzcache/optimizer-forcing/',
5210 'This can cause inefficient plans by taking seek vs scan choice away from the optimizer.') ;
5211
5212 IF EXISTS (SELECT 1/0
5213 FROM ##bou_BlitzCacheProcs p
5214 WHERE p.columnstore_row_mode = 1
5215 AND SPID = @@SPID)
5216 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5217 VALUES (@@SPID,
5218 41,
5219 100,
5220 'ColumnStore indexes operating in Row Mode',
5221 'Batch Mode is optimal for ColumnStore indexes',
5222 'https://www.brentozar.com/blitzcache/columnstore-indexes-operating-row-mode/',
5223 'ColumnStore indexes operating in Row Mode indicate really poor query choices.') ;
5224
5225 IF EXISTS (SELECT 1/0
5226 FROM ##bou_BlitzCacheProcs p
5227 WHERE p.is_computed_scalar = 1
5228 AND SPID = @@SPID)
5229 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5230 VALUES (@@SPID,
5231 42,
5232 50,
5233 'Computed Columns Referencing Scalar UDFs',
5234 'This makes a whole lot of stuff run serially',
5235 'https://www.brentozar.com/blitzcache/computed-columns-referencing-functions/',
5236 'This can cause a whole mess of bad serializartion problems.') ;
5237
5238 IF EXISTS (SELECT 1/0
5239 FROM ##bou_BlitzCacheProcs p
5240 WHERE p.is_sort_expensive = 1
5241 AND SPID = @@SPID)
5242 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5243 VALUES (@@SPID,
5244 43,
5245 100,
5246 'Execution Plans',
5247 'Expensive Sort',
5248 'http://www.brentozar.com/blitzcache/expensive-sorts/',
5249 'There''s a sort in your plan that costs >=50% of the total plan cost.') ;
5250
5251 IF EXISTS (SELECT 1/0
5252 FROM ##bou_BlitzCacheProcs p
5253 WHERE p.is_computed_filter = 1
5254 AND SPID = @@SPID)
5255 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5256 VALUES (@@SPID,
5257 44,
5258 50,
5259 'Filters Referencing Scalar UDFs',
5260 'This forces serialization',
5261 'https://www.brentozar.com/blitzcache/compute-scalar-functions/',
5262 'Someone put a Scalar UDF in the WHERE clause!') ;
5263
5264 IF EXISTS (SELECT 1/0
5265 FROM ##bou_BlitzCacheProcs p
5266 WHERE p.index_ops >= 5
5267 AND SPID = @@SPID)
5268 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5269 VALUES (@@SPID,
5270 45,
5271 100,
5272 'Many Indexes Modified',
5273 'Write Queries Are Hitting >= 5 Indexes',
5274 'https://www.brentozar.com/blitzcache/many-indexes-modified/',
5275 'This can cause lots of hidden I/O -- Run sp_BlitzIndex for more information.') ;
5276
5277 IF EXISTS (SELECT 1/0
5278 FROM ##bou_BlitzCacheProcs p
5279 WHERE p.is_row_level = 1
5280 AND SPID = @@SPID)
5281 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5282 VALUES (@@SPID,
5283 46,
5284 200,
5285 'Plan Confusion',
5286 'Row Level Security is in use',
5287 'https://www.brentozar.com/blitzcache/row-level-security/',
5288 'You may see a lot of confusing junk in your query plan.') ;
5289
5290 IF EXISTS (SELECT 1/0
5291 FROM ##bou_BlitzCacheProcs p
5292 WHERE p.is_spatial = 1
5293 AND SPID = @@SPID)
5294 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5295 VALUES (@@SPID,
5296 47,
5297 200,
5298 'Spatial Abuse',
5299 'You hit a Spatial Index',
5300 'https://www.brentozar.com/blitzcache/spatial-indexes/',
5301 'Purely informational.') ;
5302
5303 IF EXISTS (SELECT 1/0
5304 FROM ##bou_BlitzCacheProcs p
5305 WHERE p.index_dml = 1
5306 AND SPID = @@SPID)
5307 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5308 VALUES (@@SPID,
5309 48,
5310 150,
5311 'Index DML',
5312 'Indexes were created or dropped',
5313 'https://www.brentozar.com/blitzcache/index-dml/',
5314 'This can cause recompiles and stuff.') ;
5315
5316 IF EXISTS (SELECT 1/0
5317 FROM ##bou_BlitzCacheProcs p
5318 WHERE p.table_dml = 1
5319 AND SPID = @@SPID)
5320 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5321 VALUES (@@SPID,
5322 49,
5323 150,
5324 'Table DML',
5325 'Tables were created or dropped',
5326 'https://www.brentozar.com/blitzcache/table-dml/',
5327 'This can cause recompiles and stuff.') ;
5328
5329 IF EXISTS (SELECT 1/0
5330 FROM ##bou_BlitzCacheProcs p
5331 WHERE p.long_running_low_cpu = 1
5332 AND SPID = @@SPID)
5333 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5334 VALUES (@@SPID,
5335 50,
5336 150,
5337 'Long Running Low CPU',
5338 'You have a query that runs for much longer than it uses CPU',
5339 'https://www.brentozar.com/blitzcache/long-running-low-cpu/',
5340 'This can be a sign of blocking, linked servers, or poor client application code (ASYNC_NETWORK_IO).') ;
5341
5342 IF EXISTS (SELECT 1/0
5343 FROM ##bou_BlitzCacheProcs p
5344 WHERE p.low_cost_high_cpu = 1
5345 AND SPID = @@SPID)
5346 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5347 VALUES (@@SPID,
5348 51,
5349 150,
5350 'Low Cost Query With High CPU',
5351 'You have a low cost query that uses a lot of CPU',
5352 'https://www.brentozar.com/blitzcache/low-cost-high-cpu/',
5353 'This can be a sign of functions or Dynamic SQL that calls black-box code.') ;
5354
5355 IF EXISTS (SELECT 1/0
5356 FROM ##bou_BlitzCacheProcs p
5357 WHERE p.stale_stats = 1
5358 AND SPID = @@SPID)
5359 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5360 VALUES (@@SPID,
5361 52,
5362 150,
5363 'Biblical Statistics',
5364 'Statistics used in queries are >7 days old with >100k modifications',
5365 'https://www.brentozar.com/blitzcache/stale-statistics/',
5366 'Ever heard of updating statistics?') ;
5367
5368 IF EXISTS (SELECT 1/0
5369 FROM ##bou_BlitzCacheProcs p
5370 WHERE p.is_adaptive = 1
5371 AND SPID = @@SPID)
5372 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5373 VALUES (@@SPID,
5374 53,
5375 200,
5376 'Adaptive joins',
5377 'This is pretty cool -- you''re living in the future.',
5378 'https://www.brentozar.com/blitzcache/adaptive-joins/',
5379 'Joe Sack rules.') ;
5380
5381 IF EXISTS (SELECT 1/0
5382 FROM ##bou_BlitzCacheProcs p
5383 WHERE p.is_spool_expensive = 1
5384 )
5385 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5386 VALUES (@@SPID,
5387 54,
5388 150,
5389 'Expensive Index Spool',
5390 'You have an index spool, this is usually a sign that there''s an index missing somewhere.',
5391 'https://www.brentozar.com/blitzcache/eager-index-spools/',
5392 'Check operator predicates and output for index definition guidance') ;
5393
5394 IF EXISTS (SELECT 1/0
5395 FROM ##bou_BlitzCacheProcs p
5396 WHERE p.is_spool_more_rows = 1
5397 )
5398 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5399 VALUES (@@SPID,
5400 55,
5401 150,
5402 'Index Spools Many Rows',
5403 'You have an index spool that spools more rows than the query returns',
5404 'https://www.brentozar.com/blitzcache/eager-index-spools/',
5405 'Check operator predicates and output for index definition guidance') ;
5406
5407 IF EXISTS (SELECT 1/0
5408 FROM ##bou_BlitzCacheProcs p
5409 WHERE p.is_bad_estimate = 1
5410 )
5411 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5412 VALUES (@@SPID,
5413 56,
5414 100,
5415 'Potentially bad cardinality estimates',
5416 'Estimated rows are different from average rows by a factor of 10000',
5417 'https://www.brentozar.com/blitzcache/bad-estimates/',
5418 'This may indicate a performance problem if mismatches occur regularly') ;
5419
5420 IF EXISTS (SELECT 1/0
5421 FROM ##bou_BlitzCacheProcs p
5422 WHERE p.is_paul_white_electric = 1
5423 )
5424 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5425 VALUES (@@SPID,
5426 998,
5427 200,
5428 'Is Paul White Electric?',
5429 'This query has a Switch operator in it!',
5430 'http://sqlblog.com/blogs/paul_white/archive/2013/06/11/hello-operator-my-switch-is-bored.aspx',
5431 'You should email this query plan to Paul: SQLkiwi at gmail dot com') ;
5432
5433 IF @v >= 14
5434 BEGIN
5435
5436 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5437 SELECT
5438 @@SPID,
5439 999,
5440 200,
5441 'Database Level Statistics',
5442 'The database ' + sa.[Database] + ' last had a stats update on ' + CONVERT(NVARCHAR(10), CONVERT(DATE, MAX(sa.LastUpdate))) + ' and has ' + CONVERT(NVARCHAR(10), AVG(sa.ModificationCount)) + ' modifications on average.' AS [Finding],
5443 'https://www.brentozar.com/blitzcache/stale-statistics/' AS URL,
5444 'Consider updating statistics more frequently,' AS [Details]
5445 FROM #stats_agg AS sa
5446 GROUP BY sa.[Database]
5447 HAVING MAX(sa.LastUpdate) <= DATEADD(DAY, -7, SYSDATETIME())
5448 AND AVG(sa.ModificationCount) >= 100000;
5449
5450 IF EXISTS (SELECT 1/0
5451 FROM ##bou_BlitzCacheProcs p
5452 WHERE p.is_row_goal = 1
5453 )
5454 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5455 VALUES (@@SPID,
5456 58,
5457 200,
5458 'Row Goals',
5459 'This query had row goals introduced',
5460 'https://www.brentozar.com/go/rowgoals/',
5461 'This can be good or bad, and should be investigated for high read queries') ;
5462
5463 IF EXISTS (SELECT 1/0
5464 FROM ##bou_BlitzCacheProcs p
5465 WHERE p.is_big_spills = 1
5466 )
5467 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5468 VALUES (@@SPID,
5469 59,
5470 100,
5471 'tempdb Spills',
5472 'This query spills >500mb to tempdb on average',
5473 'https://www.brentozar.com/blitzcache/tempdb-spills/',
5474 'One way or another, this query didn''t get enough memory') ;
5475
5476
5477 END;
5478
5479 IF EXISTS (SELECT 1/0
5480 FROM ##bou_BlitzCacheProcs p
5481 WHERE p.is_mstvf = 1
5482 )
5483 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5484 VALUES (@@SPID,
5485 60,
5486 100,
5487 'MSTVFs',
5488 'These have many of the same problems scalar UDFs have',
5489 'http://brentozar.com/blitzcache/tvf-join/',
5490 'Execution plans have been found that join to table valued functions (TVFs). TVFs produce inaccurate estimates of the number of rows returned and can lead to any number of query plan problems.');
5491
5492 IF EXISTS (SELECT 1/0
5493 FROM ##bou_BlitzCacheProcs p
5494 WHERE p.is_mm_join = 1
5495 )
5496 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5497 VALUES (@@SPID,
5498 61,
5499 100,
5500 'Many to Many Merge',
5501 'These use secret worktables that could be doing lots of reads',
5502 'link to blog post when published',
5503 'Occurs when join inputs aren''t known to be unique. Can be really bad when parallel.');
5504
5505 IF EXISTS (SELECT 1/0
5506 FROM ##bou_BlitzCacheProcs p
5507 WHERE p.is_nonsargable = 1
5508 )
5509 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5510 VALUES (@@SPID,
5511 62,
5512 50,
5513 'Non-SARGable queries',
5514 'Queries may have non-SARGable predicates',
5515 'http://brentozar.com/go/sargable',
5516 'Looks for intrinsic functions and expressions as predicates, and leading wildcard LIKE searches.');
5517
5518 IF EXISTS (SELECT 1/0
5519 FROM #plan_creation p
5520 WHERE (p.percent_24 > 0)
5521 AND SPID = @@SPID)
5522 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5523 SELECT SPID,
5524 999,
5525 254,
5526 'Plan Cache Information',
5527 'You have ' + CONVERT(NVARCHAR(10), ISNULL(p.total_plans, 0))
5528 + ' total plans in your cache, with '
5529 + CONVERT(NVARCHAR(10), ISNULL(p.percent_24, 0))
5530 + '% plans created in the past 24 hours, '
5531 + CONVERT(NVARCHAR(10), ISNULL(p.percent_4, 0))
5532 + '% created in the past 4 hours, and '
5533 + CONVERT(NVARCHAR(10), ISNULL(p.percent_1, 0))
5534 + '% created in the past 1 hour.',
5535 '',
5536 'If these percentages are high, it may be a sign of memory pressure or plan cache instability.'
5537 FROM #plan_creation p ;
5538
5539 IF @v >= 11
5540 BEGIN
5541 IF EXISTS (SELECT 1/0
5542 FROM #trace_flags AS tf
5543 WHERE tf.global_trace_flags IS NOT NULL
5544 )
5545 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5546 VALUES (@@SPID,
5547 1000,
5548 255,
5549 'Global Trace Flags Enabled',
5550 'You have Global Trace Flags enabled on your server',
5551 'https://www.brentozar.com/blitz/trace-flags-enabled-globally/',
5552 'You have the following Global Trace Flags enabled: ' + (SELECT TOP 1 tf.global_trace_flags FROM #trace_flags AS tf WHERE tf.global_trace_flags IS NOT NULL)) ;
5553 END;
5554
5555 IF NOT EXISTS (SELECT 1/0
5556 FROM ##bou_BlitzCacheResults AS bcr
5557 WHERE bcr.Priority = 2147483646
5558 )
5559 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5560 VALUES (@@SPID,
5561 2147483646,
5562 255,
5563 'Need more help?' ,
5564 'Paste your plan on the internet!',
5565 'http://pastetheplan.com',
5566 'This makes it easy to share plans and post them to Q&A sites like https://dba.stackexchange.com/!') ;
5567
5568
5569
5570 IF NOT EXISTS (SELECT 1/0
5571 FROM ##bou_BlitzCacheResults AS bcr
5572 WHERE bcr.Priority = 2147483647
5573 )
5574 INSERT INTO ##bou_BlitzCacheResults (SPID, CheckID, Priority, FindingsGroup, Finding, URL, Details)
5575 VALUES (@@SPID,
5576 2147483647,
5577 255,
5578 'Thanks for using sp_BlitzCache!' ,
5579 'From Your Community Volunteers',
5580 'http://FirstResponderKit.org',
5581 'We hope you found this tool useful. Current version: ' + @Version + ' released on ' + CONVERT(NVARCHAR(30), @VersionDate) + '.') ;
5582
5583 END;
5584
5585
5586 SELECT Priority,
5587 FindingsGroup,
5588 Finding,
5589 URL,
5590 Details,
5591 CheckID
5592 FROM ##bou_BlitzCacheResults
5593 WHERE SPID = @@SPID
5594 GROUP BY Priority,
5595 FindingsGroup,
5596 Finding,
5597 URL,
5598 Details,
5599 CheckID
5600 ORDER BY Priority ASC, CheckID ASC
5601 OPTION (RECOMPILE);
5602END;
5603
5604IF @Debug = 1
5605 BEGIN
5606
5607 SELECT '##bou_BlitzCacheResults' AS table_name, *
5608 FROM ##bou_BlitzCacheResults
5609 OPTION ( RECOMPILE );
5610
5611 SELECT '##bou_BlitzCacheProcs' AS table_name, *
5612 FROM ##bou_BlitzCacheProcs
5613 OPTION ( RECOMPILE );
5614
5615 SELECT '#statements' AS table_name, *
5616 FROM #statements AS s
5617 OPTION (RECOMPILE);
5618
5619 SELECT '#query_plan' AS table_name, *
5620 FROM #query_plan AS qp
5621 OPTION (RECOMPILE);
5622
5623 SELECT '#relop' AS table_name, *
5624 FROM #relop AS r
5625 OPTION (RECOMPILE);
5626
5627 SELECT '#only_query_hashes' AS table_name, *
5628 FROM #only_query_hashes
5629 OPTION ( RECOMPILE );
5630
5631 SELECT '#ignore_query_hashes' AS table_name, *
5632 FROM #ignore_query_hashes
5633 OPTION ( RECOMPILE );
5634
5635 SELECT '#only_sql_handles' AS table_name, *
5636 FROM #only_sql_handles
5637 OPTION ( RECOMPILE );
5638
5639 SELECT '#ignore_sql_handles' AS table_name, *
5640 FROM #ignore_sql_handles
5641 OPTION ( RECOMPILE );
5642
5643 SELECT '#p' AS table_name, *
5644 FROM #p
5645 OPTION ( RECOMPILE );
5646
5647 SELECT '#checkversion' AS table_name, *
5648 FROM #checkversion
5649 OPTION ( RECOMPILE );
5650
5651 SELECT '#configuration' AS table_name, *
5652 FROM #configuration
5653 OPTION ( RECOMPILE );
5654
5655 SELECT '#stored_proc_info' AS table_name, *
5656 FROM #stored_proc_info
5657 OPTION ( RECOMPILE );
5658
5659 SELECT '#conversion_info' AS table_name, *
5660 FROM #conversion_info AS ci
5661 OPTION ( RECOMPILE );
5662
5663 SELECT '#variable_info' AS table_name, *
5664 FROM #variable_info AS vi
5665 OPTION ( RECOMPILE );
5666
5667 SELECT '#plan_creation' AS table_name, *
5668 FROM #plan_creation
5669 OPTION ( RECOMPILE );
5670
5671 SELECT '#plan_cost' AS table_name, *
5672 FROM #plan_cost
5673 OPTION ( RECOMPILE );
5674
5675 SELECT '#proc_costs' AS table_name, *
5676 FROM #proc_costs
5677 OPTION ( RECOMPILE );
5678
5679 SELECT '#stats_agg' AS table_name, *
5680 FROM #stats_agg
5681 OPTION ( RECOMPILE );
5682
5683 SELECT '#trace_flags' AS table_name, *
5684 FROM #trace_flags
5685 OPTION ( RECOMPILE );
5686
5687 END;
5688
5689
5690RETURN; --Avoid going into the AllSort GOTO
5691
5692/*Begin code to sort by all*/
5693AllSorts:
5694RAISERROR('Beginning all sort loop', 0, 1) WITH NOWAIT;
5695
5696
5697IF (
5698 @Top > 10
5699 AND @BringThePain = 0
5700 )
5701 BEGIN
5702 RAISERROR(
5703 '
5704 You''ve chosen a value greater than 10 to sort the whole plan cache by.
5705 That can take a long time and harm performance.
5706 Please choose a number <= 10, or set @BringThePain = 1 to signify you understand this might be a bad idea.
5707 ', 0, 1) WITH NOWAIT;
5708 RETURN;
5709 END;
5710
5711
5712IF OBJECT_ID('tempdb..#checkversion_allsort') IS NULL
5713 BEGIN
5714 CREATE TABLE #checkversion_allsort
5715 (
5716 version NVARCHAR(128),
5717 common_version AS SUBSTRING(version, 1, CHARINDEX('.', version) + 1),
5718 major AS PARSENAME(CONVERT(VARCHAR(32), version), 4),
5719 minor AS PARSENAME(CONVERT(VARCHAR(32), version), 3),
5720 build AS PARSENAME(CONVERT(VARCHAR(32), version), 2),
5721 revision AS PARSENAME(CONVERT(VARCHAR(32), version), 1)
5722 );
5723
5724 INSERT INTO #checkversion_allsort
5725 (version)
5726 SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
5727 OPTION ( RECOMPILE );
5728 END;
5729
5730
5731SELECT @v = common_version,
5732 @build = build
5733FROM #checkversion_allsort
5734OPTION ( RECOMPILE );
5735
5736IF OBJECT_ID('tempdb.. #bou_allsort') IS NULL
5737 BEGIN
5738 CREATE TABLE #bou_allsort
5739 (
5740 Id INT IDENTITY(1, 1),
5741 DatabaseName NVARCHAR(128),
5742 Cost FLOAT,
5743 QueryText NVARCHAR(MAX),
5744 QueryType NVARCHAR(258),
5745 Warnings VARCHAR(MAX),
5746 QueryPlan XML,
5747 missing_indexes XML,
5748 implicit_conversion_info XML,
5749 cached_execution_parameters XML,
5750 ExecutionCount BIGINT,
5751 ExecutionsPerMinute MONEY,
5752 ExecutionWeight MONEY,
5753 TotalCPU BIGINT,
5754 AverageCPU BIGINT,
5755 CPUWeight MONEY,
5756 TotalDuration BIGINT,
5757 AverageDuration BIGINT,
5758 DurationWeight MONEY,
5759 TotalReads BIGINT,
5760 AverageReads BIGINT,
5761 ReadWeight MONEY,
5762 TotalWrites BIGINT,
5763 AverageWrites BIGINT,
5764 WriteWeight MONEY,
5765 AverageReturnedRows MONEY,
5766 MinGrantKB BIGINT,
5767 MaxGrantKB BIGINT,
5768 MinUsedGrantKB BIGINT,
5769 MaxUsedGrantKB BIGINT,
5770 AvgMaxMemoryGrant MONEY,
5771 MinSpills BIGINT,
5772 MaxSpills BIGINT,
5773 TotalSpills BIGINT,
5774 AvgSpills MONEY,
5775 PlanCreationTime DATETIME,
5776 LastExecutionTime DATETIME,
5777 PlanHandle VARBINARY(64),
5778 SqlHandle VARBINARY(64),
5779 SetOptions VARCHAR(MAX),
5780 Pattern NVARCHAR(20)
5781 );
5782 END;
5783
5784DECLARE @AllSortSql NVARCHAR(MAX) = N'';
5785DECLARE @MemGrant BIT;
5786SELECT @MemGrant = CASE WHEN (
5787 ( @v < 11 )
5788 OR (
5789 @v = 11
5790 AND @build < 6020
5791 )
5792 OR (
5793 @v = 12
5794 AND @build < 5000
5795 )
5796 OR (
5797 @v = 13
5798 AND @build < 1601
5799 )
5800 ) THEN 0
5801 ELSE 1
5802 END;
5803
5804DECLARE @Spills BIT;
5805SELECT @Spills = CASE WHEN (@v >= 14) THEN 1 ELSE 0 END;
5806
5807
5808IF LOWER(@SortOrder) = 'all'
5809BEGIN
5810RAISERROR('Beginning for ALL', 0, 1) WITH NOWAIT;
5811SET @AllSortSql += N'
5812 DECLARE @ISH NVARCHAR(MAX) = N''''
5813
5814 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5815 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5816 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5817 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5818
5819 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''cpu'', @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5820
5821 UPDATE #bou_allsort SET Pattern = ''cpu'' WHERE Pattern IS NULL OPTION(RECOMPILE);
5822
5823 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5824
5825 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5826 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5827 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5828 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5829
5830 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''reads'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5831
5832 UPDATE #bou_allsort SET Pattern = ''reads'' WHERE Pattern IS NULL OPTION(RECOMPILE);
5833
5834 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5835
5836 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5837 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5838 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5839 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5840
5841 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''writes'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5842
5843 UPDATE #bou_allsort SET Pattern = ''writes'' WHERE Pattern IS NULL OPTION(RECOMPILE);
5844
5845 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5846
5847 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5848 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5849 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5850 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5851
5852 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''duration'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5853
5854 UPDATE #bou_allsort SET Pattern = ''duration'' WHERE Pattern IS NULL OPTION(RECOMPILE);
5855
5856 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5857
5858 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5859 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5860 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5861 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5862
5863 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''executions'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5864
5865 UPDATE #bou_allsort SET Pattern = ''executions'' WHERE Pattern IS NULL OPTION(RECOMPILE);
5866
5867 ';
5868
5869 IF @MemGrant = 0
5870 BEGIN
5871 IF @ExportToExcel = 1
5872 BEGIN
5873 SET @AllSortSql += N' UPDATE #bou_allsort
5874 SET
5875 QueryPlan = NULL,
5876 implicit_conversion_info = NULL,
5877 cached_execution_parameters = NULL,
5878 missing_indexes = NULL
5879 OPTION (RECOMPILE);
5880
5881 UPDATE ##bou_BlitzCacheProcs
5882 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
5883 OPTION(RECOMPILE);';
5884 END;
5885 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5886 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5887 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5888 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
5889 FROM #bou_allsort
5890 ORDER BY Id
5891 OPTION(RECOMPILE); ';
5892 END;
5893
5894 IF @MemGrant = 1
5895 BEGIN
5896 SET @AllSortSql += N' SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5897
5898 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5899 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5900 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5901 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5902
5903 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''memory grant'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5904
5905 UPDATE #bou_allsort SET Pattern = ''memory grant'' WHERE Pattern IS NULL OPTION(RECOMPILE);';
5906 IF @ExportToExcel = 1
5907 BEGIN
5908 SET @AllSortSql += N' UPDATE #bou_allsort
5909 SET
5910 QueryPlan = NULL,
5911 implicit_conversion_info = NULL,
5912 cached_execution_parameters = NULL,
5913 missing_indexes = NULL
5914 OPTION (RECOMPILE);
5915
5916 UPDATE ##bou_BlitzCacheProcs
5917 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
5918 OPTION(RECOMPILE);';
5919 END;
5920 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5921 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5922 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5923 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
5924 FROM #bou_allsort
5925 ORDER BY Id
5926 OPTION(RECOMPILE); ';
5927 END;
5928
5929 IF @Spills = 0
5930 BEGIN
5931 IF @ExportToExcel = 1
5932 BEGIN
5933 SET @AllSortSql += N' UPDATE #bou_allsort
5934 SET
5935 QueryPlan = NULL,
5936 implicit_conversion_info = NULL,
5937 cached_execution_parameters = NULL,
5938 missing_indexes = NULL
5939 OPTION (RECOMPILE);
5940
5941 UPDATE ##bou_BlitzCacheProcs
5942 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
5943 OPTION(RECOMPILE);';
5944 END;
5945 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5946 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5947 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5948 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
5949 FROM #bou_allsort
5950 ORDER BY Id
5951 OPTION(RECOMPILE); ';
5952 END;
5953
5954 IF @Spills = 1
5955 BEGIN
5956 SET @AllSortSql += N' SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
5957
5958 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5959 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5960 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5961 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
5962
5963 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''spills'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
5964
5965 UPDATE #bou_allsort SET Pattern = ''memory grant'' WHERE Pattern IS NULL OPTION(RECOMPILE);';
5966 IF @ExportToExcel = 1
5967 BEGIN
5968 SET @AllSortSql += N' UPDATE #bou_allsort
5969 SET
5970 QueryPlan = NULL,
5971 implicit_conversion_info = NULL,
5972 cached_execution_parameters = NULL,
5973 missing_indexes = NULL
5974 OPTION (RECOMPILE);
5975
5976 UPDATE ##bou_BlitzCacheProcs
5977 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
5978 OPTION(RECOMPILE);';
5979 END;
5980 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5981 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
5982 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
5983 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
5984 FROM #bou_allsort
5985 ORDER BY Id
5986 OPTION(RECOMPILE); ';
5987 END;
5988
5989END;
5990
5991
5992IF LOWER(@SortOrder) = 'all avg'
5993BEGIN
5994RAISERROR('Beginning for ALL AVG', 0, 1) WITH NOWAIT;
5995SET @AllSortSql += N'
5996 DECLARE @ISH NVARCHAR(MAX) = N''''
5997
5998 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
5999 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6000 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6001 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6002
6003 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg cpu'', @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6004
6005 UPDATE #bou_allsort SET Pattern = ''avg cpu'' WHERE Pattern IS NULL OPTION(RECOMPILE);
6006
6007 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6008
6009 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6010 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6011 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6012 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6013
6014 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg reads'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6015
6016 UPDATE #bou_allsort SET Pattern = ''avg reads'' WHERE Pattern IS NULL OPTION(RECOMPILE);
6017
6018 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6019
6020 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6021 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6022 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6023 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6024
6025 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg writes'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6026
6027 UPDATE #bou_allsort SET Pattern = ''avg writes'' WHERE Pattern IS NULL OPTION(RECOMPILE);
6028
6029 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6030
6031 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6032 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6033 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6034 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6035
6036 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg duration'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6037
6038 UPDATE #bou_allsort SET Pattern = ''avg duration'' WHERE Pattern IS NULL OPTION(RECOMPILE);
6039
6040 SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6041
6042 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6043 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6044 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6045 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6046
6047 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg executions'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6048
6049 UPDATE #bou_allsort SET Pattern = ''avg executions'' WHERE Pattern IS NULL OPTION(RECOMPILE);
6050
6051 ';
6052
6053 IF @MemGrant = 0
6054 BEGIN
6055 IF @ExportToExcel = 1
6056 BEGIN
6057 SET @AllSortSql += N' UPDATE #bou_allsort
6058 SET
6059 QueryPlan = NULL,
6060 implicit_conversion_info = NULL,
6061 cached_execution_parameters = NULL,
6062 missing_indexes = NULL
6063 OPTION (RECOMPILE);
6064
6065 UPDATE ##bou_BlitzCacheProcs
6066 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
6067 OPTION(RECOMPILE);';
6068 END;
6069 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6070 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6071 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6072 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
6073 FROM #bou_allsort
6074 ORDER BY Id
6075 OPTION(RECOMPILE); ';
6076 END;
6077
6078 IF @MemGrant = 1
6079 BEGIN
6080 SET @AllSortSql += N' SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6081
6082 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6083 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6084 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6085 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6086
6087 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg memory grant'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6088
6089 UPDATE #bou_allsort SET Pattern = ''avg memory grant'' WHERE Pattern IS NULL OPTION(RECOMPILE);';
6090 IF @ExportToExcel = 1
6091 BEGIN
6092 SET @AllSortSql += N' UPDATE #bou_allsort
6093 SET
6094 QueryPlan = NULL,
6095 implicit_conversion_info = NULL,
6096 cached_execution_parameters = NULL,
6097 missing_indexes = NULL
6098 OPTION (RECOMPILE);
6099
6100 UPDATE ##bou_BlitzCacheProcs
6101 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
6102 OPTION(RECOMPILE);';
6103 END;
6104 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6105 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6106 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6107 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
6108 FROM #bou_allsort
6109 ORDER BY Id
6110 OPTION(RECOMPILE); ';
6111 END;
6112
6113 IF @Spills = 0
6114 BEGIN
6115 IF @ExportToExcel = 1
6116 BEGIN
6117 SET @AllSortSql += N' UPDATE #bou_allsort
6118 SET
6119 QueryPlan = NULL,
6120 implicit_conversion_info = NULL,
6121 cached_execution_parameters = NULL,
6122 missing_indexes = NULL
6123 OPTION (RECOMPILE);
6124
6125 UPDATE ##bou_BlitzCacheProcs
6126 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
6127 OPTION(RECOMPILE);';
6128 END;
6129 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6130 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6131 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6132 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
6133 FROM #bou_allsort
6134 ORDER BY Id
6135 OPTION(RECOMPILE); ';
6136 END;
6137
6138 IF @Spills = 1
6139 BEGIN
6140 SET @AllSortSql += N' SELECT TOP 1 @ISH = STUFF((SELECT DISTINCT N'','' + CONVERT(NVARCHAR(MAX),b2.SqlHandle, 1) FROM #bou_allsort AS b2 FOR XML PATH(N''''), TYPE).value(N''.[1]'', N''NVARCHAR(MAX)''), 1, 1, N'''') OPTION(RECOMPILE);
6141
6142 INSERT #bou_allsort ( DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters, ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6143 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6144 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6145 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions )
6146
6147 EXEC sp_BlitzCache @ExpertMode = 0, @HideSummary = 1, @Top = @i_Top, @SortOrder = ''avg spills'', @IgnoreSqlHandles = @ISH, @DatabaseName = @i_DatabaseName WITH RECOMPILE;
6148
6149 UPDATE #bou_allsort SET Pattern = ''avg memory grant'' WHERE Pattern IS NULL OPTION(RECOMPILE);';
6150 IF @ExportToExcel = 1
6151 BEGIN
6152 SET @AllSortSql += N' UPDATE #bou_allsort
6153 SET
6154 QueryPlan = NULL,
6155 implicit_conversion_info = NULL,
6156 cached_execution_parameters = NULL,
6157 missing_indexes = NULL
6158 OPTION (RECOMPILE);
6159
6160 UPDATE ##bou_BlitzCacheProcs
6161 SET QueryText = SUBSTRING(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(QueryText)),'' '',''<>''),''><'',''''),''<>'','' ''), 1, 32000)
6162 OPTION(RECOMPILE);';
6163 END;
6164 SET @AllSortSql += N' SELECT DatabaseName, Cost, QueryText, QueryType, Warnings, QueryPlan, missing_indexes, implicit_conversion_info, cached_execution_parameters,ExecutionCount, ExecutionsPerMinute, ExecutionWeight,
6165 TotalCPU, AverageCPU, CPUWeight, TotalDuration, AverageDuration, DurationWeight, TotalReads, AverageReads,
6166 ReadWeight, TotalWrites, AverageWrites, WriteWeight, AverageReturnedRows, MinGrantKB, MaxGrantKB, MinUsedGrantKB,
6167 MaxUsedGrantKB, AvgMaxMemoryGrant, MinSpills, MaxSpills, TotalSpills, AvgSpills, PlanCreationTime, LastExecutionTime, PlanHandle, SqlHandle, SetOptions
6168 FROM #bou_allsort
6169 ORDER BY Id
6170 OPTION(RECOMPILE); ';
6171 END;
6172END;
6173
6174 IF @Debug = 1
6175 BEGIN
6176 PRINT SUBSTRING(@AllSortSql, 0, 4000);
6177 PRINT SUBSTRING(@AllSortSql, 4000, 8000);
6178 PRINT SUBSTRING(@AllSortSql, 8000, 12000);
6179 PRINT SUBSTRING(@AllSortSql, 12000, 16000);
6180 PRINT SUBSTRING(@AllSortSql, 16000, 20000);
6181 PRINT SUBSTRING(@AllSortSql, 20000, 24000);
6182 PRINT SUBSTRING(@AllSortSql, 24000, 28000);
6183 PRINT SUBSTRING(@AllSortSql, 28000, 32000);
6184 PRINT SUBSTRING(@AllSortSql, 32000, 36000);
6185 PRINT SUBSTRING(@AllSortSql, 36000, 40000);
6186 END;
6187
6188 EXEC sys.sp_executesql @stmt = @AllSortSql, @params = N'@i_DatabaseName NVARCHAR(128), @i_Top INT', @i_DatabaseName = @DatabaseName, @i_Top = @Top;
6189
6190
6191/*End of AllSort section*/
6192
6193END; /*Final End*/
6194
6195GO