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