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