· 5 years ago · Feb 24, 2020, 02:28 PM
1USE [master]
2GO
3/****** Object: StoredProcedure [dbo].[sp_BlitzIndex] Script Date: 2020-02-24 15:23:57 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9ALTER PROCEDURE [dbo].[sp_BlitzIndex]
10 @DatabaseName NVARCHAR(128) = NULL, /*Defaults to current DB if not specified*/
11 @SchemaName NVARCHAR(128) = NULL, /*Requires table_name as well.*/
12 @TableName NVARCHAR(128) = NULL, /*Requires schema_name as well.*/
13 @Mode TINYINT=0, /*0=Diagnose, 1=Summarize, 2=Index Usage Detail, 3=Missing Index Detail, 4=Diagnose Details*/
14 /*Note:@Mode doesn't matter if you're specifying schema_name and @TableName.*/
15 @Filter TINYINT = 0, /* 0=no filter (default). 1=No low-usage warnings for objects with 0 reads. 2=Only warn for objects >= 500MB */
16 /*Note:@Filter doesn't do anything unless @Mode=0*/
17 @SkipPartitions BIT = 0,
18 @SkipStatistics BIT = 1,
19 @GetAllDatabases BIT = 0,
20 @BringThePain BIT = 0,
21 @IgnoreDatabases NVARCHAR(MAX) = NULL, /* Comma-delimited list of databases you want to skip */
22 @ThresholdMB INT = 250 /* Number of megabytes that an object must be before we include it in basic results */,
23 @OutputType VARCHAR(20) = 'TABLE' ,
24 @OutputServerName NVARCHAR(256) = NULL ,
25 @OutputDatabaseName NVARCHAR(256) = NULL ,
26 @OutputSchemaName NVARCHAR(256) = NULL ,
27 @OutputTableName NVARCHAR(256) = NULL ,
28 @Help TINYINT = 0,
29 @Version VARCHAR(30) = NULL OUTPUT,
30 @VersionDate DATETIME = NULL OUTPUT,
31 @VersionCheckMode BIT = 0
32WITH RECOMPILE
33AS
34SET NOCOUNT ON;
35SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
36
37SELECT @Version = '7.9', @VersionDate = '20191024';
38SET @OutputType = UPPER(@OutputType);
39
40IF(@VersionCheckMode = 1)
41BEGIN
42 RETURN;
43END;
44
45IF @Help = 1 PRINT '
46/*
47sp_BlitzIndex from http://FirstResponderKit.org
48
49This script analyzes the design and performance of your indexes.
50
51To learn more, visit http://FirstResponderKit.org where you can download new
52versions for free, watch training videos on how it works, get more info on
53the findings, contribute your own code, and more.
54
55Known limitations of this version:
56 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
57 - The @OutputDatabaseName parameters are not functional yet. To check the
58 status of this enhancement request, visit:
59 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/221
60 - Does not analyze columnstore, spatial, XML, or full text indexes. If you
61 would like to contribute code to analyze those, head over to Github and
62 check out the issues list: http://FirstResponderKit.org
63 - Index create statements are just to give you a rough idea of the syntax. It includes filters and fillfactor.
64 -- Example 1: index creates use ONLINE=? instead of ONLINE=ON / ONLINE=OFF. This is because it is important
65 for the user to understand if it is going to be offline and not just run a script.
66 -- Example 2: they do not include all the options the index may have been created with (padding, compression
67 filegroup/partition scheme etc.)
68 -- (The compression and filegroup index create syntax is not trivial because it is set at the partition
69 level and is not trivial to code.)
70 - Does not advise you about data modeling for clustered indexes and primary keys (primarily looks for signs of insanity.)
71
72Unknown limitations of this version:
73 - We knew them once, but we forgot.
74
75
76MIT License
77
78Copyright (c) 2019 Brent Ozar Unlimited
79
80Permission is hereby granted, free of charge, to any person obtaining a copy
81of this software and associated documentation files (the "Software"), to deal
82in the Software without restriction, including without limitation the rights
83to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
84copies of the Software, and to permit persons to whom the Software is
85furnished to do so, subject to the following conditions:
86
87The above copyright notice and this permission notice shall be included in all
88copies or substantial portions of the Software.
89
90THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
91IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
92FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
93AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
94LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
95OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
96SOFTWARE.
97';
98
99
100DECLARE @ScriptVersionName NVARCHAR(50);
101DECLARE @DaysUptime NUMERIC(23,2);
102DECLARE @DatabaseID INT;
103DECLARE @ObjectID INT;
104DECLARE @dsql NVARCHAR(MAX);
105DECLARE @params NVARCHAR(MAX);
106DECLARE @msg NVARCHAR(4000);
107DECLARE @ErrorSeverity INT;
108DECLARE @ErrorState INT;
109DECLARE @Rowcount BIGINT;
110DECLARE @SQLServerProductVersion NVARCHAR(128);
111DECLARE @SQLServerEdition INT;
112DECLARE @FilterMB INT;
113DECLARE @collation NVARCHAR(256);
114DECLARE @NumDatabases INT;
115DECLARE @LineFeed NVARCHAR(5);
116DECLARE @DaysUptimeInsertValue NVARCHAR(256);
117DECLARE @DatabaseToIgnore NVARCHAR(MAX);
118
119SET @LineFeed = CHAR(13) + CHAR(10);
120SELECT @SQLServerProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
121SELECT @SQLServerEdition =CAST(SERVERPROPERTY('EngineEdition') AS INT); /* We default to online index creates where EngineEdition=3*/
122SET @FilterMB=250;
123SELECT @ScriptVersionName = 'sp_BlitzIndex(TM) v' + @Version + ' - ' + DATENAME(MM, @VersionDate) + ' ' + RIGHT('0'+DATENAME(DD, @VersionDate),2) + ', ' + DATENAME(YY, @VersionDate);
124SET @IgnoreDatabases = REPLACE(REPLACE(LTRIM(RTRIM(@IgnoreDatabases)), CHAR(10), ''), CHAR(13), '');
125
126RAISERROR(N'Starting run. %s', 0,1, @ScriptVersionName) WITH NOWAIT;
127
128IF(@OutputType NOT IN ('TABLE','NONE'))
129BEGIN
130 RAISERROR('Invalid value for parameter @OutputType. Expected: (TABLE;NONE)',12,1);
131 RETURN;
132END;
133
134IF(@OutputType = 'NONE')
135BEGIN
136 IF(@OutputTableName IS NULL OR @OutputSchemaName IS NULL OR @OutputDatabaseName IS NULL)
137 BEGIN
138 RAISERROR('This procedure should be called with a value for @Output* parameters, as @OutputType is set to NONE',12,1);
139 RETURN;
140 END;
141 IF(@BringThePain = 1)
142 BEGIN
143 RAISERROR('Incompatible Parameters: @BringThePain set to 1 and @OutputType set to NONE',12,1);
144 RETURN;
145 END;
146 /* Eventually limit by mode
147 IF(@Mode not in (0,4))
148 BEGIN
149 RAISERROR('Incompatible Parameters: @Mode set to %d and @OutputType set to NONE',12,1,@Mode);
150 RETURN;
151 END;
152 */
153END;
154
155IF OBJECT_ID('tempdb..#IndexSanity') IS NOT NULL
156 DROP TABLE #IndexSanity;
157
158IF OBJECT_ID('tempdb..#IndexPartitionSanity') IS NOT NULL
159 DROP TABLE #IndexPartitionSanity;
160
161IF OBJECT_ID('tempdb..#IndexSanitySize') IS NOT NULL
162 DROP TABLE #IndexSanitySize;
163
164IF OBJECT_ID('tempdb..#IndexColumns') IS NOT NULL
165 DROP TABLE #IndexColumns;
166
167IF OBJECT_ID('tempdb..#MissingIndexes') IS NOT NULL
168 DROP TABLE #MissingIndexes;
169
170IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
171 DROP TABLE #ForeignKeys;
172
173IF OBJECT_ID('tempdb..#BlitzIndexResults') IS NOT NULL
174 DROP TABLE #BlitzIndexResults;
175
176IF OBJECT_ID('tempdb..#IndexCreateTsql') IS NOT NULL
177 DROP TABLE #IndexCreateTsql;
178
179IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
180 DROP TABLE #DatabaseList;
181
182IF OBJECT_ID('tempdb..#Statistics') IS NOT NULL
183 DROP TABLE #Statistics;
184
185IF OBJECT_ID('tempdb..#PartitionCompressionInfo') IS NOT NULL
186 DROP TABLE #PartitionCompressionInfo;
187
188IF OBJECT_ID('tempdb..#ComputedColumns') IS NOT NULL
189 DROP TABLE #ComputedColumns;
190
191IF OBJECT_ID('tempdb..#TraceStatus') IS NOT NULL
192 DROP TABLE #TraceStatus;
193
194IF OBJECT_ID('tempdb..#TemporalTables') IS NOT NULL
195 DROP TABLE #TemporalTables;
196
197IF OBJECT_ID('tempdb..#CheckConstraints') IS NOT NULL
198 DROP TABLE #CheckConstraints;
199
200IF OBJECT_ID('tempdb..#FilteredIndexes') IS NOT NULL
201 DROP TABLE #FilteredIndexes;
202
203IF OBJECT_ID('tempdb..#Ignore_Databases') IS NOT NULL
204 DROP TABLE #Ignore_Databases
205
206 RAISERROR (N'Create temp tables.',0,1) WITH NOWAIT;
207 CREATE TABLE #BlitzIndexResults
208 (
209 blitz_result_id INT IDENTITY PRIMARY KEY,
210 check_id INT NOT NULL,
211 index_sanity_id INT NULL,
212 Priority INT NULL,
213 findings_group NVARCHAR(4000) NOT NULL,
214 finding NVARCHAR(200) NOT NULL,
215 [database_name] NVARCHAR(128) NULL,
216 URL NVARCHAR(200) NOT NULL,
217 details NVARCHAR(MAX) NOT NULL,
218 index_definition NVARCHAR(MAX) NOT NULL,
219 secret_columns NVARCHAR(MAX) NULL,
220 index_usage_summary NVARCHAR(MAX) NULL,
221 index_size_summary NVARCHAR(MAX) NULL,
222 create_tsql NVARCHAR(MAX) NULL,
223 more_info NVARCHAR(MAX)NULL
224 );
225
226 CREATE TABLE #IndexSanity
227 (
228 [index_sanity_id] INT IDENTITY PRIMARY KEY CLUSTERED,
229 [database_id] SMALLINT NOT NULL ,
230 [object_id] INT NOT NULL ,
231 [index_id] INT NOT NULL ,
232 [index_type] TINYINT NOT NULL,
233 [database_name] NVARCHAR(128) NOT NULL ,
234 [schema_name] NVARCHAR(128) NOT NULL ,
235 [object_name] NVARCHAR(128) NOT NULL ,
236 index_name NVARCHAR(128) NULL ,
237 key_column_names NVARCHAR(MAX) NULL ,
238 key_column_names_with_sort_order NVARCHAR(MAX) NULL ,
239 key_column_names_with_sort_order_no_types NVARCHAR(MAX) NULL ,
240 count_key_columns INT NULL ,
241 include_column_names NVARCHAR(MAX) NULL ,
242 include_column_names_no_types NVARCHAR(MAX) NULL ,
243 count_included_columns INT NULL ,
244 partition_key_column_name NVARCHAR(MAX) NULL,
245 filter_definition NVARCHAR(MAX) NOT NULL ,
246 is_indexed_view BIT NOT NULL ,
247 is_unique BIT NOT NULL ,
248 is_primary_key BIT NOT NULL ,
249 is_XML BIT NOT NULL,
250 is_spatial BIT NOT NULL,
251 is_NC_columnstore BIT NOT NULL,
252 is_CX_columnstore BIT NOT NULL,
253 is_disabled BIT NOT NULL ,
254 is_hypothetical BIT NOT NULL ,
255 is_padded BIT NOT NULL ,
256 fill_factor SMALLINT NOT NULL ,
257 user_seeks BIGINT NOT NULL ,
258 user_scans BIGINT NOT NULL ,
259 user_lookups BIGINT NOT NULL ,
260 user_updates BIGINT NULL ,
261 last_user_seek DATETIME NULL ,
262 last_user_scan DATETIME NULL ,
263 last_user_lookup DATETIME NULL ,
264 last_user_update DATETIME NULL ,
265 is_referenced_by_foreign_key BIT DEFAULT(0),
266 secret_columns NVARCHAR(MAX) NULL,
267 count_secret_columns INT NULL,
268 create_date DATETIME NOT NULL,
269 modify_date DATETIME NOT NULL,
270 filter_columns_not_in_index NVARCHAR(MAX),
271 [db_schema_object_name] AS [schema_name] + N'.' + [object_name] ,
272 [db_schema_object_indexid] AS [schema_name] + N'.' + [object_name]
273 + CASE WHEN [index_name] IS NOT NULL THEN N'.' + index_name
274 ELSE N''
275 END + N' (' + CAST(index_id AS NVARCHAR(20)) + N')' ,
276 first_key_column_name AS CASE WHEN count_key_columns > 1
277 THEN LEFT(key_column_names, CHARINDEX(',', key_column_names, 0) - 1)
278 ELSE key_column_names
279 END ,
280 index_definition AS
281 CASE WHEN partition_key_column_name IS NOT NULL
282 THEN N'[PARTITIONED BY:' + partition_key_column_name + N']'
283 ELSE ''
284 END +
285 CASE index_id
286 WHEN 0 THEN N'[HEAP] '
287 WHEN 1 THEN N'[CX] '
288 ELSE N'' END + CASE WHEN is_indexed_view = 1 THEN N'[VIEW] '
289 ELSE N'' END + CASE WHEN is_primary_key = 1 THEN N'[PK] '
290 ELSE N'' END + CASE WHEN is_XML = 1 THEN N'[XML] '
291 ELSE N'' END + CASE WHEN is_spatial = 1 THEN N'[SPATIAL] '
292 ELSE N'' END + CASE WHEN is_NC_columnstore = 1 THEN N'[COLUMNSTORE] '
293 ELSE N'' END + CASE WHEN is_disabled = 1 THEN N'[DISABLED] '
294 ELSE N'' END + CASE WHEN is_hypothetical = 1 THEN N'[HYPOTHETICAL] '
295 ELSE N'' END + CASE WHEN is_unique = 1 AND is_primary_key = 0 THEN N'[UNIQUE] '
296 ELSE N'' END + CASE WHEN count_key_columns > 0 THEN
297 N'[' + CAST(count_key_columns AS NVARCHAR(10)) + N' KEY'
298 + CASE WHEN count_key_columns > 1 THEN N'S' ELSE N'' END
299 + N'] ' + LTRIM(key_column_names_with_sort_order)
300 ELSE N'' END + CASE WHEN count_included_columns > 0 THEN
301 N' [' + CAST(count_included_columns AS NVARCHAR(10)) + N' INCLUDE' +
302 + CASE WHEN count_included_columns > 1 THEN N'S' ELSE N'' END
303 + N'] ' + include_column_names
304 ELSE N'' END + CASE WHEN filter_definition <> N'' THEN N' [FILTER] ' + filter_definition
305 ELSE N'' END ,
306 [total_reads] AS user_seeks + user_scans + user_lookups,
307 [reads_per_write] AS CAST(CASE WHEN user_updates > 0
308 THEN ( user_seeks + user_scans + user_lookups ) / (1.0 * user_updates)
309 ELSE 0 END AS MONEY) ,
310 [index_usage_summary] AS N'Reads: ' +
311 REPLACE(CONVERT(NVARCHAR(30),CAST((user_seeks + user_scans + user_lookups) AS MONEY), 1), N'.00', N'')
312 + CASE WHEN user_seeks + user_scans + user_lookups > 0 THEN
313 N' ('
314 + RTRIM(
315 CASE WHEN user_seeks > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_seeks) AS MONEY), 1), N'.00', N'') + N' seek ' ELSE N'' END
316 + CASE WHEN user_scans > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_scans) AS MONEY), 1), N'.00', N'') + N' scan ' ELSE N'' END
317 + CASE WHEN user_lookups > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_lookups) AS MONEY), 1), N'.00', N'') + N' lookup' ELSE N'' END
318 )
319 + N') '
320 ELSE N' ' END
321 + N'Writes:' +
322 REPLACE(CONVERT(NVARCHAR(30),CAST(user_updates AS MONEY), 1), N'.00', N''),
323 [more_info] AS N'EXEC dbo.sp_BlitzIndex @DatabaseName=' + QUOTENAME([database_name],N'''') +
324 N', @SchemaName=' + QUOTENAME([schema_name],N'''') + N', @TableName=' + QUOTENAME([object_name],N'''') + N';'
325 );
326 RAISERROR (N'Adding UQ index on #IndexSanity (database_id, object_id, index_id)',0,1) WITH NOWAIT;
327 IF NOT EXISTS(SELECT 1 FROM tempdb.sys.indexes WHERE name='uq_database_id_object_id_index_id')
328 CREATE UNIQUE INDEX uq_database_id_object_id_index_id ON #IndexSanity (database_id, object_id, index_id);
329
330
331 CREATE TABLE #IndexPartitionSanity
332 (
333 [index_partition_sanity_id] INT IDENTITY,
334 [index_sanity_id] INT NULL ,
335 [database_id] INT NOT NULL ,
336 [object_id] INT NOT NULL ,
337 [schema_name] NVARCHAR(128) NOT NULL,
338 [index_id] INT NOT NULL ,
339 [partition_number] INT NOT NULL ,
340 row_count BIGINT NOT NULL ,
341 reserved_MB NUMERIC(29,2) NOT NULL ,
342 reserved_LOB_MB NUMERIC(29,2) NOT NULL ,
343 reserved_row_overflow_MB NUMERIC(29,2) NOT NULL ,
344 leaf_insert_count BIGINT NULL ,
345 leaf_delete_count BIGINT NULL ,
346 leaf_update_count BIGINT NULL ,
347 range_scan_count BIGINT NULL ,
348 singleton_lookup_count BIGINT NULL ,
349 forwarded_fetch_count BIGINT NULL ,
350 lob_fetch_in_pages BIGINT NULL ,
351 lob_fetch_in_bytes BIGINT NULL ,
352 row_overflow_fetch_in_pages BIGINT NULL ,
353 row_overflow_fetch_in_bytes BIGINT NULL ,
354 row_lock_count BIGINT NULL ,
355 row_lock_wait_count BIGINT NULL ,
356 row_lock_wait_in_ms BIGINT NULL ,
357 page_lock_count BIGINT NULL ,
358 page_lock_wait_count BIGINT NULL ,
359 page_lock_wait_in_ms BIGINT NULL ,
360 index_lock_promotion_attempt_count BIGINT NULL ,
361 index_lock_promotion_count BIGINT NULL,
362 data_compression_desc NVARCHAR(60) NULL,
363 page_latch_wait_count BIGINT NULL,
364 page_latch_wait_in_ms BIGINT NULL,
365 page_io_latch_wait_count BIGINT NULL,
366 page_io_latch_wait_in_ms BIGINT NULL
367 );
368
369 CREATE TABLE #IndexSanitySize
370 (
371 [index_sanity_size_id] INT IDENTITY NOT NULL ,
372 [index_sanity_id] INT NULL ,
373 [database_id] INT NOT NULL,
374 [schema_name] NVARCHAR(128) NOT NULL,
375 partition_count INT NOT NULL ,
376 total_rows BIGINT NOT NULL ,
377 total_reserved_MB NUMERIC(29,2) NOT NULL ,
378 total_reserved_LOB_MB NUMERIC(29,2) NOT NULL ,
379 total_reserved_row_overflow_MB NUMERIC(29,2) NOT NULL ,
380 total_leaf_delete_count BIGINT NULL,
381 total_leaf_update_count BIGINT NULL,
382 total_range_scan_count BIGINT NULL,
383 total_singleton_lookup_count BIGINT NULL,
384 total_forwarded_fetch_count BIGINT NULL,
385 total_row_lock_count BIGINT NULL ,
386 total_row_lock_wait_count BIGINT NULL ,
387 total_row_lock_wait_in_ms BIGINT NULL ,
388 avg_row_lock_wait_in_ms BIGINT NULL ,
389 total_page_lock_count BIGINT NULL ,
390 total_page_lock_wait_count BIGINT NULL ,
391 total_page_lock_wait_in_ms BIGINT NULL ,
392 avg_page_lock_wait_in_ms BIGINT NULL ,
393 total_index_lock_promotion_attempt_count BIGINT NULL ,
394 total_index_lock_promotion_count BIGINT NULL ,
395 data_compression_desc NVARCHAR(4000) NULL,
396 page_latch_wait_count BIGINT NULL,
397 page_latch_wait_in_ms BIGINT NULL,
398 page_io_latch_wait_count BIGINT NULL,
399 page_io_latch_wait_in_ms BIGINT NULL,
400 index_size_summary AS ISNULL(
401 CASE WHEN partition_count > 1
402 THEN N'[' + CAST(partition_count AS NVARCHAR(10)) + N' PARTITIONS] '
403 ELSE N''
404 END + REPLACE(CONVERT(NVARCHAR(30),CAST([total_rows] AS MONEY), 1), N'.00', N'') + N' rows; '
405 + CASE WHEN total_reserved_MB > 1024 THEN
406 CAST(CAST(total_reserved_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB'
407 ELSE
408 CAST(CAST(total_reserved_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB'
409 END
410 + CASE WHEN total_reserved_LOB_MB > 1024 THEN
411 N'; ' + CAST(CAST(total_reserved_LOB_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB LOB'
412 WHEN total_reserved_LOB_MB > 0 THEN
413 N'; ' + CAST(CAST(total_reserved_LOB_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB LOB'
414 ELSE ''
415 END
416 + CASE WHEN total_reserved_row_overflow_MB > 1024 THEN
417 N'; ' + CAST(CAST(total_reserved_row_overflow_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB Row Overflow'
418 WHEN total_reserved_row_overflow_MB > 0 THEN
419 N'; ' + CAST(CAST(total_reserved_row_overflow_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB Row Overflow'
420 ELSE ''
421 END ,
422 N'Error- NULL in computed column'),
423 index_op_stats AS ISNULL(
424 (
425 REPLACE(CONVERT(NVARCHAR(30),CAST(total_singleton_lookup_count AS MONEY), 1),N'.00',N'') + N' singleton lookups; '
426 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_range_scan_count AS MONEY), 1),N'.00',N'') + N' scans/seeks; '
427 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_leaf_delete_count AS MONEY), 1),N'.00',N'') + N' deletes; '
428 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_leaf_update_count AS MONEY), 1),N'.00',N'') + N' updates; '
429 + CASE WHEN ISNULL(total_forwarded_fetch_count,0) >0 THEN
430 REPLACE(CONVERT(NVARCHAR(30),CAST(total_forwarded_fetch_count AS MONEY), 1),N'.00',N'') + N' forward records fetched; '
431 ELSE N'' END
432
433 /* rows will only be in this dmv when data is in memory for the table */
434 ), N'Table metadata not in memory'),
435 index_lock_wait_summary AS ISNULL(
436 CASE WHEN total_row_lock_wait_count = 0 AND total_page_lock_wait_count = 0 AND
437 total_index_lock_promotion_attempt_count = 0 THEN N'0 lock waits.'
438 ELSE
439 CASE WHEN total_row_lock_wait_count > 0 THEN
440 N'Row lock waits: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_row_lock_wait_count AS MONEY), 1), N'.00', N'')
441 + N'; total duration: ' +
442 CASE WHEN total_row_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
443 REPLACE(CONVERT(NVARCHAR(30),CAST((total_row_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
444 ELSE
445 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_row_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
446 END
447 + N'avg duration: ' +
448 CASE WHEN avg_row_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
449 REPLACE(CONVERT(NVARCHAR(30),CAST((avg_row_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
450 ELSE
451 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(avg_row_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
452 END
453 ELSE N''
454 END +
455 CASE WHEN total_page_lock_wait_count > 0 THEN
456 N'Page lock waits: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_page_lock_wait_count AS MONEY), 1), N'.00', N'')
457 + N'; total duration: ' +
458 CASE WHEN total_page_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
459 REPLACE(CONVERT(NVARCHAR(30),CAST((total_page_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
460 ELSE
461 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_page_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
462 END
463 + N'avg duration: ' +
464 CASE WHEN avg_page_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
465 REPLACE(CONVERT(NVARCHAR(30),CAST((avg_page_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
466 ELSE
467 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(avg_page_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
468 END
469 ELSE N''
470 END +
471 CASE WHEN total_index_lock_promotion_attempt_count > 0 THEN
472 N'Lock escalation attempts: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_index_lock_promotion_attempt_count AS MONEY), 1), N'.00', N'')
473 + N'; Actual Escalations: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_index_lock_promotion_count,0) AS MONEY), 1), N'.00', N'') + N'.'
474 ELSE N''
475 END
476 END
477 ,'Error- NULL in computed column')
478 );
479
480 CREATE TABLE #IndexColumns
481 (
482 [database_id] INT NOT NULL,
483 [schema_name] NVARCHAR(128),
484 [object_id] INT NOT NULL ,
485 [index_id] INT NOT NULL ,
486 [key_ordinal] INT NULL ,
487 is_included_column BIT NULL ,
488 is_descending_key BIT NULL ,
489 [partition_ordinal] INT NULL ,
490 column_name NVARCHAR(256) NOT NULL ,
491 system_type_name NVARCHAR(256) NOT NULL,
492 max_length SMALLINT NOT NULL,
493 [precision] TINYINT NOT NULL,
494 [scale] TINYINT NOT NULL,
495 collation_name NVARCHAR(256) NULL,
496 is_nullable BIT NULL,
497 is_identity BIT NULL,
498 is_computed BIT NULL,
499 is_replicated BIT NULL,
500 is_sparse BIT NULL,
501 is_filestream BIT NULL,
502 seed_value BIGINT NULL,
503 increment_value INT NULL ,
504 last_value BIGINT NULL,
505 is_not_for_replication BIT NULL
506 );
507 CREATE CLUSTERED INDEX CLIX_database_id_object_id_index_id ON #IndexColumns
508 (database_id, object_id, index_id);
509
510 CREATE TABLE #MissingIndexes
511 ([database_id] INT NOT NULL,
512 [object_id] INT NOT NULL,
513 [database_name] NVARCHAR(128) NOT NULL ,
514 [schema_name] NVARCHAR(128) NOT NULL ,
515 [table_name] NVARCHAR(128),
516 [statement] NVARCHAR(512) NOT NULL,
517 magic_benefit_number AS (( user_seeks + user_scans ) * avg_total_user_cost * avg_user_impact),
518 avg_total_user_cost NUMERIC(29,4) NOT NULL,
519 avg_user_impact NUMERIC(29,1) NOT NULL,
520 user_seeks BIGINT NOT NULL,
521 user_scans BIGINT NOT NULL,
522 unique_compiles BIGINT NULL,
523 equality_columns NVARCHAR(4000),
524 inequality_columns NVARCHAR(4000),
525 included_columns NVARCHAR(4000),
526 is_low BIT,
527 [index_estimated_impact] AS
528 REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
529 (user_seeks + user_scans)
530 AS BIGINT) AS MONEY), 1), '.00', '') + N' use'
531 + CASE WHEN (user_seeks + user_scans) > 1 THEN N's' ELSE N'' END
532 +N'; Impact: ' + CAST(avg_user_impact AS NVARCHAR(30))
533 + N'%; Avg query cost: '
534 + CAST(avg_total_user_cost AS NVARCHAR(30)),
535 [missing_index_details] AS
536 CASE WHEN equality_columns IS NOT NULL THEN N'EQUALITY: ' + equality_columns + N' '
537 ELSE N''
538 END + CASE WHEN inequality_columns IS NOT NULL THEN N'INEQUALITY: ' + inequality_columns + N' '
539 ELSE N''
540 END + CASE WHEN included_columns IS NOT NULL THEN N'INCLUDES: ' + included_columns + N' '
541 ELSE N''
542 END,
543 [create_tsql] AS N'CREATE INDEX [IX_'
544 + REPLACE(REPLACE(REPLACE(REPLACE(
545 ISNULL(equality_columns,N'')+
546 CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
547 + ISNULL(inequality_columns,''),',','')
548 ,'[',''),']',''),' ','_')
549 + CASE WHEN included_columns IS NOT NULL THEN N'_Includes' ELSE N'' END + N'] ON '
550 + [statement] + N' (' + ISNULL(equality_columns,N'')
551 + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
552 + CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END +
553 ') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
554 + N' WITH ('
555 + N'FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?'
556 + N')'
557 + N';'
558 ,
559 [more_info] AS N'EXEC dbo.sp_BlitzIndex @DatabaseName=' + QUOTENAME([database_name],'''') +
560 N', @SchemaName=' + QUOTENAME([schema_name],'''') + N', @TableName=' + QUOTENAME([table_name],'''') + N';'
561 );
562
563 CREATE TABLE #ForeignKeys (
564 [database_id] INT NOT NULL,
565 [database_name] NVARCHAR(128) NOT NULL ,
566 [schema_name] NVARCHAR(128) NOT NULL ,
567 foreign_key_name NVARCHAR(256),
568 parent_object_id INT,
569 parent_object_name NVARCHAR(256),
570 referenced_object_id INT,
571 referenced_object_name NVARCHAR(256),
572 is_disabled BIT,
573 is_not_trusted BIT,
574 is_not_for_replication BIT,
575 parent_fk_columns NVARCHAR(MAX),
576 referenced_fk_columns NVARCHAR(MAX),
577 update_referential_action_desc NVARCHAR(16),
578 delete_referential_action_desc NVARCHAR(60)
579 );
580
581 CREATE TABLE #IndexCreateTsql (
582 index_sanity_id INT NOT NULL,
583 create_tsql NVARCHAR(MAX) NOT NULL
584 );
585
586 CREATE TABLE #DatabaseList (
587 DatabaseName NVARCHAR(256),
588 secondary_role_allow_connections_desc NVARCHAR(50)
589
590 );
591
592 CREATE TABLE #PartitionCompressionInfo (
593 [index_sanity_id] INT NULL,
594 [partition_compression_detail] NVARCHAR(4000) NULL
595 );
596
597 CREATE TABLE #Statistics (
598 database_id INT NOT NULL,
599 database_name NVARCHAR(256) NOT NULL,
600 table_name NVARCHAR(128) NULL,
601 schema_name NVARCHAR(128) NULL,
602 index_name NVARCHAR(128) NULL,
603 column_names NVARCHAR(MAX) NULL,
604 statistics_name NVARCHAR(128) NULL,
605 last_statistics_update DATETIME NULL,
606 days_since_last_stats_update INT NULL,
607 rows BIGINT NULL,
608 rows_sampled BIGINT NULL,
609 percent_sampled DECIMAL(18, 1) NULL,
610 histogram_steps INT NULL,
611 modification_counter BIGINT NULL,
612 percent_modifications DECIMAL(18, 1) NULL,
613 modifications_before_auto_update INT NULL,
614 index_type_desc NVARCHAR(128) NULL,
615 table_create_date DATETIME NULL,
616 table_modify_date DATETIME NULL,
617 no_recompute BIT NULL,
618 has_filter BIT NULL,
619 filter_definition NVARCHAR(MAX) NULL
620 );
621
622 CREATE TABLE #ComputedColumns
623 (
624 index_sanity_id INT IDENTITY(1, 1) NOT NULL,
625 database_name NVARCHAR(128) NULL,
626 database_id INT NOT NULL,
627 table_name NVARCHAR(128) NOT NULL,
628 schema_name NVARCHAR(128) NOT NULL,
629 column_name NVARCHAR(128) NULL,
630 is_nullable BIT NULL,
631 definition NVARCHAR(MAX) NULL,
632 uses_database_collation BIT NOT NULL,
633 is_persisted BIT NOT NULL,
634 is_computed BIT NOT NULL,
635 is_function INT NOT NULL,
636 column_definition NVARCHAR(MAX) NULL
637 );
638
639 CREATE TABLE #TraceStatus
640 (
641 TraceFlag NVARCHAR(10) ,
642 status BIT ,
643 Global BIT ,
644 Session BIT
645 );
646
647 CREATE TABLE #TemporalTables
648 (
649 index_sanity_id INT IDENTITY(1, 1) NOT NULL,
650 database_name NVARCHAR(128) NOT NULL,
651 database_id INT NOT NULL,
652 schema_name NVARCHAR(128) NOT NULL,
653 table_name NVARCHAR(128) NOT NULL,
654 history_table_name NVARCHAR(128) NOT NULL,
655 history_schema_name NVARCHAR(128) NOT NULL,
656 start_column_name NVARCHAR(128) NOT NULL,
657 end_column_name NVARCHAR(128) NOT NULL,
658 period_name NVARCHAR(128) NOT NULL
659 );
660
661 CREATE TABLE #CheckConstraints
662 (
663 index_sanity_id INT IDENTITY(1, 1) NOT NULL,
664 database_name NVARCHAR(128) NULL,
665 database_id INT NOT NULL,
666 table_name NVARCHAR(128) NOT NULL,
667 schema_name NVARCHAR(128) NOT NULL,
668 constraint_name NVARCHAR(128) NULL,
669 is_disabled BIT NULL,
670 definition NVARCHAR(MAX) NULL,
671 uses_database_collation BIT NOT NULL,
672 is_not_trusted BIT NOT NULL,
673 is_function INT NOT NULL,
674 column_definition NVARCHAR(MAX) NULL
675 );
676
677 CREATE TABLE #FilteredIndexes
678 (
679 index_sanity_id INT IDENTITY(1, 1) NOT NULL,
680 database_name NVARCHAR(128) NULL,
681 database_id INT NOT NULL,
682 schema_name NVARCHAR(128) NOT NULL,
683 table_name NVARCHAR(128) NOT NULL,
684 index_name NVARCHAR(128) NULL,
685 column_name NVARCHAR(128) NULL
686 );
687
688 CREATE TABLE #Ignore_Databases
689 (
690 DatabaseName NVARCHAR(128),
691 Reason NVARCHAR(100)
692 );
693
694/* Sanitize our inputs */
695SELECT
696 @OutputServerName = QUOTENAME(@OutputServerName),
697 @OutputDatabaseName = QUOTENAME(@OutputDatabaseName),
698 @OutputSchemaName = QUOTENAME(@OutputSchemaName),
699 @OutputTableName = QUOTENAME(@OutputTableName);
700
701
702IF @GetAllDatabases = 1
703 BEGIN
704 INSERT INTO #DatabaseList (DatabaseName)
705 SELECT DB_NAME(database_id)
706 FROM sys.databases
707 WHERE user_access_desc = 'MULTI_USER'
708 AND state_desc = 'ONLINE'
709 AND database_id > 4
710 AND DB_NAME(database_id) NOT LIKE 'ReportServer%'
711 AND DB_NAME(database_id) NOT LIKE 'rdsadmin%'
712 AND is_distributor = 0
713 OPTION ( RECOMPILE );
714
715 /* Skip non-readable databases in an AG - see Github issue #1160 */
716 IF EXISTS (SELECT * FROM sys.all_objects o INNER JOIN sys.all_columns c ON o.object_id = c.object_id AND o.name = 'dm_hadr_availability_replica_states' AND c.name = 'role_desc')
717 BEGIN
718 SET @dsql = N'UPDATE #DatabaseList SET secondary_role_allow_connections_desc = ''NO'' WHERE DatabaseName IN (
719 SELECT d.name
720 FROM sys.dm_hadr_availability_replica_states rs
721 INNER JOIN sys.databases d ON rs.replica_id = d.replica_id
722 INNER JOIN sys.availability_replicas r ON rs.replica_id = r.replica_id
723 WHERE rs.role_desc = ''SECONDARY''
724 AND r.secondary_role_allow_connections_desc = ''NO'')
725 OPTION ( RECOMPILE );';
726 EXEC sp_executesql @dsql;
727
728 IF EXISTS (SELECT * FROM #DatabaseList WHERE secondary_role_allow_connections_desc = 'NO')
729 BEGIN
730 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, database_name, URL, details, index_definition,
731 index_usage_summary, index_size_summary )
732 VALUES ( 1,
733 0,
734 N'Skipped non-readable AG secondary databases.',
735 N'You are running this on an AG secondary, and some of your databases are configured as non-readable when this is a secondary node.',
736 N'To analyze those databases, run sp_BlitzIndex on the primary, or on a readable secondary.',
737 'http://FirstResponderKit.org', '', '', '', ''
738 );
739 END;
740 END;
741
742 IF @IgnoreDatabases IS NOT NULL
743 AND LEN(@IgnoreDatabases) > 0
744 BEGIN
745 RAISERROR(N'Setting up filter to ignore databases', 0, 1) WITH NOWAIT;
746 SET @DatabaseToIgnore = '';
747
748 WHILE LEN(@IgnoreDatabases) > 0
749 BEGIN
750 IF PATINDEX('%,%', @IgnoreDatabases) > 0
751 BEGIN
752 SET @DatabaseToIgnore = SUBSTRING(@IgnoreDatabases, 0, PATINDEX('%,%',@IgnoreDatabases)) ;
753
754 INSERT INTO #Ignore_Databases (DatabaseName, Reason)
755 SELECT LTRIM(RTRIM(@DatabaseToIgnore)), 'Specified in the @IgnoreDatabases parameter'
756 OPTION (RECOMPILE) ;
757
758 SET @IgnoreDatabases = SUBSTRING(@IgnoreDatabases, LEN(@DatabaseToIgnore + ',') + 1, LEN(@IgnoreDatabases)) ;
759 END;
760 ELSE
761 BEGIN
762 SET @DatabaseToIgnore = @IgnoreDatabases ;
763 SET @IgnoreDatabases = NULL ;
764
765 INSERT INTO #Ignore_Databases (DatabaseName, Reason)
766 SELECT LTRIM(RTRIM(@DatabaseToIgnore)), 'Specified in the @IgnoreDatabases parameter'
767 OPTION (RECOMPILE) ;
768 END;
769 END;
770
771 END
772
773 END;
774ELSE
775 BEGIN
776 INSERT INTO #DatabaseList
777 ( DatabaseName )
778 SELECT CASE
779 WHEN @DatabaseName IS NULL OR @DatabaseName = N''
780 THEN DB_NAME()
781 ELSE @DatabaseName END;
782 END;
783
784SET @NumDatabases = @@ROWCOUNT;
785
786/* Running on 50+ databases can take a reaaallly long time, so we want explicit permission to do so (and only after warning about it) */
787
788BEGIN TRY
789 IF @NumDatabases >= 50 AND @BringThePain != 1 AND @TableName IS NULL
790 BEGIN
791
792 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
793 index_usage_summary, index_size_summary )
794 VALUES ( -1,
795 0 ,
796 @ScriptVersionName,
797 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16), GETDATE(), 121) END,
798 N'From Your Community Volunteers',
799 N'http://FirstResponderKit.org',
800 N'',
801 N'',
802 N''
803 );
804 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, database_name, URL, details, index_definition,
805 index_usage_summary, index_size_summary )
806 VALUES ( 1,
807 0,
808 N'You''re trying to run sp_BlitzIndex on a server with ' + CAST(@NumDatabases AS NVARCHAR(8)) + N' databases. ',
809 N'Running sp_BlitzIndex on a server with 50+ databases may cause temporary insanity for the server and/or user.',
810 N'If you''re sure you want to do this, run again with the parameter @BringThePain = 1.',
811 'http://FirstResponderKit.org',
812 '',
813 '',
814 '',
815 ''
816 );
817
818 if(@OutputType <> 'NONE')
819 BEGIN
820 SELECT bir.blitz_result_id,
821 bir.check_id,
822 bir.index_sanity_id,
823 bir.Priority,
824 bir.findings_group,
825 bir.finding,
826 bir.database_name,
827 bir.URL,
828 bir.details,
829 bir.index_definition,
830 bir.secret_columns,
831 bir.index_usage_summary,
832 bir.index_size_summary,
833 bir.create_tsql,
834 bir.more_info
835 FROM #BlitzIndexResults AS bir;
836 RAISERROR('Running sp_BlitzIndex on a server with 50+ databases may cause temporary insanity for the server', 12, 1);
837 END;
838
839 RETURN;
840
841 END;
842END TRY
843BEGIN CATCH
844 RAISERROR (N'Failure to execute due to number of databases.', 0,1) WITH NOWAIT;
845
846 SELECT @msg = ERROR_MESSAGE(),
847 @ErrorSeverity = ERROR_SEVERITY(),
848 @ErrorState = ERROR_STATE();
849
850 RAISERROR (@msg, @ErrorSeverity, @ErrorState);
851
852 WHILE @@trancount > 0
853 ROLLBACK;
854
855 RETURN;
856 END CATCH;
857
858
859RAISERROR (N'Checking partition counts to exclude databases with over 100 partitions',0,1) WITH NOWAIT;
860IF @BringThePain = 0 AND @SkipPartitions = 0 AND @TableName IS NULL
861 BEGIN
862 DECLARE partition_cursor CURSOR FOR
863 SELECT dl.DatabaseName
864 FROM #DatabaseList dl
865 LEFT OUTER JOIN #Ignore_Databases i ON dl.DatabaseName = i.DatabaseName
866 WHERE COALESCE(dl.secondary_role_allow_connections_desc, 'OK') <> 'NO'
867 AND i.DatabaseName IS NULL
868
869 OPEN partition_cursor
870 FETCH NEXT FROM partition_cursor INTO @DatabaseName
871
872 WHILE @@FETCH_STATUS = 0
873 BEGIN
874 /* Count the total number of partitions */
875 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
876 SELECT @RowcountOUT = SUM(1) FROM ' + QUOTENAME(@DatabaseName) + '.sys.partitions WHERE partition_number > 1 OPTION ( RECOMPILE );';
877 EXEC sp_executesql @dsql, N'@RowcountOUT BIGINT OUTPUT', @RowcountOUT = @Rowcount OUTPUT;
878 IF @Rowcount > 100
879 BEGIN
880 RAISERROR (N'Skipping database %s because > 100 partitions were found. To check this database, you must set @BringThePain = 1.',0,1,@DatabaseName) WITH NOWAIT;
881 INSERT INTO #Ignore_Databases (DatabaseName, Reason)
882 SELECT @DatabaseName, 'Over 100 partitions found - use @BringThePain = 1 to analyze'
883 END;
884 FETCH NEXT FROM partition_cursor INTO @DatabaseName
885 END;
886 CLOSE partition_cursor
887 DEALLOCATE partition_cursor
888
889 END;
890
891INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
892 index_usage_summary, index_size_summary )
893SELECT 1, 0 ,
894 'Database Skipped',
895 i.DatabaseName,
896 'http://FirstResponderKit.org',
897 i.Reason, '', '', ''
898FROM #Ignore_Databases i;
899
900
901/* Last startup */
902SELECT @DaysUptime = CAST(DATEDIFF(HOUR, create_date, GETDATE()) / 24. AS NUMERIC (23,2))
903FROM sys.databases
904WHERE database_id = 2;
905
906IF @DaysUptime = 0 OR @DaysUptime IS NULL
907 SET @DaysUptime = .01;
908
909SELECT @DaysUptimeInsertValue = 'Server: ' + (CONVERT(VARCHAR(256), (SERVERPROPERTY('ServerName')))) + ' Days Uptime: ' + RTRIM(@DaysUptime);
910
911
912/* Permission granted or unnecessary? Ok, let's go! */
913
914RAISERROR (N'Starting loop through databases',0,1) WITH NOWAIT;
915DECLARE c1 CURSOR
916LOCAL FAST_FORWARD
917FOR
918SELECT dl.DatabaseName
919FROM #DatabaseList dl
920LEFT OUTER JOIN #Ignore_Databases i ON dl.DatabaseName = i.DatabaseName
921WHERE COALESCE(dl.secondary_role_allow_connections_desc, 'OK') <> 'NO'
922 AND i.DatabaseName IS NULL
923ORDER BY dl.DatabaseName;
924
925OPEN c1;
926FETCH NEXT FROM c1 INTO @DatabaseName;
927 WHILE @@FETCH_STATUS = 0
928
929BEGIN
930
931 RAISERROR (@LineFeed, 0, 1) WITH NOWAIT;
932 RAISERROR (@LineFeed, 0, 1) WITH NOWAIT;
933 RAISERROR (@DatabaseName, 0, 1) WITH NOWAIT;
934
935SELECT @DatabaseID = [database_id]
936FROM sys.databases
937 WHERE [name] = @DatabaseName
938 AND user_access_desc='MULTI_USER'
939 AND state_desc = 'ONLINE';
940
941----------------------------------------
942--STEP 1: OBSERVE THE PATIENT
943--This step puts index information into temp tables.
944----------------------------------------
945BEGIN TRY
946 BEGIN
947
948 --Validate SQL Server Version
949
950 IF (SELECT LEFT(@SQLServerProductVersion,
951 CHARINDEX('.',@SQLServerProductVersion,0)-1
952 )) <= 9
953 BEGIN
954 SET @msg=N'sp_BlitzIndex is only supported on SQL Server 2008 and higher. The version of this instance is: ' + @SQLServerProductVersion;
955 RAISERROR(@msg,16,1);
956 END;
957
958 --Short circuit here if database name does not exist.
959 IF @DatabaseName IS NULL OR @DatabaseID IS NULL
960 BEGIN
961 SET @msg='Database does not exist or is not online/multi-user: cannot proceed.';
962 RAISERROR(@msg,16,1);
963 END;
964
965 --Validate parameters.
966 IF (@Mode NOT IN (0,1,2,3,4))
967 BEGIN
968 SET @msg=N'Invalid @Mode parameter. 0=diagnose, 1=summarize, 2=index detail, 3=missing index detail, 4=diagnose detail';
969 RAISERROR(@msg,16,1);
970 END;
971
972 IF (@Mode <> 0 AND @TableName IS NOT NULL)
973 BEGIN
974 SET @msg=N'Setting the @Mode doesn''t change behavior if you supply @TableName. Use default @Mode=0 to see table detail.';
975 RAISERROR(@msg,16,1);
976 END;
977
978 IF ((@Mode <> 0 OR @TableName IS NOT NULL) AND @Filter <> 0)
979 BEGIN
980 SET @msg=N'@Filter only applies when @Mode=0 and @TableName is not specified. Please try again.';
981 RAISERROR(@msg,16,1);
982 END;
983
984 IF (@SchemaName IS NOT NULL AND @TableName IS NULL)
985 BEGIN
986 SET @msg='We can''t run against a whole schema! Specify a @TableName, or leave both NULL for diagnosis.';
987 RAISERROR(@msg,16,1);
988 END;
989
990
991 IF (@TableName IS NOT NULL AND @SchemaName IS NULL)
992 BEGIN
993 SET @SchemaName=N'dbo';
994 SET @msg='@SchemaName wasn''t specified-- assuming schema=dbo.';
995 RAISERROR(@msg,1,1) WITH NOWAIT;
996 END;
997
998 --If a table is specified, grab the object id.
999 --Short circuit if it doesn't exist.
1000 IF @TableName IS NOT NULL
1001 BEGIN
1002 SET @dsql = N'
1003 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1004 SELECT @ObjectID= OBJECT_ID
1005 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so
1006 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS sc on
1007 so.schema_id=sc.schema_id
1008 where so.type in (''U'', ''V'')
1009 and so.name=' + QUOTENAME(@TableName,'''')+ N'
1010 and sc.name=' + QUOTENAME(@SchemaName,'''')+ N'
1011 /*Has a row in sys.indexes. This lets us get indexed views.*/
1012 and exists (
1013 SELECT si.name
1014 FROM ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS si
1015 WHERE so.object_id=si.object_id)
1016 OPTION (RECOMPILE);';
1017
1018 SET @params='@ObjectID INT OUTPUT';
1019
1020 IF @dsql IS NULL
1021 RAISERROR('@dsql is null',16,1);
1022
1023 EXEC sp_executesql @dsql, @params, @ObjectID=@ObjectID OUTPUT;
1024
1025 IF @ObjectID IS NULL
1026 BEGIN
1027 SET @msg=N'Oh, this is awkward. I can''t find the table or indexed view you''re looking for in that database.' + CHAR(10) +
1028 N'Please check your parameters.';
1029 RAISERROR(@msg,1,1);
1030 RETURN;
1031 END;
1032 END;
1033
1034 --set @collation
1035 SELECT @collation=collation_name
1036 FROM sys.databases
1037 WHERE database_id=@DatabaseID;
1038
1039 --insert columns for clustered indexes and heaps
1040 --collect info on identity columns for this one
1041 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1042 SELECT ' + CAST(@DatabaseID AS NVARCHAR(16)) + ',
1043 s.name,
1044 si.object_id,
1045 si.index_id,
1046 sc.key_ordinal,
1047 sc.is_included_column,
1048 sc.is_descending_key,
1049 sc.partition_ordinal,
1050 c.name as column_name,
1051 st.name as system_type_name,
1052 c.max_length,
1053 c.[precision],
1054 c.[scale],
1055 c.collation_name,
1056 c.is_nullable,
1057 c.is_identity,
1058 c.is_computed,
1059 c.is_replicated,
1060 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_sparse' ELSE N'NULL as is_sparse' END + N',
1061 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_filestream' ELSE N'NULL as is_filestream' END + N',
1062 CAST(ic.seed_value AS BIGINT),
1063 CAST(ic.increment_value AS INT),
1064 CAST(ic.last_value AS BIGINT),
1065 ic.is_not_for_replication
1066 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.indexes si
1067 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c ON
1068 si.object_id=c.object_id
1069 LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.index_columns sc ON
1070 sc.object_id = si.object_id
1071 and sc.index_id=si.index_id
1072 AND sc.column_id=c.column_id
1073 LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.identity_columns ic ON
1074 c.object_id=ic.object_id and
1075 c.column_id=ic.column_id
1076 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.types st ON
1077 c.system_type_id=st.system_type_id
1078 AND c.user_type_id=st.user_type_id
1079 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so ON si.object_id = so.object_id
1080 AND so.is_ms_shipped = 0
1081 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s ON s.schema_id = so.schema_id
1082 WHERE si.index_id in (0,1) '
1083 + CASE WHEN @ObjectID IS NOT NULL
1084 THEN N' AND si.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
1085 ELSE N'' END
1086 + N'OPTION (RECOMPILE);';
1087
1088 IF @dsql IS NULL
1089 RAISERROR('@dsql is null',16,1);
1090
1091 RAISERROR (N'Inserting data into #IndexColumns for clustered indexes and heaps',0,1) WITH NOWAIT;
1092 INSERT #IndexColumns ( database_id, [schema_name], [object_id], index_id, key_ordinal, is_included_column, is_descending_key, partition_ordinal,
1093 column_name, system_type_name, max_length, precision, scale, collation_name, is_nullable, is_identity, is_computed,
1094 is_replicated, is_sparse, is_filestream, seed_value, increment_value, last_value, is_not_for_replication )
1095 EXEC sp_executesql @dsql;
1096
1097 --insert columns for nonclustered indexes
1098 --this uses a full join to sys.index_columns
1099 --We don't collect info on identity columns here. They may be in NC indexes, but we just analyze identities in the base table.
1100 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1101 SELECT ' + CAST(@DatabaseID AS NVARCHAR(16)) + ',
1102 s.name,
1103 si.object_id,
1104 si.index_id,
1105 sc.key_ordinal,
1106 sc.is_included_column,
1107 sc.is_descending_key,
1108 sc.partition_ordinal,
1109 c.name as column_name,
1110 st.name as system_type_name,
1111 c.max_length,
1112 c.[precision],
1113 c.[scale],
1114 c.collation_name,
1115 c.is_nullable,
1116 c.is_identity,
1117 c.is_computed,
1118 c.is_replicated,
1119 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_sparse' ELSE N'NULL AS is_sparse' END + N',
1120 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_filestream' ELSE N'NULL AS is_filestream' END + N'
1121 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS si
1122 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c ON
1123 si.object_id=c.object_id
1124 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.index_columns AS sc ON
1125 sc.object_id = si.object_id
1126 and sc.index_id=si.index_id
1127 AND sc.column_id=c.column_id
1128 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.types AS st ON
1129 c.system_type_id=st.system_type_id
1130 AND c.user_type_id=st.user_type_id
1131 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so ON si.object_id = so.object_id
1132 AND so.is_ms_shipped = 0
1133 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s ON s.schema_id = so.schema_id
1134 WHERE si.index_id not in (0,1) '
1135 + CASE WHEN @ObjectID IS NOT NULL
1136 THEN N' AND si.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
1137 ELSE N'' END
1138 + N'OPTION (RECOMPILE);';
1139
1140 IF @dsql IS NULL
1141 RAISERROR('@dsql is null',16,1);
1142
1143 RAISERROR (N'Inserting data into #IndexColumns for nonclustered indexes',0,1) WITH NOWAIT;
1144 INSERT #IndexColumns ( database_id, [schema_name], [object_id], index_id, key_ordinal, is_included_column, is_descending_key, partition_ordinal,
1145 column_name, system_type_name, max_length, precision, scale, collation_name, is_nullable, is_identity, is_computed,
1146 is_replicated, is_sparse, is_filestream )
1147 EXEC sp_executesql @dsql;
1148
1149 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1150 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + N' AS database_id,
1151 so.object_id,
1152 si.index_id,
1153 si.type,
1154 @i_DatabaseName AS database_name,
1155 COALESCE(sc.NAME, ''Unknown'') AS [schema_name],
1156 COALESCE(so.name, ''Unknown'') AS [object_name],
1157 COALESCE(si.name, ''Unknown'') AS [index_name],
1158 CASE WHEN so.[type] = CAST(''V'' AS CHAR(2)) THEN 1 ELSE 0 END,
1159 si.is_unique,
1160 si.is_primary_key,
1161 CASE when si.type = 3 THEN 1 ELSE 0 END AS is_XML,
1162 CASE when si.type = 4 THEN 1 ELSE 0 END AS is_spatial,
1163 CASE when si.type = 6 THEN 1 ELSE 0 END AS is_NC_columnstore,
1164 CASE when si.type = 5 then 1 else 0 end as is_CX_columnstore,
1165 si.is_disabled,
1166 si.is_hypothetical,
1167 si.is_padded,
1168 si.fill_factor,'
1169 + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'
1170 CASE WHEN si.filter_definition IS NOT NULL THEN si.filter_definition
1171 ELSE N''''
1172 END AS filter_definition' ELSE N''''' AS filter_definition' END + N'
1173 , ISNULL(us.user_seeks, 0), ISNULL(us.user_scans, 0),
1174 ISNULL(us.user_lookups, 0), ISNULL(us.user_updates, 0), us.last_user_seek, us.last_user_scan,
1175 us.last_user_lookup, us.last_user_update,
1176 so.create_date, so.modify_date
1177 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS si WITH (NOLOCK)
1178 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so WITH (NOLOCK) ON si.object_id = so.object_id
1179 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
1180 AND so.type <> ''TF'' /*Exclude table valued functions*/
1181 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sc ON so.schema_id = sc.schema_id
1182 LEFT JOIN sys.dm_db_index_usage_stats AS us WITH (NOLOCK) ON si.[object_id] = us.[object_id]
1183 AND si.index_id = us.index_id
1184 AND us.database_id = ' + CAST(@DatabaseID AS NVARCHAR(10)) + N'
1185 WHERE si.[type] IN ( 0, 1, 2, 3, 4, 5, 6 )
1186 /* Heaps, clustered, nonclustered, XML, spatial, Cluster Columnstore, NC Columnstore */ ' +
1187 CASE WHEN @TableName IS NOT NULL THEN N' and so.name=' + QUOTENAME(@TableName,N'''') + N' ' ELSE N'' END +
1188 N'OPTION ( RECOMPILE );
1189 ';
1190 IF @dsql IS NULL
1191 RAISERROR('@dsql is null',16,1);
1192
1193 RAISERROR (N'Inserting data into #IndexSanity',0,1) WITH NOWAIT;
1194 INSERT #IndexSanity ( [database_id], [object_id], [index_id], [index_type], [database_name], [schema_name], [object_name],
1195 index_name, is_indexed_view, is_unique, is_primary_key, is_XML, is_spatial, is_NC_columnstore, is_CX_columnstore,
1196 is_disabled, is_hypothetical, is_padded, fill_factor, filter_definition, user_seeks, user_scans,
1197 user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update,
1198 create_date, modify_date )
1199 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1200
1201
1202 RAISERROR (N'Checking partition count',0,1) WITH NOWAIT;
1203 IF @BringThePain = 0 AND @SkipPartitions = 0 AND @TableName IS NULL
1204 BEGIN
1205 /* Count the total number of partitions */
1206 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1207 SELECT @RowcountOUT = SUM(1) FROM ' + QUOTENAME(@DatabaseName) + '.sys.partitions WHERE partition_number > 1 OPTION ( RECOMPILE );';
1208 EXEC sp_executesql @dsql, N'@RowcountOUT BIGINT OUTPUT', @RowcountOUT = @Rowcount OUTPUT;
1209 IF @Rowcount > 100
1210 BEGIN
1211 RAISERROR (N'Setting @SkipPartitions = 1 because > 100 partitions were found. To check them, you must set @BringThePain = 1.',0,1) WITH NOWAIT;
1212 SET @SkipPartitions = 1;
1213 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
1214 index_usage_summary, index_size_summary )
1215 VALUES ( 1, 0 ,
1216 'Some Checks Were Skipped',
1217 '@SkipPartitions Forced to 1',
1218 'http://FirstResponderKit.org', CAST(@Rowcount AS NVARCHAR(50)) + ' partitions found. To analyze them, use @BringThePain = 1.', 'We try to keep things quick - and warning, running @BringThePain = 1 can take tens of minutes.', '', ''
1219 );
1220 END;
1221 END;
1222
1223
1224
1225 IF (@SkipPartitions = 0)
1226 BEGIN
1227 IF (SELECT LEFT(@SQLServerProductVersion,
1228 CHARINDEX('.',@SQLServerProductVersion,0)-1 )) <= 2147483647 --Make change here
1229 BEGIN
1230
1231 RAISERROR (N'Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats',0,1) WITH NOWAIT;
1232
1233 --NOTE: If you want to use the newer syntax for 2012+, you'll have to change 2147483647 to 11 on line ~819
1234 --This change was made because on a table with lots of paritions, the OUTER APPLY was crazy slow.
1235 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1236 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + ' AS database_id,
1237 ps.object_id,
1238 s.name,
1239 ps.index_id,
1240 ps.partition_number,
1241 ps.row_count,
1242 ps.reserved_page_count * 8. / 1024. AS reserved_MB,
1243 ps.lob_reserved_page_count * 8. / 1024. AS reserved_LOB_MB,
1244 ps.row_overflow_reserved_page_count * 8. / 1024. AS reserved_row_overflow_MB,
1245 os.leaf_insert_count,
1246 os.leaf_delete_count,
1247 os.leaf_update_count,
1248 os.range_scan_count,
1249 os.singleton_lookup_count,
1250 os.forwarded_fetch_count,
1251 os.lob_fetch_in_pages,
1252 os.lob_fetch_in_bytes,
1253 os.row_overflow_fetch_in_pages,
1254 os.row_overflow_fetch_in_bytes,
1255 os.row_lock_count,
1256 os.row_lock_wait_count,
1257 os.row_lock_wait_in_ms,
1258 os.page_lock_count,
1259 os.page_lock_wait_count,
1260 os.page_lock_wait_in_ms,
1261 os.index_lock_promotion_attempt_count,
1262 os.index_lock_promotion_count,
1263 os.page_latch_wait_count,
1264 os.page_latch_wait_in_ms,
1265 os.page_io_latch_wait_count,
1266 os.page_io_latch_wait_in_ms,
1267 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN 'par.data_compression_desc ' ELSE 'null as data_compression_desc' END + '
1268 FROM ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_partition_stats AS ps
1269 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.partitions AS par on ps.partition_id=par.partition_id
1270 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.objects AS so ON ps.object_id = so.object_id
1271 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
1272 AND so.type <> ''TF'' /*Exclude table valued functions*/
1273 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.schemas AS s ON s.schema_id = so.schema_id
1274 LEFT JOIN ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_operational_stats('
1275 + CAST(@DatabaseID AS NVARCHAR(10)) + ', NULL, NULL,NULL) AS os ON
1276 ps.object_id=os.object_id and ps.index_id=os.index_id and ps.partition_number=os.partition_number
1277 WHERE 1=1
1278 ' + CASE WHEN @ObjectID IS NOT NULL THEN N'AND so.object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' ' ELSE N' ' END + '
1279 ' + CASE WHEN @Filter = 2 THEN N'AND ps.reserved_page_count * 8./1024. > ' + CAST(@FilterMB AS NVARCHAR(5)) + N' ' ELSE N' ' END + '
1280 ORDER BY ps.object_id, ps.index_id, ps.partition_number
1281 OPTION ( RECOMPILE );
1282 ';
1283 END;
1284 ELSE
1285 BEGIN
1286 RAISERROR (N'Using 2012 syntax to query sys.dm_db_index_operational_stats',0,1) WITH NOWAIT;
1287 --This is the syntax that will be used if you change 2147483647 to 11 on line ~819.
1288 --If you have a lot of paritions and this suddenly starts running for a long time, change it back.
1289 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1290 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + ' AS database_id,
1291 ps.object_id,
1292 s.name,
1293 ps.index_id,
1294 ps.partition_number,
1295 ps.row_count,
1296 ps.reserved_page_count * 8. / 1024. AS reserved_MB,
1297 ps.lob_reserved_page_count * 8. / 1024. AS reserved_LOB_MB,
1298 ps.row_overflow_reserved_page_count * 8. / 1024. AS reserved_row_overflow_MB,
1299 os.leaf_insert_count,
1300 os.leaf_delete_count,
1301 os.leaf_update_count,
1302 os.range_scan_count,
1303 os.singleton_lookup_count,
1304 os.forwarded_fetch_count,
1305 os.lob_fetch_in_pages,
1306 os.lob_fetch_in_bytes,
1307 os.row_overflow_fetch_in_pages,
1308 os.row_overflow_fetch_in_bytes,
1309 os.row_lock_count,
1310 os.row_lock_wait_count,
1311 os.row_lock_wait_in_ms,
1312 os.page_lock_count,
1313 os.page_lock_wait_count,
1314 os.page_lock_wait_in_ms,
1315 os.index_lock_promotion_attempt_count,
1316 os.index_lock_promotion_count,
1317 os.page_latch_wait_count,
1318 os.page_latch_wait_in_ms,
1319 os.page_io_latch_wait_count,
1320 os.page_io_latch_wait_in_ms,
1321 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'par.data_compression_desc ' ELSE N'null as data_compression_desc' END + N'
1322 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_partition_stats AS ps
1323 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.partitions AS par on ps.partition_id=par.partition_id
1324 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so ON ps.object_id = so.object_id
1325 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
1326 AND so.type <> ''TF'' /*Exclude table valued functions*/
1327 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.schemas AS s ON s.schema_id = so.schema_id
1328 OUTER APPLY ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_index_operational_stats('
1329 + CAST(@DatabaseID AS NVARCHAR(10)) + N', ps.object_id, ps.index_id,ps.partition_number) AS os
1330 WHERE 1=1
1331 ' + CASE WHEN @ObjectID IS NOT NULL THEN N'AND so.object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' ' ELSE N' ' END + N'
1332 ' + CASE WHEN @Filter = 2 THEN N'AND ps.reserved_page_count * 8./1024. > ' + CAST(@FilterMB AS NVARCHAR(5)) + N' ' ELSE N' ' END + '
1333 ORDER BY ps.object_id, ps.index_id, ps.partition_number
1334 OPTION ( RECOMPILE );
1335 ';
1336 END;
1337
1338 IF @dsql IS NULL
1339 RAISERROR('@dsql is null',16,1);
1340
1341 RAISERROR (N'Inserting data into #IndexPartitionSanity',0,1) WITH NOWAIT;
1342 INSERT #IndexPartitionSanity ( [database_id],
1343 [object_id],
1344 [schema_name],
1345 index_id,
1346 partition_number,
1347 row_count,
1348 reserved_MB,
1349 reserved_LOB_MB,
1350 reserved_row_overflow_MB,
1351 leaf_insert_count,
1352 leaf_delete_count,
1353 leaf_update_count,
1354 range_scan_count,
1355 singleton_lookup_count,
1356 forwarded_fetch_count,
1357 lob_fetch_in_pages,
1358 lob_fetch_in_bytes,
1359 row_overflow_fetch_in_pages,
1360 row_overflow_fetch_in_bytes,
1361 row_lock_count,
1362 row_lock_wait_count,
1363 row_lock_wait_in_ms,
1364 page_lock_count,
1365 page_lock_wait_count,
1366 page_lock_wait_in_ms,
1367 index_lock_promotion_attempt_count,
1368 index_lock_promotion_count,
1369 page_latch_wait_count,
1370 page_latch_wait_in_ms,
1371 page_io_latch_wait_count,
1372 page_io_latch_wait_in_ms,
1373 data_compression_desc )
1374 EXEC sp_executesql @dsql;
1375
1376 END; --End Check For @SkipPartitions = 0
1377
1378
1379
1380 RAISERROR (N'Inserting data into #MissingIndexes',0,1) WITH NOWAIT;
1381 SET @dsql=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1382 SELECT id.database_id, id.object_id, @i_DatabaseName, sc.[name], so.[name], id.statement , gs.avg_total_user_cost,
1383 gs.avg_user_impact, gs.user_seeks, gs.user_scans, gs.unique_compiles,id.equality_columns,
1384 id.inequality_columns,id.included_columns
1385 FROM sys.dm_db_missing_index_groups ig
1386 JOIN sys.dm_db_missing_index_details id ON ig.index_handle = id.index_handle
1387 JOIN sys.dm_db_missing_index_group_stats gs ON ig.index_group_handle = gs.group_handle
1388 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects so on
1389 id.object_id=so.object_id
1390 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sc on
1391 so.schema_id=sc.schema_id
1392 WHERE id.database_id = ' + CAST(@DatabaseID AS NVARCHAR(30)) + '
1393 ' + CASE WHEN @ObjectID IS NULL THEN N''
1394 ELSE N'and id.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
1395 END +
1396 N'OPTION (RECOMPILE);';
1397
1398 IF @dsql IS NULL
1399 RAISERROR('@dsql is null',16,1);
1400 INSERT #MissingIndexes ( [database_id], [object_id], [database_name], [schema_name], [table_name], [statement], avg_total_user_cost,
1401 avg_user_impact, user_seeks, user_scans, unique_compiles, equality_columns,
1402 inequality_columns, included_columns)
1403 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1404
1405 SET @dsql = N'
1406 SELECT DB_ID(N' + QUOTENAME(@DatabaseName,'''') + N') AS [database_id],
1407 @i_DatabaseName AS database_name,
1408 s.name,
1409 fk_object.name AS foreign_key_name,
1410 parent_object.[object_id] AS parent_object_id,
1411 parent_object.name AS parent_object_name,
1412 referenced_object.[object_id] AS referenced_object_id,
1413 referenced_object.name AS referenced_object_name,
1414 fk.is_disabled,
1415 fk.is_not_trusted,
1416 fk.is_not_for_replication,
1417 parent.fk_columns,
1418 referenced.fk_columns,
1419 [update_referential_action_desc],
1420 [delete_referential_action_desc]
1421 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_keys fk
1422 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects fk_object ON fk.object_id=fk_object.object_id
1423 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects parent_object ON fk.parent_object_id=parent_object.object_id
1424 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects referenced_object ON fk.referenced_object_id=referenced_object.object_id
1425 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s ON fk.schema_id=s.schema_id
1426 CROSS APPLY ( SELECT STUFF( (SELECT N'', '' + c_parent.name AS fk_columns
1427 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_key_columns fkc
1428 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c_parent ON fkc.parent_object_id=c_parent.[object_id]
1429 AND fkc.parent_column_id=c_parent.column_id
1430 WHERE fk.parent_object_id=fkc.parent_object_id
1431 AND fk.[object_id]=fkc.constraint_object_id
1432 ORDER BY fkc.constraint_column_id
1433 FOR XML PATH('''') ,
1434 TYPE).value(''.'', ''nvarchar(max)''), 1, 1, '''')/*This is how we remove the first comma*/ ) parent ( fk_columns )
1435 CROSS APPLY ( SELECT STUFF( (SELECT N'', '' + c_referenced.name AS fk_columns
1436 FROM ' + QUOTENAME(@DatabaseName) + N'.sys. foreign_key_columns fkc
1437 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c_referenced ON fkc.referenced_object_id=c_referenced.[object_id]
1438 AND fkc.referenced_column_id=c_referenced.column_id
1439 WHERE fk.referenced_object_id=fkc.referenced_object_id
1440 and fk.[object_id]=fkc.constraint_object_id
1441 ORDER BY fkc.constraint_column_id /*order by col name, we don''t have anything better*/
1442 FOR XML PATH('''') ,
1443 TYPE).value(''.'', ''nvarchar(max)''), 1, 1, '''') ) referenced ( fk_columns )
1444 ' + CASE WHEN @ObjectID IS NOT NULL THEN
1445 'WHERE fk.parent_object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' OR fk.referenced_object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' '
1446 ELSE N' ' END + '
1447 ORDER BY parent_object_name, foreign_key_name
1448 OPTION (RECOMPILE);';
1449 IF @dsql IS NULL
1450 RAISERROR('@dsql is null',16,1);
1451
1452 RAISERROR (N'Inserting data into #ForeignKeys',0,1) WITH NOWAIT;
1453 INSERT #ForeignKeys ( [database_id], [database_name], [schema_name], foreign_key_name, parent_object_id,parent_object_name, referenced_object_id, referenced_object_name,
1454 is_disabled, is_not_trusted, is_not_for_replication, parent_fk_columns, referenced_fk_columns,
1455 [update_referential_action_desc], [delete_referential_action_desc] )
1456 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1457
1458
1459 IF @SkipStatistics = 0 AND DB_NAME() = @DatabaseName /* Can only get stats in the current database - see https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1947 */
1460 BEGIN
1461 IF ((PARSENAME(@SQLServerProductVersion, 4) >= 12)
1462 OR (PARSENAME(@SQLServerProductVersion, 4) = 11 AND PARSENAME(@SQLServerProductVersion, 2) >= 3000)
1463 OR (PARSENAME(@SQLServerProductVersion, 4) = 10 AND PARSENAME(@SQLServerProductVersion, 3) = 50 AND PARSENAME(@SQLServerProductVersion, 2) >= 2500))
1464 BEGIN
1465 RAISERROR (N'Gathering Statistics Info With Newer Syntax.',0,1) WITH NOWAIT;
1466 SET @dsql=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1467 SELECT DB_ID(N' + QUOTENAME(@DatabaseName,'''') + N') AS [database_id],
1468 @i_DatabaseName AS database_name,
1469 obj.name AS table_name,
1470 sch.name AS schema_name,
1471 ISNULL(i.name, ''System Or User Statistic'') AS index_name,
1472 ca.column_names AS column_names,
1473 s.name AS statistics_name,
1474 CONVERT(DATETIME, ddsp.last_updated) AS last_statistics_update,
1475 DATEDIFF(DAY, ddsp.last_updated, GETDATE()) AS days_since_last_stats_update,
1476 ddsp.rows,
1477 ddsp.rows_sampled,
1478 CAST(ddsp.rows_sampled / ( 1. * NULLIF(ddsp.rows, 0) ) * 100 AS DECIMAL(18, 1)) AS percent_sampled,
1479 ddsp.steps AS histogram_steps,
1480 ddsp.modification_counter,
1481 CASE WHEN ddsp.modification_counter > 0
1482 THEN CAST(ddsp.modification_counter / ( 1. * NULLIF(ddsp.rows, 0) ) * 100 AS DECIMAL(18, 1))
1483 ELSE ddsp.modification_counter
1484 END AS percent_modifications,
1485 CASE WHEN ddsp.rows < 500 THEN 500
1486 ELSE CAST(( ddsp.rows * .20 ) + 500 AS INT)
1487 END AS modifications_before_auto_update,
1488 ISNULL(i.type_desc, ''System Or User Statistic - N/A'') AS index_type_desc,
1489 CONVERT(DATETIME, obj.create_date) AS table_create_date,
1490 CONVERT(DATETIME, obj.modify_date) AS table_modify_date,
1491 s.no_recompute,
1492 s.has_filter,
1493 s.filter_definition
1494 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.stats AS s
1495 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects obj
1496 ON s.object_id = obj.object_id
1497 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sch
1498 ON sch.schema_id = obj.schema_id
1499 LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS i
1500 ON i.object_id = s.object_id
1501 AND i.index_id = s.stats_id
1502 OUTER APPLY ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_stats_properties(s.object_id, s.stats_id) AS ddsp
1503 CROSS APPLY ( SELECT STUFF((SELECT '', '' + c.name
1504 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.stats_columns AS sc
1505 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
1506 ON sc.column_id = c.column_id AND sc.object_id = c.object_id
1507 WHERE sc.stats_id = s.stats_id AND sc.object_id = s.object_id
1508 ORDER BY sc.stats_column_id
1509 FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(max)''), 1, 2, '''')
1510 ) ca (column_names)
1511 WHERE obj.is_ms_shipped = 0
1512 OPTION (RECOMPILE);';
1513
1514 IF @dsql IS NULL
1515 RAISERROR('@dsql is null',16,1);
1516
1517 RAISERROR (N'Inserting data into #Statistics',0,1) WITH NOWAIT;
1518 INSERT #Statistics ( database_id, database_name, table_name, schema_name, index_name, column_names, statistics_name, last_statistics_update,
1519 days_since_last_stats_update, rows, rows_sampled, percent_sampled, histogram_steps, modification_counter,
1520 percent_modifications, modifications_before_auto_update, index_type_desc, table_create_date, table_modify_date,
1521 no_recompute, has_filter, filter_definition)
1522
1523 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1524 END;
1525 ELSE
1526 BEGIN
1527 RAISERROR (N'Gathering Statistics Info With Older Syntax.',0,1) WITH NOWAIT;
1528 SET @dsql=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1529 SELECT DB_ID(N' + QUOTENAME(@DatabaseName,'''') + N') AS [database_id],
1530 @i_DatabaseName AS database_name,
1531 obj.name AS table_name,
1532 sch.name AS schema_name,
1533 ISNULL(i.name, ''System Or User Statistic'') AS index_name,
1534 ca.column_names AS column_names,
1535 s.name AS statistics_name,
1536 CONVERT(DATETIME, STATS_DATE(s.object_id, s.stats_id)) AS last_statistics_update,
1537 DATEDIFF(DAY, STATS_DATE(s.object_id, s.stats_id), GETDATE()) AS days_since_last_stats_update,
1538 si.rowcnt,
1539 si.rowmodctr,
1540 CASE WHEN si.rowmodctr > 0 THEN CAST(si.rowmodctr / ( 1. * NULLIF(si.rowcnt, 0) ) * 100 AS DECIMAL(18, 1))
1541 ELSE si.rowmodctr
1542 END AS percent_modifications,
1543 CASE WHEN si.rowcnt < 500 THEN 500
1544 ELSE CAST(( si.rowcnt * .20 ) + 500 AS INT)
1545 END AS modifications_before_auto_update,
1546 ISNULL(i.type_desc, ''System Or User Statistic - N/A'') AS index_type_desc,
1547 CONVERT(DATETIME, obj.create_date) AS table_create_date,
1548 CONVERT(DATETIME, obj.modify_date) AS table_modify_date,
1549 s.no_recompute,
1550 '
1551 + CASE WHEN @SQLServerProductVersion NOT LIKE '9%'
1552 THEN N's.has_filter,
1553 s.filter_definition'
1554 ELSE N'NULL AS has_filter,
1555 NULL AS filter_definition' END
1556 + N'
1557 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.stats AS s
1558 INNER HASH JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.sysindexes si
1559 ON si.name = s.name AND s.object_id = si.id
1560 INNER HASH JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects obj
1561 ON s.object_id = obj.object_id
1562 INNER HASH JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sch
1563 ON sch.schema_id = obj.schema_id
1564 LEFT HASH JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS i
1565 ON i.object_id = s.object_id
1566 AND i.index_id = s.stats_id
1567 CROSS APPLY ( SELECT STUFF((SELECT '', '' + c.name
1568 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.stats_columns AS sc
1569 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
1570 ON sc.column_id = c.column_id AND sc.object_id = c.object_id
1571 WHERE sc.stats_id = s.stats_id AND sc.object_id = s.object_id
1572 ORDER BY sc.stats_column_id
1573 FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(max)''), 1, 2, '''')
1574 ) ca (column_names)
1575 WHERE obj.is_ms_shipped = 0
1576 AND si.rowcnt > 0
1577 OPTION (RECOMPILE);';
1578
1579 IF @dsql IS NULL
1580 RAISERROR('@dsql is null',16,1);
1581
1582 RAISERROR (N'Inserting data into #Statistics',0,1) WITH NOWAIT;
1583 INSERT #Statistics(database_id, database_name, table_name, schema_name, index_name, column_names, statistics_name,
1584 last_statistics_update, days_since_last_stats_update, rows, modification_counter,
1585 percent_modifications, modifications_before_auto_update, index_type_desc, table_create_date, table_modify_date,
1586 no_recompute, has_filter, filter_definition)
1587
1588 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1589 END;
1590
1591 END;
1592
1593 IF (PARSENAME(@SQLServerProductVersion, 4) >= 10)
1594 BEGIN
1595 RAISERROR (N'Gathering Computed Column Info.',0,1) WITH NOWAIT;
1596 SET @dsql=N'SELECT DB_ID(@i_DatabaseName) AS [database_id],
1597 @i_DatabaseName AS database_name,
1598 t.name AS table_name,
1599 s.name AS schema_name,
1600 c.name AS column_name,
1601 cc.is_nullable,
1602 cc.definition,
1603 cc.uses_database_collation,
1604 cc.is_persisted,
1605 cc.is_computed,
1606 CASE WHEN cc.definition LIKE ''%|].|[%'' ESCAPE ''|'' THEN 1 ELSE 0 END AS is_function,
1607 ''ALTER TABLE '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) +
1608 '' ADD '' + QUOTENAME(c.name) + '' AS '' + cc.definition +
1609 CASE WHEN is_persisted = 1 THEN '' PERSISTED'' ELSE '''' END + '';'' COLLATE DATABASE_DEFAULT AS [column_definition]
1610 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.computed_columns AS cc
1611 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
1612 ON cc.object_id = c.object_id
1613 AND cc.column_id = c.column_id
1614 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
1615 ON t.object_id = cc.object_id
1616 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
1617 ON s.schema_id = t.schema_id
1618 OPTION (RECOMPILE);';
1619
1620 IF @dsql IS NULL RAISERROR('@dsql is null',16,1);
1621
1622 INSERT #ComputedColumns
1623 ( database_id, [database_name], table_name, schema_name, column_name, is_nullable, definition,
1624 uses_database_collation, is_persisted, is_computed, is_function, column_definition )
1625 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1626
1627 END;
1628
1629 RAISERROR (N'Gathering Trace Flag Information',0,1) WITH NOWAIT;
1630 INSERT #TraceStatus
1631 EXEC ('DBCC TRACESTATUS(-1) WITH NO_INFOMSGS');
1632
1633 IF (PARSENAME(@SQLServerProductVersion, 4) >= 13)
1634 BEGIN
1635 RAISERROR (N'Gathering Temporal Table Info',0,1) WITH NOWAIT;
1636 SET @dsql=N'SELECT ' + QUOTENAME(@DatabaseName,'''') + N' AS database_name,
1637 DB_ID(N' + QUOTENAME(@DatabaseName,'''') + N') AS [database_id],
1638 s.name AS schema_name,
1639 t.name AS table_name,
1640 oa.hsn as history_schema_name,
1641 oa.htn AS history_table_name,
1642 c1.name AS start_column_name,
1643 c2.name AS end_column_name,
1644 p.name AS period_name
1645 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.periods AS p
1646 INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
1647 ON p.object_id = t.object_id
1648 INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c1
1649 ON t.object_id = c1.object_id
1650 AND p.start_column_id = c1.column_id
1651 INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c2
1652 ON t.object_id = c2.object_id
1653 AND p.end_column_id = c2.column_id
1654 INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
1655 ON t.schema_id = s.schema_id
1656 CROSS APPLY ( SELECT s2.name as hsn, t2.name htn
1657 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t2
1658 INNER JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s2
1659 ON t2.schema_id = s2.schema_id
1660 WHERE t2.object_id = t.history_table_id
1661 AND t2.temporal_type = 1 /*History table*/ ) AS oa
1662 WHERE t.temporal_type IN ( 2, 4 ) /*BOL currently points to these types, but has no definition for 4*/
1663 OPTION (RECOMPILE);
1664 ';
1665
1666 IF @dsql IS NULL
1667 RAISERROR('@dsql is null',16,1);
1668
1669 INSERT #TemporalTables ( database_name, database_id, schema_name, table_name, history_table_name,
1670 history_schema_name, start_column_name, end_column_name, period_name )
1671
1672 EXEC sp_executesql @dsql;
1673
1674 SET @dsql=N'SELECT DB_ID(@i_DatabaseName) AS [database_id],
1675 @i_DatabaseName AS database_name,
1676 t.name AS table_name,
1677 s.name AS schema_name,
1678 cc.name AS constraint_name,
1679 cc.is_disabled,
1680 cc.definition,
1681 cc.uses_database_collation,
1682 cc.is_not_trusted,
1683 CASE WHEN cc.definition LIKE ''%|].|[%'' ESCAPE ''|'' THEN 1 ELSE 0 END AS is_function,
1684 ''ALTER TABLE '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) +
1685 '' ADD CONSTRAINT '' + QUOTENAME(cc.name) + '' CHECK '' + cc.definition + '';'' COLLATE DATABASE_DEFAULT AS [column_definition]
1686 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.check_constraints AS cc
1687 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
1688 ON t.object_id = cc.parent_object_id
1689 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
1690 ON s.schema_id = t.schema_id
1691 OPTION (RECOMPILE);';
1692
1693 INSERT #CheckConstraints
1694 ( database_id, [database_name], table_name, schema_name, constraint_name, is_disabled, definition,
1695 uses_database_collation, is_not_trusted, is_function, column_definition )
1696 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1697
1698
1699 SET @dsql=N'SELECT DB_ID(@i_DatabaseName) AS [database_id],
1700 @i_DatabaseName AS database_name,
1701 s.name AS missing_schema_name,
1702 t.name AS missing_table_name,
1703 i.name AS missing_index_name,
1704 c.name AS missing_column_name
1705 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.sql_expression_dependencies AS sed
1706 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.tables AS t
1707 ON t.object_id = sed.referenced_id
1708 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS s
1709 ON t.schema_id = s.schema_id
1710 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS i
1711 ON i.object_id = sed.referenced_id
1712 AND i.index_id = sed.referencing_minor_id
1713 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c
1714 ON c.object_id = sed.referenced_id
1715 AND c.column_id = sed.referenced_minor_id
1716 WHERE sed.referencing_class = 7
1717 AND sed.referenced_class = 1
1718 AND i.has_filter = 1
1719 AND NOT EXISTS ( SELECT 1/0
1720 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.index_columns AS ic
1721 WHERE ic.index_id = sed.referencing_minor_id
1722 AND ic.column_id = sed.referenced_minor_id
1723 AND ic.object_id = sed.referenced_id )
1724 OPTION(RECOMPILE);'
1725
1726 INSERT #FilteredIndexes ( database_id, database_name, schema_name, table_name, index_name, column_name )
1727 EXEC sp_executesql @dsql, @params = N'@i_DatabaseName NVARCHAR(128)', @i_DatabaseName = @DatabaseName;
1728
1729
1730 END;
1731
1732END;
1733END TRY
1734BEGIN CATCH
1735 RAISERROR (N'Failure populating temp tables.', 0,1) WITH NOWAIT;
1736
1737 IF @dsql IS NOT NULL
1738 BEGIN
1739 SET @msg= 'Last @dsql: ' + @dsql;
1740 RAISERROR(@msg, 0, 1) WITH NOWAIT;
1741 END;
1742
1743 SELECT @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
1744 RAISERROR (@msg,@ErrorSeverity, @ErrorState )WITH NOWAIT;
1745
1746
1747 WHILE @@trancount > 0
1748 ROLLBACK;
1749
1750 RETURN;
1751END CATCH;
1752 FETCH NEXT FROM c1 INTO @DatabaseName;
1753END;
1754DEALLOCATE c1;
1755
1756
1757
1758
1759
1760
1761----------------------------------------
1762--STEP 2: PREP THE TEMP TABLES
1763--EVERY QUERY AFTER THIS GOES AGAINST TEMP TABLES ONLY.
1764----------------------------------------
1765
1766RAISERROR (N'Updating #IndexSanity.key_column_names',0,1) WITH NOWAIT;
1767UPDATE #IndexSanity
1768SET key_column_names = D1.key_column_names
1769FROM #IndexSanity si
1770 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name
1771 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
1772 AS col_definition
1773 FROM #IndexColumns c
1774 WHERE c.database_id= si.database_id
1775 AND c.schema_name = si.schema_name
1776 AND c.object_id = si.object_id
1777 AND c.index_id = si.index_id
1778 AND c.is_included_column = 0 /*Just Keys*/
1779 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
1780 ORDER BY c.object_id, c.index_id, c.key_ordinal
1781 FOR XML PATH('') ,TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1782 ) D1 ( key_column_names );
1783
1784RAISERROR (N'Updating #IndexSanity.partition_key_column_name',0,1) WITH NOWAIT;
1785UPDATE #IndexSanity
1786SET partition_key_column_name = D1.partition_key_column_name
1787FROM #IndexSanity si
1788 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name AS col_definition
1789 FROM #IndexColumns c
1790 WHERE c.database_id= si.database_id
1791 AND c.schema_name = si.schema_name
1792 AND c.object_id = si.object_id
1793 AND c.index_id = si.index_id
1794 AND c.partition_ordinal <> 0 /*Just Partitioned Keys*/
1795 ORDER BY c.object_id, c.index_id, c.key_ordinal
1796 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1,''))) D1
1797 ( partition_key_column_name );
1798
1799RAISERROR (N'Updating #IndexSanity.key_column_names_with_sort_order',0,1) WITH NOWAIT;
1800UPDATE #IndexSanity
1801SET key_column_names_with_sort_order = D2.key_column_names_with_sort_order
1802FROM #IndexSanity si
1803 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name + CASE c.is_descending_key
1804 WHEN 1 THEN N' DESC'
1805 ELSE N''
1806 END
1807 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
1808 AS col_definition
1809 FROM #IndexColumns c
1810 WHERE c.database_id= si.database_id
1811 AND c.schema_name = si.schema_name
1812 AND c.object_id = si.object_id
1813 AND c.index_id = si.index_id
1814 AND c.is_included_column = 0 /*Just Keys*/
1815 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
1816 ORDER BY c.object_id, c.index_id, c.key_ordinal
1817 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1818 ) D2 ( key_column_names_with_sort_order );
1819
1820RAISERROR (N'Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql)',0,1) WITH NOWAIT;
1821UPDATE #IndexSanity
1822SET key_column_names_with_sort_order_no_types = D2.key_column_names_with_sort_order_no_types
1823FROM #IndexSanity si
1824 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + QUOTENAME(c.column_name) + CASE c.is_descending_key
1825 WHEN 1 THEN N' DESC'
1826 ELSE N''
1827 END AS col_definition
1828 FROM #IndexColumns c
1829 WHERE c.database_id= si.database_id
1830 AND c.schema_name = si.schema_name
1831 AND c.object_id = si.object_id
1832 AND c.index_id = si.index_id
1833 AND c.is_included_column = 0 /*Just Keys*/
1834 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
1835 ORDER BY c.object_id, c.index_id, c.key_ordinal
1836 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1837 ) D2 ( key_column_names_with_sort_order_no_types );
1838
1839RAISERROR (N'Updating #IndexSanity.include_column_names',0,1) WITH NOWAIT;
1840UPDATE #IndexSanity
1841SET include_column_names = D3.include_column_names
1842FROM #IndexSanity si
1843 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name
1844 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
1845 FROM #IndexColumns c
1846 WHERE c.database_id= si.database_id
1847 AND c.schema_name = si.schema_name
1848 AND c.object_id = si.object_id
1849 AND c.index_id = si.index_id
1850 AND c.is_included_column = 1 /*Just includes*/
1851 ORDER BY c.column_name /*Order doesn't matter in includes,
1852 this is here to make rows easy to compare.*/
1853 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1854 ) D3 ( include_column_names );
1855
1856RAISERROR (N'Updating #IndexSanity.include_column_names_no_types (for create tsql)',0,1) WITH NOWAIT;
1857UPDATE #IndexSanity
1858SET include_column_names_no_types = D3.include_column_names_no_types
1859FROM #IndexSanity si
1860 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + QUOTENAME(c.column_name)
1861 FROM #IndexColumns c
1862 WHERE c.database_id= si.database_id
1863 AND c.schema_name = si.schema_name
1864 AND c.object_id = si.object_id
1865 AND c.index_id = si.index_id
1866 AND c.is_included_column = 1 /*Just includes*/
1867 ORDER BY c.column_name /*Order doesn't matter in includes,
1868 this is here to make rows easy to compare.*/
1869 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1870 ) D3 ( include_column_names_no_types );
1871
1872RAISERROR (N'Updating #IndexSanity.count_key_columns and count_include_columns',0,1) WITH NOWAIT;
1873UPDATE #IndexSanity
1874SET count_included_columns = D4.count_included_columns,
1875 count_key_columns = D4.count_key_columns
1876FROM #IndexSanity si
1877 CROSS APPLY ( SELECT SUM(CASE WHEN is_included_column = 'true' THEN 1
1878 ELSE 0
1879 END) AS count_included_columns,
1880 SUM(CASE WHEN is_included_column = 'false' AND c.key_ordinal > 0 THEN 1
1881 ELSE 0
1882 END) AS count_key_columns
1883 FROM #IndexColumns c
1884 WHERE c.database_id= si.database_id
1885 AND c.schema_name = si.schema_name
1886 AND c.object_id = si.object_id
1887 AND c.index_id = si.index_id
1888 ) AS D4 ( count_included_columns, count_key_columns );
1889
1890RAISERROR (N'Updating index_sanity_id on #IndexPartitionSanity',0,1) WITH NOWAIT;
1891UPDATE #IndexPartitionSanity
1892SET index_sanity_id = i.index_sanity_id
1893FROM #IndexPartitionSanity ps
1894 JOIN #IndexSanity i ON ps.[object_id] = i.[object_id]
1895 AND ps.index_id = i.index_id
1896 AND i.database_id = ps.database_id
1897 AND i.schema_name = ps.schema_name;
1898
1899
1900RAISERROR (N'Inserting data into #IndexSanitySize',0,1) WITH NOWAIT;
1901INSERT #IndexSanitySize ( [index_sanity_id], [database_id], [schema_name], partition_count, total_rows, total_reserved_MB,
1902 total_reserved_LOB_MB, total_reserved_row_overflow_MB, total_range_scan_count,
1903 total_singleton_lookup_count, total_leaf_delete_count, total_leaf_update_count,
1904 total_forwarded_fetch_count,total_row_lock_count,
1905 total_row_lock_wait_count, total_row_lock_wait_in_ms, avg_row_lock_wait_in_ms,
1906 total_page_lock_count, total_page_lock_wait_count, total_page_lock_wait_in_ms,
1907 avg_page_lock_wait_in_ms, total_index_lock_promotion_attempt_count,
1908 total_index_lock_promotion_count, data_compression_desc,
1909 page_latch_wait_count, page_latch_wait_in_ms, page_io_latch_wait_count, page_io_latch_wait_in_ms)
1910 SELECT index_sanity_id, ipp.database_id, ipp.schema_name,
1911 COUNT(*), SUM(row_count), SUM(reserved_MB), SUM(reserved_LOB_MB),
1912 SUM(reserved_row_overflow_MB),
1913 SUM(range_scan_count),
1914 SUM(singleton_lookup_count),
1915 SUM(leaf_delete_count),
1916 SUM(leaf_update_count),
1917 SUM(forwarded_fetch_count),
1918 SUM(row_lock_count),
1919 SUM(row_lock_wait_count),
1920 SUM(row_lock_wait_in_ms),
1921 CASE WHEN SUM(row_lock_wait_in_ms) > 0 THEN
1922 SUM(row_lock_wait_in_ms)/(1.*SUM(row_lock_wait_count))
1923 ELSE 0 END AS avg_row_lock_wait_in_ms,
1924 SUM(page_lock_count),
1925 SUM(page_lock_wait_count),
1926 SUM(page_lock_wait_in_ms),
1927 CASE WHEN SUM(page_lock_wait_in_ms) > 0 THEN
1928 SUM(page_lock_wait_in_ms)/(1.*SUM(page_lock_wait_count))
1929 ELSE 0 END AS avg_page_lock_wait_in_ms,
1930 SUM(index_lock_promotion_attempt_count),
1931 SUM(index_lock_promotion_count),
1932 LEFT(MAX(data_compression_info.data_compression_rollup),4000),
1933 SUM(page_latch_wait_count),
1934 SUM(page_latch_wait_in_ms),
1935 SUM(page_io_latch_wait_count),
1936 SUM(page_io_latch_wait_in_ms)
1937 FROM #IndexPartitionSanity ipp
1938 /* individual partitions can have distinct compression settings, just roll them into a list here*/
1939 OUTER APPLY (SELECT STUFF((
1940 SELECT N', ' + data_compression_desc
1941 FROM #IndexPartitionSanity ipp2
1942 WHERE ipp.[object_id]=ipp2.[object_id]
1943 AND ipp.[index_id]=ipp2.[index_id]
1944 AND ipp.database_id = ipp2.database_id
1945 AND ipp.schema_name = ipp2.schema_name
1946 ORDER BY ipp2.partition_number
1947 FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)'), 1, 1, ''))
1948 data_compression_info(data_compression_rollup)
1949 GROUP BY index_sanity_id, ipp.database_id, ipp.schema_name
1950 ORDER BY index_sanity_id
1951OPTION ( RECOMPILE );
1952
1953RAISERROR (N'Determining index usefulness',0,1) WITH NOWAIT;
1954UPDATE #MissingIndexes
1955SET is_low = CASE WHEN (user_seeks + user_scans) < 5000
1956 OR unique_compiles = 1
1957 THEN 1
1958 ELSE 0
1959 END;
1960
1961RAISERROR (N'Updating #IndexSanity.referenced_by_foreign_key',0,1) WITH NOWAIT;
1962UPDATE #IndexSanity
1963 SET is_referenced_by_foreign_key=1
1964FROM #IndexSanity s
1965JOIN #ForeignKeys fk ON
1966 s.object_id=fk.referenced_object_id
1967 AND s.database_id=fk.database_id
1968 AND LEFT(s.key_column_names,LEN(fk.referenced_fk_columns)) = fk.referenced_fk_columns;
1969
1970RAISERROR (N'Update index_secret on #IndexSanity for NC indexes.',0,1) WITH NOWAIT;
1971UPDATE nc
1972SET secret_columns=
1973 N'[' +
1974 CASE tb.count_key_columns WHEN 0 THEN '1' ELSE CAST(tb.count_key_columns AS NVARCHAR(10)) END +
1975 CASE nc.is_unique WHEN 1 THEN N' INCLUDE' ELSE N' KEY' END +
1976 CASE WHEN tb.count_key_columns > 1 THEN N'S] ' ELSE N'] ' END +
1977 CASE tb.index_id WHEN 0 THEN '[RID]' ELSE LTRIM(tb.key_column_names) +
1978 /* Uniquifiers only needed on non-unique clustereds-- not heaps */
1979 CASE tb.is_unique WHEN 0 THEN ' [UNIQUIFIER]' ELSE N'' END
1980 END
1981 , count_secret_columns=
1982 CASE tb.index_id WHEN 0 THEN 1 ELSE
1983 tb.count_key_columns +
1984 CASE tb.is_unique WHEN 0 THEN 1 ELSE 0 END
1985 END
1986FROM #IndexSanity AS nc
1987JOIN #IndexSanity AS tb ON nc.object_id=tb.object_id
1988 AND nc.database_id = tb.database_id
1989 AND nc.schema_name = tb.schema_name
1990 AND tb.index_id IN (0,1)
1991WHERE nc.index_id > 1;
1992
1993RAISERROR (N'Update index_secret on #IndexSanity for heaps and non-unique clustered.',0,1) WITH NOWAIT;
1994UPDATE tb
1995SET secret_columns= CASE tb.index_id WHEN 0 THEN '[RID]' ELSE '[UNIQUIFIER]' END
1996 , count_secret_columns = 1
1997FROM #IndexSanity AS tb
1998WHERE tb.index_id = 0 /*Heaps-- these have the RID */
1999 OR (tb.index_id=1 AND tb.is_unique=0); /* Non-unique CX: has uniquifer (when needed) */
2000
2001
2002RAISERROR (N'Populate #IndexCreateTsql.',0,1) WITH NOWAIT;
2003INSERT #IndexCreateTsql (index_sanity_id, create_tsql)
2004SELECT
2005 index_sanity_id,
2006 ISNULL (
2007 /* Script drops for disabled non-clustered indexes*/
2008 CASE WHEN is_disabled = 1 AND index_id <> 1
2009 THEN N'--DROP INDEX ' + QUOTENAME([index_name]) + N' ON '
2010 + QUOTENAME([schema_name]) + N'.' + QUOTENAME([object_name])
2011 ELSE
2012 CASE index_id WHEN 0 THEN N'ALTER TABLE ' + QUOTENAME([database_name]) + N'.' + QUOTENAME([schema_name]) + N'.' + QUOTENAME([object_name]) + ' REBUILD;'
2013 ELSE
2014 CASE WHEN is_XML = 1 OR is_spatial=1 THEN N'' /* Not even trying for these just yet...*/
2015 ELSE
2016 CASE WHEN is_primary_key=1 THEN
2017 N'ALTER TABLE ' + QUOTENAME([schema_name]) +
2018 N'.' + QUOTENAME([object_name]) +
2019 N' ADD CONSTRAINT [' +
2020 index_name +
2021 N'] PRIMARY KEY ' +
2022 CASE WHEN index_id=1 THEN N'CLUSTERED (' ELSE N'(' END +
2023 key_column_names_with_sort_order_no_types + N' )'
2024 WHEN is_CX_columnstore= 1 THEN
2025 N'CREATE CLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(index_name) + N' on ' + QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name])
2026 ELSE /*Else not a PK or cx columnstore */
2027 N'CREATE ' +
2028 CASE WHEN is_unique=1 THEN N'UNIQUE ' ELSE N'' END +
2029 CASE WHEN index_id=1 THEN N'CLUSTERED ' ELSE N'' END +
2030 CASE WHEN is_NC_columnstore=1 THEN N'NONCLUSTERED COLUMNSTORE '
2031 ELSE N'' END +
2032 N'INDEX ['
2033 + index_name + N'] ON ' +
2034 QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name]) +
2035 CASE WHEN is_NC_columnstore=1 THEN
2036 N' (' + ISNULL(include_column_names_no_types,'') + N' )'
2037 ELSE /*Else not colunnstore */
2038 N' (' + ISNULL(key_column_names_with_sort_order_no_types,'') + N' )'
2039 + CASE WHEN include_column_names_no_types IS NOT NULL THEN
2040 N' INCLUDE (' + include_column_names_no_types + N')'
2041 ELSE N''
2042 END
2043 END /*End non-colunnstore case */
2044 + CASE WHEN filter_definition <> N'' THEN N' WHERE ' + filter_definition ELSE N'' END
2045 END /*End Non-PK index CASE */
2046 + CASE WHEN is_NC_columnstore=0 AND is_CX_columnstore=0 THEN
2047 N' WITH ('
2048 + N'FILLFACTOR=' + CASE fill_factor WHEN 0 THEN N'100' ELSE CAST(fill_factor AS NVARCHAR(5)) END + ', '
2049 + N'ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?'
2050 + N')'
2051 ELSE N'' END
2052 + N';'
2053 END /*End non-spatial and non-xml CASE */
2054 END
2055 END, '[Unknown Error]')
2056 AS create_tsql
2057FROM #IndexSanity;
2058
2059RAISERROR (N'Populate #PartitionCompressionInfo.',0,1) WITH NOWAIT;
2060WITH maps
2061 AS
2062 (
2063 SELECT ips.index_sanity_id,
2064 ips.partition_number,
2065 ips.data_compression_desc,
2066 ips.partition_number - ROW_NUMBER() OVER ( PARTITION BY ips.index_sanity_id, ips.data_compression_desc
2067 ORDER BY ips.partition_number ) AS rn
2068 FROM #IndexPartitionSanity AS ips
2069 )
2070SELECT *
2071INTO #maps
2072FROM maps;
2073
2074WITH grps
2075 AS
2076 (
2077 SELECT MIN(maps.partition_number) AS MinKey,
2078 MAX(maps.partition_number) AS MaxKey,
2079 maps.index_sanity_id,
2080 maps.data_compression_desc
2081 FROM #maps AS maps
2082 GROUP BY maps.rn, maps.index_sanity_id, maps.data_compression_desc
2083 )
2084SELECT *
2085INTO #grps
2086FROM grps;
2087
2088INSERT #PartitionCompressionInfo ( index_sanity_id, partition_compression_detail )
2089SELECT DISTINCT
2090 grps.index_sanity_id,
2091 SUBSTRING(
2092 ( STUFF(
2093 ( SELECT N', ' + N' Partition'
2094 + CASE
2095 WHEN grps2.MinKey < grps2.MaxKey
2096 THEN
2097 + N's ' + CAST(grps2.MinKey AS NVARCHAR(10)) + N' - '
2098 + CAST(grps2.MaxKey AS NVARCHAR(10)) + N' use ' + grps2.data_compression_desc
2099 ELSE
2100 N' ' + CAST(grps2.MinKey AS NVARCHAR(10)) + N' uses ' + grps2.data_compression_desc
2101 END AS Partitions
2102 FROM #grps AS grps2
2103 WHERE grps2.index_sanity_id = grps.index_sanity_id
2104 ORDER BY grps2.MinKey, grps2.MaxKey
2105 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')), 0, 8000) AS partition_compression_detail
2106FROM #grps AS grps;
2107
2108RAISERROR (N'Update #PartitionCompressionInfo.',0,1) WITH NOWAIT;
2109UPDATE sz
2110SET sz.data_compression_desc = pci.partition_compression_detail
2111FROM #IndexSanitySize sz
2112JOIN #PartitionCompressionInfo AS pci
2113ON pci.index_sanity_id = sz.index_sanity_id;
2114
2115RAISERROR (N'Update #IndexSanity for filtered indexes with columns not in the index definition.',0,1) WITH NOWAIT;
2116UPDATE #IndexSanity
2117SET filter_columns_not_in_index = D1.filter_columns_not_in_index
2118FROM #IndexSanity si
2119 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name AS col_definition
2120 FROM #FilteredIndexes AS c
2121 WHERE c.database_id= si.database_id
2122 AND c.schema_name = si.schema_name
2123 AND c.table_name = si.object_name
2124 AND c.index_name = si.index_name
2125 ORDER BY c.index_sanity_id
2126 FOR XML PATH('') , TYPE).value('.', 'nvarchar(max)'), 1, 1,''))) D1
2127 ( filter_columns_not_in_index );
2128
2129
2130/*This is for debugging*/
2131--SELECT '#IndexSanity' AS table_name, * FROM #IndexSanity;
2132--SELECT '#IndexPartitionSanity' AS table_name, * FROM #IndexPartitionSanity;
2133--SELECT '#IndexSanitySize' AS table_name, * FROM #IndexSanitySize;
2134--SELECT '#IndexColumns' AS table_name, * FROM #IndexColumns;
2135--SELECT '#MissingIndexes' AS table_name, * FROM #MissingIndexes;
2136--SELECT '#ForeignKeys' AS table_name, * FROM #ForeignKeys;
2137--SELECT '#BlitzIndexResults' AS table_name, * FROM #BlitzIndexResults;
2138--SELECT '#IndexCreateTsql' AS table_name, * FROM #IndexCreateTsql;
2139--SELECT '#DatabaseList' AS table_name, * FROM #DatabaseList;
2140--SELECT '#Statistics' AS table_name, * FROM #Statistics;
2141--SELECT '#PartitionCompressionInfo' AS table_name, * FROM #PartitionCompressionInfo;
2142--SELECT '#ComputedColumns' AS table_name, * FROM #ComputedColumns;
2143--SELECT '#TraceStatus' AS table_name, * FROM #TraceStatus;
2144--SELECT '#CheckConstraints' AS table_name, * FROM #CheckConstraints;
2145--SELECT '#FilteredIndexes' AS table_name, * FROM #FilteredIndexes;
2146/*End debug*/
2147
2148
2149----------------------------------------
2150--STEP 3: DIAGNOSE THE PATIENT
2151----------------------------------------
2152
2153
2154BEGIN TRY
2155----------------------------------------
2156--If @TableName is specified, just return information for that table.
2157--The @Mode parameter doesn't matter if you're looking at a specific table.
2158----------------------------------------
2159IF @TableName IS NOT NULL
2160BEGIN
2161 RAISERROR(N'@TableName specified, giving detail only on that table.', 0,1) WITH NOWAIT;
2162
2163 --We do a left join here in case this is a disabled NC.
2164 --In that case, it won't have any size info/pages allocated.
2165
2166
2167 WITH table_mode_cte AS (
2168 SELECT
2169 s.db_schema_object_indexid,
2170 s.key_column_names,
2171 s.index_definition,
2172 ISNULL(s.secret_columns,N'') AS secret_columns,
2173 s.fill_factor,
2174 s.index_usage_summary,
2175 sz.index_op_stats,
2176 ISNULL(sz.index_size_summary,'') /*disabled NCs will be null*/ AS index_size_summary,
2177 partition_compression_detail ,
2178 ISNULL(sz.index_lock_wait_summary,'') AS index_lock_wait_summary,
2179 s.is_referenced_by_foreign_key,
2180 (SELECT COUNT(*)
2181 FROM #ForeignKeys fk WHERE fk.parent_object_id=s.object_id
2182 AND PATINDEX (fk.parent_fk_columns, s.key_column_names)=1) AS FKs_covered_by_index,
2183 s.last_user_seek,
2184 s.last_user_scan,
2185 s.last_user_lookup,
2186 s.last_user_update,
2187 s.create_date,
2188 s.modify_date,
2189 sz.page_latch_wait_count,
2190 CONVERT(VARCHAR(10), (sz.page_latch_wait_in_ms / 1000) / 86400) + ':' + CONVERT(VARCHAR(20), DATEADD(s, (sz.page_latch_wait_in_ms / 1000), 0), 108) AS page_latch_wait_time,
2191 sz.page_io_latch_wait_count,
2192 CONVERT(VARCHAR(10), (sz.page_io_latch_wait_in_ms / 1000) / 86400) + ':' + CONVERT(VARCHAR(20), DATEADD(s, (sz.page_io_latch_wait_in_ms / 1000), 0), 108) AS page_io_latch_wait_time,
2193 ct.create_tsql,
2194 1 AS display_order
2195 FROM #IndexSanity s
2196 LEFT JOIN #IndexSanitySize sz ON
2197 s.index_sanity_id=sz.index_sanity_id
2198 LEFT JOIN #IndexCreateTsql ct ON
2199 s.index_sanity_id=ct.index_sanity_id
2200 LEFT JOIN #PartitionCompressionInfo pci ON
2201 pci.index_sanity_id = s.index_sanity_id
2202 WHERE s.[object_id]=@ObjectID
2203 UNION ALL
2204 SELECT N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) +
2205 N' (' + @ScriptVersionName + ')' ,
2206 N'SQL Server First Responder Kit' ,
2207 N'http://FirstResponderKit.org' ,
2208 N'From Your Community Volunteers',
2209 NULL,@DaysUptimeInsertValue,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
2210 0 AS display_order
2211 )
2212 SELECT
2213 db_schema_object_indexid AS [Details: db_schema.table.index(indexid)],
2214 index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
2215 secret_columns AS [Secret Columns],
2216 fill_factor AS [Fillfactor],
2217 index_usage_summary AS [Usage Stats],
2218 index_op_stats AS [Op Stats],
2219 index_size_summary AS [Size],
2220 partition_compression_detail AS [Compression Type],
2221 index_lock_wait_summary AS [Lock Waits],
2222 is_referenced_by_foreign_key AS [Referenced by FK?],
2223 FKs_covered_by_index AS [FK Covered by Index?],
2224 last_user_seek AS [Last User Seek],
2225 last_user_scan AS [Last User Scan],
2226 last_user_lookup AS [Last User Lookup],
2227 last_user_update AS [Last User Write],
2228 create_date AS [Created],
2229 modify_date AS [Last Modified],
2230 page_latch_wait_count AS [Page Latch Wait Count],
2231 page_latch_wait_time as [Page Latch Wait Time (D:H:M:S)],
2232 page_io_latch_wait_count AS [Page IO Latch Wait Count],
2233 page_io_latch_wait_time as [Page IO Latch Wait Time (D:H:M:S)],
2234 create_tsql AS [Create TSQL]
2235 FROM table_mode_cte
2236 ORDER BY display_order ASC, key_column_names ASC
2237 OPTION ( RECOMPILE );
2238
2239 IF (SELECT TOP 1 [object_id] FROM #MissingIndexes mi) IS NOT NULL
2240 BEGIN;
2241
2242 WITH create_date AS (
2243 SELECT i.database_id,
2244 i.schema_name,
2245 i.[object_id],
2246 ISNULL(NULLIF(MAX(DATEDIFF(DAY, i.create_date, SYSDATETIME())), 0), 1) AS create_days
2247 FROM #IndexSanity AS i
2248 GROUP BY i.database_id, i.schema_name, i.object_id
2249 )
2250 SELECT N'Missing index.' AS Finding ,
2251 N'http://BrentOzar.com/go/Indexaphobia' AS URL ,
2252 mi.[statement] +
2253 ' Est. Benefit: '
2254 + CASE WHEN magic_benefit_number >= 922337203685477 THEN '>= 922,337,203,685,477'
2255 ELSE REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
2256 (magic_benefit_number / CASE WHEN cd.create_days < @DaysUptime THEN cd.create_days ELSE @DaysUptime END)
2257 AS BIGINT) AS MONEY), 1), '.00', '')
2258 END AS [Estimated Benefit],
2259 missing_index_details AS [Missing Index Request] ,
2260 index_estimated_impact AS [Estimated Impact],
2261 create_tsql AS [Create TSQL]
2262 FROM #MissingIndexes mi
2263 LEFT JOIN create_date AS cd
2264 ON mi.[object_id] = cd.object_id
2265 AND mi.database_id = cd.database_id
2266 AND mi.schema_name = cd.schema_name
2267 WHERE mi.[object_id] = @ObjectID
2268 /* Minimum benefit threshold = 100k/day of uptime OR since table creation date, whichever is lower*/
2269 AND (magic_benefit_number / CASE WHEN cd.create_days < @DaysUptime THEN cd.create_days ELSE @DaysUptime END) >= 100000
2270 ORDER BY magic_benefit_number DESC
2271 OPTION ( RECOMPILE );
2272 END;
2273 ELSE
2274 SELECT 'No missing indexes.' AS finding;
2275
2276 SELECT
2277 column_name AS [Column Name],
2278 (SELECT COUNT(*)
2279 FROM #IndexColumns c2
2280 WHERE c2.column_name=c.column_name
2281 AND c2.key_ordinal IS NOT NULL)
2282 + CASE WHEN c.index_id = 1 AND c.key_ordinal IS NOT NULL THEN
2283 -1+ (SELECT COUNT(DISTINCT index_id)
2284 FROM #IndexColumns c3
2285 WHERE c3.index_id NOT IN (0,1))
2286 ELSE 0 END
2287 AS [Found In],
2288 system_type_name +
2289 CASE max_length WHEN -1 THEN N' (max)' ELSE
2290 CASE
2291 WHEN system_type_name IN (N'char',N'varchar',N'binary',N'varbinary') THEN N' (' + CAST(max_length AS NVARCHAR(20)) + N')'
2292 WHEN system_type_name IN (N'nchar',N'nvarchar') THEN N' (' + CAST(max_length/2 AS NVARCHAR(20)) + N')'
2293 ELSE ''
2294 END
2295 END
2296 AS [Type],
2297 CASE is_computed WHEN 1 THEN 'yes' ELSE '' END AS [Computed?],
2298 max_length AS [Length (max bytes)],
2299 [precision] AS [Prec],
2300 [scale] AS [Scale],
2301 CASE is_nullable WHEN 1 THEN 'yes' ELSE '' END AS [Nullable?],
2302 CASE is_identity WHEN 1 THEN 'yes' ELSE '' END AS [Identity?],
2303 CASE is_replicated WHEN 1 THEN 'yes' ELSE '' END AS [Replicated?],
2304 CASE is_sparse WHEN 1 THEN 'yes' ELSE '' END AS [Sparse?],
2305 CASE is_filestream WHEN 1 THEN 'yes' ELSE '' END AS [Filestream?],
2306 collation_name AS [Collation]
2307 FROM #IndexColumns AS c
2308 WHERE index_id IN (0,1);
2309
2310 IF (SELECT TOP 1 parent_object_id FROM #ForeignKeys) IS NOT NULL
2311 BEGIN
2312 SELECT [database_name] + N':' + parent_object_name + N': ' + foreign_key_name AS [Foreign Key],
2313 parent_fk_columns AS [Foreign Key Columns],
2314 referenced_object_name AS [Referenced Table],
2315 referenced_fk_columns AS [Referenced Table Columns],
2316 is_disabled AS [Is Disabled?],
2317 is_not_trusted AS [Not Trusted?],
2318 is_not_for_replication [Not for Replication?],
2319 [update_referential_action_desc] AS [Cascading Updates?],
2320 [delete_referential_action_desc] AS [Cascading Deletes?]
2321 FROM #ForeignKeys
2322 ORDER BY [Foreign Key]
2323 OPTION ( RECOMPILE );
2324 END;
2325 ELSE
2326 SELECT 'No foreign keys.' AS finding;
2327
2328 /* Show histograms for all stats on this table. More info: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1900 */
2329 IF EXISTS (SELECT * FROM sys.all_objects WHERE name = 'dm_db_stats_histogram')
2330 BEGIN
2331 SET @dsql=N'SELECT s.name AS [Stat Name], c.name AS [Leading Column Name], hist.step_number AS [Step Number],
2332 hist.range_high_key AS [Range High Key], hist.range_rows AS [Range Rows],
2333 hist.equal_rows AS [Equal Rows], hist.distinct_range_rows AS [Distinct Range Rows], hist.average_range_rows AS [Average Range Rows],
2334 s.auto_created AS [Auto-Created], s.user_created AS [User-Created],
2335 props.last_updated AS [Last Updated], s.stats_id AS [StatsID]
2336 FROM sys.stats AS s
2337 INNER JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id AND sc.stats_column_id = 1
2338 INNER JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
2339 CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS props
2340 CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
2341 WHERE s.object_id = @ObjectID
2342 ORDER BY s.auto_created, s.user_created, s.name, hist.step_number;';
2343 EXEC sp_executesql @dsql, N'@ObjectID INT', @ObjectID;
2344 END
2345
2346
2347END;
2348
2349--If @TableName is NOT specified...
2350--Act based on the @Mode and @Filter. (@Filter applies only when @Mode=0 "diagnose")
2351ELSE
2352BEGIN;
2353 IF @Mode IN (0, 4) /* DIAGNOSE*/
2354 BEGIN;
2355 RAISERROR(N'@Mode=0 or 4, we are diagnosing.', 0,1) WITH NOWAIT;
2356
2357 ----------------------------------------
2358 --Multiple Index Personalities: Check_id 0-10
2359 ----------------------------------------
2360 BEGIN;
2361
2362 --SELECT [object_id], key_column_names, database_id
2363 -- FROM #IndexSanity
2364 -- WHERE index_type IN (1,2) /* Clustered, NC only*/
2365 -- AND is_hypothetical = 0
2366 -- AND is_disabled = 0
2367 -- GROUP BY [object_id], key_column_names, database_id
2368 -- HAVING COUNT(*) > 1
2369
2370
2371 RAISERROR('check_id 1: Duplicate keys', 0,1) WITH NOWAIT;
2372 WITH duplicate_indexes
2373 AS ( SELECT [object_id], key_column_names, database_id, [schema_name]
2374 FROM #IndexSanity AS ip
2375 WHERE index_type IN (1,2) /* Clustered, NC only*/
2376 AND is_hypothetical = 0
2377 AND is_disabled = 0
2378 AND is_primary_key = 0
2379 AND EXISTS (
2380 SELECT 1/0
2381 FROM #IndexSanitySize ips
2382 WHERE ip.index_sanity_id = ips.index_sanity_id
2383 AND ip.database_id = ips.database_id
2384 AND ip.schema_name = ips.schema_name
2385 AND ips.total_reserved_MB >= CASE
2386 WHEN (@GetAllDatabases = 1 OR @Mode = 0)
2387 THEN @ThresholdMB
2388 ELSE ips.total_reserved_MB
2389 END
2390 )
2391 GROUP BY [object_id], key_column_names, database_id, [schema_name]
2392 HAVING COUNT(*) > 1)
2393 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2394 secret_columns, index_usage_summary, index_size_summary )
2395 SELECT 1 AS check_id,
2396 ip.index_sanity_id,
2397 50 AS Priority,
2398 'Multiple Index Personalities' AS findings_group,
2399 'Duplicate keys' AS finding,
2400 [database_name] AS [Database Name],
2401 N'http://BrentOzar.com/go/duplicateindex' AS URL,
2402 N'Index Name: ' + ip.index_name + N' Table Name: ' + ip.db_schema_object_name AS details,
2403 ip.index_definition,
2404 ip.secret_columns,
2405 ip.index_usage_summary,
2406 ips.index_size_summary
2407 FROM duplicate_indexes di
2408 JOIN #IndexSanity ip ON di.[object_id] = ip.[object_id]
2409 AND ip.database_id = di.database_id
2410 AND ip.[schema_name] = di.[schema_name]
2411 AND di.key_column_names = ip.key_column_names
2412 JOIN #IndexSanitySize ips ON ip.index_sanity_id = ips.index_sanity_id
2413 AND ip.database_id = ips.database_id
2414 AND ip.schema_name = ips.schema_name
2415 /* WHERE clause limits to only @ThresholdMB or larger duplicate indexes when getting all databases or using PainRelief mode */
2416 WHERE ips.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE ips.total_reserved_MB END
2417 AND ip.is_primary_key = 0
2418 ORDER BY ip.object_id, ip.key_column_names_with_sort_order
2419 OPTION ( RECOMPILE );
2420
2421 RAISERROR('check_id 2: Keys w/ identical leading columns.', 0,1) WITH NOWAIT;
2422 WITH borderline_duplicate_indexes
2423 AS ( SELECT DISTINCT database_id, [object_id], first_key_column_name, key_column_names,
2424 COUNT([object_id]) OVER ( PARTITION BY database_id, [object_id], first_key_column_name ) AS number_dupes
2425 FROM #IndexSanity
2426 WHERE index_type IN (1,2) /* Clustered, NC only*/
2427 AND is_hypothetical=0
2428 AND is_disabled=0
2429 AND is_primary_key = 0)
2430 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2431 secret_columns, index_usage_summary, index_size_summary )
2432 SELECT 2 AS check_id,
2433 ip.index_sanity_id,
2434 60 AS Priority,
2435 'Multiple Index Personalities' AS findings_group,
2436 'Borderline duplicate keys' AS finding,
2437 [database_name] AS [Database Name],
2438 N'http://BrentOzar.com/go/duplicateindex' AS URL,
2439 ip.db_schema_object_indexid AS details,
2440 ip.index_definition,
2441 ip.secret_columns,
2442 ip.index_usage_summary,
2443 ips.index_size_summary
2444 FROM #IndexSanity AS ip
2445 JOIN #IndexSanitySize ips ON ip.index_sanity_id = ips.index_sanity_id
2446 WHERE EXISTS (
2447 SELECT di.[object_id]
2448 FROM borderline_duplicate_indexes AS di
2449 WHERE di.[object_id] = ip.[object_id] AND
2450 di.database_id = ip.database_id AND
2451 di.first_key_column_name = ip.first_key_column_name AND
2452 di.key_column_names <> ip.key_column_names AND
2453 di.number_dupes > 1
2454 )
2455 AND ip.is_primary_key = 0
2456 ORDER BY ip.[schema_name], ip.[object_name], ip.key_column_names, ip.include_column_names
2457 OPTION ( RECOMPILE );
2458
2459 END;
2460 ----------------------------------------
2461 --Aggressive Indexes: Check_id 10-19
2462 ----------------------------------------
2463 BEGIN;
2464
2465 RAISERROR(N'check_id 11: Total lock wait time > 5 minutes (row + page) with long average waits', 0,1) WITH NOWAIT;
2466 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2467 secret_columns, index_usage_summary, index_size_summary )
2468 SELECT 11 AS check_id,
2469 i.index_sanity_id,
2470 10 AS Priority,
2471 N'Aggressive '
2472 + CASE COALESCE((SELECT SUM(1)
2473 FROM #IndexSanity iMe
2474 INNER JOIN #IndexSanity iOthers
2475 ON iMe.database_id = iOthers.database_id
2476 AND iMe.object_id = iOthers.object_id
2477 AND iOthers.index_id > 1
2478 WHERE i.index_sanity_id = iMe.index_sanity_id
2479 AND iOthers.is_hypothetical = 0
2480 AND iOthers.is_disabled = 0
2481 ), 0)
2482 WHEN 0 THEN N'Under-Indexing'
2483 WHEN 1 THEN N'Under-Indexing'
2484 WHEN 2 THEN N'Under-Indexing'
2485 WHEN 3 THEN N'Under-Indexing'
2486 WHEN 4 THEN N'Indexes'
2487 WHEN 5 THEN N'Indexes'
2488 WHEN 6 THEN N'Indexes'
2489 WHEN 7 THEN N'Indexes'
2490 WHEN 8 THEN N'Indexes'
2491 WHEN 9 THEN N'Indexes'
2492 ELSE N'Over-Indexing'
2493 END AS findings_group,
2494 N'Total lock wait time > 5 minutes (row + page) with long average waits' AS finding,
2495 [database_name] AS [Database Name],
2496 N'http://BrentOzar.com/go/AggressiveIndexes' AS URL,
2497 (i.db_schema_object_indexid + N': ' +
2498 sz.index_lock_wait_summary + N' NC indexes on table: ') COLLATE DATABASE_DEFAULT +
2499 CAST(COALESCE((SELECT SUM(1)
2500 FROM #IndexSanity iMe
2501 INNER JOIN #IndexSanity iOthers
2502 ON iMe.database_id = iOthers.database_id
2503 AND iMe.object_id = iOthers.object_id
2504 AND iOthers.index_id > 1
2505 WHERE i.index_sanity_id = iMe.index_sanity_id
2506 AND iOthers.is_hypothetical = 0
2507 AND iOthers.is_disabled = 0
2508 ), 0)
2509 AS NVARCHAR(30)) AS details,
2510 i.index_definition,
2511 i.secret_columns,
2512 i.index_usage_summary,
2513 sz.index_size_summary
2514 FROM #IndexSanity AS i
2515 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2516 WHERE (total_row_lock_wait_in_ms + total_page_lock_wait_in_ms) > 300000
2517 AND (sz.avg_page_lock_wait_in_ms + sz.avg_row_lock_wait_in_ms) > 5000
2518 GROUP BY i.index_sanity_id, [database_name], i.db_schema_object_indexid, sz.index_lock_wait_summary, i.index_definition, i.secret_columns, i.index_usage_summary, sz.index_size_summary, sz.index_sanity_id
2519 ORDER BY 4, [database_name], 8
2520 OPTION ( RECOMPILE );
2521
2522 RAISERROR(N'check_id 12: Total lock wait time > 5 minutes (row + page) with short average waits', 0,1) WITH NOWAIT;
2523 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2524 secret_columns, index_usage_summary, index_size_summary )
2525 SELECT 12 AS check_id,
2526 i.index_sanity_id,
2527 10 AS Priority,
2528 N'Aggressive '
2529 + CASE COALESCE((SELECT SUM(1)
2530 FROM #IndexSanity iMe
2531 INNER JOIN #IndexSanity iOthers
2532 ON iMe.database_id = iOthers.database_id
2533 AND iMe.object_id = iOthers.object_id
2534 AND iOthers.index_id > 1
2535 WHERE i.index_sanity_id = iMe.index_sanity_id
2536 AND iOthers.is_hypothetical = 0
2537 AND iOthers.is_disabled = 0
2538 ), 0)
2539 WHEN 0 THEN N'Under-Indexing'
2540 WHEN 1 THEN N'Under-Indexing'
2541 WHEN 2 THEN N'Under-Indexing'
2542 WHEN 3 THEN N'Under-Indexing'
2543 WHEN 4 THEN N'Indexes'
2544 WHEN 5 THEN N'Indexes'
2545 WHEN 6 THEN N'Indexes'
2546 WHEN 7 THEN N'Indexes'
2547 WHEN 8 THEN N'Indexes'
2548 WHEN 9 THEN N'Indexes'
2549 ELSE N'Over-Indexing'
2550 END AS findings_group,
2551 N'Total lock wait time > 5 minutes (row + page) with short average waits' AS finding,
2552 [database_name] AS [Database Name],
2553 N'http://BrentOzar.com/go/AggressiveIndexes' AS URL,
2554 (i.db_schema_object_indexid + N': ' +
2555 sz.index_lock_wait_summary + N' NC indexes on table: ') COLLATE DATABASE_DEFAULT +
2556 CAST(COALESCE((SELECT SUM(1)
2557 FROM #IndexSanity iMe
2558 INNER JOIN #IndexSanity iOthers
2559 ON iMe.database_id = iOthers.database_id
2560 AND iMe.object_id = iOthers.object_id
2561 AND iOthers.index_id > 1
2562 WHERE i.index_sanity_id = iMe.index_sanity_id
2563 AND iOthers.is_hypothetical = 0
2564 AND iOthers.is_disabled = 0
2565 ),0)
2566 AS NVARCHAR(30)) AS details,
2567 i.index_definition,
2568 i.secret_columns,
2569 i.index_usage_summary,
2570 sz.index_size_summary
2571 FROM #IndexSanity AS i
2572 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2573 WHERE (total_row_lock_wait_in_ms + total_page_lock_wait_in_ms) > 300000
2574 AND (sz.avg_page_lock_wait_in_ms + sz.avg_row_lock_wait_in_ms) < 5000
2575 GROUP BY i.index_sanity_id, [database_name], i.db_schema_object_indexid, sz.index_lock_wait_summary, i.index_definition, i.secret_columns, i.index_usage_summary, sz.index_size_summary, sz.index_sanity_id
2576 ORDER BY 4, [database_name], 8
2577 OPTION ( RECOMPILE );
2578
2579 END;
2580
2581 ----------------------------------------
2582 --Index Hoarder: Check_id 20-29
2583 ----------------------------------------
2584 BEGIN
2585 RAISERROR(N'check_id 20: >=7 NC indexes on any given table. Yes, 7 is an arbitrary number.', 0,1) WITH NOWAIT;
2586 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2587 secret_columns, index_usage_summary, index_size_summary )
2588 SELECT 20 AS check_id,
2589 MAX(i.index_sanity_id) AS index_sanity_id,
2590 100 AS Priority,
2591 'Index Hoarder' AS findings_group,
2592 'Many NC indexes on a single table' AS finding,
2593 [database_name] AS [Database Name],
2594 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2595 CAST (COUNT(*) AS NVARCHAR(30)) + ' NC indexes on ' + i.db_schema_object_name AS details,
2596 i.db_schema_object_name + ' (' + CAST (COUNT(*) AS NVARCHAR(30)) + ' indexes)' AS index_definition,
2597 '' AS secret_columns,
2598 REPLACE(CONVERT(NVARCHAR(30),CAST(SUM(total_reads) AS MONEY), 1), N'.00', N'') + N' reads (ALL); '
2599 + REPLACE(CONVERT(NVARCHAR(30),CAST(SUM(user_updates) AS MONEY), 1), N'.00', N'') + N' writes (ALL); ',
2600 REPLACE(CONVERT(NVARCHAR(30),CAST(MAX(total_rows) AS MONEY), 1), N'.00', N'') + N' rows (MAX)'
2601 + CASE WHEN SUM(total_reserved_MB) > 1024 THEN
2602 N'; ' + CAST(CAST(SUM(total_reserved_MB)/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'GB (ALL)'
2603 WHEN SUM(total_reserved_MB) > 0 THEN
2604 N'; ' + CAST(CAST(SUM(total_reserved_MB) AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'MB (ALL)'
2605 ELSE ''
2606 END AS index_size_summary
2607 FROM #IndexSanity i
2608 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2609 WHERE index_id NOT IN ( 0, 1 )
2610 GROUP BY db_schema_object_name, [i].[database_name]
2611 HAVING COUNT(*) >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0)
2612 THEN 21
2613 ELSE 7
2614 END
2615 ORDER BY i.db_schema_object_name DESC
2616 OPTION ( RECOMPILE );
2617
2618 IF @Filter = 1 /*@Filter=1 is "ignore unusued" */
2619 BEGIN
2620 RAISERROR(N'Skipping checks on unused indexes (21 and 22) because @Filter=1', 0,1) WITH NOWAIT;
2621 END;
2622 ELSE /*Otherwise, go ahead and do the checks*/
2623 BEGIN
2624 RAISERROR(N'check_id 21: >=5 percent of indexes are unused. Yes, 5 is an arbitrary number.', 0,1) WITH NOWAIT;
2625 DECLARE @percent_NC_indexes_unused NUMERIC(29,1);
2626 DECLARE @NC_indexes_unused_reserved_MB NUMERIC(29,1);
2627
2628 SELECT @percent_NC_indexes_unused = ( 100.00 * SUM(CASE
2629 WHEN total_reads = 0
2630 THEN 1
2631 ELSE 0
2632 END) ) / COUNT(*),
2633 @NC_indexes_unused_reserved_MB = SUM(CASE
2634 WHEN total_reads = 0
2635 THEN sz.total_reserved_MB
2636 ELSE 0
2637 END)
2638 FROM #IndexSanity i
2639 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2640 WHERE index_id NOT IN ( 0, 1 )
2641 AND i.is_unique = 0
2642 /*Skipping tables created in the last week, or modified in past 2 days*/
2643 AND i.create_date >= DATEADD(dd,-7,GETDATE())
2644 AND i.modify_date > DATEADD(dd,-2,GETDATE())
2645 OPTION ( RECOMPILE );
2646
2647 IF @percent_NC_indexes_unused >= 5
2648 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2649 secret_columns, index_usage_summary, index_size_summary )
2650 SELECT 21 AS check_id,
2651 MAX(i.index_sanity_id) AS index_sanity_id,
2652 150 AS Priority,
2653 N'Index Hoarder' AS findings_group,
2654 N'More than 5 percent NC indexes are unused' AS finding,
2655 [database_name] AS [Database Name],
2656 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2657 CAST (@percent_NC_indexes_unused AS NVARCHAR(30)) + N' percent NC indexes (' + CAST(COUNT(*) AS NVARCHAR(10)) + N') unused. ' +
2658 N'These take up ' + CAST (@NC_indexes_unused_reserved_MB AS NVARCHAR(30)) + N'MB of space.' AS details,
2659 i.database_name + ' (' + CAST (COUNT(*) AS NVARCHAR(30)) + N' indexes)' AS index_definition,
2660 '' AS secret_columns,
2661 CAST(SUM(total_reads) AS NVARCHAR(256)) + N' reads (ALL); '
2662 + CAST(SUM([user_updates]) AS NVARCHAR(256)) + N' writes (ALL)' AS index_usage_summary,
2663
2664 REPLACE(CONVERT(NVARCHAR(30),CAST(MAX([total_rows]) AS MONEY), 1), '.00', '') + N' rows (MAX)'
2665 + CASE WHEN SUM(total_reserved_MB) > 1024 THEN
2666 N'; ' + CAST(CAST(SUM(total_reserved_MB)/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'GB (ALL)'
2667 WHEN SUM(total_reserved_MB) > 0 THEN
2668 N'; ' + CAST(CAST(SUM(total_reserved_MB) AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'MB (ALL)'
2669 ELSE ''
2670 END AS index_size_summary
2671 FROM #IndexSanity i
2672 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2673 WHERE index_id NOT IN ( 0, 1 )
2674 AND i.is_unique = 0
2675 AND total_reads = 0
2676 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2677 /*Skipping tables created in the last week, or modified in past 2 days*/
2678 AND i.create_date >= DATEADD(dd,-7,GETDATE())
2679 AND i.modify_date > DATEADD(dd,-2,GETDATE())
2680 GROUP BY i.database_name
2681 OPTION ( RECOMPILE );
2682
2683 RAISERROR(N'check_id 22: NC indexes with 0 reads. (Borderline) and >= 10,000 writes', 0,1) WITH NOWAIT;
2684 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2685 secret_columns, index_usage_summary, index_size_summary )
2686 SELECT 22 AS check_id,
2687 i.index_sanity_id,
2688 100 AS Priority,
2689 N'Index Hoarder' AS findings_group,
2690 N'Unused NC index with High Writes' AS finding,
2691 [database_name] AS [Database Name],
2692 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2693 N'0 reads: ' + i.db_schema_object_indexid AS details,
2694 i.index_definition,
2695 i.secret_columns,
2696 i.index_usage_summary,
2697 sz.index_size_summary
2698 FROM #IndexSanity AS i
2699 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2700 WHERE i.total_reads=0
2701 AND i.user_updates >= 10000
2702 AND i.index_id NOT IN (0,1) /*NCs only*/
2703 AND i.is_unique = 0
2704 AND sz.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE sz.total_reserved_MB END
2705 ORDER BY i.db_schema_object_indexid
2706 OPTION ( RECOMPILE );
2707 END; /*end checks only run when @Filter <> 1*/
2708
2709 RAISERROR(N'check_id 23: Indexes with 7 or more columns. (Borderline)', 0,1) WITH NOWAIT;
2710 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2711 secret_columns, index_usage_summary, index_size_summary )
2712 SELECT 23 AS check_id,
2713 i.index_sanity_id,
2714 150 AS Priority,
2715 N'Index Hoarder' AS findings_group,
2716 N'Borderline: Wide indexes (7 or more columns)' AS finding,
2717 [database_name] AS [Database Name],
2718 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2719 CAST(count_key_columns + count_included_columns AS NVARCHAR(10)) + ' columns on '
2720 + i.db_schema_object_indexid AS details, i.index_definition,
2721 i.secret_columns,
2722 i.index_usage_summary,
2723 sz.index_size_summary
2724 FROM #IndexSanity AS i
2725 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2726 WHERE ( count_key_columns + count_included_columns ) >= 7
2727 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2728 OPTION ( RECOMPILE );
2729
2730 RAISERROR(N'check_id 24: Wide clustered indexes (> 3 columns or > 16 bytes).', 0,1) WITH NOWAIT;
2731 WITH count_columns AS (
2732 SELECT database_id, [object_id],
2733 SUM(CASE max_length WHEN -1 THEN 0 ELSE max_length END) AS sum_max_length
2734 FROM #IndexColumns ic
2735 WHERE index_id IN (1,0) /*Heap or clustered only*/
2736 AND key_ordinal > 0
2737 GROUP BY database_id, object_id
2738 )
2739 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2740 secret_columns, index_usage_summary, index_size_summary )
2741 SELECT 24 AS check_id,
2742 i.index_sanity_id,
2743 150 AS Priority,
2744 N'Index Hoarder' AS findings_group,
2745 N'Wide clustered index (> 3 columns OR > 16 bytes)' AS finding,
2746 [database_name] AS [Database Name],
2747 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2748 CAST (i.count_key_columns AS NVARCHAR(10)) + N' columns with potential size of '
2749 + CAST(cc.sum_max_length AS NVARCHAR(10))
2750 + N' bytes in clustered index:' + i.db_schema_object_name
2751 + N'. ' +
2752 (SELECT CAST(COUNT(*) AS NVARCHAR(23))
2753 FROM #IndexSanity i2
2754 WHERE i2.[object_id]=i.[object_id]
2755 AND i2.database_id = i.database_id
2756 AND i2.index_id <> 1
2757 AND i2.is_disabled=0
2758 AND i2.is_hypothetical=0)
2759 + N' NC indexes on the table.'
2760 AS details,
2761 i.index_definition,
2762 secret_columns,
2763 i.index_usage_summary,
2764 ip.index_size_summary
2765 FROM #IndexSanity i
2766 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2767 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2768 AND i.database_id = cc.database_id
2769 WHERE index_id = 1 /* clustered only */
2770 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2771 AND
2772 (count_key_columns > 3 /*More than three key columns.*/
2773 OR cc.sum_max_length > 16 /*More than 16 bytes in key */)
2774 AND i.is_CX_columnstore = 0
2775 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2776
2777 RAISERROR(N'check_id 25: Addicted to nullable columns.', 0,1) WITH NOWAIT;
2778 WITH count_columns AS (
2779 SELECT [object_id],
2780 [database_id],
2781 [schema_name],
2782 SUM(CASE is_nullable WHEN 1 THEN 0 ELSE 1 END) AS non_nullable_columns,
2783 COUNT(*) AS total_columns
2784 FROM #IndexColumns ic
2785 WHERE index_id IN (1,0) /*Heap or clustered only*/
2786 GROUP BY [object_id],
2787 [database_id],
2788 [schema_name]
2789 )
2790 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2791 secret_columns, index_usage_summary, index_size_summary )
2792 SELECT 25 AS check_id,
2793 i.index_sanity_id,
2794 200 AS Priority,
2795 N'Index Hoarder' AS findings_group,
2796 N'Addicted to nulls' AS finding,
2797 [database_name] AS [Database Name],
2798 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2799 i.db_schema_object_name
2800 + N' allows null in ' + CAST((total_columns-non_nullable_columns) AS NVARCHAR(10))
2801 + N' of ' + CAST(total_columns AS NVARCHAR(10))
2802 + N' columns.' AS details,
2803 i.index_definition,
2804 secret_columns,
2805 ISNULL(i.index_usage_summary,''),
2806 ISNULL(ip.index_size_summary,'')
2807 FROM #IndexSanity i
2808 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2809 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2810 AND cc.database_id = ip.database_id
2811 AND cc.[schema_name] = ip.[schema_name]
2812 WHERE i.index_id IN (1,0)
2813 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2814 AND cc.non_nullable_columns < 2
2815 AND cc.total_columns > 3
2816 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2817
2818 RAISERROR(N'check_id 26: Wide tables (35+ cols or > 2000 non-LOB bytes).', 0,1) WITH NOWAIT;
2819 WITH count_columns AS (
2820 SELECT [object_id],
2821 [database_id],
2822 [schema_name],
2823 SUM(CASE max_length WHEN -1 THEN 1 ELSE 0 END) AS count_lob_columns,
2824 SUM(CASE max_length WHEN -1 THEN 0 ELSE max_length END) AS sum_max_length,
2825 COUNT(*) AS total_columns
2826 FROM #IndexColumns ic
2827 WHERE index_id IN (1,0) /*Heap or clustered only*/
2828 GROUP BY [object_id],
2829 [database_id],
2830 [schema_name]
2831 )
2832 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2833 secret_columns, index_usage_summary, index_size_summary )
2834 SELECT 26 AS check_id,
2835 i.index_sanity_id,
2836 150 AS Priority,
2837 N'Index Hoarder' AS findings_group,
2838 N'Wide tables: 35+ cols or > 2000 non-LOB bytes' AS finding,
2839 [database_name] AS [Database Name],
2840 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2841 i.db_schema_object_name
2842 + N' has ' + CAST((total_columns) AS NVARCHAR(10))
2843 + N' total columns with a max possible width of ' + CAST(sum_max_length AS NVARCHAR(10))
2844 + N' bytes.' +
2845 CASE WHEN count_lob_columns > 0 THEN CAST((count_lob_columns) AS NVARCHAR(10))
2846 + ' columns are LOB types.' ELSE ''
2847 END
2848 AS details,
2849 i.index_definition,
2850 secret_columns,
2851 ISNULL(i.index_usage_summary,''),
2852 ISNULL(ip.index_size_summary,'')
2853 FROM #IndexSanity i
2854 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2855 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2856 AND cc.database_id = i.database_id
2857 AND cc.[schema_name] = i.[schema_name]
2858 WHERE i.index_id IN (1,0)
2859 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2860 AND
2861 (cc.total_columns >= 35 OR
2862 cc.sum_max_length >= 2000)
2863 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2864
2865 RAISERROR(N'check_id 27: Addicted to strings.', 0,1) WITH NOWAIT;
2866 WITH count_columns AS (
2867 SELECT [object_id],
2868 [database_id],
2869 [schema_name],
2870 SUM(CASE WHEN system_type_name IN ('varchar','nvarchar','char') OR max_length=-1 THEN 1 ELSE 0 END) AS string_or_LOB_columns,
2871 COUNT(*) AS total_columns
2872 FROM #IndexColumns ic
2873 WHERE index_id IN (1,0) /*Heap or clustered only*/
2874 GROUP BY [object_id],
2875 [database_id],
2876 [schema_name]
2877 )
2878 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2879 secret_columns, index_usage_summary, index_size_summary )
2880 SELECT 27 AS check_id,
2881 i.index_sanity_id,
2882 200 AS Priority,
2883 N'Index Hoarder' AS findings_group,
2884 N'Addicted to strings' AS finding,
2885 [database_name] AS [Database Name],
2886 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2887 i.db_schema_object_name
2888 + N' uses string or LOB types for ' + CAST((string_or_LOB_columns) AS NVARCHAR(10))
2889 + N' of ' + CAST(total_columns AS NVARCHAR(10))
2890 + N' columns. Check if data types are valid.' AS details,
2891 i.index_definition,
2892 secret_columns,
2893 ISNULL(i.index_usage_summary,''),
2894 ISNULL(ip.index_size_summary,'')
2895 FROM #IndexSanity i
2896 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2897 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2898 AND cc.database_id = i.database_id
2899 AND cc.[schema_name] = i.[schema_name]
2900 CROSS APPLY (SELECT cc.total_columns - string_or_LOB_columns AS non_string_or_lob_columns) AS calc1
2901 WHERE i.index_id IN (1,0)
2902 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2903 AND calc1.non_string_or_lob_columns <= 1
2904 AND cc.total_columns > 3
2905 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2906
2907 RAISERROR(N'check_id 28: Non-unique clustered index.', 0,1) WITH NOWAIT;
2908 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2909 secret_columns, index_usage_summary, index_size_summary )
2910 SELECT 28 AS check_id,
2911 i.index_sanity_id,
2912 100 AS Priority,
2913 N'Index Hoarder' AS findings_group,
2914 N'Non-Unique clustered index' AS finding,
2915 [database_name] AS [Database Name],
2916 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2917 N'Uniquifiers will be required! Clustered index: ' + i.db_schema_object_name
2918 + N' and all NC indexes. ' +
2919 (SELECT CAST(COUNT(*) AS NVARCHAR(23))
2920 FROM #IndexSanity i2
2921 WHERE i2.[object_id]=i.[object_id]
2922 AND i2.database_id = i.database_id
2923 AND i2.index_id <> 1
2924 AND i2.is_disabled=0
2925 AND i2.is_hypothetical=0)
2926 + N' NC indexes on the table.'
2927 AS details,
2928 i.index_definition,
2929 secret_columns,
2930 i.index_usage_summary,
2931 ip.index_size_summary
2932 FROM #IndexSanity i
2933 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2934 WHERE index_id = 1 /* clustered only */
2935 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2936 AND is_unique=0 /* not unique */
2937 AND is_CX_columnstore=0 /* not a clustered columnstore-- no unique option on those */
2938 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2939
2940 RAISERROR(N'check_id 29: NC indexes with 0 reads. (Borderline) and < 10,000 writes', 0,1) WITH NOWAIT;
2941 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2942 secret_columns, index_usage_summary, index_size_summary )
2943 SELECT 29 AS check_id,
2944 i.index_sanity_id,
2945 150 AS Priority,
2946 N'Index Hoarder' AS findings_group,
2947 N'Unused NC index with Low Writes' AS finding,
2948 [database_name] AS [Database Name],
2949 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2950 N'0 reads: ' + i.db_schema_object_indexid AS details,
2951 i.index_definition,
2952 i.secret_columns,
2953 i.index_usage_summary,
2954 sz.index_size_summary
2955 FROM #IndexSanity AS i
2956 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2957 WHERE i.total_reads=0
2958 AND i.user_updates < 10000
2959 AND i.index_id NOT IN (0,1) /*NCs only*/
2960 AND i.is_unique = 0
2961 AND sz.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE sz.total_reserved_MB END
2962 /*Skipping tables created in the last week, or modified in past 2 days*/
2963 AND i.create_date >= DATEADD(dd,-7,GETDATE())
2964 AND i.modify_date > DATEADD(dd,-2,GETDATE())
2965 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2966 ORDER BY i.db_schema_object_indexid
2967 OPTION ( RECOMPILE );
2968
2969 END;
2970 ----------------------------------------
2971 --Feature-Phobic Indexes: Check_id 30-39
2972 ----------------------------------------
2973 BEGIN
2974 RAISERROR(N'check_id 30: No indexes with includes', 0,1) WITH NOWAIT;
2975 /* This does not work the way you'd expect with @GetAllDatabases = 1. For details:
2976 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/825
2977 */
2978
2979 SELECT database_name,
2980 SUM(CASE WHEN count_included_columns > 0 THEN 1 ELSE 0 END) AS number_indexes_with_includes,
2981 100.* SUM(CASE WHEN count_included_columns > 0 THEN 1 ELSE 0 END) / ( 1.0 * COUNT(*) ) AS percent_indexes_with_includes
2982 INTO #index_includes
2983 FROM #IndexSanity
2984 WHERE is_hypothetical = 0
2985 AND is_disabled = 0
2986 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2987 GROUP BY database_name;
2988
2989 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2990 secret_columns, index_usage_summary, index_size_summary )
2991 SELECT 30 AS check_id,
2992 NULL AS index_sanity_id,
2993 250 AS Priority,
2994 N'Feature-Phobic Indexes' AS findings_group,
2995 database_name AS [Database Name],
2996 N'No indexes use includes' AS finding, 'http://BrentOzar.com/go/IndexFeatures' AS URL,
2997 N'No indexes use includes' AS details,
2998 database_name + N' (Entire database)' AS index_definition,
2999 N'' AS secret_columns,
3000 N'N/A' AS index_usage_summary,
3001 N'N/A' AS index_size_summary
3002 FROM #index_includes
3003 WHERE number_indexes_with_includes = 0
3004 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3005 OPTION ( RECOMPILE );
3006
3007 RAISERROR(N'check_id 31: < 3 percent of indexes have includes', 0,1) WITH NOWAIT;
3008 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3009 secret_columns, index_usage_summary, index_size_summary )
3010 SELECT 31 AS check_id,
3011 NULL AS index_sanity_id,
3012 150 AS Priority,
3013 N'Feature-Phobic Indexes' AS findings_group,
3014 N'Borderline: Includes are used in < 3% of indexes' AS findings,
3015 database_name AS [Database Name],
3016 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
3017 N'Only ' + CAST(percent_indexes_with_includes AS NVARCHAR(20)) + '% of indexes have includes' AS details,
3018 N'Entire database' AS index_definition,
3019 N'' AS secret_columns,
3020 N'N/A' AS index_usage_summary,
3021 N'N/A' AS index_size_summary
3022 FROM #index_includes
3023 WHERE number_indexes_with_includes > 0 AND percent_indexes_with_includes <= 3
3024 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3025 OPTION ( RECOMPILE );
3026
3027 RAISERROR(N'check_id 32: filtered indexes and indexed views', 0,1) WITH NOWAIT;
3028
3029 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3030 secret_columns, index_usage_summary, index_size_summary )
3031 SELECT DISTINCT
3032 32 AS check_id,
3033 NULL AS index_sanity_id,
3034 250 AS Priority,
3035 N'Feature-Phobic Indexes' AS findings_group,
3036 N'Borderline: No filtered indexes or indexed views exist' AS finding,
3037 i.database_name AS [Database Name],
3038 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
3039 N'These are NOT always needed-- but do you know when you would use them?' AS details,
3040 i.database_name + N' (Entire database)' AS index_definition,
3041 N'' AS secret_columns,
3042 N'N/A' AS index_usage_summary,
3043 N'N/A' AS index_size_summary
3044 FROM #IndexSanity i
3045 WHERE i.database_name NOT IN (
3046 SELECT database_name
3047 FROM #IndexSanity
3048 WHERE filter_definition <> '' )
3049 AND i.database_name NOT IN (
3050 SELECT database_name
3051 FROM #IndexSanity
3052 WHERE is_indexed_view = 1 )
3053 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3054 OPTION ( RECOMPILE );
3055 END;
3056
3057 RAISERROR(N'check_id 33: Potential filtered indexes based on column names.', 0,1) WITH NOWAIT;
3058
3059 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3060 secret_columns, index_usage_summary, index_size_summary )
3061 SELECT 33 AS check_id,
3062 i.index_sanity_id AS index_sanity_id,
3063 250 AS Priority,
3064 N'Feature-Phobic Indexes' AS findings_group,
3065 N'Potential filtered index (based on column name)' AS finding,
3066 [database_name] AS [Database Name],
3067 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
3068 N'A column name in this index suggests it might be a candidate for filtering (is%, %archive%, %active%, %flag%)' AS details,
3069 i.index_definition,
3070 i.secret_columns,
3071 i.index_usage_summary,
3072 sz.index_size_summary
3073 FROM #IndexColumns ic
3074 JOIN #IndexSanity i ON ic.[object_id]=i.[object_id]
3075 AND ic.database_id =i.database_id
3076 AND ic.schema_name = i.schema_name
3077 AND ic.[index_id]=i.[index_id]
3078 AND i.[index_id] > 1 /* non-clustered index */
3079 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
3080 WHERE (column_name LIKE 'is%'
3081 OR column_name LIKE '%archive%'
3082 OR column_name LIKE '%active%'
3083 OR column_name LIKE '%flag%')
3084 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3085 OPTION ( RECOMPILE );
3086
3087 RAISERROR(N'check_id 34: Filtered index definition columns not in index definition', 0,1) WITH NOWAIT;
3088
3089 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3090 secret_columns, index_usage_summary, index_size_summary )
3091 SELECT 34 AS check_id,
3092 i.index_sanity_id,
3093 80 AS Priority,
3094 N'Forgetful Indexes' AS findings_group,
3095 N'Filter Columns Not In Index Definition' AS finding,
3096 [database_name] AS [Database Name],
3097 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
3098 N'The index '
3099 + QUOTENAME(i.index_name)
3100 + N' on ['
3101 + i.db_schema_object_name
3102 + N'] has a filter on ['
3103 + i.filter_definition
3104 + N'] but is missing ['
3105 + LTRIM(i.filter_columns_not_in_index)
3106 + N'] from the index definition.'
3107 AS details,
3108 i.index_definition,
3109 i.secret_columns,
3110 i.index_usage_summary,
3111 sz.index_size_summary
3112 FROM #IndexSanity i
3113 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3114 WHERE i.filter_columns_not_in_index IS NOT NULL
3115 ORDER BY i.db_schema_object_indexid
3116 OPTION ( RECOMPILE );
3117
3118 ----------------------------------------
3119 --Self Loathing Indexes : Check_id 40-49
3120 ----------------------------------------
3121 BEGIN
3122
3123 RAISERROR(N'check_id 40: Fillfactor in nonclustered 80 percent or less', 0,1) WITH NOWAIT;
3124 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3125 secret_columns, index_usage_summary, index_size_summary )
3126 SELECT 40 AS check_id,
3127 i.index_sanity_id,
3128 100 AS Priority,
3129 N'Self Loathing Indexes' AS findings_group,
3130 N'Low Fill Factor: nonclustered index' AS finding,
3131 [database_name] AS [Database Name],
3132 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3133 CAST(fill_factor AS NVARCHAR(10)) + N'% fill factor on ' + db_schema_object_indexid + N'. '+
3134 CASE WHEN (last_user_update IS NULL OR user_updates < 1)
3135 THEN N'No writes have been made.'
3136 ELSE
3137 N'Last write was ' + CONVERT(NVARCHAR(16),last_user_update,121) + N' and ' +
3138 CAST(user_updates AS NVARCHAR(25)) + N' updates have been made.'
3139 END
3140 AS details,
3141 i.index_definition,
3142 i.secret_columns,
3143 i.index_usage_summary,
3144 sz.index_size_summary
3145 FROM #IndexSanity AS i
3146 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
3147 WHERE index_id > 1
3148 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3149 AND fill_factor BETWEEN 1 AND 80 OPTION ( RECOMPILE );
3150
3151 RAISERROR(N'check_id 40: Fillfactor in clustered 80 percent or less', 0,1) WITH NOWAIT;
3152 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3153 secret_columns, index_usage_summary, index_size_summary )
3154 SELECT 40 AS check_id,
3155 i.index_sanity_id,
3156 100 AS Priority,
3157 N'Self Loathing Indexes' AS findings_group,
3158 N'Low Fill Factor: clustered index' AS finding,
3159 [database_name] AS [Database Name],
3160 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3161 N'Fill factor on ' + db_schema_object_indexid + N' is ' + CAST(fill_factor AS NVARCHAR(10)) + N'%. '+
3162 CASE WHEN (last_user_update IS NULL OR user_updates < 1)
3163 THEN N'No writes have been made.'
3164 ELSE
3165 N'Last write was ' + CONVERT(NVARCHAR(16),last_user_update,121) + N' and ' +
3166 CAST(user_updates AS NVARCHAR(25)) + N' updates have been made.'
3167 END
3168 AS details,
3169 i.index_definition,
3170 i.secret_columns,
3171 i.index_usage_summary,
3172 sz.index_size_summary
3173 FROM #IndexSanity AS i
3174 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
3175 WHERE index_id = 1
3176 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3177 AND fill_factor BETWEEN 1 AND 80 OPTION ( RECOMPILE );
3178
3179
3180 RAISERROR(N'check_id 41: Hypothetical indexes ', 0,1) WITH NOWAIT;
3181 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3182 secret_columns, index_usage_summary, index_size_summary )
3183 SELECT 41 AS check_id,
3184 i.index_sanity_id,
3185 150 AS Priority,
3186 N'Self Loathing Indexes' AS findings_group,
3187 N'Hypothetical Index' AS finding,
3188 [database_name] AS [Database Name],
3189 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3190 N'Hypothetical Index: ' + db_schema_object_indexid AS details,
3191 i.index_definition,
3192 i.secret_columns,
3193 N'' AS index_usage_summary,
3194 N'' AS index_size_summary
3195 FROM #IndexSanity AS i
3196 WHERE is_hypothetical = 1
3197 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3198 OPTION ( RECOMPILE );
3199
3200
3201 RAISERROR(N'check_id 42: Disabled indexes', 0,1) WITH NOWAIT;
3202 --Note: disabled NC indexes will have O rows in #IndexSanitySize!
3203 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3204 secret_columns, index_usage_summary, index_size_summary )
3205 SELECT 42 AS check_id,
3206 index_sanity_id,
3207 150 AS Priority,
3208 N'Self Loathing Indexes' AS findings_group,
3209 N'Disabled Index' AS finding,
3210 [database_name] AS [Database Name],
3211 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3212 N'Disabled Index:' + db_schema_object_indexid AS details,
3213 i.index_definition,
3214 i.secret_columns,
3215 i.index_usage_summary,
3216 'DISABLED' AS index_size_summary
3217 FROM #IndexSanity AS i
3218 WHERE is_disabled = 1
3219 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3220 OPTION ( RECOMPILE );
3221
3222 RAISERROR(N'check_id 43: Heaps with forwarded records', 0,1) WITH NOWAIT;
3223 WITH heaps_cte
3224 AS ( SELECT [object_id],
3225 [database_id],
3226 [schema_name],
3227 SUM(forwarded_fetch_count) AS forwarded_fetch_count,
3228 SUM(leaf_delete_count) AS leaf_delete_count
3229 FROM #IndexPartitionSanity
3230 GROUP BY [object_id],
3231 [database_id],
3232 [schema_name]
3233 HAVING SUM(forwarded_fetch_count) > 0)
3234 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3235 secret_columns, index_usage_summary, index_size_summary )
3236 SELECT 43 AS check_id,
3237 i.index_sanity_id,
3238 100 AS Priority,
3239 N'Self Loathing Indexes' AS findings_group,
3240 N'Heaps with forwarded records' AS finding,
3241 [database_name] AS [Database Name],
3242 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3243 CASE WHEN h.forwarded_fetch_count >= 922337203685477 THEN '>= 922,337,203,685,477'
3244 WHEN @DaysUptime < 1 THEN CAST(h.forwarded_fetch_count AS NVARCHAR(256)) + N' forwarded fetches against heap: ' + db_schema_object_indexid
3245 ELSE REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
3246 (h.forwarded_fetch_count /*/@DaysUptime */)
3247 AS BIGINT) AS MONEY), 1), '.00', '')
3248 END + N' forwarded fetches per day against heap: '
3249 + db_schema_object_indexid AS details,
3250 i.index_definition,
3251 i.secret_columns,
3252 i.index_usage_summary,
3253 sz.index_size_summary
3254 FROM #IndexSanity i
3255 JOIN heaps_cte h ON i.[object_id] = h.[object_id]
3256 AND i.[database_id] = h.[database_id]
3257 AND i.[schema_name] = h.[schema_name]
3258 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3259 WHERE i.index_id = 0
3260 AND h.forwarded_fetch_count / @DaysUptime > 1000
3261 AND sz.total_reserved_MB >= CASE WHEN NOT (@GetAllDatabases = 1 OR @Mode = 4) THEN @ThresholdMB ELSE sz.total_reserved_MB END
3262 OPTION ( RECOMPILE );
3263
3264 RAISERROR(N'check_id 49: Heaps with deletes', 0,1) WITH NOWAIT;
3265 WITH heaps_cte
3266 AS ( SELECT [object_id],
3267 [database_id],
3268 [schema_name],
3269 SUM(leaf_delete_count) AS leaf_delete_count
3270 FROM #IndexPartitionSanity
3271 GROUP BY [object_id],
3272 [database_id],
3273 [schema_name]
3274 HAVING SUM(forwarded_fetch_count) < 1000 * @DaysUptime /* Only alert about indexes with no forwarded fetches - we already alerted about those in check_id 43 */
3275 AND SUM(leaf_delete_count) > 0)
3276 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3277 secret_columns, index_usage_summary, index_size_summary )
3278 SELECT 49 AS check_id,
3279 i.index_sanity_id,
3280 200 AS Priority,
3281 N'Self Loathing Indexes' AS findings_group,
3282 N'Heaps with deletes' AS finding,
3283 [database_name] AS [Database Name],
3284 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3285 CAST(h.leaf_delete_count AS NVARCHAR(256)) + N' deletes against heap:'
3286 + db_schema_object_indexid AS details,
3287 i.index_definition,
3288 i.secret_columns,
3289 i.index_usage_summary,
3290 sz.index_size_summary
3291 FROM #IndexSanity i
3292 JOIN heaps_cte h ON i.[object_id] = h.[object_id]
3293 AND i.[database_id] = h.[database_id]
3294 AND i.[schema_name] = h.[schema_name]
3295 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3296 WHERE i.index_id = 0
3297 AND sz.total_reserved_MB >= CASE WHEN NOT (@GetAllDatabases = 1 OR @Mode = 4) THEN @ThresholdMB ELSE sz.total_reserved_MB END
3298 OPTION ( RECOMPILE );
3299
3300 RAISERROR(N'check_id 44: Large Heaps with reads or writes.', 0,1) WITH NOWAIT;
3301 WITH heaps_cte
3302 AS ( SELECT [object_id],
3303 [database_id],
3304 [schema_name],
3305 SUM(forwarded_fetch_count) AS forwarded_fetch_count,
3306 SUM(leaf_delete_count) AS leaf_delete_count
3307 FROM #IndexPartitionSanity
3308 GROUP BY [object_id],
3309 [database_id],
3310 [schema_name]
3311 HAVING SUM(forwarded_fetch_count) > 0
3312 OR SUM(leaf_delete_count) > 0)
3313 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3314 secret_columns, index_usage_summary, index_size_summary )
3315 SELECT 44 AS check_id,
3316 i.index_sanity_id,
3317 100 AS Priority,
3318 N'Self Loathing Indexes' AS findings_group,
3319 N'Large Active heap' AS finding,
3320 [database_name] AS [Database Name],
3321 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3322 N'Should this table be a heap? ' + db_schema_object_indexid AS details,
3323 i.index_definition,
3324 'N/A' AS secret_columns,
3325 i.index_usage_summary,
3326 sz.index_size_summary
3327 FROM #IndexSanity i
3328 LEFT JOIN heaps_cte h ON i.[object_id] = h.[object_id]
3329 AND i.[database_id] = h.[database_id]
3330 AND i.[schema_name] = h.[schema_name]
3331 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3332 WHERE i.index_id = 0
3333 AND (i.total_reads > 0 OR i.user_updates > 0)
3334 AND sz.total_rows >= 100000
3335 AND h.[object_id] IS NULL /*don't duplicate the prior check.*/
3336 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3337 OPTION ( RECOMPILE );
3338
3339 RAISERROR(N'check_id 45: Medium Heaps with reads or writes.', 0,1) WITH NOWAIT;
3340 WITH heaps_cte
3341 AS ( SELECT [object_id],
3342 [database_id],
3343 [schema_name],
3344 SUM(forwarded_fetch_count) AS forwarded_fetch_count,
3345 SUM(leaf_delete_count) AS leaf_delete_count
3346 FROM #IndexPartitionSanity
3347 GROUP BY [object_id],
3348 [database_id],
3349 [schema_name]
3350 HAVING SUM(forwarded_fetch_count) > 0
3351 OR SUM(leaf_delete_count) > 0)
3352 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3353 secret_columns, index_usage_summary, index_size_summary )
3354 SELECT 45 AS check_id,
3355 i.index_sanity_id,
3356 100 AS Priority,
3357 N'Self Loathing Indexes' AS findings_group,
3358 N'Medium Active heap' AS finding,
3359 [database_name] AS [Database Name],
3360 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3361 N'Should this table be a heap? ' + db_schema_object_indexid AS details,
3362 i.index_definition,
3363 'N/A' AS secret_columns,
3364 i.index_usage_summary,
3365 sz.index_size_summary
3366 FROM #IndexSanity i
3367 LEFT JOIN heaps_cte h ON i.[object_id] = h.[object_id]
3368 AND i.[database_id] = h.[database_id]
3369 AND i.[schema_name] = h.[schema_name]
3370 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3371 WHERE i.index_id = 0
3372 AND
3373 (i.total_reads > 0 OR i.user_updates > 0)
3374 AND sz.total_rows >= 10000 AND sz.total_rows < 100000
3375 AND h.[object_id] IS NULL /*don't duplicate the prior check.*/
3376 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3377 OPTION ( RECOMPILE );
3378
3379 RAISERROR(N'check_id 46: Small Heaps with reads or writes.', 0,1) WITH NOWAIT;
3380 WITH heaps_cte
3381 AS ( SELECT [object_id],
3382 [database_id],
3383 [schema_name],
3384 SUM(forwarded_fetch_count) AS forwarded_fetch_count,
3385 SUM(leaf_delete_count) AS leaf_delete_count
3386 FROM #IndexPartitionSanity
3387 GROUP BY [object_id],
3388 [database_id],
3389 [schema_name]
3390 HAVING SUM(forwarded_fetch_count) > 0
3391 OR SUM(leaf_delete_count) > 0)
3392 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3393 secret_columns, index_usage_summary, index_size_summary )
3394 SELECT 46 AS check_id,
3395 i.index_sanity_id,
3396 100 AS Priority,
3397 N'Self Loathing Indexes' AS findings_group,
3398 N'Small Active heap' AS finding,
3399 [database_name] AS [Database Name],
3400 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3401 N'Should this table be a heap? ' + db_schema_object_indexid AS details,
3402 i.index_definition,
3403 'N/A' AS secret_columns,
3404 i.index_usage_summary,
3405 sz.index_size_summary
3406 FROM #IndexSanity i
3407 LEFT JOIN heaps_cte h ON i.[object_id] = h.[object_id]
3408 AND i.[database_id] = h.[database_id]
3409 AND i.[schema_name] = h.[schema_name]
3410 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3411 WHERE i.index_id = 0
3412 AND
3413 (i.total_reads > 0 OR i.user_updates > 0)
3414 AND sz.total_rows < 10000
3415 AND h.[object_id] IS NULL /*don't duplicate the prior check.*/
3416 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3417 OPTION ( RECOMPILE );
3418
3419 RAISERROR(N'check_id 47: Heap with a Nonclustered Primary Key', 0,1) WITH NOWAIT;
3420 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3421 secret_columns, index_usage_summary, index_size_summary )
3422 SELECT 47 AS check_id,
3423 i.index_sanity_id,
3424 100 AS Priority,
3425 N'Self Loathing Indexes' AS findings_group,
3426 N'Heap with a Nonclustered Primary Key' AS finding,
3427 [database_name] AS [Database Name],
3428 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
3429 db_schema_object_indexid + N' is a HEAP with a Nonclustered Primary Key' AS details,
3430 i.index_definition,
3431 i.secret_columns,
3432 i.index_usage_summary,
3433 sz.index_size_summary
3434 FROM #IndexSanity i
3435 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3436 WHERE i.index_type = 2 AND i.is_primary_key = 1
3437 AND EXISTS
3438 (
3439 SELECT 1/0
3440 FROM #IndexSanity AS isa
3441 WHERE i.database_id = isa.database_id
3442 AND i.object_id = isa.object_id
3443 AND isa.index_id = 0
3444 )
3445 OPTION ( RECOMPILE );
3446
3447 RAISERROR(N'check_id 48: Nonclustered indexes with a bad read to write ratio', 0,1) WITH NOWAIT;
3448 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3449 secret_columns, index_usage_summary, index_size_summary )
3450 SELECT 48 AS check_id,
3451 i.index_sanity_id,
3452 100 AS Priority,
3453 N'Index Hoarder' AS findings_group,
3454 N'NC index with High Writes:Reads' AS finding,
3455 [database_name] AS [Database Name],
3456 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
3457 N'Reads: '
3458 + REPLACE(CONVERT(NVARCHAR(30), CAST((i.total_reads) AS MONEY), 1), N'.00', N'')
3459 + N' Writes: '
3460 + REPLACE(CONVERT(NVARCHAR(30), CAST((i.user_updates) AS MONEY), 1), N'.00', N'')
3461 + N' on: '
3462 + i.db_schema_object_indexid AS details,
3463 i.index_definition,
3464 i.secret_columns,
3465 i.index_usage_summary,
3466 sz.index_size_summary
3467 FROM #IndexSanity i
3468 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3469 WHERE i.total_reads > 0 /*Not totally unused*/
3470 AND i.user_updates >= 10000 /*Decent write activity*/
3471 AND i.total_reads < 10000
3472 AND ((i.total_reads * 10) < i.user_updates) /*10x more writes than reads*/
3473 AND i.index_id NOT IN (0,1) /*NCs only*/
3474 AND i.is_unique = 0
3475 AND sz.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE sz.total_reserved_MB END
3476 ORDER BY i.db_schema_object_indexid
3477 OPTION ( RECOMPILE );
3478
3479 END;
3480 ----------------------------------------
3481 --Indexaphobia
3482 --Missing indexes with value >= 5 million: : Check_id 50-59
3483 ----------------------------------------
3484 BEGIN
3485 RAISERROR(N'check_id 50: Indexaphobia.', 0,1) WITH NOWAIT;
3486 WITH index_size_cte
3487 AS ( SELECT i.database_id,
3488 i.schema_name,
3489 i.[object_id],
3490 MAX(i.index_sanity_id) AS index_sanity_id,
3491 ISNULL(NULLIF(MAX(DATEDIFF(DAY, i.create_date, SYSDATETIME())), 0), 1) AS create_days,
3492 ISNULL (
3493 CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN 1 ELSE 0 END)
3494 AS NVARCHAR(30))+ N' NC indexes exist (' +
3495 CASE WHEN SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) > 1024
3496 THEN CAST(CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END )/1024.
3497
3498 AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB); '
3499 ELSE CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
3500 AS NVARCHAR(30)) + N'MB); '
3501 END +
3502 CASE WHEN MAX(sz.[total_rows]) >= 922337203685477 THEN '>= 922,337,203,685,477'
3503 ELSE REPLACE(CONVERT(NVARCHAR(30),CAST(MAX(sz.[total_rows]) AS MONEY), 1), '.00', '')
3504 END +
3505 + N' Estimated Rows;'
3506 ,N'') AS index_size_summary
3507 FROM #IndexSanity AS i
3508 LEFT JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id AND i.database_id = sz.database_id
3509 WHERE i.is_hypothetical = 0
3510 AND i.is_disabled = 0
3511 GROUP BY i.database_id, i.schema_name, i.[object_id])
3512 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3513 index_usage_summary, index_size_summary, create_tsql, more_info )
3514
3515 SELECT check_id, t.index_sanity_id, t.check_id, t.findings_group, t.finding, t.[Database Name], t.URL, t.details, t.[definition],
3516 index_estimated_impact, t.index_size_summary, create_tsql, more_info
3517 FROM
3518 (
3519 SELECT ROW_NUMBER() OVER (ORDER BY magic_benefit_number DESC) AS rownum,
3520 50 AS check_id,
3521 sz.index_sanity_id,
3522 10 AS Priority,
3523 N'Indexaphobia' AS findings_group,
3524 N'High value missing index' AS finding,
3525 [database_name] AS [Database Name],
3526 N'http://BrentOzar.com/go/Indexaphobia' AS URL,
3527 mi.[statement] +
3528 N' Est. benefit per day: ' +
3529 CASE WHEN magic_benefit_number >= 922337203685477 THEN '>= 922,337,203,685,477'
3530 ELSE REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
3531 (magic_benefit_number/@DaysUptime)
3532 AS BIGINT) AS MONEY), 1), '.00', '')
3533 END AS details,
3534 missing_index_details AS [definition],
3535 index_estimated_impact,
3536 sz.index_size_summary,
3537 mi.create_tsql,
3538 mi.more_info,
3539 magic_benefit_number,
3540 mi.is_low
3541 FROM #MissingIndexes mi
3542 LEFT JOIN index_size_cte sz ON mi.[object_id] = sz.object_id
3543 AND mi.database_id = sz.database_id
3544 AND mi.schema_name = sz.schema_name
3545 /* Minimum benefit threshold = 100k/day of uptime OR since table creation date, whichever is lower*/
3546 WHERE ( @Mode = 4 AND (magic_benefit_number / CASE WHEN sz.create_days < @DaysUptime THEN sz.create_days ELSE @DaysUptime END) >= 100000 )
3547 OR (magic_benefit_number / CASE WHEN sz.create_days < @DaysUptime THEN sz.create_days ELSE @DaysUptime END) >= 100000
3548 ) AS t
3549 WHERE t.rownum <= CASE WHEN (@Mode <> 4) THEN 20 ELSE t.rownum END
3550 ORDER BY magic_benefit_number DESC
3551 OPTION ( RECOMPILE );
3552
3553
3554 END;
3555 ----------------------------------------
3556 --Abnormal Psychology : Check_id 60-79
3557 ----------------------------------------
3558 BEGIN
3559 RAISERROR(N'check_id 60: XML indexes', 0,1) WITH NOWAIT;
3560 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3561 secret_columns, index_usage_summary, index_size_summary )
3562 SELECT 60 AS check_id,
3563 i.index_sanity_id,
3564 150 AS Priority,
3565 N'Abnormal Psychology' AS findings_group,
3566 N'XML Indexes' AS finding,
3567 [database_name] AS [Database Name],
3568 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3569 i.db_schema_object_indexid AS details,
3570 i.index_definition,
3571 i.secret_columns,
3572 N'' AS index_usage_summary,
3573 ISNULL(sz.index_size_summary,'') AS index_size_summary
3574 FROM #IndexSanity AS i
3575 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3576 WHERE i.is_XML = 1
3577 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3578 OPTION ( RECOMPILE );
3579
3580 RAISERROR(N'check_id 61: Columnstore indexes', 0,1) WITH NOWAIT;
3581 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3582 secret_columns, index_usage_summary, index_size_summary )
3583 SELECT 61 AS check_id,
3584 i.index_sanity_id,
3585 150 AS Priority,
3586 N'Abnormal Psychology' AS findings_group,
3587 CASE WHEN i.is_NC_columnstore=1
3588 THEN N'NC Columnstore Index'
3589 ELSE N'Clustered Columnstore Index'
3590 END AS finding,
3591 [database_name] AS [Database Name],
3592 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3593 i.db_schema_object_indexid AS details,
3594 i.index_definition,
3595 i.secret_columns,
3596 i.index_usage_summary,
3597 ISNULL(sz.index_size_summary,'') AS index_size_summary
3598 FROM #IndexSanity AS i
3599 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3600 WHERE i.is_NC_columnstore = 1 OR i.is_CX_columnstore=1
3601 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3602 OPTION ( RECOMPILE );
3603
3604
3605 RAISERROR(N'check_id 62: Spatial indexes', 0,1) WITH NOWAIT;
3606 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3607 secret_columns, index_usage_summary, index_size_summary )
3608 SELECT 62 AS check_id,
3609 i.index_sanity_id,
3610 150 AS Priority,
3611 N'Abnormal Psychology' AS findings_group,
3612 N'Spatial indexes' AS finding,
3613 [database_name] AS [Database Name],
3614 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3615 i.db_schema_object_indexid AS details,
3616 i.index_definition,
3617 i.secret_columns,
3618 i.index_usage_summary,
3619 ISNULL(sz.index_size_summary,'') AS index_size_summary
3620 FROM #IndexSanity AS i
3621 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3622 WHERE i.is_spatial = 1
3623 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3624 OPTION ( RECOMPILE );
3625
3626 RAISERROR(N'check_id 63: Compressed indexes', 0,1) WITH NOWAIT;
3627 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3628 secret_columns, index_usage_summary, index_size_summary )
3629 SELECT 63 AS check_id,
3630 i.index_sanity_id,
3631 150 AS Priority,
3632 N'Abnormal Psychology' AS findings_group,
3633 N'Compressed indexes' AS finding,
3634 [database_name] AS [Database Name],
3635 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3636 i.db_schema_object_indexid + N'. COMPRESSION: ' + sz.data_compression_desc AS details,
3637 i.index_definition,
3638 i.secret_columns,
3639 i.index_usage_summary,
3640 ISNULL(sz.index_size_summary,'') AS index_size_summary
3641 FROM #IndexSanity AS i
3642 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3643 WHERE sz.data_compression_desc LIKE '%PAGE%' OR sz.data_compression_desc LIKE '%ROW%'
3644 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3645 OPTION ( RECOMPILE );
3646
3647 RAISERROR(N'check_id 64: Partitioned', 0,1) WITH NOWAIT;
3648 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3649 secret_columns, index_usage_summary, index_size_summary )
3650 SELECT 64 AS check_id,
3651 i.index_sanity_id,
3652 150 AS Priority,
3653 N'Abnormal Psychology' AS findings_group,
3654 N'Partitioned indexes' AS finding,
3655 [database_name] AS [Database Name],
3656 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3657 i.db_schema_object_indexid AS details,
3658 i.index_definition,
3659 i.secret_columns,
3660 i.index_usage_summary,
3661 ISNULL(sz.index_size_summary,'') AS index_size_summary
3662 FROM #IndexSanity AS i
3663 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3664 WHERE i.partition_key_column_name IS NOT NULL
3665 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3666 OPTION ( RECOMPILE );
3667
3668 RAISERROR(N'check_id 65: Non-Aligned Partitioned', 0,1) WITH NOWAIT;
3669 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3670 secret_columns, index_usage_summary, index_size_summary )
3671 SELECT 65 AS check_id,
3672 i.index_sanity_id,
3673 150 AS Priority,
3674 N'Abnormal Psychology' AS findings_group,
3675 N'Non-Aligned index on a partitioned table' AS finding,
3676 i.[database_name] AS [Database Name],
3677 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3678 i.db_schema_object_indexid AS details,
3679 i.index_definition,
3680 i.secret_columns,
3681 i.index_usage_summary,
3682 ISNULL(sz.index_size_summary,'') AS index_size_summary
3683 FROM #IndexSanity AS i
3684 JOIN #IndexSanity AS iParent ON
3685 i.[object_id]=iParent.[object_id]
3686 AND i.database_id = iParent.database_id
3687 AND i.schema_name = iParent.schema_name
3688 AND iParent.index_id IN (0,1) /* could be a partitioned heap or clustered table */
3689 AND iParent.partition_key_column_name IS NOT NULL /* parent is partitioned*/
3690 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3691 WHERE i.partition_key_column_name IS NULL
3692 OPTION ( RECOMPILE );
3693
3694 RAISERROR(N'check_id 66: Recently created tables/indexes (1 week)', 0,1) WITH NOWAIT;
3695 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3696 secret_columns, index_usage_summary, index_size_summary )
3697 SELECT 66 AS check_id,
3698 i.index_sanity_id,
3699 200 AS Priority,
3700 N'Abnormal Psychology' AS findings_group,
3701 N'Recently created tables/indexes (1 week)' AS finding,
3702 [database_name] AS [Database Name],
3703 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3704 i.db_schema_object_indexid + N' was created on ' +
3705 CONVERT(NVARCHAR(16),i.create_date,121) +
3706 N'. Tables/indexes which are dropped/created regularly require special methods for index tuning.'
3707 AS details,
3708 i.index_definition,
3709 i.secret_columns,
3710 i.index_usage_summary,
3711 ISNULL(sz.index_size_summary,'') AS index_size_summary
3712 FROM #IndexSanity AS i
3713 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3714 WHERE i.create_date >= DATEADD(dd,-7,GETDATE())
3715 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3716 OPTION ( RECOMPILE );
3717
3718 RAISERROR(N'check_id 67: Recently modified tables/indexes (2 days)', 0,1) WITH NOWAIT;
3719 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3720 secret_columns, index_usage_summary, index_size_summary )
3721 SELECT 67 AS check_id,
3722 i.index_sanity_id,
3723 200 AS Priority,
3724 N'Abnormal Psychology' AS findings_group,
3725 N'Recently modified tables/indexes (2 days)' AS finding,
3726 [database_name] AS [Database Name],
3727 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3728 i.db_schema_object_indexid + N' was modified on ' +
3729 CONVERT(NVARCHAR(16),i.modify_date,121) +
3730 N'. A large amount of recently modified indexes may mean a lot of rebuilds are occurring each night.'
3731 AS details,
3732 i.index_definition,
3733 i.secret_columns,
3734 i.index_usage_summary,
3735 ISNULL(sz.index_size_summary,'') AS index_size_summary
3736 FROM #IndexSanity AS i
3737 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3738 WHERE i.modify_date > DATEADD(dd,-2,GETDATE())
3739 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3740 AND /*Exclude recently created tables.*/
3741 i.create_date < DATEADD(dd,-7,GETDATE())
3742 OPTION ( RECOMPILE );
3743
3744 RAISERROR(N'check_id 68: Identity columns within 30 percent of the end of range', 0,1) WITH NOWAIT;
3745 -- Allowed Ranges:
3746 --int -2,147,483,648 to 2,147,483,647
3747 --smallint -32,768 to 32,768
3748 --tinyint 0 to 255
3749
3750 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3751 secret_columns, index_usage_summary, index_size_summary )
3752 SELECT 68 AS check_id,
3753 i.index_sanity_id,
3754 200 AS Priority,
3755 N'Abnormal Psychology' AS findings_group,
3756 N'Identity column within ' +
3757 CAST (calc1.percent_remaining AS NVARCHAR(256))
3758 + N' percent end of range' AS finding,
3759 [database_name] AS [Database Name],
3760 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3761 i.db_schema_object_name + N'.' + QUOTENAME(ic.column_name)
3762 + N' is an identity with type ' + ic.system_type_name
3763 + N', last value of '
3764 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.last_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
3765 + N', seed of '
3766 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.seed_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
3767 + N', increment of ' + CAST(ic.increment_value AS NVARCHAR(256))
3768 + N', and range of ' +
3769 CASE ic.system_type_name WHEN 'int' THEN N'+/- 2,147,483,647'
3770 WHEN 'smallint' THEN N'+/- 32,768'
3771 WHEN 'tinyint' THEN N'0 to 255'
3772 END
3773 AS details,
3774 i.index_definition,
3775 secret_columns,
3776 ISNULL(i.index_usage_summary,''),
3777 ISNULL(ip.index_size_summary,'')
3778 FROM #IndexSanity i
3779 JOIN #IndexColumns ic ON
3780 i.object_id=ic.object_id
3781 AND i.database_id = ic.database_id
3782 AND i.schema_name = ic.schema_name
3783 AND i.index_id IN (0,1) /* heaps and cx only */
3784 AND ic.is_identity=1
3785 AND ic.system_type_name IN ('tinyint', 'smallint', 'int')
3786 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
3787 CROSS APPLY (
3788 SELECT CAST(CASE WHEN ic.increment_value >= 0
3789 THEN
3790 CASE ic.system_type_name
3791 WHEN 'int' THEN (2147483647 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 2147483647.*100
3792 WHEN 'smallint' THEN (32768 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 32768.*100
3793 WHEN 'tinyint' THEN ( 255 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 255.*100
3794 ELSE 999
3795 END
3796 ELSE --ic.increment_value is negative
3797 CASE ic.system_type_name
3798 WHEN 'int' THEN ABS(-2147483647 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 2147483647.*100
3799 WHEN 'smallint' THEN ABS(-32768 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 32768.*100
3800 WHEN 'tinyint' THEN ABS( 0 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 255.*100
3801 ELSE -1
3802 END
3803 END AS NUMERIC(5,1)) AS percent_remaining
3804 ) AS calc1
3805 WHERE i.index_id IN (1,0)
3806 AND calc1.percent_remaining <= 30
3807 UNION ALL
3808 SELECT 68 AS check_id,
3809 i.index_sanity_id,
3810 200 AS Priority,
3811 N'Abnormal Psychology' AS findings_group,
3812 N'Identity column using a negative seed or increment other than 1' AS finding,
3813 [database_name] AS [Database Name],
3814 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3815 i.db_schema_object_name + N'.' + QUOTENAME(ic.column_name)
3816 + N' is an identity with type ' + ic.system_type_name
3817 + N', last value of '
3818 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.last_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
3819 + N', seed of '
3820 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.seed_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
3821 + N', increment of ' + CAST(ic.increment_value AS NVARCHAR(256))
3822 + N', and range of ' +
3823 CASE ic.system_type_name WHEN 'int' THEN N'+/- 2,147,483,647'
3824 WHEN 'smallint' THEN N'+/- 32,768'
3825 WHEN 'tinyint' THEN N'0 to 255'
3826 END
3827 AS details,
3828 i.index_definition,
3829 secret_columns,
3830 ISNULL(i.index_usage_summary,''),
3831 ISNULL(ip.index_size_summary,'')
3832 FROM #IndexSanity i
3833 JOIN #IndexColumns ic ON
3834 i.object_id=ic.object_id
3835 AND i.database_id = ic.database_id
3836 AND i.schema_name = ic.schema_name
3837 AND i.index_id IN (0,1) /* heaps and cx only */
3838 AND ic.is_identity=1
3839 AND ic.system_type_name IN ('tinyint', 'smallint', 'int')
3840 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
3841 WHERE i.index_id IN (1,0)
3842 AND (ic.seed_value < 0 OR ic.increment_value <> 1)
3843 ORDER BY finding, details DESC
3844 OPTION ( RECOMPILE );
3845
3846 RAISERROR(N'check_id 69: Column collation does not match database collation', 0,1) WITH NOWAIT;
3847 WITH count_columns AS (
3848 SELECT [object_id],
3849 database_id,
3850 schema_name,
3851 COUNT(*) AS column_count
3852 FROM #IndexColumns ic
3853 WHERE index_id IN (1,0) /*Heap or clustered only*/
3854 AND collation_name <> @collation
3855 GROUP BY [object_id],
3856 database_id,
3857 schema_name
3858 )
3859 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3860 secret_columns, index_usage_summary, index_size_summary )
3861 SELECT 69 AS check_id,
3862 i.index_sanity_id,
3863 150 AS Priority,
3864 N'Abnormal Psychology' AS findings_group,
3865 N'Column collation does not match database collation' AS finding,
3866 [database_name] AS [Database Name],
3867 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3868 i.db_schema_object_name
3869 + N' has ' + CAST(column_count AS NVARCHAR(20))
3870 + N' column' + CASE WHEN column_count > 1 THEN 's' ELSE '' END
3871 + N' with a different collation than the db collation of '
3872 + @collation AS details,
3873 i.index_definition,
3874 secret_columns,
3875 ISNULL(i.index_usage_summary,''),
3876 ISNULL(ip.index_size_summary,'')
3877 FROM #IndexSanity i
3878 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
3879 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
3880 AND cc.database_id = i.database_id
3881 AND cc.schema_name = i.schema_name
3882 WHERE i.index_id IN (1,0)
3883 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3884 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
3885
3886 RAISERROR(N'check_id 70: Replicated columns', 0,1) WITH NOWAIT;
3887 WITH count_columns AS (
3888 SELECT [object_id],
3889 database_id,
3890 schema_name,
3891 COUNT(*) AS column_count,
3892 SUM(CASE is_replicated WHEN 1 THEN 1 ELSE 0 END) AS replicated_column_count
3893 FROM #IndexColumns ic
3894 WHERE index_id IN (1,0) /*Heap or clustered only*/
3895 GROUP BY object_id,
3896 database_id,
3897 schema_name
3898 )
3899 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3900 secret_columns, index_usage_summary, index_size_summary )
3901 SELECT 70 AS check_id,
3902 i.index_sanity_id,
3903 200 AS Priority,
3904 N'Abnormal Psychology' AS findings_group,
3905 N'Replicated columns' AS finding,
3906 [database_name] AS [Database Name],
3907 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3908 i.db_schema_object_name
3909 + N' has ' + CAST(replicated_column_count AS NVARCHAR(20))
3910 + N' out of ' + CAST(column_count AS NVARCHAR(20))
3911 + N' column' + CASE WHEN column_count > 1 THEN 's' ELSE '' END
3912 + N' in one or more publications.'
3913 AS details,
3914 i.index_definition,
3915 secret_columns,
3916 ISNULL(i.index_usage_summary,''),
3917 ISNULL(ip.index_size_summary,'')
3918 FROM #IndexSanity i
3919 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
3920 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
3921 AND i.database_id = cc.database_id
3922 AND i.schema_name = cc.schema_name
3923 WHERE i.index_id IN (1,0)
3924 AND replicated_column_count > 0
3925 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3926 ORDER BY i.db_schema_object_name DESC
3927 OPTION ( RECOMPILE );
3928
3929 RAISERROR(N'check_id 71: Cascading updates or cascading deletes.', 0,1) WITH NOWAIT;
3930 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3931 secret_columns, index_usage_summary, index_size_summary, more_info )
3932 SELECT 71 AS check_id,
3933 NULL AS index_sanity_id,
3934 150 AS Priority,
3935 N'Abnormal Psychology' AS findings_group,
3936 N'Cascading Updates or Deletes' AS finding,
3937 [database_name] AS [Database Name],
3938 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
3939 N'Foreign Key ' + foreign_key_name +
3940 N' on ' + QUOTENAME(parent_object_name) + N'(' + LTRIM(parent_fk_columns) + N')'
3941 + N' referencing ' + QUOTENAME(referenced_object_name) + N'(' + LTRIM(referenced_fk_columns) + N')'
3942 + N' has settings:'
3943 + CASE [delete_referential_action_desc] WHEN N'NO_ACTION' THEN N'' ELSE N' ON DELETE ' +[delete_referential_action_desc] END
3944 + CASE [update_referential_action_desc] WHEN N'NO_ACTION' THEN N'' ELSE N' ON UPDATE ' + [update_referential_action_desc] END
3945 AS details,
3946 [fk].[database_name]
3947 AS index_definition,
3948 N'N/A' AS secret_columns,
3949 N'N/A' AS index_usage_summary,
3950 N'N/A' AS index_size_summary,
3951 (SELECT TOP 1 more_info FROM #IndexSanity i WHERE i.object_id=fk.parent_object_id AND i.database_id = fk.database_id AND i.schema_name = fk.schema_name)
3952 AS more_info
3953 FROM #ForeignKeys fk
3954 WHERE ([delete_referential_action_desc] <> N'NO_ACTION'
3955 OR [update_referential_action_desc] <> N'NO_ACTION')
3956 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
3957 OPTION ( RECOMPILE );
3958
3959 RAISERROR(N'check_id 72: Columnstore indexes with Trace Flag 834', 0,1) WITH NOWAIT;
3960 IF EXISTS (SELECT * FROM #IndexSanity WHERE index_type IN (5,6))
3961 AND EXISTS (SELECT * FROM #TraceStatus WHERE TraceFlag = 834 AND status = 1)
3962 BEGIN
3963 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3964 secret_columns, index_usage_summary, index_size_summary )
3965 SELECT 72 AS check_id,
3966 i.index_sanity_id,
3967 150 AS Priority,
3968 N'Abnormal Psychology' AS findings_group,
3969 'Columnstore Indexes are being used in conjunction with trace flag 834. Visit the link to see why this can be a bad idea' AS finding,
3970 [database_name] AS [Database Name],
3971 N'https://support.microsoft.com/en-us/kb/3210239' AS URL,
3972 i.db_schema_object_indexid AS details,
3973 i.index_definition,
3974 i.secret_columns,
3975 i.index_usage_summary,
3976 ISNULL(sz.index_size_summary,'') AS index_size_summary
3977 FROM #IndexSanity AS i
3978 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
3979 WHERE i.index_type IN (5,6)
3980 OPTION ( RECOMPILE );
3981 END;
3982
3983 END;
3984
3985 ----------------------------------------
3986 --Workaholics: Check_id 80-89
3987 ----------------------------------------
3988 BEGIN
3989
3990 RAISERROR(N'check_id 80: Most scanned indexes (index_usage_stats)', 0,1) WITH NOWAIT;
3991 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
3992 secret_columns, index_usage_summary, index_size_summary )
3993
3994 --Workaholics according to index_usage_stats
3995 --This isn't perfect: it mentions the number of scans present in a plan
3996 --A "scan" isn't necessarily a full scan, but hey, we gotta do the best with what we've got.
3997 --in the case of things like indexed views, the operator might be in the plan but never executed
3998 SELECT TOP 5
3999 80 AS check_id,
4000 i.index_sanity_id AS index_sanity_id,
4001 200 AS Priority,
4002 N'Workaholics' AS findings_group,
4003 N'Scan-a-lots (index_usage_stats)' AS finding,
4004 [database_name] AS [Database Name],
4005 N'http://BrentOzar.com/go/Workaholics' AS URL,
4006 REPLACE(CONVERT( NVARCHAR(50),CAST(i.user_scans AS MONEY),1),'.00','')
4007 + N' scans against ' + i.db_schema_object_indexid
4008 + N'. Latest scan: ' + ISNULL(CAST(i.last_user_scan AS NVARCHAR(128)),'?') + N'. '
4009 + N'ScanFactor=' + CAST(((i.user_scans * iss.total_reserved_MB)/1000000.) AS NVARCHAR(256)) AS details,
4010 ISNULL(i.key_column_names_with_sort_order,'N/A') AS index_definition,
4011 ISNULL(i.secret_columns,'') AS secret_columns,
4012 i.index_usage_summary AS index_usage_summary,
4013 iss.index_size_summary AS index_size_summary
4014 FROM #IndexSanity i
4015 JOIN #IndexSanitySize iss ON i.index_sanity_id=iss.index_sanity_id
4016 WHERE ISNULL(i.user_scans,0) > 0
4017 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4018 ORDER BY i.user_scans * iss.total_reserved_MB DESC
4019 OPTION ( RECOMPILE );
4020
4021 RAISERROR(N'check_id 81: Top recent accesses (op stats)', 0,1) WITH NOWAIT;
4022 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4023 secret_columns, index_usage_summary, index_size_summary )
4024 --Workaholics according to index_operational_stats
4025 --This isn't perfect either: range_scan_count contains full scans, partial scans, even seeks in nested loop ops
4026 --But this can help bubble up some most-accessed tables
4027 SELECT TOP 5
4028 81 AS check_id,
4029 i.index_sanity_id AS index_sanity_id,
4030 200 AS Priority,
4031 N'Workaholics' AS findings_group,
4032 N'Top recent accesses (index_op_stats)' AS finding,
4033 [database_name] AS [Database Name],
4034 N'http://BrentOzar.com/go/Workaholics' AS URL,
4035 ISNULL(REPLACE(
4036 CONVERT(NVARCHAR(50),CAST((iss.total_range_scan_count + iss.total_singleton_lookup_count) AS MONEY),1),
4037 N'.00',N'')
4038 + N' uses of ' + i.db_schema_object_indexid + N'. '
4039 + REPLACE(CONVERT(NVARCHAR(50), CAST(iss.total_range_scan_count AS MONEY),1),N'.00',N'') + N' scans or seeks. '
4040 + REPLACE(CONVERT(NVARCHAR(50), CAST(iss.total_singleton_lookup_count AS MONEY), 1),N'.00',N'') + N' singleton lookups. '
4041 + N'OpStatsFactor=' + CAST(((((iss.total_range_scan_count + iss.total_singleton_lookup_count) * iss.total_reserved_MB))/1000000.) AS VARCHAR(256)),'') AS details,
4042 ISNULL(i.key_column_names_with_sort_order,'N/A') AS index_definition,
4043 ISNULL(i.secret_columns,'') AS secret_columns,
4044 i.index_usage_summary AS index_usage_summary,
4045 iss.index_size_summary AS index_size_summary
4046 FROM #IndexSanity i
4047 JOIN #IndexSanitySize iss ON i.index_sanity_id=iss.index_sanity_id
4048 WHERE (ISNULL(iss.total_range_scan_count,0) > 0 OR ISNULL(iss.total_singleton_lookup_count,0) > 0)
4049 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4050 ORDER BY ((iss.total_range_scan_count + iss.total_singleton_lookup_count) * iss.total_reserved_MB) DESC
4051 OPTION ( RECOMPILE );
4052
4053
4054 END;
4055
4056 ----------------------------------------
4057 --Statistics Info: Check_id 90-99
4058 ----------------------------------------
4059 BEGIN
4060
4061 RAISERROR(N'check_id 90: Outdated statistics', 0,1) WITH NOWAIT;
4062 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4063 secret_columns, index_usage_summary, index_size_summary )
4064 SELECT 90 AS check_id,
4065 200 AS Priority,
4066 'Functioning Statistaholics' AS findings_group,
4067 'Statistic Abandonment Issues',
4068 s.database_name,
4069 '' AS URL,
4070 'Statistics on this table were last updated ' +
4071 CASE s.last_statistics_update WHEN NULL THEN N' NEVER '
4072 ELSE CONVERT(NVARCHAR(20), s.last_statistics_update) +
4073 ' have had ' + CONVERT(NVARCHAR(100), s.modification_counter) +
4074 ' modifications in that time, which is ' +
4075 CONVERT(NVARCHAR(100), s.percent_modifications) +
4076 '% of the table.'
4077 END AS details,
4078 QUOTENAME(database_name) + '.' + QUOTENAME(s.schema_name) + '.' + QUOTENAME(s.table_name) + '.' + QUOTENAME(s.index_name) + '.' + QUOTENAME(s.statistics_name) + '.' + QUOTENAME(s.column_names) AS index_definition,
4079 'N/A' AS secret_columns,
4080 'N/A' AS index_usage_summary,
4081 'N/A' AS index_size_summary
4082 FROM #Statistics AS s
4083 WHERE s.last_statistics_update <= CONVERT(DATETIME, GETDATE() - 7)
4084 AND s.percent_modifications >= 10.
4085 AND s.rows >= 10000
4086 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4087 OPTION ( RECOMPILE );
4088
4089 RAISERROR(N'check_id 91: Statistics with a low sample rate', 0,1) WITH NOWAIT;
4090 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4091 secret_columns, index_usage_summary, index_size_summary )
4092 SELECT 91 AS check_id,
4093 200 AS Priority,
4094 'Functioning Statistaholics' AS findings_group,
4095 'Antisocial Samples',
4096 s.database_name,
4097 '' AS URL,
4098 'Only ' + CONVERT(NVARCHAR(100), s.percent_sampled) + '% of the rows were sampled during the last statistics update. This may lead to poor cardinality estimates.' AS details,
4099 QUOTENAME(database_name) + '.' + QUOTENAME(s.schema_name) + '.' + QUOTENAME(s.table_name) + '.' + QUOTENAME(s.index_name) + '.' + QUOTENAME(s.statistics_name) + '.' + QUOTENAME(s.column_names) AS index_definition,
4100 'N/A' AS secret_columns,
4101 'N/A' AS index_usage_summary,
4102 'N/A' AS index_size_summary
4103 FROM #Statistics AS s
4104 WHERE s.rows_sampled < 1.
4105 AND s.rows >= 10000
4106 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4107 OPTION ( RECOMPILE );
4108
4109 RAISERROR(N'check_id 92: Statistics with NO RECOMPUTE', 0,1) WITH NOWAIT;
4110 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4111 secret_columns, index_usage_summary, index_size_summary )
4112 SELECT 92 AS check_id,
4113 200 AS Priority,
4114 'Functioning Statistaholics' AS findings_group,
4115 'Cyberphobic Samples',
4116 s.database_name,
4117 '' AS URL,
4118 'The statistic ' + QUOTENAME(s.statistics_name) + ' is set to not recompute. This can be helpful if data is really skewed, but harmful if you expect automatic statistics updates.' AS details,
4119 QUOTENAME(database_name) + '.' + QUOTENAME(s.schema_name) + '.' + QUOTENAME(s.table_name) + '.' + QUOTENAME(s.index_name) + '.' + QUOTENAME(s.statistics_name) + '.' + QUOTENAME(s.column_names) AS index_definition,
4120 'N/A' AS secret_columns,
4121 'N/A' AS index_usage_summary,
4122 'N/A' AS index_size_summary
4123 FROM #Statistics AS s
4124 WHERE s.no_recompute = 1
4125 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4126 OPTION ( RECOMPILE );
4127
4128 RAISERROR(N'check_id 93: Statistics with filters', 0,1) WITH NOWAIT;
4129 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4130 secret_columns, index_usage_summary, index_size_summary )
4131 SELECT 93 AS check_id,
4132 200 AS Priority,
4133 'Functioning Statistaholics' AS findings_group,
4134 'Filter Fixation',
4135 s.database_name,
4136 '' AS URL,
4137 'The statistic ' + QUOTENAME(s.statistics_name) + ' is filtered on [' + s.filter_definition + ']. It could be part of a filtered index, or just a filtered statistic. This is purely informational.' AS details,
4138 QUOTENAME(database_name) + '.' + QUOTENAME(s.schema_name) + '.' + QUOTENAME(s.table_name) + '.' + QUOTENAME(s.index_name) + '.' + QUOTENAME(s.statistics_name) + '.' + QUOTENAME(s.column_names) AS index_definition,
4139 'N/A' AS secret_columns,
4140 'N/A' AS index_usage_summary,
4141 'N/A' AS index_size_summary
4142 FROM #Statistics AS s
4143 WHERE s.has_filter = 1
4144 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4145 OPTION ( RECOMPILE );
4146
4147 END;
4148
4149 ----------------------------------------
4150 --Computed Column Info: Check_id 99-109
4151 ----------------------------------------
4152 BEGIN
4153
4154 RAISERROR(N'check_id 99: Computed Columns That Reference Functions', 0,1) WITH NOWAIT;
4155 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4156 secret_columns, index_usage_summary, index_size_summary )
4157 SELECT 99 AS check_id,
4158 50 AS Priority,
4159 'Cold Calculators' AS findings_group,
4160 'Serial Forcer' AS finding,
4161 cc.database_name,
4162 '' AS URL,
4163 'The computed column ' + QUOTENAME(cc.column_name) + ' on ' + QUOTENAME(cc.schema_name) + '.' + QUOTENAME(cc.table_name) + ' is based on ' + cc.definition
4164 + '. That indicates it may reference a scalar function, or a CLR function with data access, which can cause all queries and maintenance to run serially.' AS details,
4165 cc.column_definition,
4166 'N/A' AS secret_columns,
4167 'N/A' AS index_usage_summary,
4168 'N/A' AS index_size_summary
4169 FROM #ComputedColumns AS cc
4170 WHERE cc.is_function = 1
4171 OPTION ( RECOMPILE );
4172
4173 RAISERROR(N'check_id 100: Computed Columns that are not Persisted.', 0,1) WITH NOWAIT;
4174 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4175 secret_columns, index_usage_summary, index_size_summary )
4176 SELECT 100 AS check_id,
4177 200 AS Priority,
4178 'Cold Calculators' AS findings_group,
4179 'Definition Defeatists' AS finding,
4180 cc.database_name,
4181 '' AS URL,
4182 'The computed column ' + QUOTENAME(cc.column_name) + ' on ' + QUOTENAME(cc.schema_name) + '.' + QUOTENAME(cc.table_name) + ' is not persisted, which means it will be calculated when a query runs.' +
4183 'You can change this with the following command, if the definition is deterministic: ALTER TABLE ' + QUOTENAME(cc.schema_name) + '.' + QUOTENAME(cc.table_name) + ' ALTER COLUMN ' + cc.column_name +
4184 ' ADD PERSISTED' AS details,
4185 cc.column_definition,
4186 'N/A' AS secret_columns,
4187 'N/A' AS index_usage_summary,
4188 'N/A' AS index_size_summary
4189 FROM #ComputedColumns AS cc
4190 WHERE cc.is_persisted = 0
4191 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
4192 OPTION ( RECOMPILE );
4193
4194 ----------------------------------------
4195 --Temporal Table Info: Check_id 110-119
4196 ----------------------------------------
4197 RAISERROR(N'check_id 110: Temporal Tables.', 0,1) WITH NOWAIT;
4198 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4199 secret_columns, index_usage_summary, index_size_summary )
4200
4201 SELECT 110 AS check_id,
4202 200 AS Priority,
4203 'Temporal Tables' AS findings_group,
4204 'Obsessive Compulsive Tables',
4205 t.database_name,
4206 '' AS URL,
4207 'The table ' + QUOTENAME(t.schema_name) + '.' + QUOTENAME(t.table_name) + ' is a temporal table, with rows versioned in '
4208 + QUOTENAME(t.history_schema_name) + '.' + QUOTENAME(t.history_table_name) + ' on History columns ' + QUOTENAME(t.start_column_name) + ' and ' + QUOTENAME(t.end_column_name) + '.'
4209 AS details,
4210 '' AS index_definition,
4211 'N/A' AS secret_columns,
4212 'N/A' AS index_usage_summary,
4213 'N/A' AS index_size_summary
4214 FROM #TemporalTables AS t
4215 WHERE NOT (@GetAllDatabases = 1 OR @Mode = 0)
4216 OPTION ( RECOMPILE );
4217
4218 ----------------------------------------
4219 --Check Constraint Info: Check_id 120-129
4220 ----------------------------------------
4221
4222 RAISERROR(N'check_id 120: Check Constraints That Reference Functions', 0,1) WITH NOWAIT;
4223 INSERT #BlitzIndexResults ( check_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
4224 secret_columns, index_usage_summary, index_size_summary )
4225 SELECT 99 AS check_id,
4226 50 AS Priority,
4227 'Obsessive Constraintive' AS findings_group,
4228 'Serial Forcer' AS finding,
4229 cc.database_name,
4230 '' AS URL,
4231 'The check constraint ' + QUOTENAME(cc.constraint_name) + ' on ' + QUOTENAME(cc.schema_name) + '.' + QUOTENAME(cc.table_name) + ' is based on ' + cc.definition
4232 + '. That indicates it may reference a scalar function, or a CLR function with data access, which can cause all queries and maintenance to run serially.' AS details,
4233 cc.column_definition,
4234 'N/A' AS secret_columns,
4235 'N/A' AS index_usage_summary,
4236 'N/A' AS index_size_summary
4237 FROM #CheckConstraints AS cc
4238 WHERE cc.is_function = 1
4239 OPTION ( RECOMPILE );
4240
4241 END;
4242
4243 RAISERROR(N'Insert a row to help people find help', 0,1) WITH NOWAIT;
4244 IF DATEDIFF(MM, @VersionDate, GETDATE()) > 6
4245 BEGIN
4246 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4247 index_usage_summary, index_size_summary )
4248 VALUES ( -1, 0 ,
4249 'Outdated sp_BlitzIndex', 'sp_BlitzIndex is Over 6 Months Old', 'http://FirstResponderKit.org/',
4250 'Fine wine gets better with age, but this ' + @ScriptVersionName + ' is more like bad cheese. Time to get a new one.',
4251 @DaysUptimeInsertValue,N'',N''
4252 );
4253 END;
4254
4255 IF EXISTS(SELECT * FROM #BlitzIndexResults)
4256 BEGIN
4257 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4258 index_usage_summary, index_size_summary )
4259 VALUES ( -1, 0 ,
4260 @ScriptVersionName,
4261 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
4262 N'From Your Community Volunteers' , N'http://FirstResponderKit.org' ,
4263 @DaysUptimeInsertValue,N'',N''
4264 );
4265 END;
4266 ELSE IF @Mode = 0 OR (@GetAllDatabases = 1 AND @Mode <> 4)
4267 BEGIN
4268 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4269 index_usage_summary, index_size_summary )
4270 VALUES ( -1, 0 ,
4271 @ScriptVersionName,
4272 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
4273 N'From Your Community Volunteers' , N'http://FirstResponderKit.org' ,
4274 @DaysUptimeInsertValue, N'',N''
4275 );
4276 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4277 index_usage_summary, index_size_summary )
4278 VALUES ( 1, 0 ,
4279 N'No Major Problems Found',
4280 N'Nice Work!',
4281 N'http://FirstResponderKit.org',
4282 N'Consider running with @Mode = 4 in individual databases (not all) for more detailed diagnostics.',
4283 N'The new default Mode 0 only looks for very serious index issues.',
4284 @DaysUptimeInsertValue, N''
4285 );
4286
4287 END;
4288 ELSE
4289 BEGIN
4290 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4291 index_usage_summary, index_size_summary )
4292 VALUES ( -1, 0 ,
4293 @ScriptVersionName,
4294 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
4295 N'From Your Community Volunteers' , N'http://FirstResponderKit.org' ,
4296 @DaysUptimeInsertValue, N'',N''
4297 );
4298 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
4299 index_usage_summary, index_size_summary )
4300 VALUES ( 1, 0 ,
4301 N'No Problems Found',
4302 N'Nice job! Or more likely, you have a nearly empty database.',
4303 N'http://FirstResponderKit.org', 'Time to go read some blog posts.',
4304 @DaysUptimeInsertValue, N'', N''
4305 );
4306
4307 END;
4308
4309 RAISERROR(N'Returning results.', 0,1) WITH NOWAIT;
4310
4311 /*Return results.*/
4312 IF (@Mode = 0)
4313 BEGIN
4314 IF(@OutputType <> 'NONE')
4315 BEGIN
4316 SELECT Priority, ISNULL(br.findings_group,N'') +
4317 CASE WHEN ISNULL(br.finding,N'') <> N'' THEN N': ' ELSE N'' END
4318 + br.finding AS [Finding],
4319 br.[database_name] AS [Database Name],
4320 br.details AS [Details: schema.table.index(indexid)],
4321 br.index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
4322 ISNULL(br.secret_columns,'') AS [Secret Columns],
4323 br.index_usage_summary AS [Usage],
4324 br.index_size_summary AS [Size],
4325 COALESCE(br.more_info,sn.more_info,'') AS [More Info],
4326 br.URL,
4327 COALESCE(br.create_tsql,ts.create_tsql,'') AS [Create TSQL]
4328 FROM #BlitzIndexResults br
4329 LEFT JOIN #IndexSanity sn ON
4330 br.index_sanity_id=sn.index_sanity_id
4331 LEFT JOIN #IndexCreateTsql ts ON
4332 br.index_sanity_id=ts.index_sanity_id
4333 WHERE br.check_id IN ( 0, 1, 2, 11, 12, 13,
4334 22, 34, 43, 47, 48,
4335 50, 65, 68, 73, 99 )
4336 ORDER BY br.Priority ASC, br.check_id ASC, br.blitz_result_id ASC, br.findings_group ASC
4337 OPTION (RECOMPILE);
4338 END;
4339
4340 END;
4341 ELSE IF (@Mode = 4)
4342 IF(@OutputType <> 'NONE')
4343 BEGIN
4344 SELECT Priority, ISNULL(br.findings_group,N'') +
4345 CASE WHEN ISNULL(br.finding,N'') <> N'' THEN N': ' ELSE N'' END
4346 + br.finding AS [Finding],
4347 br.[database_name] AS [Database Name],
4348 br.details AS [Details: schema.table.index(indexid)],
4349 br.index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
4350 ISNULL(br.secret_columns,'') AS [Secret Columns],
4351 br.index_usage_summary AS [Usage],
4352 br.index_size_summary AS [Size],
4353 COALESCE(br.more_info,sn.more_info,'') AS [More Info],
4354 br.URL,
4355 COALESCE(br.create_tsql,ts.create_tsql,'') AS [Create TSQL]
4356 FROM #BlitzIndexResults br
4357 LEFT JOIN #IndexSanity sn ON
4358 br.index_sanity_id=sn.index_sanity_id
4359 LEFT JOIN #IndexCreateTsql ts ON
4360 br.index_sanity_id=ts.index_sanity_id
4361 ORDER BY br.Priority ASC, br.check_id ASC, br.blitz_result_id ASC, br.findings_group ASC
4362 OPTION (RECOMPILE);
4363 END;
4364
4365 END; /* End @Mode=0 or 4 (diagnose)*/
4366 ELSE IF (@Mode=1) /*Summarize*/
4367 BEGIN
4368 --This mode is to give some overall stats on the database.
4369 IF(@OutputType <> 'NONE')
4370 BEGIN
4371 RAISERROR(N'@Mode=1, we are summarizing.', 0,1) WITH NOWAIT;
4372
4373 SELECT DB_NAME(i.database_id) AS [Database Name],
4374 CAST((COUNT(*)) AS NVARCHAR(256)) AS [Number Objects],
4375 CAST(CAST(SUM(sz.total_reserved_MB)/
4376 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [All GB],
4377 CAST(CAST(SUM(sz.total_reserved_LOB_MB)/
4378 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [LOB GB],
4379 CAST(CAST(SUM(sz.total_reserved_row_overflow_MB)/
4380 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [Row Overflow GB],
4381 CAST(SUM(CASE WHEN index_id=1 THEN 1 ELSE 0 END)AS NVARCHAR(50)) AS [Clustered Tables],
4382 CAST(SUM(CASE WHEN index_id=1 THEN sz.total_reserved_MB ELSE 0 END)
4383 /1024. AS NUMERIC(29,1)) AS [Clustered Tables GB],
4384 SUM(CASE WHEN index_id NOT IN (0,1) THEN 1 ELSE 0 END) AS [NC Indexes],
4385 CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
4386 /1024. AS NUMERIC(29,1)) AS [NC Indexes GB],
4387 CASE WHEN SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) > 0 THEN
4388 CAST(SUM(CASE WHEN index_id IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
4389 / SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) AS NUMERIC(29,1))
4390 ELSE 0 END AS [ratio table: NC Indexes],
4391 SUM(CASE WHEN index_id=0 THEN 1 ELSE 0 END) AS [Heaps],
4392 CAST(SUM(CASE WHEN index_id=0 THEN sz.total_reserved_MB ELSE 0 END)
4393 /1024. AS NUMERIC(29,1)) AS [Heaps GB],
4394 SUM(CASE WHEN index_id IN (0,1) AND partition_key_column_name IS NOT NULL THEN 1 ELSE 0 END) AS [Partitioned Tables],
4395 SUM(CASE WHEN index_id NOT IN (0,1) AND partition_key_column_name IS NOT NULL THEN 1 ELSE 0 END) AS [Partitioned NCs],
4396 CAST(SUM(CASE WHEN partition_key_column_name IS NOT NULL THEN sz.total_reserved_MB ELSE 0 END)/1024. AS NUMERIC(29,1)) AS [Partitioned GB],
4397 SUM(CASE WHEN filter_definition <> '' THEN 1 ELSE 0 END) AS [Filtered Indexes],
4398 SUM(CASE WHEN is_indexed_view=1 THEN 1 ELSE 0 END) AS [Indexed Views],
4399 MAX(total_rows) AS [Max Row Count],
4400 CAST(MAX(CASE WHEN index_id IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
4401 /1024. AS NUMERIC(29,1)) AS [Max Table GB],
4402 CAST(MAX(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
4403 /1024. AS NUMERIC(29,1)) AS [Max NC Index GB],
4404 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 1024 THEN 1 ELSE 0 END) AS [Count Tables > 1GB],
4405 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 10240 THEN 1 ELSE 0 END) AS [Count Tables > 10GB],
4406 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 102400 THEN 1 ELSE 0 END) AS [Count Tables > 100GB],
4407 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 1024 THEN 1 ELSE 0 END) AS [Count NCs > 1GB],
4408 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 10240 THEN 1 ELSE 0 END) AS [Count NCs > 10GB],
4409 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 102400 THEN 1 ELSE 0 END) AS [Count NCs > 100GB],
4410 MIN(create_date) AS [Oldest Create Date],
4411 MAX(create_date) AS [Most Recent Create Date],
4412 MAX(modify_date) AS [Most Recent Modify Date],
4413 1 AS [Display Order]
4414 FROM #IndexSanity AS i
4415 --left join here so we don't lose disabled nc indexes
4416 LEFT JOIN #IndexSanitySize AS sz
4417 ON i.index_sanity_id=sz.index_sanity_id
4418 GROUP BY DB_NAME(i.database_id)
4419 UNION ALL
4420 SELECT CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
4421 @ScriptVersionName,
4422 N'From Your Community Volunteers' ,
4423 N'http://FirstResponderKit.org' ,
4424 @DaysUptimeInsertValue,
4425 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
4426 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
4427 NULL,NULL,0 AS display_order
4428 ORDER BY [Display Order] ASC
4429 OPTION (RECOMPILE);
4430 END;
4431
4432 END; /* End @Mode=1 (summarize)*/
4433 ELSE IF (@Mode=2) /*Index Detail*/
4434 BEGIN
4435 --This mode just spits out all the detail without filters.
4436 --This supports slicing AND dicing in Excel
4437 RAISERROR(N'@Mode=2, here''s the details on existing indexes.', 0,1) WITH NOWAIT;
4438
4439
4440 /* Checks if @OutputServerName is populated with a valid linked server, and that the database name specified is valid */
4441 DECLARE @ValidOutputServer BIT;
4442 DECLARE @ValidOutputLocation BIT;
4443 DECLARE @LinkedServerDBCheck NVARCHAR(2000);
4444 DECLARE @ValidLinkedServerDB INT;
4445 DECLARE @tmpdbchk TABLE (cnt INT);
4446 DECLARE @StringToExecute NVARCHAR(MAX);
4447
4448 IF @OutputServerName IS NOT NULL
4449 BEGIN
4450 IF (SUBSTRING(@OutputTableName, 2, 1) = '#')
4451 BEGIN
4452 RAISERROR('Due to the nature of temporary tables, outputting to a linked server requires a permanent table.', 16, 0);
4453 END;
4454 ELSE IF EXISTS (SELECT server_id FROM sys.servers WHERE QUOTENAME([name]) = @OutputServerName)
4455 BEGIN
4456 SET @LinkedServerDBCheck = 'SELECT 1 WHERE EXISTS (SELECT * FROM '+@OutputServerName+'.master.sys.databases WHERE QUOTENAME([name]) = '''+@OutputDatabaseName+''')';
4457 INSERT INTO @tmpdbchk EXEC sys.sp_executesql @LinkedServerDBCheck;
4458 SET @ValidLinkedServerDB = (SELECT COUNT(*) FROM @tmpdbchk);
4459 IF (@ValidLinkedServerDB > 0)
4460 BEGIN
4461 SET @ValidOutputServer = 1;
4462 SET @ValidOutputLocation = 1;
4463 END;
4464 ELSE
4465 RAISERROR('The specified database was not found on the output server', 16, 0);
4466 END;
4467 ELSE
4468 BEGIN
4469 RAISERROR('The specified output server was not found', 16, 0);
4470 END;
4471 END;
4472 ELSE
4473 BEGIN
4474 IF (SUBSTRING(@OutputTableName, 2, 2) = '##')
4475 BEGIN
4476 SET @StringToExecute = N' IF (OBJECT_ID(''[tempdb].[dbo].@@@OutputTableName@@@'') IS NOT NULL) DROP TABLE @@@OutputTableName@@@';
4477 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputTableName@@@', @OutputTableName);
4478 EXEC(@StringToExecute);
4479
4480 SET @OutputServerName = QUOTENAME(CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)));
4481 SET @OutputDatabaseName = '[tempdb]';
4482 SET @OutputSchemaName = '[dbo]';
4483 SET @ValidOutputLocation = 1;
4484 END;
4485 ELSE IF (SUBSTRING(@OutputTableName, 2, 1) = '#')
4486 BEGIN
4487 RAISERROR('Due to the nature of Dymamic SQL, only global (i.e. double pound (##)) temp tables are supported for @OutputTableName', 16, 0);
4488 END;
4489 ELSE IF @OutputDatabaseName IS NOT NULL
4490 AND @OutputSchemaName IS NOT NULL
4491 AND @OutputTableName IS NOT NULL
4492 AND EXISTS ( SELECT *
4493 FROM sys.databases
4494 WHERE QUOTENAME([name]) = @OutputDatabaseName)
4495 BEGIN
4496 SET @ValidOutputLocation = 1;
4497 SET @OutputServerName = QUOTENAME(CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)));
4498 END;
4499 ELSE IF @OutputDatabaseName IS NOT NULL
4500 AND @OutputSchemaName IS NOT NULL
4501 AND @OutputTableName IS NOT NULL
4502 AND NOT EXISTS ( SELECT *
4503 FROM sys.databases
4504 WHERE QUOTENAME([name]) = @OutputDatabaseName)
4505 BEGIN
4506 RAISERROR('The specified output database was not found on this server', 16, 0);
4507 END;
4508 ELSE
4509 BEGIN
4510 SET @ValidOutputLocation = 0;
4511 END;
4512 END;
4513
4514 IF (@ValidOutputLocation = 0 AND @OutputType = 'NONE')
4515 BEGIN
4516 RAISERROR('Invalid output location and no output asked',12,1);
4517 RETURN;
4518 END;
4519
4520 /* @OutputTableName lets us export the results to a permanent table */
4521 DECLARE @RunID UNIQUEIDENTIFIER;
4522 SET @RunID = NEWID();
4523
4524 IF (@ValidOutputLocation = 1 AND COALESCE(@OutputServerName, @OutputDatabaseName, @OutputSchemaName, @OutputTableName) IS NOT NULL)
4525 BEGIN
4526 DECLARE @TableExists BIT;
4527 DECLARE @SchemaExists BIT;
4528 SET @StringToExecute =
4529 N'SET @SchemaExists = 0;
4530 SET @TableExists = 0;
4531 IF EXISTS(SELECT * FROM @@@OutputServerName@@@.@@@OutputDatabaseName@@@.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = ''@@@OutputSchemaName@@@'')
4532 SET @SchemaExists = 1
4533 IF EXISTS (SELECT * FROM @@@OutputServerName@@@.@@@OutputDatabaseName@@@.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = ''@@@OutputSchemaName@@@'' AND QUOTENAME(TABLE_NAME) = ''@@@OutputTableName@@@'')
4534 SET @TableExists = 1';
4535
4536 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputServerName@@@', @OutputServerName);
4537 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputDatabaseName@@@', @OutputDatabaseName);
4538 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputSchemaName@@@', @OutputSchemaName);
4539 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputTableName@@@', @OutputTableName);
4540
4541 EXEC sp_executesql @StringToExecute, N'@TableExists BIT OUTPUT, @SchemaExists BIT OUTPUT', @TableExists OUTPUT, @SchemaExists OUTPUT;
4542
4543 IF @SchemaExists = 1
4544 BEGIN
4545 IF @TableExists = 0
4546 BEGIN
4547 SET @StringToExecute =
4548 N'CREATE TABLE @@@OutputDatabaseName@@@.@@@OutputSchemaName@@@.@@@OutputTableName@@@
4549 (
4550 [id] INT IDENTITY(1,1) NOT NULL,
4551 [run_id] UNIQUEIDENTIFIER,
4552 [run_datetime] DATETIME,
4553 [server_name] NVARCHAR(128),
4554 [database_name] NVARCHAR(128),
4555 [schema_name] NVARCHAR(128),
4556 [table_name] NVARCHAR(128),
4557 [index_name] NVARCHAR(128),
4558 [Drop_Tsql] NVARCHAR(4000),
4559 [Create_Tsql] NVARCHAR(4000),
4560 [index_id] INT,
4561 [db_schema_object_indexid] NVARCHAR(500),
4562 [object_type] NVARCHAR(15),
4563 [index_definition] NVARCHAR(4000),
4564 [key_column_names_with_sort_order] NVARCHAR(MAX),
4565 [count_key_columns] INT,
4566 [include_column_names] NVARCHAR(MAX),
4567 [count_included_columns] INT,
4568 [secret_columns] NVARCHAR(MAX),
4569 [count_secret_columns] INT,
4570 [partition_key_column_name] NVARCHAR(MAX),
4571 [filter_definition] NVARCHAR(MAX),
4572 [is_indexed_view] BIT,
4573 [is_primary_key] BIT,
4574 [is_XML] BIT,
4575 [is_spatial] BIT,
4576 [is_NC_columnstore] BIT,
4577 [is_CX_columnstore] BIT,
4578 [is_disabled] BIT,
4579 [is_hypothetical] BIT,
4580 [is_padded] BIT,
4581 [fill_factor] INT,
4582 [is_referenced_by_foreign_key] BIT,
4583 [last_user_seek] DATETIME,
4584 [last_user_scan] DATETIME,
4585 [last_user_lookup] DATETIME,
4586 [last_user_update] DATETIME,
4587 [total_reads] BIGINT,
4588 [user_updates] BIGINT,
4589 [reads_per_write] MONEY,
4590 [index_usage_summary] NVARCHAR(200),
4591 [total_singleton_lookup_count] BIGINT,
4592 [total_range_scan_count] BIGINT,
4593 [total_leaf_delete_count] BIGINT,
4594 [total_leaf_update_count] BIGINT,
4595 [index_op_stats] NVARCHAR(200),
4596 [partition_count] INT,
4597 [total_rows] BIGINT,
4598 [total_reserved_MB] NUMERIC(29,2),
4599 [total_reserved_LOB_MB] NUMERIC(29,2),
4600 [total_reserved_row_overflow_MB] NUMERIC(29,2),
4601 [index_size_summary] NVARCHAR(300),
4602 [total_row_lock_count] BIGINT,
4603 [total_row_lock_wait_count] BIGINT,
4604 [total_row_lock_wait_in_ms] BIGINT,
4605 [avg_row_lock_wait_in_ms] BIGINT,
4606 [total_page_lock_count] BIGINT,
4607 [total_page_lock_wait_count] BIGINT,
4608 [total_page_lock_wait_in_ms] BIGINT,
4609 [avg_page_lock_wait_in_ms] BIGINT,
4610 [total_index_lock_promotion_attempt_count] BIGINT,
4611 [total_index_lock_promotion_count] BIGINT,
4612 [data_compression_desc] NVARCHAR(4000),
4613 [page_latch_wait_count] BIGINT,
4614 [page_latch_wait_in_ms] BIGINT,
4615 [page_io_latch_wait_count] BIGINT,
4616 [page_io_latch_wait_in_ms] BIGINT,
4617 [create_date] DATETIME,
4618 [modify_date] DATETIME,
4619 [more_info] NVARCHAR(500),
4620 [display_order] INT,
4621 CONSTRAINT [PK_ID_@@@RunID@@@] PRIMARY KEY CLUSTERED ([id] ASC)
4622 );';
4623
4624 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputDatabaseName@@@', @OutputDatabaseName);
4625 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputSchemaName@@@', @OutputSchemaName);
4626 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputTableName@@@', @OutputTableName);
4627 SET @StringToExecute = REPLACE(@StringToExecute, '@@@RunID@@@', @RunID);
4628
4629 IF @ValidOutputServer = 1
4630 BEGIN
4631 SET @StringToExecute = REPLACE(@StringToExecute,'''','''''');
4632 EXEC('EXEC('''+@StringToExecute+''') AT ' + @OutputServerName);
4633 END;
4634 ELSE
4635 BEGIN
4636 EXEC(@StringToExecute);
4637 END;
4638 END; /* @TableExists = 0 */
4639
4640 SET @StringToExecute =
4641 N'IF EXISTS(SELECT * FROM @@@OutputServerName@@@.@@@OutputDatabaseName@@@.INFORMATION_SCHEMA.SCHEMATA WHERE QUOTENAME(SCHEMA_NAME) = ''@@@OutputSchemaName@@@'')
4642 AND NOT EXISTS (SELECT * FROM @@@OutputServerName@@@.@@@OutputDatabaseName@@@.INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA) = ''@@@OutputSchemaName@@@'' AND QUOTENAME(TABLE_NAME) = ''@@@OutputTableName@@@'')
4643 SET @TableExists = 0
4644 ELSE
4645 SET @TableExists = 1';
4646
4647 SET @TableExists = NULL;
4648 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputServerName@@@', @OutputServerName);
4649 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputDatabaseName@@@', @OutputDatabaseName);
4650 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputSchemaName@@@', @OutputSchemaName);
4651 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputTableName@@@', @OutputTableName);
4652
4653 EXEC sp_executesql @StringToExecute, N'@TableExists BIT OUTPUT', @TableExists OUTPUT;
4654
4655 IF @TableExists = 1
4656 BEGIN
4657 SET @StringToExecute =
4658 N'INSERT @@@OutputServerName@@@.@@@OutputDatabaseName@@@.@@@OutputSchemaName@@@.@@@OutputTableName@@@
4659 (
4660 [run_id],
4661 [run_datetime],
4662 [server_name],
4663 [database_name],
4664 [schema_name],
4665 [table_name],
4666 [index_name],
4667 [Drop_Tsql],
4668 [Create_Tsql],
4669 [index_id],
4670 [db_schema_object_indexid],
4671 [object_type],
4672 [index_definition],
4673 [key_column_names_with_sort_order],
4674 [count_key_columns],
4675 [include_column_names],
4676 [count_included_columns],
4677 [secret_columns],
4678 [count_secret_columns],
4679 [partition_key_column_name],
4680 [filter_definition],
4681 [is_indexed_view],
4682 [is_primary_key],
4683 [is_XML],
4684 [is_spatial],
4685 [is_NC_columnstore],
4686 [is_CX_columnstore],
4687 [is_disabled],
4688 [is_hypothetical],
4689 [is_padded],
4690 [fill_factor],
4691 [is_referenced_by_foreign_key],
4692 [last_user_seek],
4693 [last_user_scan],
4694 [last_user_lookup],
4695 [last_user_update],
4696 [total_reads],
4697 [user_updates],
4698 [reads_per_write],
4699 [index_usage_summary],
4700 [total_singleton_lookup_count],
4701 [total_range_scan_count],
4702 [total_leaf_delete_count],
4703 [total_leaf_update_count],
4704 [index_op_stats],
4705 [partition_count],
4706 [total_rows],
4707 [total_reserved_MB],
4708 [total_reserved_LOB_MB],
4709 [total_reserved_row_overflow_MB],
4710 [index_size_summary],
4711 [total_row_lock_count],
4712 [total_row_lock_wait_count],
4713 [total_row_lock_wait_in_ms],
4714 [avg_row_lock_wait_in_ms],
4715 [total_page_lock_count],
4716 [total_page_lock_wait_count],
4717 [total_page_lock_wait_in_ms],
4718 [avg_page_lock_wait_in_ms],
4719 [total_index_lock_promotion_attempt_count],
4720 [total_index_lock_promotion_count],
4721 [data_compression_desc],
4722 [page_latch_wait_count],
4723 [page_latch_wait_in_ms],
4724 [page_io_latch_wait_count],
4725 [page_io_latch_wait_in_ms],
4726 [create_date],
4727 [modify_date],
4728 [more_info],
4729 [display_order]
4730 )
4731 SELECT ''@@@RunID@@@'',
4732 ''@@@GETDATE@@@'',
4733 ''@@@LocalServerName@@@'',
4734 -- Below should be a copy/paste of the real query
4735 -- Make sure all quotes are escaped
4736 i.[database_name] AS [Database Name],
4737 i.[schema_name] AS [Schema Name],
4738 i.[object_name] AS [Object Name],
4739 ISNULL(i.index_name, '''') AS [Index Name],
4740 CASE
4741 WHEN i.is_primary_key = 1 AND i.index_definition <> ''[HEAP]''
4742 THEN N''-ALTER TABLE '' + QUOTENAME(i.[schema_name]) + N''.'' + QUOTENAME(i.[object_name]) +
4743 N'' DROP CONSTRAINT '' + QUOTENAME(i.index_name) + N'';''
4744 WHEN i.is_primary_key = 0 AND i.index_definition <> ''[HEAP]''
4745 THEN N''--DROP INDEX ''+ QUOTENAME(i.index_name) + N'' ON '' +
4746 QUOTENAME(i.[schema_name]) + N''.'' + QUOTENAME(i.[object_name]) + N'';''
4747 ELSE N''''
4748 END AS [Drop TSQL],
4749 CASE
4750 WHEN i.index_definition = ''[HEAP]'' THEN N''''
4751 ELSE N''--'' + ict.create_tsql END AS [Create TSQL],
4752 CAST(i.index_id AS NVARCHAR(10))AS [Index ID],
4753 db_schema_object_indexid AS [Details: schema.table.index(indexid)],
4754 CASE WHEN index_id IN ( 1, 0 ) THEN ''TABLE''
4755 ELSE ''NonClustered''
4756 END AS [Object Type],
4757 LEFT(index_definition,4000) AS [Definition: [Property]] ColumnName {datatype maxbytes}],
4758 ISNULL(LTRIM(key_column_names_with_sort_order), '''') AS [Key Column Names With Sort],
4759 ISNULL(count_key_columns, 0) AS [Count Key Columns],
4760 ISNULL(include_column_names, '''') AS [Include Column Names],
4761 ISNULL(count_included_columns,0) AS [Count Included Columns],
4762 ISNULL(secret_columns,'''') AS [Secret Column Names],
4763 ISNULL(count_secret_columns,0) AS [Count Secret Columns],
4764 ISNULL(partition_key_column_name, '''') AS [Partition Key Column Name],
4765 ISNULL(filter_definition, '''') AS [Filter Definition],
4766 is_indexed_view AS [Is Indexed View],
4767 is_primary_key AS [Is Primary Key],
4768 is_XML AS [Is XML],
4769 is_spatial AS [Is Spatial],
4770 is_NC_columnstore AS [Is NC Columnstore],
4771 is_CX_columnstore AS [Is CX Columnstore],
4772 is_disabled AS [Is Disabled],
4773 is_hypothetical AS [Is Hypothetical],
4774 is_padded AS [Is Padded],
4775 fill_factor AS [Fill Factor],
4776 is_referenced_by_foreign_key AS [Is Reference by Foreign Key],
4777 last_user_seek AS [Last User Seek],
4778 last_user_scan AS [Last User Scan],
4779 last_user_lookup AS [Last User Lookup],
4780 last_user_update AS [Last User Update],
4781 total_reads AS [Total Reads],
4782 user_updates AS [User Updates],
4783 reads_per_write AS [Reads Per Write],
4784 index_usage_summary AS [Index Usage],
4785 sz.total_singleton_lookup_count AS [Singleton Lookups],
4786 sz.total_range_scan_count AS [Range Scans],
4787 sz.total_leaf_delete_count AS [Leaf Deletes],
4788 sz.total_leaf_update_count AS [Leaf Updates],
4789 sz.index_op_stats AS [Index Op Stats],
4790 sz.partition_count AS [Partition Count],
4791 sz.total_rows AS [Rows],
4792 sz.total_reserved_MB AS [Reserved MB],
4793 sz.total_reserved_LOB_MB AS [Reserved LOB MB],
4794 sz.total_reserved_row_overflow_MB AS [Reserved Row Overflow MB],
4795 sz.index_size_summary AS [Index Size],
4796 sz.total_row_lock_count AS [Row Lock Count],
4797 sz.total_row_lock_wait_count AS [Row Lock Wait Count],
4798 sz.total_row_lock_wait_in_ms AS [Row Lock Wait ms],
4799 sz.avg_row_lock_wait_in_ms AS [Avg Row Lock Wait ms],
4800 sz.total_page_lock_count AS [Page Lock Count],
4801 sz.total_page_lock_wait_count AS [Page Lock Wait Count],
4802 sz.total_page_lock_wait_in_ms AS [Page Lock Wait ms],
4803 sz.avg_page_lock_wait_in_ms AS [Avg Page Lock Wait ms],
4804 sz.total_index_lock_promotion_attempt_count AS [Lock Escalation Attempts],
4805 sz.total_index_lock_promotion_count AS [Lock Escalations],
4806 sz.data_compression_desc AS [Data Compression],
4807 sz.page_latch_wait_count,
4808 sz.page_latch_wait_in_ms,
4809 sz.page_io_latch_wait_count,
4810 sz.page_io_latch_wait_in_ms,
4811 i.create_date AS [Create Date],
4812 i.modify_date AS [Modify Date],
4813 more_info AS [More Info],
4814 1 AS [Display Order]
4815 FROM #IndexSanity AS i
4816 LEFT JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
4817 LEFT JOIN #IndexCreateTsql AS ict ON i.index_sanity_id = ict.index_sanity_id
4818 ORDER BY [Database Name], [Schema Name], [Object Name], [Index ID]
4819 OPTION (RECOMPILE);';
4820
4821 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputServerName@@@', @OutputServerName);
4822 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputDatabaseName@@@', @OutputDatabaseName);
4823 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputSchemaName@@@', @OutputSchemaName);
4824 SET @StringToExecute = REPLACE(@StringToExecute, '@@@OutputTableName@@@', @OutputTableName);
4825 SET @StringToExecute = REPLACE(@StringToExecute, '@@@RunID@@@', @RunID);
4826 SET @StringToExecute = REPLACE(@StringToExecute, '@@@GETDATE@@@', GETDATE());
4827 SET @StringToExecute = REPLACE(@StringToExecute, '@@@LocalServerName@@@', CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128)));
4828 EXEC(@StringToExecute);
4829 END; /* @TableExists = 1 */
4830 ELSE
4831 RAISERROR('Creation of the output table failed.', 16, 0);
4832 END; /* @TableExists = 0 */
4833 ELSE
4834 RAISERROR (N'Invalid schema name, data could not be saved.', 16, 0);
4835 END; /* @ValidOutputLocation = 1 */
4836 ELSE
4837
4838 IF(@OutputType <> 'NONE')
4839 BEGIN
4840 SELECT i.[database_name] AS [Database Name],
4841 i.[schema_name] AS [Schema Name],
4842 i.[object_name] AS [Object Name],
4843 ISNULL(i.index_name, '') AS [Index Name],
4844 CAST(i.index_id AS NVARCHAR(10))AS [Index ID],
4845 db_schema_object_indexid AS [Details: schema.table.index(indexid)],
4846 CASE WHEN index_id IN ( 1, 0 ) THEN 'TABLE'
4847 ELSE 'NonClustered'
4848 END AS [Object Type],
4849 index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
4850 ISNULL(LTRIM(key_column_names_with_sort_order), '') AS [Key Column Names With Sort],
4851 ISNULL(count_key_columns, 0) AS [Count Key Columns],
4852 ISNULL(include_column_names, '') AS [Include Column Names],
4853 ISNULL(count_included_columns,0) AS [Count Included Columns],
4854 ISNULL(secret_columns,'') AS [Secret Column Names],
4855 ISNULL(count_secret_columns,0) AS [Count Secret Columns],
4856 ISNULL(partition_key_column_name, '') AS [Partition Key Column Name],
4857 ISNULL(filter_definition, '') AS [Filter Definition],
4858 is_indexed_view AS [Is Indexed View],
4859 is_primary_key AS [Is Primary Key],
4860 is_XML AS [Is XML],
4861 is_spatial AS [Is Spatial],
4862 is_NC_columnstore AS [Is NC Columnstore],
4863 is_CX_columnstore AS [Is CX Columnstore],
4864 is_disabled AS [Is Disabled],
4865 is_hypothetical AS [Is Hypothetical],
4866 is_padded AS [Is Padded],
4867 fill_factor AS [Fill Factor],
4868 is_referenced_by_foreign_key AS [Is Reference by Foreign Key],
4869 last_user_seek AS [Last User Seek],
4870 last_user_scan AS [Last User Scan],
4871 last_user_lookup AS [Last User Lookup],
4872 last_user_update AS [Last User Update],
4873 total_reads AS [Total Reads],
4874 user_updates AS [User Updates],
4875 reads_per_write AS [Reads Per Write],
4876 index_usage_summary AS [Index Usage],
4877 sz.total_singleton_lookup_count AS [Singleton Lookups],
4878 sz.total_range_scan_count AS [Range Scans],
4879 sz.total_leaf_delete_count AS [Leaf Deletes],
4880 sz.total_leaf_update_count AS [Leaf Updates],
4881 sz.index_op_stats AS [Index Op Stats],
4882 sz.partition_count AS [Partition Count],
4883 sz.total_rows AS [Rows],
4884 sz.total_reserved_MB AS [Reserved MB],
4885 sz.total_reserved_LOB_MB AS [Reserved LOB MB],
4886 sz.total_reserved_row_overflow_MB AS [Reserved Row Overflow MB],
4887 sz.index_size_summary AS [Index Size],
4888 sz.total_row_lock_count AS [Row Lock Count],
4889 sz.total_row_lock_wait_count AS [Row Lock Wait Count],
4890 sz.total_row_lock_wait_in_ms AS [Row Lock Wait ms],
4891 sz.avg_row_lock_wait_in_ms AS [Avg Row Lock Wait ms],
4892 sz.total_page_lock_count AS [Page Lock Count],
4893 sz.total_page_lock_wait_count AS [Page Lock Wait Count],
4894 sz.total_page_lock_wait_in_ms AS [Page Lock Wait ms],
4895 sz.avg_page_lock_wait_in_ms AS [Avg Page Lock Wait ms],
4896 sz.total_index_lock_promotion_attempt_count AS [Lock Escalation Attempts],
4897 sz.total_index_lock_promotion_count AS [Lock Escalations],
4898 sz.page_latch_wait_count AS [Page Latch Wait Count],
4899 sz.page_latch_wait_in_ms AS [Page Latch Wait ms],
4900 sz.page_io_latch_wait_count AS [Page IO Latch Wait Count],
4901 sz.page_io_latch_wait_in_ms as [Page IO Latch Wait ms],
4902 sz.data_compression_desc AS [Data Compression],
4903 i.create_date AS [Create Date],
4904 i.modify_date AS [Modify Date],
4905 more_info AS [More Info],
4906 CASE
4907 WHEN i.is_primary_key = 1 AND i.index_definition <> '[HEAP]'
4908 THEN N'--ALTER TABLE ' + QUOTENAME(i.[schema_name]) + N'.' + QUOTENAME(i.[object_name])
4909 + N' DROP CONSTRAINT ' + QUOTENAME(i.index_name) + N';'
4910 WHEN i.is_primary_key = 0 AND i.index_definition <> '[HEAP]'
4911 THEN N'--DROP INDEX '+ QUOTENAME(i.index_name) + N' ON ' +
4912 QUOTENAME(i.[schema_name]) + N'.' + QUOTENAME(i.[object_name]) + N';'
4913 ELSE N''
4914 END AS [Drop TSQL],
4915 CASE
4916 WHEN i.index_definition = '[HEAP]' THEN N''
4917 ELSE N'--' + ict.create_tsql END AS [Create TSQL],
4918 1 AS [Display Order]
4919 FROM #IndexSanity AS i --left join here so we don't lose disabled nc indexes
4920 LEFT JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
4921 LEFT JOIN #IndexCreateTsql AS ict ON i.index_sanity_id = ict.index_sanity_id
4922 ORDER BY [Database Name], [Schema Name], [Object Name], [Index ID]
4923 OPTION (RECOMPILE);
4924 END;
4925
4926
4927
4928 END; /* End @Mode=2 (index detail)*/
4929 ELSE IF (@Mode=3) /*Missing index Detail*/
4930 BEGIN
4931 IF(@OutputType <> 'NONE')
4932 BEGIN;
4933 WITH create_date AS (
4934 SELECT i.database_id,
4935 i.schema_name,
4936 i.[object_id],
4937 ISNULL(NULLIF(MAX(DATEDIFF(DAY, i.create_date, SYSDATETIME())), 0), 1) AS create_days
4938 FROM #IndexSanity AS i
4939 GROUP BY i.database_id, i.schema_name, i.object_id
4940 )
4941 SELECT
4942 mi.database_name AS [Database Name],
4943 mi.[schema_name] AS [Schema],
4944 mi.table_name AS [Table],
4945 CAST((mi.magic_benefit_number / CASE WHEN cd.create_days < @DaysUptime THEN cd.create_days ELSE @DaysUptime END) AS BIGINT)
4946 AS [Magic Benefit Number],
4947 mi.missing_index_details AS [Missing Index Details],
4948 mi.avg_total_user_cost AS [Avg Query Cost],
4949 mi.avg_user_impact AS [Est Index Improvement],
4950 mi.user_seeks AS [Seeks],
4951 mi.user_scans AS [Scans],
4952 mi.unique_compiles AS [Compiles],
4953 mi.equality_columns AS [Equality Columns],
4954 mi.inequality_columns AS [Inequality Columns],
4955 mi.included_columns AS [Included Columns],
4956 mi.index_estimated_impact AS [Estimated Impact],
4957 mi.create_tsql AS [Create TSQL],
4958 mi.more_info AS [More Info],
4959 1 AS [Display Order],
4960 mi.is_low
4961 FROM #MissingIndexes AS mi
4962 LEFT JOIN create_date AS cd
4963 ON mi.[object_id] = cd.object_id
4964 AND mi.database_id = cd.database_id
4965 AND mi.schema_name = cd.schema_name
4966 /* Minimum benefit threshold = 100k/day of uptime OR since table creation date, whichever is lower*/
4967 WHERE (mi.magic_benefit_number / CASE WHEN cd.create_days < @DaysUptime THEN cd.create_days ELSE @DaysUptime END) >= 100000
4968 UNION ALL
4969 SELECT
4970 @ScriptVersionName,
4971 N'From Your Community Volunteers' ,
4972 N'http://FirstResponderKit.org' ,
4973 100000000000,
4974 @DaysUptimeInsertValue,
4975 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
4976 NULL, 0 AS [Display Order], NULL AS is_low
4977 ORDER BY [Display Order] ASC, [Magic Benefit Number] DESC
4978 OPTION (RECOMPILE);
4979 END;
4980
4981 IF (@BringThePain = 1
4982 AND @DatabaseName IS NOT NULL
4983 AND @GetAllDatabases = 0)
4984
4985 BEGIN
4986
4987 EXEC sp_BlitzCache @SortOrder = 'sp_BlitzIndex', @DatabaseName = @DatabaseName, @BringThePain = 1, @QueryFilter = 'statement', @HideSummary = 1;
4988
4989 END;
4990
4991
4992 END; /* End @Mode=3 (index detail)*/
4993END;
4994END TRY
4995
4996BEGIN CATCH
4997 RAISERROR (N'Failure analyzing temp tables.', 0,1) WITH NOWAIT;
4998
4999 SELECT @msg = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
5000
5001 RAISERROR (@msg,
5002 @ErrorSeverity,
5003 @ErrorState
5004 );
5005
5006 WHILE @@trancount > 0
5007 ROLLBACK;
5008
5009 RETURN;
5010 END CATCH;