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