· 9 years ago · Feb 02, 2017, 09:48 AM
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 @GetAllDatabases BIT = 0,
24 @BringThePain BIT = 0,
25 @ThresholdMB INT = 250 /* Number of megabytes that an object must be before we include it in basic results */,
26 @OutputServerName NVARCHAR(256) = NULL ,
27 @OutputDatabaseName NVARCHAR(256) = NULL ,
28 @OutputSchemaName NVARCHAR(256) = NULL ,
29 @OutputTableName NVARCHAR(256) = NULL ,
30 @Help TINYINT = 0,
31 @VersionDate DATETIME = NULL OUTPUT
32AS
33SET NOCOUNT ON;
34SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
35DECLARE @Version VARCHAR(30);
36SET @Version = '4.1';
37SET @VersionDate = '20160715';
38IF @Help = 1 PRINT '
39/*
40sp_BlitzIndex from http://FirstResponderKit.org
41
42This script analyzes the design and performance of your indexes.
43
44To learn more, visit http://FirstResponderKit.org where you can download new
45versions for free, watch training videos on how it works, get more info on
46the findings, contribute your own code, and more.
47
48Known limitations of this version:
49 - Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
50 - The @OutputDatabaseName parameters are not functional yet. To check the
51 status of this enhancement request, visit:
52 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/221
53 - Does not analyze columnstore, spatial, XML, or full text indexes. If you
54 would like to contribute code to analyze those, head over to Github and
55 check out the issues list: http://FirstResponderKit.org
56 - Index create statements are just to give you a rough idea of the syntax. It includes filters and fillfactor.
57 -- Example 1: index creates use ONLINE=? instead of ONLINE=ON / ONLINE=OFF. This is because it is important
58 for the user to understand if it is going to be offline and not just run a script.
59 -- Example 2: they do not include all the options the index may have been created with (padding, compression
60 filegroup/partition scheme etc.)
61 -- (The compression and filegroup index create syntax is not trivial because it is set at the partition
62 level and is not trivial to code.)
63 - Does not advise you about data modeling for clustered indexes and primary keys (primarily looks for signs of insanity.)
64
65Unknown limitations of this version:
66 - We knew them once, but we forgot.
67
68Changes in v4.1 - 2016/07/15:
69 - Compression information in @Mode = 2:
70 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/18
71 - Use recently-modified check to improve indexes-not-in-use recommendations:
72 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/220
73 - Alphabetical sort for @GetAllDatabases = 1, @Mode = 2 output:
74 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/351
75 - Remove per-day cost filter for missing indexes in @Mode = 4:
76 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/338
77 - Missing index benefit is now labeled per-day to make it more obvious:
78 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/330
79
80Changes in v4.0 - 2016/06/26:
81 - BREAKING CHANGE: Standardized input & output parameters to be
82 consistent across the entire First Responder Kit. This also means the old
83 old output parameter @Version is no more, because we are switching to
84 semantic versioning.
85 https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/284
86- Bug fixes and improvements
87 - Erik Darling:
88 -Changed index_sanity_id to be NULLable in #IndexPartitionSanity
89 -Changed version check to only ever use LEFT JOIN query to get partition information.
90 This was taking upwards of 6 minutes with 15k partitions.
91
92Changes in v3.0 - 2016/03/20:
93 - Prioritized results
94 - Moved URL to near the end of columns
95 - Added 100k/day minimum benefit to high-value missing index recs
96 - Expanded avg query cost on missing indexes to 4 decimal places
97 - Formatted number of uses on missing indexes to use commas (money format)
98 - Changed benefit formula to divide benefit number by uptime
99 - When using either @Mode = 0 or @GetAllDatabases = 1, results are limited to:
100 * Duplicate indexes where both are larger than @ThresholdMB
101 * Blocking with a high threshold ( TBD)
102 * Unread indexes larger than @ThresholdMB
103 * Heaps larger than @ThresholdMB with updates or deletes
104 * Identities about to run out of room
105 * The top 20 missing indexes
106 * Abnormal psychology stuff (as an FYI for query / index tuning)
107 - Running @GetAllDatabases requires an override parameter (@BringThePain = 1) to run against 50+ databases
108
109MIT License
110
111Copyright (c) 2016 Brent Ozar Unlimited
112
113Permission is hereby granted, free of charge, to any person obtaining a copy
114of this software and associated documentation files (the "Software"), to deal
115in the Software without restriction, including without limitation the rights
116to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
117copies of the Software, and to permit persons to whom the Software is
118furnished to do so, subject to the following conditions:
119
120The above copyright notice and this permission notice shall be included in all
121copies or substantial portions of the Software.
122
123THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
124IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
125FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
126AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
127LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
128OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
129SOFTWARE.
130'
131
132
133
134DECLARE @DaysUptime NUMERIC(23,2);
135DECLARE @DatabaseID INT;
136DECLARE @ObjectID INT;
137DECLARE @dsql NVARCHAR(MAX);
138DECLARE @params NVARCHAR(MAX);
139DECLARE @msg NVARCHAR(4000);
140DECLARE @ErrorSeverity INT;
141DECLARE @ErrorState INT;
142DECLARE @Rowcount BIGINT;
143DECLARE @SQLServerProductVersion NVARCHAR(128);
144DECLARE @SQLServerEdition INT;
145DECLARE @FilterMB INT;
146DECLARE @collation NVARCHAR(256);
147DECLARE @NumDatabases INT;
148DECLARE @LineFeed NVARCHAR(5);
149
150SET @LineFeed = CHAR(13) + CHAR(10);
151SELECT @SQLServerProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
152SELECT @SQLServerEdition =CAST(SERVERPROPERTY('EngineEdition') AS INT); /* We default to online index creates where EngineEdition=3*/
153SET @FilterMB=250;
154
155RAISERROR(N'Starting run. sp_BlitzIndex(R) v4.0 - June 26, 2016', 0,1) WITH NOWAIT;
156
157IF OBJECT_ID('tempdb..#IndexSanity') IS NOT NULL
158 DROP TABLE #IndexSanity;
159
160IF OBJECT_ID('tempdb..#IndexPartitionSanity') IS NOT NULL
161 DROP TABLE #IndexPartitionSanity;
162
163IF OBJECT_ID('tempdb..#IndexSanitySize') IS NOT NULL
164 DROP TABLE #IndexSanitySize;
165
166IF OBJECT_ID('tempdb..#IndexColumns') IS NOT NULL
167 DROP TABLE #IndexColumns;
168
169IF OBJECT_ID('tempdb..#MissingIndexes') IS NOT NULL
170 DROP TABLE #MissingIndexes;
171
172IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
173 DROP TABLE #ForeignKeys;
174
175IF OBJECT_ID('tempdb..#BlitzIndexResults') IS NOT NULL
176 DROP TABLE #BlitzIndexResults;
177
178IF OBJECT_ID('tempdb..#IndexCreateTsql') IS NOT NULL
179 DROP TABLE #IndexCreateTsql;
180
181IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
182 DROP TABLE #DatabaseList;
183
184 RAISERROR (N'Create temp tables.',0,1) WITH NOWAIT;
185 CREATE TABLE #BlitzIndexResults
186 (
187 blitz_result_id INT IDENTITY PRIMARY KEY,
188 check_id INT NOT NULL,
189 index_sanity_id INT NULL,
190 Priority INT NULL,
191 findings_group VARCHAR(4000) NOT NULL,
192 finding VARCHAR(200) NOT NULL,
193 [database_name] VARCHAR(200) NULL,
194 URL VARCHAR(200) NOT NULL,
195 details NVARCHAR(4000) NOT NULL,
196 index_definition NVARCHAR(MAX) NOT NULL,
197 secret_columns NVARCHAR(MAX) NULL,
198 index_usage_summary NVARCHAR(MAX) NULL,
199 index_size_summary NVARCHAR(MAX) NULL,
200 create_tsql NVARCHAR(MAX) NULL,
201 more_info NVARCHAR(MAX)NULL
202 );
203
204 CREATE TABLE #IndexSanity
205 (
206 [index_sanity_id] INT IDENTITY PRIMARY KEY,
207 [database_id] SMALLINT NOT NULL ,
208 [object_id] INT NOT NULL ,
209 [index_id] INT NOT NULL ,
210 [index_type] TINYINT NOT NULL,
211 [database_name] NVARCHAR(128) NOT NULL ,
212 [schema_name] NVARCHAR(128) NOT NULL ,
213 [object_name] NVARCHAR(128) NOT NULL ,
214 index_name NVARCHAR(128) NULL ,
215 key_column_names NVARCHAR(MAX) NULL ,
216 key_column_names_with_sort_order NVARCHAR(MAX) NULL ,
217 key_column_names_with_sort_order_no_types NVARCHAR(MAX) NULL ,
218 count_key_columns INT NULL ,
219 include_column_names NVARCHAR(MAX) NULL ,
220 include_column_names_no_types NVARCHAR(MAX) NULL ,
221 count_included_columns INT NULL ,
222 partition_key_column_name NVARCHAR(MAX) NULL,
223 filter_definition NVARCHAR(MAX) NOT NULL ,
224 is_indexed_view BIT NOT NULL ,
225 is_unique BIT NOT NULL ,
226 is_primary_key BIT NOT NULL ,
227 is_XML BIT NOT NULL,
228 is_spatial BIT NOT NULL,
229 is_NC_columnstore BIT NOT NULL,
230 is_CX_columnstore BIT NOT NULL,
231 is_disabled BIT NOT NULL ,
232 is_hypothetical BIT NOT NULL ,
233 is_padded BIT NOT NULL ,
234 fill_factor SMALLINT NOT NULL ,
235 user_seeks BIGINT NOT NULL ,
236 user_scans BIGINT NOT NULL ,
237 user_lookups BIGINT NOT NULL ,
238 user_updates BIGINT NULL ,
239 last_user_seek DATETIME NULL ,
240 last_user_scan DATETIME NULL ,
241 last_user_lookup DATETIME NULL ,
242 last_user_update DATETIME NULL ,
243 is_referenced_by_foreign_key BIT DEFAULT(0),
244 secret_columns NVARCHAR(MAX) NULL,
245 count_secret_columns INT NULL,
246 create_date DATETIME NOT NULL,
247 modify_date DATETIME NOT NULL
248 );
249
250 CREATE TABLE #IndexPartitionSanity
251 (
252 [index_partition_sanity_id] INT IDENTITY PRIMARY KEY ,
253 [index_sanity_id] INT NULL ,
254 [database_id] INT NOT NULL ,
255 [object_id] INT NOT NULL ,
256 [index_id] INT NOT NULL ,
257 [partition_number] INT NOT NULL ,
258 row_count BIGINT NOT NULL ,
259 reserved_MB NUMERIC(29,2) NOT NULL ,
260 reserved_LOB_MB NUMERIC(29,2) NOT NULL ,
261 reserved_row_overflow_MB NUMERIC(29,2) NOT NULL ,
262 leaf_insert_count BIGINT NULL ,
263 leaf_delete_count BIGINT NULL ,
264 leaf_update_count BIGINT NULL ,
265 range_scan_count BIGINT NULL ,
266 singleton_lookup_count BIGINT NULL ,
267 forwarded_fetch_count BIGINT NULL ,
268 lob_fetch_in_pages BIGINT NULL ,
269 lob_fetch_in_bytes BIGINT NULL ,
270 row_overflow_fetch_in_pages BIGINT NULL ,
271 row_overflow_fetch_in_bytes BIGINT NULL ,
272 row_lock_count BIGINT NULL ,
273 row_lock_wait_count BIGINT NULL ,
274 row_lock_wait_in_ms BIGINT NULL ,
275 page_lock_count BIGINT NULL ,
276 page_lock_wait_count BIGINT NULL ,
277 page_lock_wait_in_ms BIGINT NULL ,
278 index_lock_promotion_attempt_count BIGINT NULL ,
279 index_lock_promotion_count BIGINT NULL,
280 data_compression_desc VARCHAR(60) NULL
281 );
282
283 CREATE TABLE #IndexSanitySize
284 (
285 [index_sanity_size_id] INT IDENTITY NOT NULL ,
286 [index_sanity_id] INT NULL ,
287 [database_id] INT NOT NULL,
288 partition_count INT NOT NULL ,
289 total_rows BIGINT NOT NULL ,
290 total_reserved_MB NUMERIC(29,2) NOT NULL ,
291 total_reserved_LOB_MB NUMERIC(29,2) NOT NULL ,
292 total_reserved_row_overflow_MB NUMERIC(29,2) NOT NULL ,
293 total_leaf_delete_count BIGINT NULL,
294 total_leaf_update_count BIGINT NULL,
295 total_range_scan_count BIGINT NULL,
296 total_singleton_lookup_count BIGINT NULL,
297 total_forwarded_fetch_count BIGINT NULL,
298 total_row_lock_count BIGINT NULL ,
299 total_row_lock_wait_count BIGINT NULL ,
300 total_row_lock_wait_in_ms BIGINT NULL ,
301 avg_row_lock_wait_in_ms BIGINT NULL ,
302 total_page_lock_count BIGINT NULL ,
303 total_page_lock_wait_count BIGINT NULL ,
304 total_page_lock_wait_in_ms BIGINT NULL ,
305 avg_page_lock_wait_in_ms BIGINT NULL ,
306 total_index_lock_promotion_attempt_count BIGINT NULL ,
307 total_index_lock_promotion_count BIGINT NULL ,
308 data_compression_desc VARCHAR(8000) NULL
309 );
310
311 CREATE TABLE #IndexColumns
312 (
313 [database_id] INT NOT NULL,
314 [object_id] INT NOT NULL ,
315 [index_id] INT NOT NULL ,
316 [key_ordinal] INT NULL ,
317 is_included_column BIT NULL ,
318 is_descending_key BIT NULL ,
319 [partition_ordinal] INT NULL ,
320 column_name NVARCHAR(256) NOT NULL ,
321 system_type_name NVARCHAR(256) NOT NULL,
322 max_length SMALLINT NOT NULL,
323 [precision] TINYINT NOT NULL,
324 [scale] TINYINT NOT NULL,
325 collation_name NVARCHAR(256) NULL,
326 is_nullable BIT NULL,
327 is_identity BIT NULL,
328 is_computed BIT NULL,
329 is_replicated BIT NULL,
330 is_sparse BIT NULL,
331 is_filestream BIT NULL,
332 seed_value BIGINT NULL,
333 increment_value INT NULL ,
334 last_value BIGINT NULL,
335 is_not_for_replication BIT NULL
336 );
337
338 CREATE TABLE #MissingIndexes
339 ([object_id] INT NOT NULL,
340 [database_name] NVARCHAR(128) NOT NULL ,
341 [schema_name] NVARCHAR(128) NOT NULL ,
342 [table_name] NVARCHAR(128),
343 [statement] NVARCHAR(512) NOT NULL,
344 magic_benefit_number AS (( user_seeks + user_scans ) * avg_total_user_cost * avg_user_impact),
345 avg_total_user_cost NUMERIC(29,4) NOT NULL,
346 avg_user_impact NUMERIC(29,1) NOT NULL,
347 user_seeks BIGINT NOT NULL,
348 user_scans BIGINT NOT NULL,
349 unique_compiles BIGINT NULL,
350 equality_columns NVARCHAR(4000),
351 inequality_columns NVARCHAR(4000),
352 included_columns NVARCHAR(4000)
353 );
354
355 CREATE TABLE #ForeignKeys (
356 [database_name] NVARCHAR(128) NOT NULL ,
357 foreign_key_name NVARCHAR(256),
358 parent_object_id INT,
359 parent_object_name NVARCHAR(256),
360 referenced_object_id INT,
361 referenced_object_name NVARCHAR(256),
362 is_disabled BIT,
363 is_not_trusted BIT,
364 is_not_for_replication BIT,
365 parent_fk_columns NVARCHAR(MAX),
366 referenced_fk_columns NVARCHAR(MAX),
367 update_referential_action_desc NVARCHAR(16),
368 delete_referential_action_desc NVARCHAR(60)
369 )
370
371 CREATE TABLE #IndexCreateTsql (
372 index_sanity_id INT NOT NULL,
373 create_tsql NVARCHAR(MAX) NOT NULL
374 )
375
376 CREATE TABLE #DatabaseList (
377 DatabaseName NVARCHAR(256)
378 )
379
380IF @GetAllDatabases = 1
381 BEGIN
382 INSERT INTO #DatabaseList (DatabaseName)
383 SELECT DB_NAME(database_id)
384 FROM sys.databases
385 WHERE user_access_desc='MULTI_USER'
386 AND state_desc = 'ONLINE'
387 AND database_id > 4
388 AND DB_NAME(database_id) NOT IN ('ReportServer','ReportServerTempDB')
389 AND is_distributor = 0;
390 END
391ELSE
392 BEGIN
393 INSERT INTO #DatabaseList
394 ( DatabaseName )
395 SELECT CASE WHEN @DatabaseName IS NULL OR @DatabaseName = N'' THEN DB_NAME()
396 ELSE @DatabaseName END
397 END
398
399SET @NumDatabases = @@ROWCOUNT;
400
401/* Running on 50+ databases can take a reaaallly long time, so we want explicit permission to do so (and only after warning about it) */
402
403BEGIN TRY
404 IF @NumDatabases >= 50 AND @BringThePain != 1
405 BEGIN
406 SET @msg= N'You''re trying to run sp_BlitzIndex on a server with ' + CAST(@NumDatabases AS NVARCHAR(8)) + N' databases. '
407 + CHAR(13) + N'Running sp_BlitzIndex on a server with 50+ databases may cause temporary insanity for the server and/or user.'
408 + CHAR(13) + N'If you''re sure you want to do this, run again with the parameter @BringThePain = 1.';
409 RAISERROR(@msg, 10,1) WITH NOWAIT;
410 RETURN;
411 END
412END TRY
413BEGIN CATCH
414 RAISERROR (N'Failure to execute due to number of databases.', 0,1) WITH NOWAIT;
415
416 SELECT @msg = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
417
418 RAISERROR (@msg,
419 @ErrorSeverity,
420 @ErrorState
421 );
422
423 WHILE @@trancount > 0
424 ROLLBACK;
425
426 RETURN;
427 END CATCH;
428
429/* Permission granted or unnecessary? Ok, let's go! */
430
431DECLARE c1 CURSOR
432LOCAL FAST_FORWARD
433FOR
434SELECT DatabaseName FROM #DatabaseList ORDER BY DatabaseName
435
436OPEN c1
437FETCH NEXT FROM c1 INTO @DatabaseName
438 WHILE @@FETCH_STATUS = 0
439BEGIN
440
441 RAISERROR (@LineFeed, 0, 1) WITH NOWAIT;
442 RAISERROR (@LineFeed, 0, 1) WITH NOWAIT;
443 RAISERROR (@DatabaseName, 0, 1) WITH NOWAIT;
444
445SELECT @DatabaseID = [database_id]
446FROM sys.databases
447 WHERE [name] = @DatabaseName
448 AND user_access_desc='MULTI_USER'
449 AND state_desc = 'ONLINE';
450
451/* Last startup */
452SELECT @DaysUptime = CAST(DATEDIFF(hh,create_date,GETDATE())/24. AS NUMERIC (23,2))
453FROM sys.databases
454WHERE database_id = 2;
455
456IF @DaysUptime = 0 SET @DaysUptime = .01;
457
458----------------------------------------
459--STEP 1: OBSERVE THE PATIENT
460--This step puts index information into temp tables.
461----------------------------------------
462BEGIN TRY
463 BEGIN
464
465 --Validate SQL Server Verson
466
467 IF (SELECT LEFT(@SQLServerProductVersion,
468 CHARINDEX('.',@SQLServerProductVersion,0)-1
469 )) <= 8
470 BEGIN
471 SET @msg=N'sp_BlitzIndex is only supported on SQL Server 2005 and higher. The version of this instance is: ' + @SQLServerProductVersion;
472 RAISERROR(@msg,16,1);
473 END
474
475 --Short circuit here if database name does not exist.
476 IF @DatabaseName IS NULL OR @DatabaseID IS NULL
477 BEGIN
478 SET @msg='Database does not exist or is not online/multi-user: cannot proceed.'
479 RAISERROR(@msg,16,1);
480 END
481
482 --Validate parameters.
483 IF (@Mode NOT IN (0,1,2,3,4))
484 BEGIN
485 SET @msg=N'Invalid @Mode parameter. 0=diagnose, 1=summarize, 2=index detail, 3=missing index detail, 4=diagnose detail';
486 RAISERROR(@msg,16,1);
487 END
488
489 IF (@Mode <> 0 AND @TableName IS NOT NULL)
490 BEGIN
491 SET @msg=N'Setting the @Mode doesn''t change behavior if you supply @TableName. Use default @Mode=0 to see table detail.';
492 RAISERROR(@msg,16,1);
493 END
494
495 IF ((@Mode <> 0 OR @TableName IS NOT NULL) AND @Filter <> 0)
496 BEGIN
497 SET @msg=N'@Filter only appies when @Mode=0 and @TableName is not specified. Please try again.';
498 RAISERROR(@msg,16,1);
499 END
500
501 IF (@SchemaName IS NOT NULL AND @TableName IS NULL)
502 BEGIN
503 SET @msg='We can''t run against a whole schema! Specify a @TableName, or leave both NULL for diagnosis.'
504 RAISERROR(@msg,16,1);
505 END
506
507
508 IF (@TableName IS NOT NULL AND @SchemaName IS NULL)
509 BEGIN
510 SET @SchemaName=N'dbo'
511 SET @msg='@SchemaName wasn''t specified-- assuming schema=dbo.'
512 RAISERROR(@msg,1,1) WITH NOWAIT;
513 END
514
515 --If a table is specified, grab the object id.
516 --Short circuit if it doesn't exist.
517 IF @TableName IS NOT NULL
518 BEGIN
519 SET @dsql = N'
520 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
521 SELECT @ObjectID= OBJECT_ID
522 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so
523 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas AS sc on
524 so.schema_id=sc.schema_id
525 where so.type in (''U'', ''V'')
526 and so.name=' + QUOTENAME(@TableName,'''')+ N'
527 and sc.name=' + QUOTENAME(@SchemaName,'''')+ N'
528 /*Has a row in sys.indexes. This lets us get indexed views.*/
529 and exists (
530 SELECT si.name
531 FROM ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS si
532 WHERE so.object_id=si.object_id)
533 OPTION (RECOMPILE);';
534
535 SET @params='@ObjectID INT OUTPUT'
536
537 IF @dsql IS NULL
538 RAISERROR('@dsql is null',16,1);
539
540 EXEC sp_executesql @dsql, @params, @ObjectID=@ObjectID OUTPUT;
541
542 IF @ObjectID IS NULL
543 BEGIN
544 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) +
545 N'Please check your parameters.'
546 RAISERROR(@msg,1,1);
547 RETURN;
548 END
549 END
550
551 --set @collation
552 SELECT @collation=collation_name
553 FROM sys.databases
554 WHERE database_id=@DatabaseID;
555
556 --insert columns for clustered indexes and heaps
557 --collect info on identity columns for this one
558 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
559 SELECT ' + CAST(@DatabaseID AS NVARCHAR(16)) + ',
560 si.object_id,
561 si.index_id,
562 sc.key_ordinal,
563 sc.is_included_column,
564 sc.is_descending_key,
565 sc.partition_ordinal,
566 c.name as column_name,
567 st.name as system_type_name,
568 c.max_length,
569 c.[precision],
570 c.[scale],
571 c.collation_name,
572 c.is_nullable,
573 c.is_identity,
574 c.is_computed,
575 c.is_replicated,
576 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_sparse' ELSE N'NULL as is_sparse' END + N',
577 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_filestream' ELSE N'NULL as is_filestream' END + N',
578 CAST(ic.seed_value AS BIGINT),
579 CAST(ic.increment_value AS INT),
580 CAST(ic.last_value AS BIGINT),
581 ic.is_not_for_replication
582 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.indexes si
583 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c ON
584 si.object_id=c.object_id
585 LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.index_columns sc ON
586 sc.object_id = si.object_id
587 and sc.index_id=si.index_id
588 AND sc.column_id=c.column_id
589 LEFT JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.identity_columns ic ON
590 c.object_id=ic.object_id and
591 c.column_id=ic.column_id
592 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.types st ON
593 c.system_type_id=st.system_type_id
594 AND c.user_type_id=st.user_type_id
595 WHERE si.index_id in (0,1) '
596 + CASE WHEN @ObjectID IS NOT NULL
597 THEN N' AND si.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
598 ELSE N'' END
599 + N';';
600
601 IF @dsql IS NULL
602 RAISERROR('@dsql is null',16,1);
603
604 RAISERROR (N'Inserting data into #IndexColumns for clustered indexes and heaps',0,1) WITH NOWAIT;
605 INSERT #IndexColumns ( database_id, object_id, index_id, key_ordinal, is_included_column, is_descending_key, partition_ordinal,
606 column_name, system_type_name, max_length, precision, scale, collation_name, is_nullable, is_identity, is_computed,
607 is_replicated, is_sparse, is_filestream, seed_value, increment_value, last_value, is_not_for_replication )
608 EXEC sp_executesql @dsql;
609
610 --insert columns for nonclustered indexes
611 --this uses a full join to sys.index_columns
612 --We don't collect info on identity columns here. They may be in NC indexes, but we just analyze identities in the base table.
613 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
614 SELECT ' + CAST(@DatabaseID AS NVARCHAR(16)) + ',
615 si.object_id,
616 si.index_id,
617 sc.key_ordinal,
618 sc.is_included_column,
619 sc.is_descending_key,
620 sc.partition_ordinal,
621 c.name as column_name,
622 st.name as system_type_name,
623 c.max_length,
624 c.[precision],
625 c.[scale],
626 c.collation_name,
627 c.is_nullable,
628 c.is_identity,
629 c.is_computed,
630 c.is_replicated,
631 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_sparse' ELSE N'NULL AS is_sparse' END + N',
632 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'c.is_filestream' ELSE N'NULL AS is_filestream' END + N'
633 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.indexes AS si
634 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns AS c ON
635 si.object_id=c.object_id
636 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.index_columns AS sc ON
637 sc.object_id = si.object_id
638 and sc.index_id=si.index_id
639 AND sc.column_id=c.column_id
640 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.types AS st ON
641 c.system_type_id=st.system_type_id
642 AND c.user_type_id=st.user_type_id
643 WHERE si.index_id not in (0,1) '
644 + CASE WHEN @ObjectID IS NOT NULL
645 THEN N' AND si.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
646 ELSE N'' END
647 + N';';
648
649 IF @dsql IS NULL
650 RAISERROR('@dsql is null',16,1);
651
652 RAISERROR (N'Inserting data into #IndexColumns for nonclustered indexes',0,1) WITH NOWAIT;
653 INSERT #IndexColumns ( database_id, object_id, index_id, key_ordinal, is_included_column, is_descending_key, partition_ordinal,
654 column_name, system_type_name, max_length, precision, scale, collation_name, is_nullable, is_identity, is_computed,
655 is_replicated, is_sparse, is_filestream )
656 EXEC sp_executesql @dsql;
657
658 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
659 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + ' AS database_id,
660 so.object_id,
661 si.index_id,
662 si.type,
663 ' + QUOTENAME(@DatabaseName, '''') + ' AS database_name,
664 COALESCE(sc.NAME, ''Unknown'') AS [schema_name],
665 COALESCE(so.name, ''Unknown'') AS [object_name],
666 COALESCE(si.name, ''Unknown'') AS [index_name],
667 CASE WHEN so.[type] = CAST(''V'' AS CHAR(2)) THEN 1 ELSE 0 END,
668 si.is_unique,
669 si.is_primary_key,
670 CASE when si.type = 3 THEN 1 ELSE 0 END AS is_XML,
671 CASE when si.type = 4 THEN 1 ELSE 0 END AS is_spatial,
672 CASE when si.type = 6 THEN 1 ELSE 0 END AS is_NC_columnstore,
673 CASE when si.type = 5 then 1 else 0 end as is_CX_columnstore,
674 si.is_disabled,
675 si.is_hypothetical,
676 si.is_padded,
677 si.fill_factor,'
678 + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN '
679 CASE WHEN si.filter_definition IS NOT NULL THEN si.filter_definition
680 ELSE ''''
681 END AS filter_definition' ELSE ''''' AS filter_definition' END + '
682 , ISNULL(us.user_seeks, 0), ISNULL(us.user_scans, 0),
683 ISNULL(us.user_lookups, 0), ISNULL(us.user_updates, 0), us.last_user_seek, us.last_user_scan,
684 us.last_user_lookup, us.last_user_update,
685 so.create_date, so.modify_date
686 FROM ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS si WITH (NOLOCK)
687 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.objects AS so WITH (NOLOCK) ON si.object_id = so.object_id
688 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
689 AND so.type <> ''TF'' /*Exclude table valued functions*/
690 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.schemas sc ON so.schema_id = sc.schema_id
691 LEFT JOIN sys.dm_db_index_usage_stats AS us WITH (NOLOCK) ON si.[object_id] = us.[object_id]
692 AND si.index_id = us.index_id
693 AND us.database_id = '+ CAST(@DatabaseID AS NVARCHAR(10)) + '
694 WHERE si.[type] IN ( 0, 1, 2, 3, 4, 5, 6 )
695 /* Heaps, clustered, nonclustered, XML, spatial, Cluster Columnstore, NC Columnstore */ ' +
696 CASE WHEN @TableName IS NOT NULL THEN ' and so.name=' + QUOTENAME(@TableName,'''') + ' ' ELSE '' END +
697 'OPTION ( RECOMPILE );
698 ';
699 IF @dsql IS NULL
700 RAISERROR('@dsql is null',16,1);
701
702 RAISERROR (N'Inserting data into #IndexSanity',0,1) WITH NOWAIT;
703 INSERT #IndexSanity ( [database_id], [object_id], [index_id], [index_type], [database_name], [schema_name], [object_name],
704 index_name, is_indexed_view, is_unique, is_primary_key, is_XML, is_spatial, is_NC_columnstore, is_CX_columnstore,
705 is_disabled, is_hypothetical, is_padded, fill_factor, filter_definition, user_seeks, user_scans,
706 user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update,
707 create_date, modify_date )
708 EXEC sp_executesql @dsql;
709
710 RAISERROR (N'Updating #IndexSanity.key_column_names',0,1) WITH NOWAIT;
711 UPDATE #IndexSanity
712 SET key_column_names = D1.key_column_names
713 FROM #IndexSanity si
714 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name
715 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
716 AS col_definition
717 FROM #IndexColumns c
718 WHERE c.database_id= si.database_id
719 AND c.object_id = si.object_id
720 AND c.index_id = si.index_id
721 AND c.is_included_column = 0 /*Just Keys*/
722 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
723 ORDER BY c.object_id, c.index_id, c.key_ordinal
724 FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 1, ''))
725 ) D1 ( key_column_names )
726
727 RAISERROR (N'Updating #IndexSanity.partition_key_column_name',0,1) WITH NOWAIT;
728 UPDATE #IndexSanity
729 SET partition_key_column_name = D1.partition_key_column_name
730 FROM #IndexSanity si
731 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name AS col_definition
732 FROM #IndexColumns c
733 WHERE c.database_id= si.database_id
734 AND c.object_id = si.object_id
735 AND c.index_id = si.index_id
736 AND c.partition_ordinal <> 0 /*Just Partitioned Keys*/
737 ORDER BY c.object_id, c.index_id, c.key_ordinal
738 FOR XML PATH('') , TYPE).value('.', 'varchar(max)'), 1, 1,''))) D1
739 ( partition_key_column_name )
740
741 RAISERROR (N'Updating #IndexSanity.key_column_names_with_sort_order',0,1) WITH NOWAIT;
742 UPDATE #IndexSanity
743 SET key_column_names_with_sort_order = D2.key_column_names_with_sort_order
744 FROM #IndexSanity si
745 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name + CASE c.is_descending_key
746 WHEN 1 THEN N' DESC'
747 ELSE N''
748 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
749 END AS col_definition
750 FROM #IndexColumns c
751 WHERE c.database_id= si.database_id
752 AND c.object_id = si.object_id
753 AND c.index_id = si.index_id
754 AND c.is_included_column = 0 /*Just Keys*/
755 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
756 ORDER BY c.object_id, c.index_id, c.key_ordinal
757 FOR XML PATH('') , TYPE).value('.', 'varchar(max)'), 1, 1, ''))
758 ) D2 ( key_column_names_with_sort_order )
759
760 RAISERROR (N'Updating #IndexSanity.key_column_names_with_sort_order_no_types (for create tsql)',0,1) WITH NOWAIT;
761 UPDATE #IndexSanity
762 SET key_column_names_with_sort_order_no_types = D2.key_column_names_with_sort_order_no_types
763 FROM #IndexSanity si
764 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + QUOTENAME(c.column_name) + CASE c.is_descending_key
765 WHEN 1 THEN N' [DESC]'
766 ELSE N''
767 END AS col_definition
768 FROM #IndexColumns c
769 WHERE c.database_id= si.database_id
770 AND c.object_id = si.object_id
771 AND c.index_id = si.index_id
772 AND c.is_included_column = 0 /*Just Keys*/
773 AND c.key_ordinal > 0 /*Ignore non-key columns, such as partitioning keys*/
774 ORDER BY c.object_id, c.index_id, c.key_ordinal
775 FOR XML PATH('') , TYPE).value('.', 'varchar(max)'), 1, 1, ''))
776 ) D2 ( key_column_names_with_sort_order_no_types )
777
778 RAISERROR (N'Updating #IndexSanity.include_column_names',0,1) WITH NOWAIT;
779 UPDATE #IndexSanity
780 SET include_column_names = D3.include_column_names
781 FROM #IndexSanity si
782 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + c.column_name
783 + N' {' + system_type_name + N' ' + CAST(max_length AS NVARCHAR(50)) + N'}'
784 FROM #IndexColumns c
785 WHERE c.database_id= si.database_id
786 AND c.object_id = si.object_id
787 AND c.index_id = si.index_id
788 AND c.is_included_column = 1 /*Just includes*/
789 ORDER BY c.column_name /*Order doesn't matter in includes,
790 this is here to make rows easy to compare.*/
791 FOR XML PATH('') , TYPE).value('.', 'varchar(max)'), 1, 1, ''))
792 ) D3 ( include_column_names );
793
794 RAISERROR (N'Updating #IndexSanity.include_column_names_no_types (for create tsql)',0,1) WITH NOWAIT;
795 UPDATE #IndexSanity
796 SET include_column_names_no_types = D3.include_column_names_no_types
797 FROM #IndexSanity si
798 CROSS APPLY ( SELECT RTRIM(STUFF( (SELECT N', ' + QUOTENAME(c.column_name)
799 FROM #IndexColumns c
800 WHERE c.database_id= si.database_id
801 AND c.object_id = si.object_id
802 AND c.index_id = si.index_id
803 AND c.is_included_column = 1 /*Just includes*/
804 ORDER BY c.column_name /*Order doesn't matter in includes,
805 this is here to make rows easy to compare.*/
806 FOR XML PATH('') , TYPE).value('.', 'varchar(max)'), 1, 1, ''))
807 ) D3 ( include_column_names_no_types );
808
809 RAISERROR (N'Updating #IndexSanity.count_key_columns and count_include_columns',0,1) WITH NOWAIT;
810 UPDATE #IndexSanity
811 SET count_included_columns = D4.count_included_columns,
812 count_key_columns = D4.count_key_columns
813 FROM #IndexSanity si
814 CROSS APPLY ( SELECT SUM(CASE WHEN is_included_column = 'true' THEN 1
815 ELSE 0
816 END) AS count_included_columns,
817 SUM(CASE WHEN is_included_column = 'false' AND c.key_ordinal > 0 THEN 1
818 ELSE 0
819 END) AS count_key_columns
820 FROM #IndexColumns c
821 WHERE c.database_id= si.database_id
822 AND c.object_id = si.object_id
823 AND c.index_id = si.index_id
824 ) AS D4 ( count_included_columns, count_key_columns );
825
826 IF (SELECT LEFT(@SQLServerProductVersion,
827 CHARINDEX('.',@SQLServerProductVersion,0)-1
828 )) <= 2147483647 --Make change here
829 BEGIN
830
831 RAISERROR (N'Preferring non-2012 syntax with LEFT JOIN to sys.dm_db_index_operational_stats',0,1) WITH NOWAIT;
832
833 --NOTE: If you want to use the newer syntax for 2012+, you'll have to change 2147483647 to 11 on line ~819
834 --This change was made because on a table with lots of paritions, the OUTER APPLY was crazy slow.
835 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
836 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + ' AS database_id,
837 ps.object_id,
838 ps.index_id,
839 ps.partition_number,
840 ps.row_count,
841 ps.reserved_page_count * 8. / 1024. AS reserved_MB,
842 ps.lob_reserved_page_count * 8. / 1024. AS reserved_LOB_MB,
843 ps.row_overflow_reserved_page_count * 8. / 1024. AS reserved_row_overflow_MB,
844 os.leaf_insert_count,
845 os.leaf_delete_count,
846 os.leaf_update_count,
847 os.range_scan_count,
848 os.singleton_lookup_count,
849 os.forwarded_fetch_count,
850 os.lob_fetch_in_pages,
851 os.lob_fetch_in_bytes,
852 os.row_overflow_fetch_in_pages,
853 os.row_overflow_fetch_in_bytes,
854 os.row_lock_count,
855 os.row_lock_wait_count,
856 os.row_lock_wait_in_ms,
857 os.page_lock_count,
858 os.page_lock_wait_count,
859 os.page_lock_wait_in_ms,
860 os.index_lock_promotion_attempt_count,
861 os.index_lock_promotion_count,
862 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN 'par.data_compression_desc ' ELSE 'null as data_compression_desc' END + '
863 FROM ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_partition_stats AS ps
864 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.partitions AS par on ps.partition_id=par.partition_id
865 JOIN ' + QUOTENAME(@DatabaseName) + '.sys.objects AS so ON ps.object_id = so.object_id
866 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
867 AND so.type <> ''TF'' /*Exclude table valued functions*/
868 LEFT JOIN ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_operational_stats('
869 + CAST(@DatabaseID AS NVARCHAR(10)) + ', NULL, NULL,NULL) AS os ON
870 ps.object_id=os.object_id and ps.index_id=os.index_id and ps.partition_number=os.partition_number
871 WHERE 1=1
872 ' + CASE WHEN @ObjectID IS NOT NULL THEN N'AND so.object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' ' ELSE N' ' END + '
873 ' + CASE WHEN @Filter = 2 THEN N'AND ps.reserved_page_count * 8./1024. > ' + CAST(@FilterMB AS NVARCHAR(5)) + N' ' ELSE N' ' END + '
874 ORDER BY ps.object_id, ps.index_id, ps.partition_number
875 OPTION ( RECOMPILE );
876 ';
877 END
878 ELSE
879 BEGIN
880 RAISERROR (N'Using 2012 syntax to query sys.dm_db_index_operational_stats',0,1) WITH NOWAIT;
881 --This is the syntax that will be used if you change 2147483647 to 11 on line ~819.
882 --If you have a lot of paritions and this suddenly starts running for a long time, change it back.
883 SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
884 SELECT ' + CAST(@DatabaseID AS NVARCHAR(10)) + ' AS database_id,
885 ps.object_id,
886 ps.index_id,
887 ps.partition_number,
888 ps.row_count,
889 ps.reserved_page_count * 8. / 1024. AS reserved_MB,
890 ps.lob_reserved_page_count * 8. / 1024. AS reserved_LOB_MB,
891 ps.row_overflow_reserved_page_count * 8. / 1024. AS reserved_row_overflow_MB,
892 os.leaf_insert_count,
893 os.leaf_delete_count,
894 os.leaf_update_count,
895 os.range_scan_count,
896 os.singleton_lookup_count,
897 os.forwarded_fetch_count,
898 os.lob_fetch_in_pages,
899 os.lob_fetch_in_bytes,
900 os.row_overflow_fetch_in_pages,
901 os.row_overflow_fetch_in_bytes,
902 os.row_lock_count,
903 os.row_lock_wait_count,
904 os.row_lock_wait_in_ms,
905 os.page_lock_count,
906 os.page_lock_wait_count,
907 os.page_lock_wait_in_ms,
908 os.index_lock_promotion_attempt_count,
909 os.index_lock_promotion_count,
910 ' + CASE WHEN @SQLServerProductVersion NOT LIKE '9%' THEN N'par.data_compression_desc ' ELSE N'null as data_compression_desc' END + N'
911 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_partition_stats AS ps
912 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.partitions AS par on ps.partition_id=par.partition_id
913 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects AS so ON ps.object_id = so.object_id
914 AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
915 AND so.type <> ''TF'' /*Exclude table valued functions*/
916 OUTER APPLY ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_index_operational_stats('
917 + CAST(@DatabaseID AS NVARCHAR(10)) + N', ps.object_id, ps.index_id,ps.partition_number) AS os
918 WHERE 1=1
919 ' + CASE WHEN @ObjectID IS NOT NULL THEN N'AND so.object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' ' ELSE N' ' END + N'
920 ' + CASE WHEN @Filter = 2 THEN N'AND ps.reserved_page_count * 8./1024. > ' + CAST(@FilterMB AS NVARCHAR(5)) + N' ' ELSE N' ' END + '
921 ORDER BY ps.object_id, ps.index_id, ps.partition_number
922 OPTION ( RECOMPILE );
923 ';
924
925 END
926
927 IF @dsql IS NULL
928 RAISERROR('@dsql is null',16,1);
929
930 RAISERROR (N'Inserting data into #IndexPartitionSanity',0,1) WITH NOWAIT;
931 INSERT #IndexPartitionSanity ( [database_id],
932 [object_id],
933 index_id,
934 partition_number,
935 row_count,
936 reserved_MB,
937 reserved_LOB_MB,
938 reserved_row_overflow_MB,
939 leaf_insert_count,
940 leaf_delete_count,
941 leaf_update_count,
942 range_scan_count,
943 singleton_lookup_count,
944 forwarded_fetch_count,
945 lob_fetch_in_pages,
946 lob_fetch_in_bytes,
947 row_overflow_fetch_in_pages,
948 row_overflow_fetch_in_bytes,
949 row_lock_count,
950 row_lock_wait_count,
951 row_lock_wait_in_ms,
952 page_lock_count,
953 page_lock_wait_count,
954 page_lock_wait_in_ms,
955 index_lock_promotion_attempt_count,
956 index_lock_promotion_count,
957 data_compression_desc )
958 EXEC sp_executesql @dsql;
959
960 RAISERROR (N'Updating index_sanity_id on #IndexPartitionSanity',0,1) WITH NOWAIT;
961 UPDATE #IndexPartitionSanity
962 SET index_sanity_id = i.index_sanity_id
963 FROM #IndexPartitionSanity ps
964 JOIN #IndexSanity i ON ps.[object_id] = i.[object_id]
965 AND ps.index_id = i.index_id
966 AND i.database_id = ps.database_id
967
968 RAISERROR (N'Inserting data into #IndexSanitySize',0,1) WITH NOWAIT;
969 INSERT #IndexSanitySize ( [index_sanity_id], [database_id], partition_count, total_rows, total_reserved_MB,
970 total_reserved_LOB_MB, total_reserved_row_overflow_MB, total_range_scan_count,
971 total_singleton_lookup_count, total_leaf_delete_count, total_leaf_update_count,
972 total_forwarded_fetch_count,total_row_lock_count,
973 total_row_lock_wait_count, total_row_lock_wait_in_ms, avg_row_lock_wait_in_ms,
974 total_page_lock_count, total_page_lock_wait_count, total_page_lock_wait_in_ms,
975 avg_page_lock_wait_in_ms, total_index_lock_promotion_attempt_count,
976 total_index_lock_promotion_count, data_compression_desc )
977 SELECT index_sanity_id, ipp.database_id, COUNT(*), SUM(row_count), SUM(reserved_MB), SUM(reserved_LOB_MB),
978 SUM(reserved_row_overflow_MB),
979 SUM(range_scan_count),
980 SUM(singleton_lookup_count),
981 SUM(leaf_delete_count),
982 SUM(leaf_update_count),
983 SUM(forwarded_fetch_count),
984 SUM(row_lock_count),
985 SUM(row_lock_wait_count),
986 SUM(row_lock_wait_in_ms),
987 CASE WHEN SUM(row_lock_wait_in_ms) > 0 THEN
988 SUM(row_lock_wait_in_ms)/(1.*SUM(row_lock_wait_count))
989 ELSE 0 END AS avg_row_lock_wait_in_ms,
990 SUM(page_lock_count),
991 SUM(page_lock_wait_count),
992 SUM(page_lock_wait_in_ms),
993 CASE WHEN SUM(page_lock_wait_in_ms) > 0 THEN
994 SUM(page_lock_wait_in_ms)/(1.*SUM(page_lock_wait_count))
995 ELSE 0 END AS avg_page_lock_wait_in_ms,
996 SUM(index_lock_promotion_attempt_count),
997 SUM(index_lock_promotion_count),
998 LEFT(MAX(data_compression_info.data_compression_rollup),8000)
999 FROM #IndexPartitionSanity ipp
1000 /* individual partitions can have distinct compression settings, just roll them into a list here*/
1001 OUTER APPLY (SELECT STUFF((
1002 SELECT N', ' + data_compression_desc
1003 FROM #IndexPartitionSanity ipp2
1004 WHERE ipp.[object_id]=ipp2.[object_id]
1005 AND ipp.[index_id]=ipp2.[index_id]
1006 AND ipp.database_id = @DatabaseID
1007 ORDER BY ipp2.partition_number
1008 FOR XML PATH(''),TYPE).value('.', 'varchar(max)'), 1, 1, ''))
1009 data_compression_info(data_compression_rollup)
1010 WHERE ipp.database_id = @DatabaseID
1011 GROUP BY index_sanity_id, ipp.database_id
1012 ORDER BY index_sanity_id
1013 OPTION ( RECOMPILE );
1014
1015 RAISERROR (N'Adding UQ index on #IndexSanity (database_id, object_id, index_id)',0,1) WITH NOWAIT;
1016 IF NOT EXISTS(SELECT 1 FROM tempdb.sys.indexes WHERE name='uq_database_id_object_id_index_id')
1017 CREATE UNIQUE INDEX uq_database_id_object_id_index_id ON #IndexSanity (database_id, object_id, index_id);
1018
1019 RAISERROR (N'Inserting data into #MissingIndexes',0,1) WITH NOWAIT;
1020 SET @dsql=N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
1021 SELECT id.object_id, ' + QUOTENAME(@DatabaseName,'''') + N', sc.[name], so.[name], id.statement , gs.avg_total_user_cost,
1022 gs.avg_user_impact, gs.user_seeks, gs.user_scans, gs.unique_compiles,id.equality_columns,
1023 id.inequality_columns,id.included_columns
1024 FROM sys.dm_db_missing_index_groups ig
1025 JOIN sys.dm_db_missing_index_details id ON ig.index_handle = id.index_handle
1026 JOIN sys.dm_db_missing_index_group_stats gs ON ig.index_group_handle = gs.group_handle
1027 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects so on
1028 id.object_id=so.object_id
1029 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.schemas sc on
1030 so.schema_id=sc.schema_id
1031 WHERE id.database_id = ' + CAST(@DatabaseID AS NVARCHAR(30)) + '
1032 ' + CASE WHEN @ObjectID IS NULL THEN N''
1033 ELSE N'and id.object_id=' + CAST(@ObjectID AS NVARCHAR(30))
1034 END +
1035 N';'
1036
1037 IF @dsql IS NULL
1038 RAISERROR('@dsql is null',16,1);
1039 INSERT #MissingIndexes ( [object_id], [database_name], [schema_name], [table_name], [statement], avg_total_user_cost,
1040 avg_user_impact, user_seeks, user_scans, unique_compiles, equality_columns,
1041 inequality_columns,included_columns)
1042 EXEC sp_executesql @dsql;
1043
1044 SET @dsql = N'
1045 SELECT ' + QUOTENAME(@DatabaseName,'''') + N' AS [database_name],
1046 fk_object.name AS foreign_key_name,
1047 parent_object.[object_id] AS parent_object_id,
1048 parent_object.name AS parent_object_name,
1049 referenced_object.[object_id] AS referenced_object_id,
1050 referenced_object.name AS referenced_object_name,
1051 fk.is_disabled,
1052 fk.is_not_trusted,
1053 fk.is_not_for_replication,
1054 parent.fk_columns,
1055 referenced.fk_columns,
1056 [update_referential_action_desc],
1057 [delete_referential_action_desc]
1058 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_keys fk
1059 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects fk_object ON fk.object_id=fk_object.object_id
1060 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects parent_object ON fk.parent_object_id=parent_object.object_id
1061 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.objects referenced_object ON fk.referenced_object_id=referenced_object.object_id
1062 CROSS APPLY ( SELECT STUFF( (SELECT N'', '' + c_parent.name AS fk_columns
1063 FROM ' + QUOTENAME(@DatabaseName) + N'.sys.foreign_key_columns fkc
1064 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c_parent ON fkc.parent_object_id=c_parent.[object_id]
1065 AND fkc.parent_column_id=c_parent.column_id
1066 WHERE fk.parent_object_id=fkc.parent_object_id
1067 AND fk.[object_id]=fkc.constraint_object_id
1068 ORDER BY fkc.constraint_column_id
1069 FOR XML PATH('''') ,
1070 TYPE).value(''.'', ''varchar(max)''), 1, 1, '''')/*This is how we remove the first comma*/ ) parent ( fk_columns )
1071 CROSS APPLY ( SELECT STUFF( (SELECT N'', '' + c_referenced.name AS fk_columns
1072 FROM ' + QUOTENAME(@DatabaseName) + N'.sys. foreign_key_columns fkc
1073 JOIN ' + QUOTENAME(@DatabaseName) + N'.sys.columns c_referenced ON fkc.referenced_object_id=c_referenced.[object_id]
1074 AND fkc.referenced_column_id=c_referenced.column_id
1075 WHERE fk.referenced_object_id=fkc.referenced_object_id
1076 and fk.[object_id]=fkc.constraint_object_id
1077 ORDER BY fkc.constraint_column_id /*order by col name, we don''t have anything better*/
1078 FOR XML PATH('''') ,
1079 TYPE).value(''.'', ''varchar(max)''), 1, 1, '''') ) referenced ( fk_columns )
1080 ' + CASE WHEN @ObjectID IS NOT NULL THEN
1081 'WHERE fk.parent_object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' OR fk.referenced_object_id=' + CAST(@ObjectID AS NVARCHAR(30)) + N' '
1082 ELSE N' ' END + '
1083 ORDER BY parent_object_name, foreign_key_name;
1084 ';
1085 IF @dsql IS NULL
1086 RAISERROR('@dsql is null',16,1);
1087
1088 RAISERROR (N'Inserting data into #ForeignKeys',0,1) WITH NOWAIT;
1089 INSERT #ForeignKeys ( [database_name], foreign_key_name, parent_object_id,parent_object_name, referenced_object_id, referenced_object_name,
1090 is_disabled, is_not_trusted, is_not_for_replication, parent_fk_columns, referenced_fk_columns,
1091 [update_referential_action_desc], [delete_referential_action_desc] )
1092 EXEC sp_executesql @dsql;
1093
1094 RAISERROR (N'Updating #IndexSanity.referenced_by_foreign_key',0,1) WITH NOWAIT;
1095 UPDATE #IndexSanity
1096 SET is_referenced_by_foreign_key=1
1097 FROM #IndexSanity s
1098 JOIN #ForeignKeys fk ON
1099 s.object_id=fk.referenced_object_id
1100 AND LEFT(s.key_column_names,LEN(fk.referenced_fk_columns)) = fk.referenced_fk_columns
1101
1102 RAISERROR (N'Add computed columns to #IndexSanity to simplify queries.',0,1) WITH NOWAIT;
1103 IF NOT EXISTS(SELECT 1 FROM tempdb.sys.columns WHERE name='db_schema_object_name')
1104 ALTER TABLE #IndexSanity ADD
1105 [db_schema_object_name] AS [schema_name] + '.' + [object_name] ,
1106 [db_schema_object_indexid] AS [schema_name] + '.' + [object_name]
1107 + CASE WHEN [index_name] IS NOT NULL THEN '.' + index_name
1108 ELSE ''
1109 END + ' (' + CAST(index_id AS NVARCHAR(20)) + ')' ,
1110 first_key_column_name AS CASE WHEN count_key_columns > 1
1111 THEN LEFT(key_column_names, CHARINDEX(',', key_column_names, 0) - 1)
1112 ELSE key_column_names
1113 END ,
1114 index_definition AS
1115 CASE WHEN partition_key_column_name IS NOT NULL
1116 THEN N'[PARTITIONED BY:' + partition_key_column_name + N']'
1117 ELSE ''
1118 END +
1119 CASE index_id
1120 WHEN 0 THEN N'[HEAP] '
1121 WHEN 1 THEN N'[CX] '
1122 ELSE N'' END + CASE WHEN is_indexed_view = 1 THEN '[VIEW] '
1123 ELSE N'' END + CASE WHEN is_primary_key = 1 THEN N'[PK] '
1124 ELSE N'' END + CASE WHEN is_XML = 1 THEN N'[XML] '
1125 ELSE N'' END + CASE WHEN is_spatial = 1 THEN N'[SPATIAL] '
1126 ELSE N'' END + CASE WHEN is_NC_columnstore = 1 THEN N'[COLUMNSTORE] '
1127 ELSE N'' END + CASE WHEN is_disabled = 1 THEN N'[DISABLED] '
1128 ELSE N'' END + CASE WHEN is_hypothetical = 1 THEN N'[HYPOTHETICAL] '
1129 ELSE N'' END + CASE WHEN is_unique = 1 AND is_primary_key = 0 THEN N'[UNIQUE] '
1130 ELSE N'' END + CASE WHEN count_key_columns > 0 THEN
1131 N'[' + CAST(count_key_columns AS VARCHAR(10)) + N' KEY'
1132 + CASE WHEN count_key_columns > 1 THEN N'S' ELSE N'' END
1133 + N'] ' + LTRIM(key_column_names_with_sort_order)
1134 ELSE N'' END + CASE WHEN count_included_columns > 0 THEN
1135 N' [' + CAST(count_included_columns AS VARCHAR(10)) + N' INCLUDE' +
1136 + CASE WHEN count_included_columns > 1 THEN N'S' ELSE N'' END
1137 + N'] ' + include_column_names
1138 ELSE N'' END + CASE WHEN filter_definition <> N'' THEN N' [FILTER] ' + filter_definition
1139 ELSE N'' END ,
1140 [total_reads] AS user_seeks + user_scans + user_lookups,
1141 [reads_per_write] AS CAST(CASE WHEN user_updates > 0
1142 THEN ( user_seeks + user_scans + user_lookups ) / (1.0 * user_updates)
1143 ELSE 0 END AS MONEY) ,
1144 [index_usage_summary] AS N'Reads: ' +
1145 REPLACE(CONVERT(NVARCHAR(30),CAST((user_seeks + user_scans + user_lookups) AS MONEY), 1), '.00', '')
1146 + CASE WHEN user_seeks + user_scans + user_lookups > 0 THEN
1147 N' ('
1148 + RTRIM(
1149 CASE WHEN user_seeks > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_seeks) AS MONEY), 1), '.00', '') + N' seek ' ELSE N'' END
1150 + CASE WHEN user_scans > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_scans) AS MONEY), 1), '.00', '') + N' scan ' ELSE N'' END
1151 + CASE WHEN user_lookups > 0 THEN REPLACE(CONVERT(NVARCHAR(30),CAST((user_lookups) AS MONEY), 1), '.00', '') + N' lookup' ELSE N'' END
1152 )
1153 + N') '
1154 ELSE N' ' END
1155 + N'Writes:' +
1156 REPLACE(CONVERT(NVARCHAR(30),CAST(user_updates AS MONEY), 1), '.00', ''),
1157 [more_info] AS N'EXEC dbo.sp_BlitzIndex @DatabaseName=' + QUOTENAME([database_name],'''') +
1158 N', @SchemaName=' + QUOTENAME([schema_name],'''') + N', @TableName=' + QUOTENAME([object_name],'''') + N';'
1159
1160 RAISERROR (N'Update index_secret on #IndexSanity for NC indexes.',0,1) WITH NOWAIT;
1161 UPDATE nc
1162 SET secret_columns=
1163 N'[' +
1164 CASE tb.count_key_columns WHEN 0 THEN '1' ELSE CAST(tb.count_key_columns AS VARCHAR(10)) END +
1165 CASE nc.is_unique WHEN 1 THEN N' INCLUDE' ELSE N' KEY' END +
1166 CASE WHEN tb.count_key_columns > 1 THEN N'S] ' ELSE N'] ' END +
1167 CASE tb.index_id WHEN 0 THEN '[RID]' ELSE LTRIM(tb.key_column_names) +
1168 /* Uniquifiers only needed on non-unique clustereds-- not heaps */
1169 CASE tb.is_unique WHEN 0 THEN ' [UNIQUIFIER]' ELSE N'' END
1170 END
1171 , count_secret_columns=
1172 CASE tb.index_id WHEN 0 THEN 1 ELSE
1173 tb.count_key_columns +
1174 CASE tb.is_unique WHEN 0 THEN 1 ELSE 0 END
1175 END
1176 FROM #IndexSanity AS nc
1177 JOIN #IndexSanity AS tb ON nc.object_id=tb.object_id
1178 AND tb.index_id IN (0,1)
1179 WHERE nc.index_id > 1;
1180
1181 RAISERROR (N'Update index_secret on #IndexSanity for heaps and non-unique clustered.',0,1) WITH NOWAIT;
1182 UPDATE tb
1183 SET secret_columns= CASE tb.index_id WHEN 0 THEN '[RID]' ELSE '[UNIQUIFIER]' END
1184 , count_secret_columns = 1
1185 FROM #IndexSanity AS tb
1186 WHERE tb.index_id = 0 /*Heaps-- these have the RID */
1187 OR (tb.index_id=1 AND tb.is_unique=0); /* Non-unique CX: has uniquifer (when needed) */
1188
1189 RAISERROR (N'Add computed columns to #IndexSanitySize to simplify queries.',0,1) WITH NOWAIT;
1190
1191 IF NOT EXISTS(SELECT 1 FROM tempdb.sys.columns AS sc
1192 JOIN tempdb..sysobjects AS so ON so.id = sc.object_id
1193 WHERE sc.name='index_size_summary' AND so.name LIKE '#IndexSanitySize_%')
1194 ALTER TABLE #IndexSanitySize ADD
1195 index_size_summary AS ISNULL(
1196 CASE WHEN partition_count > 1
1197 THEN N'[' + CAST(partition_count AS NVARCHAR(10)) + N' PARTITIONS] '
1198 ELSE N''
1199 END + REPLACE(CONVERT(NVARCHAR(30),CAST([total_rows] AS MONEY), 1), N'.00', N'') + N' rows; '
1200 + CASE WHEN total_reserved_MB > 1024 THEN
1201 CAST(CAST(total_reserved_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB'
1202 ELSE
1203 CAST(CAST(total_reserved_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB'
1204 END
1205 + CASE WHEN total_reserved_LOB_MB > 1024 THEN
1206 N'; ' + CAST(CAST(total_reserved_LOB_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB LOB'
1207 WHEN total_reserved_LOB_MB > 0 THEN
1208 N'; ' + CAST(CAST(total_reserved_LOB_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB LOB'
1209 ELSE ''
1210 END
1211 + CASE WHEN total_reserved_row_overflow_MB > 1024 THEN
1212 N'; ' + CAST(CAST(total_reserved_row_overflow_MB/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB Row Overflow'
1213 WHEN total_reserved_row_overflow_MB > 0 THEN
1214 N'; ' + CAST(CAST(total_reserved_row_overflow_MB AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'MB Row Overflow'
1215 ELSE ''
1216 END ,
1217 N'Error- NULL in computed column'),
1218 index_op_stats AS ISNULL(
1219 (
1220 REPLACE(CONVERT(NVARCHAR(30),CAST(total_singleton_lookup_count AS MONEY), 1),N'.00',N'') + N' singleton lookups; '
1221 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_range_scan_count AS MONEY), 1),N'.00',N'') + N' scans/seeks; '
1222 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_leaf_delete_count AS MONEY), 1),N'.00',N'') + N' deletes; '
1223 + REPLACE(CONVERT(NVARCHAR(30),CAST(total_leaf_update_count AS MONEY), 1),N'.00',N'') + N' updates; '
1224 + CASE WHEN ISNULL(total_forwarded_fetch_count,0) >0 THEN
1225 REPLACE(CONVERT(NVARCHAR(30),CAST(total_forwarded_fetch_count AS MONEY), 1),N'.00',N'') + N' forward records fetched; '
1226 ELSE N'' END
1227
1228 /* rows will only be in this dmv when data is in memory for the table */
1229 ), N'Table metadata not in memory'),
1230 index_lock_wait_summary AS ISNULL(
1231 CASE WHEN total_row_lock_wait_count = 0 AND total_page_lock_wait_count = 0 AND
1232 total_index_lock_promotion_attempt_count = 0 THEN N'0 lock waits.'
1233 ELSE
1234 CASE WHEN total_row_lock_wait_count > 0 THEN
1235 N'Row lock waits: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_row_lock_wait_count AS MONEY), 1), N'.00', N'')
1236 + N'; total duration: ' +
1237 CASE WHEN total_row_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
1238 REPLACE(CONVERT(NVARCHAR(30),CAST((total_row_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
1239 ELSE
1240 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_row_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
1241 END
1242 + N'avg duration: ' +
1243 CASE WHEN avg_row_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
1244 REPLACE(CONVERT(NVARCHAR(30),CAST((avg_row_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
1245 ELSE
1246 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(avg_row_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
1247 END
1248 ELSE N''
1249 END +
1250 CASE WHEN total_page_lock_wait_count > 0 THEN
1251 N'Page lock waits: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_page_lock_wait_count AS MONEY), 1), N'.00', N'')
1252 + N'; total duration: ' +
1253 CASE WHEN total_page_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
1254 REPLACE(CONVERT(NVARCHAR(30),CAST((total_page_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
1255 ELSE
1256 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_page_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
1257 END
1258 + N'avg duration: ' +
1259 CASE WHEN avg_page_lock_wait_in_ms >= 60000 THEN /*More than 1 min*/
1260 REPLACE(CONVERT(NVARCHAR(30),CAST((avg_page_lock_wait_in_ms/60000) AS MONEY), 1), N'.00', N'') + N' minutes; '
1261 ELSE
1262 REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(avg_page_lock_wait_in_ms/1000,0) AS MONEY), 1), N'.00', N'') + N' seconds; '
1263 END
1264 ELSE N''
1265 END +
1266 CASE WHEN total_index_lock_promotion_attempt_count > 0 THEN
1267 N'Lock escalation attempts: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(total_index_lock_promotion_attempt_count AS MONEY), 1), N'.00', N'')
1268 + N'; Actual Escalations: ' + REPLACE(CONVERT(NVARCHAR(30),CAST(ISNULL(total_index_lock_promotion_count,0) AS MONEY), 1), N'.00', N'') + N'.'
1269 ELSE N''
1270 END
1271 END
1272 ,'Error- NULL in computed column')
1273
1274 RAISERROR (N'Add computed columns to #missing_index to simplify queries.',0,1) WITH NOWAIT;
1275 IF NOT EXISTS(SELECT 1 FROM tempdb.sys.columns AS sc
1276 JOIN tempdb..sysobjects AS so ON so.id = sc.object_id
1277 WHERE sc.name='index_estimated_impact' AND so.name LIKE '#MissingIndexes_%')
1278
1279 ALTER TABLE #MissingIndexes ADD
1280 [index_estimated_impact] AS
1281 REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
1282 (user_seeks + user_scans)
1283 AS BIGINT) AS MONEY), 1), '.00', '') + N' use'
1284 + CASE WHEN (user_seeks + user_scans) > 1 THEN N's' ELSE N'' END
1285 +N'; Impact: ' + CAST(avg_user_impact AS NVARCHAR(30))
1286 + N'%; Avg query cost: '
1287 + CAST(avg_total_user_cost AS NVARCHAR(30)),
1288 [missing_index_details] AS
1289 CASE WHEN equality_columns IS NOT NULL THEN N'EQUALITY: ' + equality_columns + N' '
1290 ELSE N''
1291 END + CASE WHEN inequality_columns IS NOT NULL THEN N'INEQUALITY: ' + inequality_columns + N' '
1292 ELSE N''
1293 END + CASE WHEN included_columns IS NOT NULL THEN N'INCLUDES: ' + included_columns + N' '
1294 ELSE N''
1295 END,
1296 [create_tsql] AS N'CREATE INDEX [ix_' + table_name + N'_'
1297 + REPLACE(REPLACE(REPLACE(REPLACE(
1298 ISNULL(equality_columns,N'')+
1299 CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N'_' ELSE N'' END
1300 + ISNULL(inequality_columns,''),',','')
1301 ,'[',''),']',''),' ','_')
1302 + CASE WHEN included_columns IS NOT NULL THEN N'_includes' ELSE N'' END + N'] ON '
1303 + [statement] + N' (' + ISNULL(equality_columns,N'')
1304 + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN N', ' ELSE N'' END
1305 + CASE WHEN inequality_columns IS NOT NULL THEN inequality_columns ELSE N'' END +
1306 ') ' + CASE WHEN included_columns IS NOT NULL THEN N' INCLUDE (' + included_columns + N')' ELSE N'' END
1307 + N' WITH ('
1308 + N'FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?'
1309 + N')'
1310 + N';'
1311 ,
1312 [more_info] AS N'EXEC dbo.sp_BlitzIndex @DatabaseName=' + QUOTENAME([database_name],'''') +
1313 N', @SchemaName=' + QUOTENAME([schema_name],'''') + N', @TableName=' + QUOTENAME([table_name],'''') + N';'
1314 ;
1315
1316 RAISERROR (N'Populate #IndexCreateTsql.',0,1) WITH NOWAIT;
1317 INSERT #IndexCreateTsql (index_sanity_id, create_tsql)
1318 SELECT
1319 index_sanity_id,
1320 ISNULL (
1321 /* Script drops for disabled non-clustered indexes*/
1322 CASE WHEN is_disabled = 1 AND index_id <> 1
1323 THEN N'--DROP INDEX ' + QUOTENAME([index_name]) + N' ON '
1324 + QUOTENAME([schema_name]) + N'.' + QUOTENAME([object_name])
1325 ELSE
1326 CASE index_id WHEN 0 THEN N'--I''m a Heap!'
1327 ELSE
1328 CASE WHEN is_XML = 1 OR is_spatial=1 THEN N'' /* Not even trying for these just yet...*/
1329 ELSE
1330 CASE WHEN is_primary_key=1 THEN
1331 N'ALTER TABLE ' + QUOTENAME([schema_name]) +
1332 N'.' + QUOTENAME([object_name]) +
1333 N' ADD CONSTRAINT [' +
1334 index_name +
1335 N'] PRIMARY KEY ' +
1336 CASE WHEN index_id=1 THEN N'CLUSTERED (' ELSE N'(' END +
1337 key_column_names_with_sort_order_no_types + N' )'
1338 WHEN is_CX_columnstore= 1 THEN
1339 N'CREATE CLUSTERED COLUMNSTORE INDEX ' + QUOTENAME(index_name) + N' on ' + QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name])
1340 ELSE /*Else not a PK or cx columnstore */
1341 N'CREATE ' +
1342 CASE WHEN is_unique=1 THEN N'UNIQUE ' ELSE N'' END +
1343 CASE WHEN index_id=1 THEN N'CLUSTERED ' ELSE N'' END +
1344 CASE WHEN is_NC_columnstore=1 THEN N'NONCLUSTERED COLUMNSTORE '
1345 ELSE N'' END +
1346 N'INDEX ['
1347 + index_name + N'] ON ' +
1348 QUOTENAME([schema_name]) + '.' + QUOTENAME([object_name]) +
1349 CASE WHEN is_NC_columnstore=1 THEN
1350 N' (' + ISNULL(include_column_names_no_types,'') + N' )'
1351 ELSE /*Else not colunnstore */
1352 N' (' + ISNULL(key_column_names_with_sort_order_no_types,'') + N' )'
1353 + CASE WHEN include_column_names_no_types IS NOT NULL THEN
1354 N' INCLUDE (' + include_column_names_no_types + N')'
1355 ELSE N''
1356 END
1357 END /*End non-colunnstore case */
1358 + CASE WHEN filter_definition <> N'' THEN N' WHERE ' + filter_definition ELSE N'' END
1359 END /*End Non-PK index CASE */
1360 + CASE WHEN is_NC_columnstore=0 AND is_CX_columnstore=0 THEN
1361 N' WITH ('
1362 + N'FILLFACTOR=' + CASE fill_factor WHEN 0 THEN N'100' ELSE CAST(fill_factor AS NVARCHAR(5)) END + ', '
1363 + N'ONLINE=?, SORT_IN_TEMPDB=?'
1364 + N')'
1365 ELSE N'' END
1366 + N';'
1367 END /*End non-spatial and non-xml CASE */
1368 END
1369 END, '[Unknown Error]')
1370 AS create_tsql
1371 FROM #IndexSanity
1372 WHERE database_id = @DatabaseID;
1373
1374 END
1375END TRY
1376BEGIN CATCH
1377 RAISERROR (N'Failure populating temp tables.', 0,1) WITH NOWAIT;
1378
1379 IF @dsql IS NOT NULL
1380 BEGIN
1381 SET @msg= 'Last @dsql: ' + @dsql;
1382 RAISERROR(@msg, 0, 1) WITH NOWAIT;
1383 END
1384
1385 SELECT @msg = @DatabaseName + N' database failed to process. ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
1386 RAISERROR (@msg,@ErrorSeverity, @ErrorState )WITH NOWAIT;
1387
1388
1389 WHILE @@trancount > 0
1390 ROLLBACK;
1391
1392 RETURN;
1393END CATCH;
1394 FETCH NEXT FROM c1 INTO @DatabaseName
1395END
1396DEALLOCATE c1;
1397
1398----------------------------------------
1399--STEP 2: DIAGNOSE THE PATIENT
1400--EVERY QUERY AFTER THIS GOES AGAINST TEMP TABLES ONLY.
1401----------------------------------------
1402 BEGIN TRY
1403----------------------------------------
1404--If @TableName is specified, just return information for that table.
1405--The @Mode parameter doesn't matter if you're looking at a specific table.
1406----------------------------------------
1407IF @TableName IS NOT NULL
1408BEGIN
1409 RAISERROR(N'@TableName specified, giving detail only on that table.', 0,1) WITH NOWAIT;
1410
1411 --We do a left join here in case this is a disabled NC.
1412 --In that case, it won't have any size info/pages allocated.
1413
1414 ;WITH [maps]
1415 AS ( SELECT
1416 index_sanity_id,
1417 partition_number,
1418 data_compression_desc,
1419 partition_number - ROW_NUMBER() OVER (PARTITION BY ips.index_sanity_id, data_compression_desc ORDER BY partition_number ) AS [rN]
1420 FROM #IndexPartitionSanity ips
1421 WHERE ips.object_id = @ObjectID
1422 ),
1423 [grps]
1424 AS ( SELECT MIN([maps].[partition_number]) AS [MinKey] ,
1425 MAX([maps].[partition_number]) AS [MaxKey] ,
1426 index_sanity_id,
1427 maps.data_compression_desc
1428 FROM [maps]
1429 GROUP BY [maps].[rN], index_sanity_id, maps.data_compression_desc)
1430 SELECT DISTINCT grps.index_sanity_id , SUBSTRING(( STUFF((SELECT ', ' + ' Partition'
1431 + CASE WHEN [grps2].[MinKey] < [grps2].[MaxKey]
1432 THEN +'s '
1433 + CAST([grps2].[MinKey] AS VARCHAR)
1434 + ' - '
1435 + CAST([grps2].[MaxKey] AS VARCHAR)
1436 + ' use ' + grps2.data_compression_desc
1437 ELSE ' '
1438 + CAST([grps2].[MinKey] AS VARCHAR)
1439 + ' uses ' + grps2.data_compression_desc
1440 END AS [Partitions]
1441 FROM [grps] AS grps2
1442 WHERE grps2.index_sanity_id = grps.index_sanity_id
1443 ORDER BY grps2.MinKey, grps2.MaxKey
1444 FOR XML PATH('') ,
1445 TYPE
1446 ).[value]('.', 'VARCHAR(MAX)'), 1, 1, '') ), 0, 8000) AS [partition_compression_detail]
1447 INTO #partition_compression_info
1448 FROM grps;
1449
1450
1451 WITH table_mode_cte AS (
1452 SELECT
1453 s.db_schema_object_indexid,
1454 s.key_column_names,
1455 s.index_definition,
1456 ISNULL(s.secret_columns,N'') AS secret_columns,
1457 s.fill_factor,
1458 s.index_usage_summary,
1459 sz.index_op_stats,
1460 ISNULL(sz.index_size_summary,'') /*disabled NCs will be null*/ AS index_size_summary,
1461 partition_compression_detail ,
1462 ISNULL(sz.index_lock_wait_summary,'') AS index_lock_wait_summary,
1463 s.is_referenced_by_foreign_key,
1464 (SELECT COUNT(*)
1465 FROM #ForeignKeys fk WHERE fk.parent_object_id=s.object_id
1466 AND PATINDEX (fk.parent_fk_columns, s.key_column_names)=1) AS FKs_covered_by_index,
1467 s.last_user_seek,
1468 s.last_user_scan,
1469 s.last_user_lookup,
1470 s.last_user_update,
1471 s.create_date,
1472 s.modify_date,
1473 ct.create_tsql,
1474 1 AS display_order
1475 FROM #IndexSanity s
1476 LEFT JOIN #IndexSanitySize sz ON
1477 s.index_sanity_id=sz.index_sanity_id
1478 LEFT JOIN #IndexCreateTsql ct ON
1479 s.index_sanity_id=ct.index_sanity_id
1480 LEFT JOIN #partition_compression_info pci ON
1481 pci.index_sanity_id = s.index_sanity_id
1482 WHERE s.[object_id]=@ObjectID
1483 UNION ALL
1484 SELECT N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) +
1485 N' (sp_BlitzIndex(R) v4.0 - June 26, 2016)' ,
1486 N'SQL Server First Responder Kit' ,
1487 N'http://FirstResponderKit.org' ,
1488 N'From Your Community Volunteers',
1489 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1490 0 AS display_order
1491 )
1492 SELECT
1493 db_schema_object_indexid AS [Details: db_schema.table.index(indexid)],
1494 index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
1495 secret_columns AS [Secret Columns],
1496 fill_factor AS [Fillfactor],
1497 index_usage_summary AS [Usage Stats],
1498 index_op_stats AS [Op Stats],
1499 index_size_summary AS [Size],
1500 partition_compression_detail AS [Compression Type],
1501 index_lock_wait_summary AS [Lock Waits],
1502 is_referenced_by_foreign_key AS [Referenced by FK?],
1503 FKs_covered_by_index AS [FK Covered by Index?],
1504 last_user_seek AS [Last User Seek],
1505 last_user_scan AS [Last User Scan],
1506 last_user_lookup AS [Last User Lookup],
1507 last_user_update AS [Last User Write],
1508 create_date AS [Created],
1509 modify_date AS [Last Modified],
1510 create_tsql AS [Create TSQL]
1511 FROM table_mode_cte
1512 ORDER BY display_order ASC, key_column_names ASC
1513 OPTION ( RECOMPILE );
1514
1515 IF (SELECT TOP 1 [object_id] FROM #MissingIndexes mi) IS NOT NULL
1516 BEGIN
1517 SELECT N'Missing index.' AS Finding ,
1518 N'http://BrentOzar.com/go/Indexaphobia' AS URL ,
1519 mi.[statement] +
1520 ' Est. Benefit: '
1521 + CASE WHEN magic_benefit_number >= 922337203685477 THEN '>= 922,337,203,685,477'
1522 ELSE REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
1523 (magic_benefit_number/@DaysUptime)
1524 AS BIGINT) AS MONEY), 1), '.00', '')
1525 END AS [Estimated Benefit],
1526 missing_index_details AS [Missing Index Request] ,
1527 index_estimated_impact AS [Estimated Impact],
1528 create_tsql AS [Create TSQL]
1529 FROM #MissingIndexes mi
1530 WHERE [object_id] = @ObjectID
1531 /* Minimum benefit threshold = 100k/day of uptime */
1532 AND (magic_benefit_number/@DaysUptime) >= 100000
1533 ORDER BY magic_benefit_number DESC
1534 OPTION ( RECOMPILE );
1535 END
1536 ELSE
1537 SELECT 'No missing indexes.' AS finding;
1538
1539 SELECT
1540 column_name AS [Column Name],
1541 (SELECT COUNT(*)
1542 FROM #IndexColumns c2
1543 WHERE c2.column_name=c.column_name
1544 AND c2.key_ordinal IS NOT NULL)
1545 + CASE WHEN c.index_id = 1 AND c.key_ordinal IS NOT NULL THEN
1546 -1+ (SELECT COUNT(DISTINCT index_id)
1547 FROM #IndexColumns c3
1548 WHERE c3.index_id NOT IN (0,1))
1549 ELSE 0 END
1550 AS [Found In],
1551 system_type_name +
1552 CASE max_length WHEN -1 THEN N' (max)' ELSE
1553 CASE
1554 WHEN system_type_name IN (N'char',N'nchar',N'binary',N'varbinary') THEN N' (' + CAST(max_length AS NVARCHAR(20)) + N')'
1555 WHEN system_type_name IN (N'varchar',N'nvarchar') THEN N' (' + CAST(max_length/2 AS NVARCHAR(20)) + N')'
1556 ELSE ''
1557 END
1558 END
1559 AS [Type],
1560 CASE is_computed WHEN 1 THEN 'yes' ELSE '' END AS [Computed?],
1561 max_length AS [Length (max bytes)],
1562 [precision] AS [Prec],
1563 [scale] AS [Scale],
1564 CASE is_nullable WHEN 1 THEN 'yes' ELSE '' END AS [Nullable?],
1565 CASE is_identity WHEN 1 THEN 'yes' ELSE '' END AS [Identity?],
1566 CASE is_replicated WHEN 1 THEN 'yes' ELSE '' END AS [Replicated?],
1567 CASE is_sparse WHEN 1 THEN 'yes' ELSE '' END AS [Sparse?],
1568 CASE is_filestream WHEN 1 THEN 'yes' ELSE '' END AS [Filestream?],
1569 collation_name AS [Collation]
1570 FROM #IndexColumns AS c
1571 WHERE index_id IN (0,1);
1572
1573 IF (SELECT TOP 1 parent_object_id FROM #ForeignKeys) IS NOT NULL
1574 BEGIN
1575 SELECT [database_name] + N':' + parent_object_name + N': ' + foreign_key_name AS [Foreign Key],
1576 parent_fk_columns AS [Foreign Key Columns],
1577 referenced_object_name AS [Referenced Table],
1578 referenced_fk_columns AS [Referenced Table Columns],
1579 is_disabled AS [Is Disabled?],
1580 is_not_trusted AS [Not Trusted?],
1581 is_not_for_replication [Not for Replication?],
1582 [update_referential_action_desc] AS [Cascading Updates?],
1583 [delete_referential_action_desc] AS [Cascading Deletes?]
1584 FROM #ForeignKeys
1585 ORDER BY [Foreign Key]
1586 OPTION ( RECOMPILE );
1587 END
1588 ELSE
1589 SELECT 'No foreign keys.' AS finding;
1590END
1591
1592--If @TableName is NOT specified...
1593--Act based on the @Mode and @Filter. (@Filter applies only when @Mode=0 "diagnose")
1594ELSE
1595BEGIN;
1596 IF @Mode IN (0, 4) /* DIAGNOSE*/
1597 BEGIN;
1598 RAISERROR(N'@Mode=0 or 4, we are diagnosing.', 0,1) WITH NOWAIT;
1599
1600 ----------------------------------------
1601 --Multiple Index Personalities: Check_id 0-10
1602 ----------------------------------------
1603 BEGIN;
1604
1605 --SELECT [object_id], key_column_names, database_id
1606 -- FROM #IndexSanity
1607 -- WHERE index_type IN (1,2) /* Clustered, NC only*/
1608 -- AND is_hypothetical = 0
1609 -- AND is_disabled = 0
1610 -- GROUP BY [object_id], key_column_names, database_id
1611 -- HAVING COUNT(*) > 1
1612
1613
1614 RAISERROR('check_id 1: Duplicate keys', 0,1) WITH NOWAIT;
1615 WITH duplicate_indexes
1616 AS ( SELECT [object_id], key_column_names, database_id
1617 FROM #IndexSanity
1618 WHERE index_type IN (1,2) /* Clustered, NC only*/
1619 AND is_hypothetical = 0
1620 AND is_disabled = 0
1621 GROUP BY [object_id], key_column_names, database_id
1622 HAVING COUNT(*) > 1)
1623 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1624 secret_columns, index_usage_summary, index_size_summary )
1625 SELECT 1 AS check_id,
1626 ip.index_sanity_id,
1627 50 AS Priority,
1628 'Multiple Index Personalities' AS findings_group,
1629 'Duplicate keys' AS finding,
1630 [database_name] AS [Database Name],
1631 N'http://BrentOzar.com/go/duplicateindex' AS URL,
1632 N'Index Name: ' + ip.index_name AS details,
1633 ip.index_definition,
1634 ip.secret_columns,
1635 ip.index_usage_summary,
1636 ips.index_size_summary
1637 FROM duplicate_indexes di
1638 JOIN #IndexSanity ip ON di.[object_id] = ip.[object_id]
1639 AND ip.database_id = di.database_id
1640 AND di.key_column_names = ip.key_column_names
1641 JOIN #IndexSanitySize ips ON ip.index_sanity_id = ips.index_sanity_id AND ip.database_id = ips.database_id
1642 /* WHERE clause limits to only @ThresholdMB or larger duplicate indexes when getting all databases or using PainRelief mode */
1643 WHERE ips.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE ips.total_reserved_MB END
1644 ORDER BY ip.object_id, ip.key_column_names_with_sort_order
1645 OPTION ( RECOMPILE );
1646
1647 RAISERROR('check_id 2: Keys w/ identical leading columns.', 0,1) WITH NOWAIT;
1648 WITH borderline_duplicate_indexes
1649 AS ( SELECT DISTINCT [object_id], first_key_column_name, key_column_names,
1650 COUNT([object_id]) OVER ( PARTITION BY [object_id], first_key_column_name ) AS number_dupes
1651 FROM #IndexSanity
1652 WHERE index_type IN (1,2) /* Clustered, NC only*/
1653 AND is_hypothetical=0
1654 AND is_disabled=0)
1655 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1656 secret_columns, index_usage_summary, index_size_summary )
1657 SELECT 2 AS check_id,
1658 ip.index_sanity_id,
1659 60 AS Priority,
1660 'Multiple Index Personalities' AS findings_group,
1661 'Borderline duplicate keys' AS finding,
1662 [database_name] AS [Database Name],
1663 N'http://BrentOzar.com/go/duplicateindex' AS URL,
1664 ip.db_schema_object_indexid AS details,
1665 ip.index_definition,
1666 ip.secret_columns,
1667 ip.index_usage_summary,
1668 ips.index_size_summary
1669 FROM #IndexSanity AS ip
1670 JOIN #IndexSanitySize ips ON ip.index_sanity_id = ips.index_sanity_id
1671 WHERE EXISTS (
1672 SELECT di.[object_id]
1673 FROM borderline_duplicate_indexes AS di
1674 WHERE di.[object_id] = ip.[object_id] AND
1675 di.first_key_column_name = ip.first_key_column_name AND
1676 di.key_column_names <> ip.key_column_names AND
1677 di.number_dupes > 1
1678 )
1679 /* WHERE clause skips near-duplicate indexes when getting all databases or using PainRelief mode */
1680 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1681
1682 ORDER BY ip.[schema_name], ip.[object_name], ip.key_column_names, ip.include_column_names
1683 OPTION ( RECOMPILE );
1684
1685 END
1686 ----------------------------------------
1687 --Aggressive Indexes: Check_id 10-19
1688 ----------------------------------------
1689 BEGIN;
1690
1691 RAISERROR(N'check_id 11: Total lock wait time > 5 minutes (row + page)', 0,1) WITH NOWAIT;
1692 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1693 secret_columns, index_usage_summary, index_size_summary )
1694 SELECT 11 AS check_id,
1695 i.index_sanity_id,
1696 10 AS Priority,
1697 N'Aggressive Indexes' AS findings_group,
1698 N'Total lock wait time > 5 minutes (row + page)' AS finding,
1699 [database_name] AS [Database Name],
1700 N'http://BrentOzar.com/go/AggressiveIndexes' AS URL,
1701 i.db_schema_object_indexid + N': ' +
1702 sz.index_lock_wait_summary AS details,
1703 i.index_definition,
1704 i.secret_columns,
1705 i.index_usage_summary,
1706 sz.index_size_summary
1707 FROM #IndexSanity AS i
1708 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
1709 WHERE (total_row_lock_wait_in_ms + total_page_lock_wait_in_ms) > 300000
1710 OPTION ( RECOMPILE );
1711 END
1712
1713 ----------------------------------------
1714 --Index Hoarder: Check_id 20-29
1715 ----------------------------------------
1716 BEGIN
1717 RAISERROR(N'check_id 20: >=7 NC indexes on any given table. Yes, 7 is an arbitrary number.', 0,1) WITH NOWAIT;
1718 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1719 secret_columns, index_usage_summary, index_size_summary )
1720 SELECT 20 AS check_id,
1721 MAX(i.index_sanity_id) AS index_sanity_id,
1722 100 AS Priority,
1723 'Index Hoarder' AS findings_group,
1724 'Many NC indexes on a single table' AS finding,
1725 [database_name] AS [Database Name],
1726 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1727 CAST (COUNT(*) AS NVARCHAR(30)) + ' NC indexes on ' + i.db_schema_object_name AS details,
1728 i.db_schema_object_name + ' (' + CAST (COUNT(*) AS NVARCHAR(30)) + ' indexes)' AS index_definition,
1729 '' AS secret_columns,
1730 REPLACE(CONVERT(NVARCHAR(30),CAST(SUM(total_reads) AS MONEY), 1), N'.00', N'') + N' reads (ALL); '
1731 + REPLACE(CONVERT(NVARCHAR(30),CAST(SUM(user_updates) AS MONEY), 1), N'.00', N'') + N' writes (ALL); ',
1732 REPLACE(CONVERT(NVARCHAR(30),CAST(MAX(total_rows) AS MONEY), 1), N'.00', N'') + N' rows (MAX)'
1733 + CASE WHEN SUM(total_reserved_MB) > 1024 THEN
1734 N'; ' + CAST(CAST(SUM(total_reserved_MB)/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'GB (ALL)'
1735 WHEN SUM(total_reserved_MB) > 0 THEN
1736 N'; ' + CAST(CAST(SUM(total_reserved_MB) AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'MB (ALL)'
1737 ELSE ''
1738 END AS index_size_summary
1739 FROM #IndexSanity i
1740 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
1741 WHERE index_id NOT IN ( 0, 1 )
1742 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1743 GROUP BY db_schema_object_name, [i].[database_name]
1744 HAVING COUNT(*) >= 7
1745 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
1746
1747 IF @Filter = 1 /*@Filter=1 is "ignore unusued" */
1748 BEGIN
1749 RAISERROR(N'Skipping checks on unused indexes (21 and 22) because @Filter=1', 0,1) WITH NOWAIT;
1750 END
1751 ELSE /*Otherwise, go ahead and do the checks*/
1752 BEGIN
1753 RAISERROR(N'check_id 21: >=5 percent of indexes are unused. Yes, 5 is an arbitrary number.', 0,1) WITH NOWAIT;
1754 DECLARE @percent_NC_indexes_unused NUMERIC(29,1);
1755 DECLARE @NC_indexes_unused_reserved_MB NUMERIC(29,1);
1756
1757 SELECT @percent_NC_indexes_unused =( 100.00 * SUM(CASE WHEN total_reads = 0 THEN 1
1758 ELSE 0
1759 END) ) / COUNT(*) ,
1760 @NC_indexes_unused_reserved_MB = SUM(CASE WHEN total_reads = 0 THEN sz.total_reserved_MB
1761 ELSE 0
1762 END)
1763 FROM #IndexSanity i
1764 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
1765 WHERE index_id NOT IN ( 0, 1 )
1766 AND i.is_unique = 0
1767 /*Skipping tables created in the last week, or modified in past 2 days*/
1768 AND i.create_date >= DATEADD(dd,-7,GETDATE())
1769 AND i.modify_date > DATEADD(dd,-2,GETDATE())
1770 OPTION ( RECOMPILE );
1771
1772 IF @percent_NC_indexes_unused >= 5
1773 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1774 secret_columns, index_usage_summary, index_size_summary )
1775 SELECT 21 AS check_id,
1776 MAX(i.index_sanity_id) AS index_sanity_id,
1777 150 AS Priority,
1778 N'Index Hoarder' AS findings_group,
1779 N'More than 5 percent NC indexes are unused' AS finding,
1780 [database_name] AS [Database Name],
1781 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1782 CAST (@percent_NC_indexes_unused AS NVARCHAR(30)) + N' percent NC indexes (' + CAST(COUNT(*) AS NVARCHAR(10)) + N') unused. ' +
1783 N'These take up ' + CAST (@NC_indexes_unused_reserved_MB AS NVARCHAR(30)) + N'MB of space.' AS details,
1784 i.database_name + ' (' + CAST (COUNT(*) AS NVARCHAR(30)) + N' indexes)' AS index_definition,
1785 '' AS secret_columns,
1786 CAST(SUM(total_reads) AS NVARCHAR(256)) + N' reads (ALL); '
1787 + CAST(SUM([user_updates]) AS NVARCHAR(256)) + N' writes (ALL)' AS index_usage_summary,
1788
1789 REPLACE(CONVERT(NVARCHAR(30),CAST(MAX([total_rows]) AS MONEY), 1), '.00', '') + N' rows (MAX)'
1790 + CASE WHEN SUM(total_reserved_MB) > 1024 THEN
1791 N'; ' + CAST(CAST(SUM(total_reserved_MB)/1024. AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'GB (ALL)'
1792 WHEN SUM(total_reserved_MB) > 0 THEN
1793 N'; ' + CAST(CAST(SUM(total_reserved_MB) AS NUMERIC(29,1)) AS NVARCHAR(30)) + 'MB (ALL)'
1794 ELSE ''
1795 END AS index_size_summary
1796 FROM #IndexSanity i
1797 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
1798 WHERE index_id NOT IN ( 0, 1 )
1799 AND i.is_unique = 0
1800 AND total_reads = 0
1801 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1802 /*Skipping tables created in the last week, or modified in past 2 days*/
1803 AND i.create_date >= DATEADD(dd,-7,GETDATE())
1804 AND i.modify_date > DATEADD(dd,-2,GETDATE())
1805 GROUP BY i.database_name
1806 OPTION ( RECOMPILE );
1807
1808 RAISERROR(N'check_id 22: NC indexes with 0 reads. (Borderline)', 0,1) WITH NOWAIT;
1809 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1810 secret_columns, index_usage_summary, index_size_summary )
1811 SELECT 22 AS check_id,
1812 i.index_sanity_id,
1813 150 AS Priority,
1814 N'Index Hoarder' AS findings_group,
1815 N'Unused NC index' AS finding,
1816 [database_name] AS [Database Name],
1817 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1818 N'0 reads: ' + i.db_schema_object_indexid AS details,
1819 i.index_definition,
1820 i.secret_columns,
1821 i.index_usage_summary,
1822 sz.index_size_summary
1823 FROM #IndexSanity AS i
1824 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
1825 WHERE i.total_reads=0
1826 AND i.index_id NOT IN (0,1) /*NCs only*/
1827 AND i.is_unique = 0
1828 AND sz.total_reserved_MB >= CASE WHEN (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE sz.total_reserved_MB END
1829 ORDER BY i.db_schema_object_indexid
1830 OPTION ( RECOMPILE );
1831 END /*end checks only run when @Filter <> 1*/
1832
1833 RAISERROR(N'check_id 23: Indexes with 7 or more columns. (Borderline)', 0,1) WITH NOWAIT;
1834 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1835 secret_columns, index_usage_summary, index_size_summary )
1836 SELECT 23 AS check_id,
1837 i.index_sanity_id,
1838 150 AS Priority,
1839 N'Index Hoarder' AS findings_group,
1840 N'Borderline: Wide indexes (7 or more columns)' AS finding,
1841 [database_name] AS [Database Name],
1842 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1843 CAST(count_key_columns + count_included_columns AS NVARCHAR(10)) + ' columns on '
1844 + i.db_schema_object_indexid AS details, i.index_definition,
1845 i.secret_columns,
1846 i.index_usage_summary,
1847 sz.index_size_summary
1848 FROM #IndexSanity AS i
1849 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
1850 WHERE ( count_key_columns + count_included_columns ) >= 7
1851 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1852 OPTION ( RECOMPILE );
1853
1854 RAISERROR(N'check_id 24: Wide clustered indexes (> 3 columns or > 16 bytes).', 0,1) WITH NOWAIT;
1855 WITH count_columns AS (
1856 SELECT [object_id],
1857 SUM(CASE max_length WHEN -1 THEN 0 ELSE max_length END) AS sum_max_length
1858 FROM #IndexColumns ic
1859 WHERE index_id IN (1,0) /*Heap or clustered only*/
1860 AND key_ordinal > 0
1861 GROUP BY object_id
1862 )
1863 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1864 secret_columns, index_usage_summary, index_size_summary )
1865 SELECT 24 AS check_id,
1866 i.index_sanity_id,
1867 150 AS Priority,
1868 N'Index Hoarder' AS findings_group,
1869 N'Wide clustered index (> 3 columns OR > 16 bytes)' AS finding,
1870 [database_name] AS [Database Name],
1871 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1872 CAST (i.count_key_columns AS NVARCHAR(10)) + N' columns with potential size of '
1873 + CAST(cc.sum_max_length AS NVARCHAR(10))
1874 + N' bytes in clustered index:' + i.db_schema_object_name
1875 + N'. ' +
1876 (SELECT CAST(COUNT(*) AS NVARCHAR(23)) FROM #IndexSanity i2
1877 WHERE i2.[object_id]=i.[object_id] AND i2.index_id <> 1
1878 AND i2.is_disabled=0 AND i2.is_hypothetical=0)
1879 + N' NC indexes on the table.'
1880 AS details,
1881 i.index_definition,
1882 secret_columns,
1883 i.index_usage_summary,
1884 ip.index_size_summary
1885 FROM #IndexSanity i
1886 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
1887 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
1888 WHERE index_id = 1 /* clustered only */
1889 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1890 AND
1891 (count_key_columns > 3 /*More than three key columns.*/
1892 OR cc.sum_max_length > 16 /*More than 16 bytes in key */)
1893 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
1894
1895 RAISERROR(N'check_id 25: Addicted to nullable columns.', 0,1) WITH NOWAIT;
1896 WITH count_columns AS (
1897 SELECT [object_id],
1898 SUM(CASE is_nullable WHEN 1 THEN 0 ELSE 1 END) AS non_nullable_columns,
1899 COUNT(*) AS total_columns
1900 FROM #IndexColumns ic
1901 WHERE index_id IN (1,0) /*Heap or clustered only*/
1902 GROUP BY object_id
1903 )
1904 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1905 secret_columns, index_usage_summary, index_size_summary )
1906 SELECT 25 AS check_id,
1907 i.index_sanity_id,
1908 200 AS Priority,
1909 N'Index Hoarder' AS findings_group,
1910 N'Addicted to nulls' AS finding,
1911 [database_name] AS [Database Name],
1912 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1913 i.db_schema_object_name
1914 + N' allows null in ' + CAST((total_columns-non_nullable_columns) AS NVARCHAR(10))
1915 + N' of ' + CAST(total_columns AS NVARCHAR(10))
1916 + N' columns.' AS details,
1917 i.index_definition,
1918 secret_columns,
1919 ISNULL(i.index_usage_summary,''),
1920 ISNULL(ip.index_size_summary,'')
1921 FROM #IndexSanity i
1922 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
1923 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
1924 WHERE i.index_id IN (1,0)
1925 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1926 AND cc.non_nullable_columns < 2
1927 AND cc.total_columns > 3
1928 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
1929
1930 RAISERROR(N'check_id 26: Wide tables (35+ cols or > 2000 non-LOB bytes).', 0,1) WITH NOWAIT;
1931 WITH count_columns AS (
1932 SELECT [object_id],
1933 SUM(CASE max_length WHEN -1 THEN 1 ELSE 0 END) AS count_lob_columns,
1934 SUM(CASE max_length WHEN -1 THEN 0 ELSE max_length END) AS sum_max_length,
1935 COUNT(*) AS total_columns
1936 FROM #IndexColumns ic
1937 WHERE index_id IN (1,0) /*Heap or clustered only*/
1938 GROUP BY object_id
1939 )
1940 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1941 secret_columns, index_usage_summary, index_size_summary )
1942 SELECT 26 AS check_id,
1943 i.index_sanity_id,
1944 150 AS Priority,
1945 N'Index Hoarder' AS findings_group,
1946 N'Wide tables: 35+ cols or > 2000 non-LOB bytes' AS finding,
1947 [database_name] AS [Database Name],
1948 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1949 i.db_schema_object_name
1950 + N' has ' + CAST((total_columns) AS NVARCHAR(10))
1951 + N' total columns with a max possible width of ' + CAST(sum_max_length AS NVARCHAR(10))
1952 + N' bytes.' +
1953 CASE WHEN count_lob_columns > 0 THEN CAST((count_lob_columns) AS NVARCHAR(10))
1954 + ' columns are LOB types.' ELSE ''
1955 END
1956 AS details,
1957 i.index_definition,
1958 secret_columns,
1959 ISNULL(i.index_usage_summary,''),
1960 ISNULL(ip.index_size_summary,'')
1961 FROM #IndexSanity i
1962 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
1963 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
1964 WHERE i.index_id IN (1,0)
1965 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
1966 AND
1967 (cc.total_columns >= 35 OR
1968 cc.sum_max_length >= 2000)
1969 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
1970
1971 RAISERROR(N'check_id 27: Addicted to strings.', 0,1) WITH NOWAIT;
1972 WITH count_columns AS (
1973 SELECT [object_id],
1974 SUM(CASE WHEN system_type_name IN ('varchar','nvarchar','char') OR max_length=-1 THEN 1 ELSE 0 END) AS string_or_LOB_columns,
1975 COUNT(*) AS total_columns
1976 FROM #IndexColumns ic
1977 WHERE index_id IN (1,0) /*Heap or clustered only*/
1978 GROUP BY object_id
1979 )
1980 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
1981 secret_columns, index_usage_summary, index_size_summary )
1982 SELECT 27 AS check_id,
1983 i.index_sanity_id,
1984 200 AS Priority,
1985 N'Index Hoarder' AS findings_group,
1986 N'Addicted to strings' AS finding,
1987 [database_name] AS [Database Name],
1988 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
1989 i.db_schema_object_name
1990 + N' uses string or LOB types for ' + CAST((string_or_LOB_columns) AS NVARCHAR(10))
1991 + N' of ' + CAST(total_columns AS NVARCHAR(10))
1992 + N' columns. Check if data types are valid.' AS details,
1993 i.index_definition,
1994 secret_columns,
1995 ISNULL(i.index_usage_summary,''),
1996 ISNULL(ip.index_size_summary,'')
1997 FROM #IndexSanity i
1998 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
1999 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2000 CROSS APPLY (SELECT cc.total_columns - string_or_LOB_columns AS non_string_or_lob_columns) AS calc1
2001 WHERE i.index_id IN (1,0)
2002 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2003 AND calc1.non_string_or_lob_columns <= 1
2004 AND cc.total_columns > 3
2005 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2006
2007 RAISERROR(N'check_id 28: Non-unique clustered index.', 0,1) WITH NOWAIT;
2008 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2009 secret_columns, index_usage_summary, index_size_summary )
2010 SELECT 28 AS check_id,
2011 i.index_sanity_id,
2012 100 AS Priority,
2013 N'Index Hoarder' AS findings_group,
2014 N'Non-Unique clustered index' AS finding,
2015 [database_name] AS [Database Name],
2016 N'http://BrentOzar.com/go/IndexHoarder' AS URL,
2017 N'Uniquifiers will be required! Clustered index: ' + i.db_schema_object_name
2018 + N' and all NC indexes. ' +
2019 (SELECT CAST(COUNT(*) AS NVARCHAR(23)) FROM #IndexSanity i2
2020 WHERE i2.[object_id]=i.[object_id] AND i2.index_id <> 1
2021 AND i2.is_disabled=0 AND i2.is_hypothetical=0)
2022 + N' NC indexes on the table.'
2023 AS details,
2024 i.index_definition,
2025 secret_columns,
2026 i.index_usage_summary,
2027 ip.index_size_summary
2028 FROM #IndexSanity i
2029 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2030 WHERE index_id = 1 /* clustered only */
2031 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2032 AND is_unique=0 /* not unique */
2033 AND is_CX_columnstore=0 /* not a clustered columnstore-- no unique option on those */
2034 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE )
2035
2036
2037
2038 END
2039 ----------------------------------------
2040 --Feature-Phobic Indexes: Check_id 30-39
2041 ----------------------------------------
2042 BEGIN
2043 RAISERROR(N'check_id 30: No indexes with includes', 0,1) WITH NOWAIT;
2044
2045 DECLARE @number_indexes_with_includes INT;
2046 DECLARE @percent_indexes_with_includes NUMERIC(10, 1);
2047
2048 SELECT @number_indexes_with_includes = SUM(CASE WHEN count_included_columns > 0 THEN 1 ELSE 0 END),
2049 @percent_indexes_with_includes = 100.*
2050 SUM(CASE WHEN count_included_columns > 0 THEN 1 ELSE 0 END) / ( 1.0 * COUNT(*) )
2051 FROM #IndexSanity;
2052
2053 IF @number_indexes_with_includes = 0 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2054 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, URL, details, index_definition,
2055 secret_columns, index_usage_summary, index_size_summary )
2056 SELECT 30 AS check_id,
2057 NULL AS index_sanity_id,
2058 250 AS Priority,
2059 N'Feature-Phobic Indexes' AS findings_group,
2060 N'No indexes use includes' AS finding, 'http://BrentOzar.com/go/IndexFeatures' AS URL,
2061 N'No indexes use includes' AS details,
2062 @DatabaseName + N' (Entire database)' AS index_definition,
2063 N'' AS secret_columns,
2064 N'N/A' AS index_usage_summary,
2065 N'N/A' AS index_size_summary OPTION ( RECOMPILE );
2066
2067 RAISERROR(N'check_id 31: < 3 percent of indexes have includes', 0,1) WITH NOWAIT;
2068 IF @percent_indexes_with_includes <= 3 AND @number_indexes_with_includes > 0 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2069 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2070 secret_columns, index_usage_summary, index_size_summary )
2071 SELECT 31 AS check_id,
2072 NULL AS index_sanity_id,
2073 150 AS Priority,
2074 N'Feature-Phobic Indexes' AS findings_group,
2075 N'Borderline: Includes are used in < 3% of indexes' AS findings,
2076 @DatabaseName AS [Database Name],
2077 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
2078 N'Only ' + CAST(@percent_indexes_with_includes AS NVARCHAR(10)) + '% of indexes have includes' AS details,
2079 N'Entire database' AS index_definition,
2080 N'' AS secret_columns,
2081 N'N/A' AS index_usage_summary,
2082 N'N/A' AS index_size_summary OPTION ( RECOMPILE );
2083
2084 RAISERROR(N'check_id 32: filtered indexes and indexed views', 0,1) WITH NOWAIT;
2085 DECLARE @count_filtered_indexes INT;
2086 DECLARE @count_indexed_views INT;
2087
2088 SELECT @count_filtered_indexes=COUNT(*)
2089 FROM #IndexSanity
2090 WHERE filter_definition <> '' OPTION ( RECOMPILE );
2091
2092 SELECT @count_indexed_views=COUNT(*)
2093 FROM #IndexSanity AS i
2094 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2095 WHERE is_indexed_view = 1 OPTION ( RECOMPILE );
2096
2097 IF @count_filtered_indexes = 0 AND @count_indexed_views=0 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2098 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2099 secret_columns, index_usage_summary, index_size_summary )
2100 SELECT 32 AS check_id,
2101 NULL AS index_sanity_id,
2102 250 AS Priority,
2103 N'Feature-Phobic Indexes' AS findings_group,
2104 N'Borderline: No filtered indexes or indexed views exist' AS finding,
2105 @DatabaseName AS [Database Name],
2106 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
2107 N'These are NOT always needed-- but do you know when you would use them?' AS details,
2108 @DatabaseName + N' (Entire database)' AS index_definition,
2109 N'' AS secret_columns,
2110 N'N/A' AS index_usage_summary,
2111 N'N/A' AS index_size_summary OPTION ( RECOMPILE );
2112 END;
2113
2114 RAISERROR(N'check_id 33: Potential filtered indexes based on column names.', 0,1) WITH NOWAIT;
2115
2116 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2117 secret_columns, index_usage_summary, index_size_summary )
2118 SELECT 33 AS check_id,
2119 i.index_sanity_id AS index_sanity_id,
2120 250 AS Priority,
2121 N'Feature-Phobic Indexes' AS findings_group,
2122 N'Potential filtered index (based on column name)' AS finding,
2123 [database_name] AS [Database Name],
2124 N'http://BrentOzar.com/go/IndexFeatures' AS URL,
2125 N'A column name in this index suggests it might be a candidate for filtering (is%, %archive%, %active%, %flag%)' AS details,
2126 i.index_definition,
2127 i.secret_columns,
2128 i.index_usage_summary,
2129 sz.index_size_summary
2130 FROM #IndexColumns ic
2131 JOIN #IndexSanity i ON
2132 ic.[object_id]=i.[object_id] AND
2133 ic.[index_id]=i.[index_id] AND
2134 i.[index_id] > 1 /* non-clustered index */
2135 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2136 WHERE (column_name LIKE 'is%'
2137 OR column_name LIKE '%archive%'
2138 OR column_name LIKE '%active%'
2139 OR column_name LIKE '%flag%')
2140 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2141 OPTION ( RECOMPILE );
2142
2143 ----------------------------------------
2144 --Self Loathing Indexes : Check_id 40-49
2145 ----------------------------------------
2146 BEGIN
2147
2148 RAISERROR(N'check_id 40: Fillfactor in nonclustered 80 percent or less', 0,1) WITH NOWAIT;
2149 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2150 secret_columns, index_usage_summary, index_size_summary )
2151 SELECT 40 AS check_id,
2152 i.index_sanity_id,
2153 100 AS Priority,
2154 N'Self Loathing Indexes' AS findings_group,
2155 N'Low Fill Factor: nonclustered index' AS finding,
2156 [database_name] AS [Database Name],
2157 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2158 CAST(fill_factor AS NVARCHAR(10)) + N'% fill factor on ' + db_schema_object_indexid + N'. '+
2159 CASE WHEN (last_user_update IS NULL OR user_updates < 1)
2160 THEN N'No writes have been made.'
2161 ELSE
2162 N'Last write was ' + CONVERT(NVARCHAR(16),last_user_update,121) + N' and ' +
2163 CAST(user_updates AS NVARCHAR(25)) + N' updates have been made.'
2164 END
2165 AS details,
2166 i.index_definition,
2167 i.secret_columns,
2168 i.index_usage_summary,
2169 sz.index_size_summary
2170 FROM #IndexSanity AS i
2171 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2172 WHERE index_id > 1
2173 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2174 AND fill_factor BETWEEN 1 AND 80 OPTION ( RECOMPILE );
2175
2176 RAISERROR(N'check_id 40: Fillfactor in clustered 80 percent or less', 0,1) WITH NOWAIT;
2177 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2178 secret_columns, index_usage_summary, index_size_summary )
2179 SELECT 40 AS check_id,
2180 i.index_sanity_id,
2181 100 AS Priority,
2182 N'Self Loathing Indexes' AS findings_group,
2183 N'Low Fill Factor: clustered index' AS finding,
2184 [database_name] AS [Database Name],
2185 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2186 N'Fill factor on ' + db_schema_object_indexid + N' is ' + CAST(fill_factor AS NVARCHAR(10)) + N'%. '+
2187 CASE WHEN (last_user_update IS NULL OR user_updates < 1)
2188 THEN N'No writes have been made.'
2189 ELSE
2190 N'Last write was ' + CONVERT(NVARCHAR(16),last_user_update,121) + N' and ' +
2191 CAST(user_updates AS NVARCHAR(25)) + N' updates have been made.'
2192 END
2193 AS details,
2194 i.index_definition,
2195 i.secret_columns,
2196 i.index_usage_summary,
2197 sz.index_size_summary
2198 FROM #IndexSanity AS i
2199 JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
2200 WHERE index_id = 1
2201 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2202 AND fill_factor BETWEEN 1 AND 80 OPTION ( RECOMPILE );
2203
2204
2205 RAISERROR(N'check_id 41: Hypothetical indexes ', 0,1) WITH NOWAIT;
2206 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2207 secret_columns, index_usage_summary, index_size_summary )
2208 SELECT 41 AS check_id,
2209 i.index_sanity_id,
2210 150 AS Priority,
2211 N'Self Loathing Indexes' AS findings_group,
2212 N'Hypothetical Index' AS finding,
2213 [database_name] AS [Database Name],
2214 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2215 N'Hypothetical Index: ' + db_schema_object_indexid AS details,
2216 i.index_definition,
2217 i.secret_columns,
2218 N'' AS index_usage_summary,
2219 N'' AS index_size_summary
2220 FROM #IndexSanity AS i
2221 WHERE is_hypothetical = 1
2222 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2223 OPTION ( RECOMPILE );
2224
2225
2226 RAISERROR(N'check_id 42: Disabled indexes', 0,1) WITH NOWAIT;
2227 --Note: disabled NC indexes will have O rows in #IndexSanitySize!
2228 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2229 secret_columns, index_usage_summary, index_size_summary )
2230 SELECT 42 AS check_id,
2231 index_sanity_id,
2232 150 AS Priority,
2233 N'Self Loathing Indexes' AS findings_group,
2234 N'Disabled Index' AS finding,
2235 [database_name] AS [Database Name],
2236 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2237 N'Disabled Index:' + db_schema_object_indexid AS details,
2238 i.index_definition,
2239 i.secret_columns,
2240 i.index_usage_summary,
2241 'DISABLED' AS index_size_summary
2242 FROM #IndexSanity AS i
2243 WHERE is_disabled = 1
2244 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2245 OPTION ( RECOMPILE );
2246
2247 RAISERROR(N'check_id 43: Heaps with forwarded records or deletes', 0,1) WITH NOWAIT;
2248 WITH heaps_cte
2249 AS ( SELECT [object_id],
2250 SUM(forwarded_fetch_count) AS forwarded_fetch_count,
2251 SUM(leaf_delete_count) AS leaf_delete_count
2252 FROM #IndexPartitionSanity
2253 GROUP BY [object_id]
2254 HAVING SUM(forwarded_fetch_count) > 0
2255 OR SUM(leaf_delete_count) > 0)
2256 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2257 secret_columns, index_usage_summary, index_size_summary )
2258 SELECT 43 AS check_id,
2259 i.index_sanity_id,
2260 100 AS Priority,
2261 N'Self Loathing Indexes' AS findings_group,
2262 N'Heaps with forwarded records or deletes' AS finding,
2263 [database_name] AS [Database Name],
2264 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2265 CAST(h.forwarded_fetch_count AS NVARCHAR(256)) + ' forwarded fetches, '
2266 + CAST(h.leaf_delete_count AS NVARCHAR(256)) + ' deletes against heap:'
2267 + db_schema_object_indexid AS details,
2268 i.index_definition,
2269 i.secret_columns,
2270 i.index_usage_summary,
2271 sz.index_size_summary
2272 FROM #IndexSanity i
2273 JOIN heaps_cte h ON i.[object_id] = h.[object_id]
2274 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2275 WHERE i.index_id = 0
2276 AND sz.total_reserved_MB >= CASE WHEN NOT (@GetAllDatabases = 1 OR @Mode = 0) THEN @ThresholdMB ELSE sz.total_reserved_MB END
2277 OPTION ( RECOMPILE );
2278
2279 RAISERROR(N'check_id 44: Heaps with reads or writes.', 0,1) WITH NOWAIT;
2280 WITH heaps_cte
2281 AS ( SELECT [object_id], SUM(forwarded_fetch_count) AS forwarded_fetch_count,
2282 SUM(leaf_delete_count) AS leaf_delete_count
2283 FROM #IndexPartitionSanity
2284 GROUP BY [object_id]
2285 HAVING SUM(forwarded_fetch_count) > 0
2286 OR SUM(leaf_delete_count) > 0)
2287 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2288 secret_columns, index_usage_summary, index_size_summary )
2289 SELECT 44 AS check_id,
2290 i.index_sanity_id,
2291 100 AS Priority,
2292 N'Self Loathing Indexes' AS findings_group,
2293 N'Active heap' AS finding,
2294 [database_name] AS [Database Name],
2295 N'http://BrentOzar.com/go/SelfLoathing' AS URL,
2296 N'Should this table be a heap? ' + db_schema_object_indexid AS details,
2297 i.index_definition,
2298 'N/A' AS secret_columns,
2299 i.index_usage_summary,
2300 sz.index_size_summary
2301 FROM #IndexSanity i
2302 LEFT JOIN heaps_cte h ON i.[object_id] = h.[object_id]
2303 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2304 WHERE i.index_id = 0
2305 AND
2306 (i.total_reads > 0 OR i.user_updates > 0)
2307 AND h.[object_id] IS NULL /*don't duplicate the prior check.*/
2308 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2309 OPTION ( RECOMPILE );
2310
2311
2312 END;
2313 ----------------------------------------
2314 --Indexaphobia
2315 --Missing indexes with value >= 5 million: : Check_id 50-59
2316 ----------------------------------------
2317 BEGIN
2318 RAISERROR(N'check_id 50: Indexaphobia.', 0,1) WITH NOWAIT;
2319 WITH index_size_cte
2320 AS ( SELECT i.database_id,
2321 i.[object_id],
2322 MAX(i.index_sanity_id) AS index_sanity_id,
2323 ISNULL (
2324 CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN 1 ELSE 0 END)
2325 AS NVARCHAR(30))+ N' NC indexes exist (' +
2326 CASE WHEN SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) > 1024
2327 THEN CAST(CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END )/1024.
2328 AS NUMERIC(29,1)) AS NVARCHAR(30)) + N'GB); '
2329 ELSE CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
2330 AS NVARCHAR(30)) + N'MB); '
2331 END +
2332 CASE WHEN MAX(sz.[total_rows]) >= 922337203685477 THEN '>= 922,337,203,685,477'
2333 ELSE REPLACE(CONVERT(NVARCHAR(30),CAST(MAX(sz.[total_rows]) AS MONEY), 1), '.00', '')
2334 END +
2335 + N' Estimated Rows;'
2336 ,N'') AS index_size_summary
2337 FROM #IndexSanity AS i
2338 LEFT JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id AND i.database_id = sz.database_id
2339 GROUP BY i.database_id, i.[object_id])
2340 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2341 index_usage_summary, index_size_summary, create_tsql, more_info )
2342
2343 SELECT check_id, t.index_sanity_id, t.check_id, t.findings_group, t.finding, t.[Database Name], t.URL, t.details, t.[definition],
2344 index_estimated_impact, t.index_size_summary, create_tsql, more_info
2345 FROM
2346 (
2347 SELECT ROW_NUMBER() OVER (ORDER BY magic_benefit_number DESC) AS rownum,
2348 50 AS check_id,
2349 sz.index_sanity_id,
2350 10 AS Priority,
2351 N'Indexaphobia' AS findings_group,
2352 N'High value missing index' AS finding,
2353 [database_name] AS [Database Name],
2354 N'http://BrentOzar.com/go/Indexaphobia' AS URL,
2355 mi.[statement] +
2356 N' Est. benefit per day: ' +
2357 CASE WHEN magic_benefit_number >= 922337203685477 THEN '>= 922,337,203,685,477'
2358 ELSE REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(
2359 (magic_benefit_number/@DaysUptime)
2360 AS BIGINT) AS MONEY), 1), '.00', '')
2361 END AS details,
2362 missing_index_details AS [definition],
2363 index_estimated_impact,
2364 sz.index_size_summary,
2365 mi.create_tsql,
2366 mi.more_info,
2367 magic_benefit_number
2368 FROM #MissingIndexes mi
2369 LEFT JOIN index_size_cte sz ON mi.[object_id] = sz.object_id AND DB_ID(mi.database_name) = sz.database_id
2370 /* Minimum benefit threshold = 100k/day of uptime */
2371 WHERE @Mode = 4 OR (magic_benefit_number/@DaysUptime) >= 100000
2372 ) AS t
2373 WHERE t.rownum <= CASE WHEN (@Mode <> 4) THEN 20 ELSE t.rownum END
2374 ORDER BY magic_benefit_number DESC
2375
2376
2377 END
2378 ----------------------------------------
2379 --Abnormal Psychology : Check_id 60-79
2380 ----------------------------------------
2381 BEGIN
2382 RAISERROR(N'check_id 60: XML indexes', 0,1) WITH NOWAIT;
2383 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2384 secret_columns, index_usage_summary, index_size_summary )
2385 SELECT 60 AS check_id,
2386 i.index_sanity_id,
2387 150 AS Priority,
2388 N'Abnormal Psychology' AS findings_group,
2389 N'XML Indexes' AS finding,
2390 [database_name] AS [Database Name],
2391 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2392 i.db_schema_object_indexid AS details,
2393 i.index_definition,
2394 i.secret_columns,
2395 N'' AS index_usage_summary,
2396 ISNULL(sz.index_size_summary,'') AS index_size_summary
2397 FROM #IndexSanity AS i
2398 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2399 WHERE i.is_XML = 1 OPTION ( RECOMPILE );
2400
2401 RAISERROR(N'check_id 61: Columnstore indexes', 0,1) WITH NOWAIT;
2402 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2403 secret_columns, index_usage_summary, index_size_summary )
2404 SELECT 61 AS check_id,
2405 i.index_sanity_id,
2406 150 AS Priority,
2407 N'Abnormal Psychology' AS findings_group,
2408 CASE WHEN i.is_NC_columnstore=1
2409 THEN N'NC Columnstore Index'
2410 ELSE N'Clustered Columnstore Index'
2411 END AS finding,
2412 [database_name] AS [Database Name],
2413 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2414 i.db_schema_object_indexid AS details,
2415 i.index_definition,
2416 i.secret_columns,
2417 i.index_usage_summary,
2418 ISNULL(sz.index_size_summary,'') AS index_size_summary
2419 FROM #IndexSanity AS i
2420 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2421 WHERE i.is_NC_columnstore = 1 OR i.is_CX_columnstore=1
2422 OPTION ( RECOMPILE );
2423
2424
2425 RAISERROR(N'check_id 62: Spatial indexes', 0,1) WITH NOWAIT;
2426 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2427 secret_columns, index_usage_summary, index_size_summary )
2428 SELECT 62 AS check_id,
2429 i.index_sanity_id,
2430 150 AS Priority,
2431 N'Abnormal Psychology' AS findings_group,
2432 N'Spatial indexes' AS finding,
2433 [database_name] AS [Database Name],
2434 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2435 i.db_schema_object_indexid AS details,
2436 i.index_definition,
2437 i.secret_columns,
2438 i.index_usage_summary,
2439 ISNULL(sz.index_size_summary,'') AS index_size_summary
2440 FROM #IndexSanity AS i
2441 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2442 WHERE i.is_spatial = 1 OPTION ( RECOMPILE );
2443
2444 RAISERROR(N'check_id 63: Compressed indexes', 0,1) WITH NOWAIT;
2445 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2446 secret_columns, index_usage_summary, index_size_summary )
2447 SELECT 63 AS check_id,
2448 i.index_sanity_id,
2449 150 AS Priority,
2450 N'Abnormal Psychology' AS findings_group,
2451 N'Compressed indexes' AS finding,
2452 [database_name] AS [Database Name],
2453 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2454 i.db_schema_object_indexid + N'. COMPRESSION: ' + sz.data_compression_desc AS details,
2455 i.index_definition,
2456 i.secret_columns,
2457 i.index_usage_summary,
2458 ISNULL(sz.index_size_summary,'') AS index_size_summary
2459 FROM #IndexSanity AS i
2460 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2461 WHERE sz.data_compression_desc LIKE '%PAGE%' OR sz.data_compression_desc LIKE '%ROW%' OPTION ( RECOMPILE );
2462
2463 RAISERROR(N'check_id 64: Partitioned', 0,1) WITH NOWAIT;
2464 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2465 secret_columns, index_usage_summary, index_size_summary )
2466 SELECT 64 AS check_id,
2467 i.index_sanity_id,
2468 150 AS Priority,
2469 N'Abnormal Psychology' AS findings_group,
2470 N'Partitioned indexes' AS finding,
2471 [database_name] AS [Database Name],
2472 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2473 i.db_schema_object_indexid AS details,
2474 i.index_definition,
2475 i.secret_columns,
2476 i.index_usage_summary,
2477 ISNULL(sz.index_size_summary,'') AS index_size_summary
2478 FROM #IndexSanity AS i
2479 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2480 WHERE i.partition_key_column_name IS NOT NULL OPTION ( RECOMPILE );
2481
2482 RAISERROR(N'check_id 65: Non-Aligned Partitioned', 0,1) WITH NOWAIT;
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 65 AS check_id,
2486 i.index_sanity_id,
2487 150 AS Priority,
2488 N'Abnormal Psychology' AS findings_group,
2489 N'Non-Aligned index on a partitioned table' AS finding,
2490 i.[database_name] AS [Database Name],
2491 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2492 i.db_schema_object_indexid AS details,
2493 i.index_definition,
2494 i.secret_columns,
2495 i.index_usage_summary,
2496 ISNULL(sz.index_size_summary,'') AS index_size_summary
2497 FROM #IndexSanity AS i
2498 JOIN #IndexSanity AS iParent ON
2499 i.[object_id]=iParent.[object_id]
2500 AND iParent.index_id IN (0,1) /* could be a partitioned heap or clustered table */
2501 AND iParent.partition_key_column_name IS NOT NULL /* parent is partitioned*/
2502 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2503 WHERE i.partition_key_column_name IS NULL
2504 OPTION ( RECOMPILE );
2505
2506 RAISERROR(N'check_id 66: Recently created tables/indexes (1 week)', 0,1) WITH NOWAIT;
2507 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2508 secret_columns, index_usage_summary, index_size_summary )
2509 SELECT 66 AS check_id,
2510 i.index_sanity_id,
2511 200 AS Priority,
2512 N'Abnormal Psychology' AS findings_group,
2513 N'Recently created tables/indexes (1 week)' AS finding,
2514 [database_name] AS [Database Name],
2515 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2516 i.db_schema_object_indexid + N' was created on ' +
2517 CONVERT(NVARCHAR(16),i.create_date,121) +
2518 N'. Tables/indexes which are dropped/created regularly require special methods for index tuning.'
2519 AS details,
2520 i.index_definition,
2521 i.secret_columns,
2522 i.index_usage_summary,
2523 ISNULL(sz.index_size_summary,'') AS index_size_summary
2524 FROM #IndexSanity AS i
2525 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2526 WHERE i.create_date >= DATEADD(dd,-7,GETDATE())
2527 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2528 OPTION ( RECOMPILE );
2529
2530 RAISERROR(N'check_id 67: Recently modified tables/indexes (2 days)', 0,1) WITH NOWAIT;
2531 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2532 secret_columns, index_usage_summary, index_size_summary )
2533 SELECT 67 AS check_id,
2534 i.index_sanity_id,
2535 200 AS Priority,
2536 N'Abnormal Psychology' AS findings_group,
2537 N'Recently modified tables/indexes (2 days)' AS finding,
2538 [database_name] AS [Database Name],
2539 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2540 i.db_schema_object_indexid + N' was modified on ' +
2541 CONVERT(NVARCHAR(16),i.modify_date,121) +
2542 N'. A large amount of recently modified indexes may mean a lot of rebuilds are occurring each night.'
2543 AS details,
2544 i.index_definition,
2545 i.secret_columns,
2546 i.index_usage_summary,
2547 ISNULL(sz.index_size_summary,'') AS index_size_summary
2548 FROM #IndexSanity AS i
2549 JOIN #IndexSanitySize sz ON i.index_sanity_id = sz.index_sanity_id
2550 WHERE i.modify_date > DATEADD(dd,-2,GETDATE())
2551 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2552 AND /*Exclude recently created tables.*/
2553 i.create_date < DATEADD(dd,-7,GETDATE())
2554 OPTION ( RECOMPILE );
2555
2556 RAISERROR(N'check_id 68: Identity columns within 30 percent of the end of range', 0,1) WITH NOWAIT;
2557 -- Allowed Ranges:
2558 --int -2,147,483,648 to 2,147,483,647
2559 --smallint -32,768 to 32,768
2560 --tinyint 0 to 255
2561
2562 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2563 secret_columns, index_usage_summary, index_size_summary )
2564 SELECT 68 AS check_id,
2565 i.index_sanity_id,
2566 200 AS Priority,
2567 N'Abnormal Psychology' AS findings_group,
2568 N'Identity column within ' +
2569 CAST (calc1.percent_remaining AS NVARCHAR(256))
2570 + N' percent end of range' AS finding,
2571 [database_name] AS [Database Name],
2572 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2573 i.db_schema_object_name + N'.' + QUOTENAME(ic.column_name)
2574 + N' is an identity with type ' + ic.system_type_name
2575 + N', last value of '
2576 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.last_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
2577 + N', seed of '
2578 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.seed_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
2579 + N', increment of ' + CAST(ic.increment_value AS NVARCHAR(256))
2580 + N', and range of ' +
2581 CASE ic.system_type_name WHEN 'int' THEN N'+/- 2,147,483,647'
2582 WHEN 'smallint' THEN N'+/- 32,768'
2583 WHEN 'tinyint' THEN N'0 to 255'
2584 END
2585 AS details,
2586 i.index_definition,
2587 secret_columns,
2588 ISNULL(i.index_usage_summary,''),
2589 ISNULL(ip.index_size_summary,'')
2590 FROM #IndexSanity i
2591 JOIN #IndexColumns ic ON
2592 i.object_id=ic.object_id
2593 AND i.index_id IN (0,1) /* heaps and cx only */
2594 AND ic.is_identity=1
2595 AND ic.system_type_name IN ('tinyint', 'smallint', 'int')
2596 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2597 CROSS APPLY (
2598 SELECT CAST(CASE WHEN ic.increment_value >= 0
2599 THEN
2600 CASE ic.system_type_name
2601 WHEN 'int' THEN (2147483647 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 2147483647.*100
2602 WHEN 'smallint' THEN (32768 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 32768.*100
2603 WHEN 'tinyint' THEN ( 255 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 255.*100
2604 ELSE 999
2605 END
2606 ELSE --ic.increment_value is negative
2607 CASE ic.system_type_name
2608 WHEN 'int' THEN ABS(-2147483647 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 2147483647.*100
2609 WHEN 'smallint' THEN ABS(-32768 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 32768.*100
2610 WHEN 'tinyint' THEN ABS( 0 - (ISNULL(ic.last_value,ic.seed_value) + ic.increment_value)) / 255.*100
2611 ELSE -1
2612 END
2613 END AS NUMERIC(5,1)) AS percent_remaining
2614 ) AS calc1
2615 WHERE i.index_id IN (1,0)
2616 AND calc1.percent_remaining <= 30
2617 UNION ALL
2618 SELECT 68 AS check_id,
2619 i.index_sanity_id,
2620 200 AS Priority,
2621 N'Abnormal Psychology' AS findings_group,
2622 N'Identity column using a negative seed or increment other than 1' AS finding,
2623 [database_name] AS [Database Name],
2624 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2625 i.db_schema_object_name + N'.' + QUOTENAME(ic.column_name)
2626 + N' is an identity with type ' + ic.system_type_name
2627 + N', last value of '
2628 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.last_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
2629 + N', seed of '
2630 + ISNULL(REPLACE(CONVERT(NVARCHAR(256),CAST(CAST(ic.seed_value AS BIGINT) AS MONEY), 1), '.00', ''),N'NULL')
2631 + N', increment of ' + CAST(ic.increment_value AS NVARCHAR(256))
2632 + N', and range of ' +
2633 CASE ic.system_type_name WHEN 'int' THEN N'+/- 2,147,483,647'
2634 WHEN 'smallint' THEN N'+/- 32,768'
2635 WHEN 'tinyint' THEN N'0 to 255'
2636 END
2637 AS details,
2638 i.index_definition,
2639 secret_columns,
2640 ISNULL(i.index_usage_summary,''),
2641 ISNULL(ip.index_size_summary,'')
2642 FROM #IndexSanity i
2643 JOIN #IndexColumns ic ON
2644 i.object_id=ic.object_id
2645 AND i.index_id IN (0,1) /* heaps and cx only */
2646 AND ic.is_identity=1
2647 AND ic.system_type_name IN ('tinyint', 'smallint', 'int')
2648 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2649 WHERE i.index_id IN (1,0)
2650 AND (ic.seed_value < 0 OR ic.increment_value <> 1)
2651 ORDER BY finding, details DESC OPTION ( RECOMPILE );
2652
2653 RAISERROR(N'check_id 69: Column collation does not match database collation', 0,1) WITH NOWAIT;
2654 WITH count_columns AS (
2655 SELECT [object_id],
2656 COUNT(*) AS column_count
2657 FROM #IndexColumns ic
2658 WHERE index_id IN (1,0) /*Heap or clustered only*/
2659 AND collation_name <> @collation
2660 GROUP BY object_id
2661 )
2662 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2663 secret_columns, index_usage_summary, index_size_summary )
2664 SELECT 69 AS check_id,
2665 i.index_sanity_id,
2666 150 AS Priority,
2667 N'Abnormal Psychology' AS findings_group,
2668 N'Column collation does not match database collation' AS finding,
2669 [database_name] AS [Database Name],
2670 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2671 i.db_schema_object_name
2672 + N' has ' + CAST(column_count AS NVARCHAR(20))
2673 + N' column' + CASE WHEN column_count > 1 THEN 's' ELSE '' END
2674 + N' with a different collation than the db collation of '
2675 + @collation AS details,
2676 i.index_definition,
2677 secret_columns,
2678 ISNULL(i.index_usage_summary,''),
2679 ISNULL(ip.index_size_summary,'')
2680 FROM #IndexSanity i
2681 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2682 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2683 WHERE i.index_id IN (1,0)
2684 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2685 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2686
2687 RAISERROR(N'check_id 70: Replicated columns', 0,1) WITH NOWAIT;
2688 WITH count_columns AS (
2689 SELECT [object_id],
2690 COUNT(*) AS column_count,
2691 SUM(CASE is_replicated WHEN 1 THEN 1 ELSE 0 END) AS replicated_column_count
2692 FROM #IndexColumns ic
2693 WHERE index_id IN (1,0) /*Heap or clustered only*/
2694 GROUP BY object_id
2695 )
2696 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2697 secret_columns, index_usage_summary, index_size_summary )
2698 SELECT 70 AS check_id,
2699 i.index_sanity_id,
2700 200 AS Priority,
2701 N'Abnormal Psychology' AS findings_group,
2702 N'Replicated columns' AS finding,
2703 [database_name] AS [Database Name],
2704 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2705 i.db_schema_object_name
2706 + N' has ' + CAST(replicated_column_count AS NVARCHAR(20))
2707 + N' out of ' + CAST(column_count AS NVARCHAR(20))
2708 + N' column' + CASE WHEN column_count > 1 THEN 's' ELSE '' END
2709 + N' in one or more publications.'
2710 AS details,
2711 i.index_definition,
2712 secret_columns,
2713 ISNULL(i.index_usage_summary,''),
2714 ISNULL(ip.index_size_summary,'')
2715 FROM #IndexSanity i
2716 JOIN #IndexSanitySize ip ON i.index_sanity_id = ip.index_sanity_id
2717 JOIN count_columns AS cc ON i.[object_id]=cc.[object_id]
2718 WHERE i.index_id IN (1,0)
2719 AND replicated_column_count > 0
2720 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2721 ORDER BY i.db_schema_object_name DESC OPTION ( RECOMPILE );
2722
2723 RAISERROR(N'check_id 71: Cascading updates or cascading deletes.', 0,1) WITH NOWAIT;
2724 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2725 secret_columns, index_usage_summary, index_size_summary, more_info )
2726 SELECT 71 AS check_id,
2727 NULL AS index_sanity_id,
2728 150 AS Priority,
2729 N'Abnormal Psychology' AS findings_group,
2730 N'Cascading Updates or Deletes' AS finding,
2731 [database_name] AS [Database Name],
2732 N'http://BrentOzar.com/go/AbnormalPsychology' AS URL,
2733 N'Foreign Key ' + foreign_key_name +
2734 N' on ' + QUOTENAME(parent_object_name) + N'(' + LTRIM(parent_fk_columns) + N')'
2735 + N' referencing ' + QUOTENAME(referenced_object_name) + N'(' + LTRIM(referenced_fk_columns) + N')'
2736 + N' has settings:'
2737 + CASE [delete_referential_action_desc] WHEN N'NO_ACTION' THEN N'' ELSE N' ON DELETE ' +[delete_referential_action_desc] END
2738 + CASE [update_referential_action_desc] WHEN N'NO_ACTION' THEN N'' ELSE N' ON UPDATE ' + [update_referential_action_desc] END
2739 AS details,
2740 [fk].[database_name]
2741 AS index_definition,
2742 N'N/A' AS secret_columns,
2743 N'N/A' AS index_usage_summary,
2744 N'N/A' AS index_size_summary,
2745 (SELECT TOP 1 more_info FROM #IndexSanity i WHERE i.object_id=fk.parent_object_id)
2746 AS more_info
2747 FROM #ForeignKeys fk
2748 WHERE ([delete_referential_action_desc] <> N'NO_ACTION'
2749 OR [update_referential_action_desc] <> N'NO_ACTION')
2750 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2751
2752 END
2753
2754 ----------------------------------------
2755 --Workaholics: Check_id 80-89
2756 ----------------------------------------
2757 BEGIN
2758
2759 RAISERROR(N'check_id 80: Most scanned indexes (index_usage_stats)', 0,1) WITH NOWAIT;
2760 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2761 secret_columns, index_usage_summary, index_size_summary )
2762
2763 --Workaholics according to index_usage_stats
2764 --This isn't perfect: it mentions the number of scans present in a plan
2765 --A "scan" isn't necessarily a full scan, but hey, we gotta do the best with what we've got.
2766 --in the case of things like indexed views, the operator might be in the plan but never executed
2767 SELECT TOP 5
2768 80 AS check_id,
2769 i.index_sanity_id AS index_sanity_id,
2770 200 AS Priority,
2771 N'Workaholics' AS findings_group,
2772 N'Scan-a-lots (index_usage_stats)' AS finding,
2773 [database_name] AS [Database Name],
2774 N'http://BrentOzar.com/go/Workaholics' AS URL,
2775 REPLACE(CONVERT( NVARCHAR(50),CAST(i.user_scans AS MONEY),1),'.00','')
2776 + N' scans against ' + i.db_schema_object_indexid
2777 + N'. Latest scan: ' + ISNULL(CAST(i.last_user_scan AS NVARCHAR(128)),'?') + N'. '
2778 + N'ScanFactor=' + CAST(((i.user_scans * iss.total_reserved_MB)/1000000.) AS NVARCHAR(256)) AS details,
2779 ISNULL(i.key_column_names_with_sort_order,'N/A') AS index_definition,
2780 ISNULL(i.secret_columns,'') AS secret_columns,
2781 i.index_usage_summary AS index_usage_summary,
2782 iss.index_size_summary AS index_size_summary
2783 FROM #IndexSanity i
2784 JOIN #IndexSanitySize iss ON i.index_sanity_id=iss.index_sanity_id
2785 WHERE ISNULL(i.user_scans,0) > 0
2786 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2787 ORDER BY i.user_scans * iss.total_reserved_MB DESC;
2788
2789 RAISERROR(N'check_id 81: Top recent accesses (op stats)', 0,1) WITH NOWAIT;
2790 INSERT #BlitzIndexResults ( check_id, index_sanity_id, Priority, findings_group, finding, [database_name], URL, details, index_definition,
2791 secret_columns, index_usage_summary, index_size_summary )
2792 --Workaholics according to index_operational_stats
2793 --This isn't perfect either: range_scan_count contains full scans, partial scans, even seeks in nested loop ops
2794 --But this can help bubble up some most-accessed tables
2795 SELECT TOP 5
2796 81 AS check_id,
2797 i.index_sanity_id AS index_sanity_id,
2798 200 AS Priority,
2799 N'Workaholics' AS findings_group,
2800 N'Top recent accesses (index_op_stats)' AS finding,
2801 [database_name] AS [Database Name],
2802 N'http://BrentOzar.com/go/Workaholics' AS URL,
2803 ISNULL(REPLACE(
2804 CONVERT(NVARCHAR(50),CAST((iss.total_range_scan_count + iss.total_singleton_lookup_count) AS MONEY),1),
2805 N'.00',N'')
2806 + N' uses of ' + i.db_schema_object_indexid + N'. '
2807 + REPLACE(CONVERT(NVARCHAR(50), CAST(iss.total_range_scan_count AS MONEY),1),N'.00',N'') + N' scans or seeks. '
2808 + REPLACE(CONVERT(NVARCHAR(50), CAST(iss.total_singleton_lookup_count AS MONEY), 1),N'.00',N'') + N' singleton lookups. '
2809 + N'OpStatsFactor=' + CAST(((((iss.total_range_scan_count + iss.total_singleton_lookup_count) * iss.total_reserved_MB))/1000000.) AS VARCHAR(256)),'') AS details,
2810 ISNULL(i.key_column_names_with_sort_order,'N/A') AS index_definition,
2811 ISNULL(i.secret_columns,'') AS secret_columns,
2812 i.index_usage_summary AS index_usage_summary,
2813 iss.index_size_summary AS index_size_summary
2814 FROM #IndexSanity i
2815 JOIN #IndexSanitySize iss ON i.index_sanity_id=iss.index_sanity_id
2816 WHERE (ISNULL(iss.total_range_scan_count,0) > 0 OR ISNULL(iss.total_singleton_lookup_count,0) > 0)
2817 AND NOT (@GetAllDatabases = 1 OR @Mode = 0)
2818 ORDER BY ((iss.total_range_scan_count + iss.total_singleton_lookup_count) * iss.total_reserved_MB) DESC;
2819
2820
2821 END
2822
2823 RAISERROR(N'Insert a row to help people find help', 0,1) WITH NOWAIT;
2824 IF DATEDIFF(MM, @VersionDate, GETDATE()) > 6
2825 BEGIN
2826 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2827 index_usage_summary, index_size_summary )
2828 VALUES ( -1, 0 ,
2829 'Outdated sp_BlitzIndex', 'sp_BlitzIndex is Over 6 Months Old', 'http://FirstResponderKit.org/',
2830 'Fine wine gets better with age, but this sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)) + ' is more like bad cheese. Time to get a new one.',
2831 N'',N'',N''
2832 );
2833 END
2834
2835 IF EXISTS(SELECT * FROM #BlitzIndexResults)
2836 BEGIN
2837 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2838 index_usage_summary, index_size_summary )
2839 VALUES ( -1, 0 ,
2840 'sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
2841 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
2842 N'From Your Community Volunteers' , N'http://FirstResponderKit.org' ,
2843 N''
2844 , N'',N''
2845 );
2846 END
2847 ELSE IF @Mode = 0 OR @GetAllDatabases = 1
2848 BEGIN
2849 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2850 index_usage_summary, index_size_summary )
2851 VALUES ( -1, 0 ,
2852 'sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
2853 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
2854 N'From Your Community Volunteers' , N'http://FirstResponderKit.org' ,
2855 N''
2856 , N'',N''
2857 );
2858 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2859 index_usage_summary, index_size_summary )
2860 VALUES ( 1, 0 ,
2861 'No Major Problems Found',
2862 'Nice Work!',
2863 '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.', '', ''
2864 );
2865
2866 END
2867 ELSE
2868 BEGIN
2869 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2870 index_usage_summary, index_size_summary )
2871 VALUES ( -1, 0 ,
2872 'sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
2873 CASE WHEN @GetAllDatabases = 1 THEN N'All Databases' ELSE N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) END,
2874 N'From Your Community Volunteers' , N'http://www.BrentOzar.com/BlitzIndex' ,
2875 N''
2876 , N'',N''
2877 );
2878 INSERT #BlitzIndexResults ( Priority, check_id, findings_group, finding, URL, details, index_definition,
2879 index_usage_summary, index_size_summary )
2880 VALUES ( 1, 0 ,
2881 'No Problems Found',
2882 'Nice job! Or more likely, you have a nearly empty database.',
2883 'http://FirstResponderKit.org', 'Time to go read some blog posts.', '', '', ''
2884 );
2885
2886 END
2887
2888 RAISERROR(N'Returning results.', 0,1) WITH NOWAIT;
2889
2890 /*Return results.*/
2891 IF @GetAllDatabases = 1
2892 BEGIN
2893
2894 SELECT Priority, ISNULL(br.findings_group,N'') +
2895 CASE WHEN ISNULL(br.finding,N'') <> N'' THEN N': ' ELSE N'' END
2896 + br.finding AS [Finding],
2897 br.[database_name] AS [Database Name],
2898 br.details AS [Details: schema.table.index(indexid)],
2899 br.index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
2900 ISNULL(br.secret_columns,'') AS [Secret Columns],
2901 br.index_usage_summary AS [Usage],
2902 br.index_size_summary AS [Size],
2903 COALESCE(br.more_info,sn.more_info,'') AS [More Info],
2904 br.URL,
2905 COALESCE(br.create_tsql,ts.create_tsql,'') AS [Create TSQL]
2906 FROM #BlitzIndexResults br
2907 LEFT JOIN #IndexSanity sn ON
2908 br.index_sanity_id=sn.index_sanity_id
2909 LEFT JOIN #IndexCreateTsql ts ON
2910 br.index_sanity_id=ts.index_sanity_id
2911 WHERE br.check_id IN (0, 1, 11, 22, 43, 68, 50, 60, 61, 62, 63, 64, 65)
2912 ORDER BY Priority, br.findings_group, br.finding, br.database_name ASC, [check_id] ASC, blitz_result_id ASC;
2913
2914 END
2915 ELSE
2916 SELECT Priority, ISNULL(br.findings_group,N'') +
2917 CASE WHEN ISNULL(br.finding,N'') <> N'' THEN N': ' ELSE N'' END
2918 + br.finding AS [Finding],
2919
2920 br.details AS [Details: schema.table.index(indexid)],
2921 br.index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
2922 ISNULL(br.secret_columns,'') AS [Secret Columns],
2923 br.index_usage_summary AS [Usage],
2924 br.index_size_summary AS [Size],
2925 COALESCE(br.more_info,sn.more_info,'') AS [More Info],
2926 br.URL,
2927 COALESCE(br.create_tsql,ts.create_tsql,'') AS [Create TSQL]
2928 FROM #BlitzIndexResults br
2929 LEFT JOIN #IndexSanity sn ON
2930 br.index_sanity_id=sn.index_sanity_id
2931 LEFT JOIN #IndexCreateTsql ts ON
2932 br.index_sanity_id=ts.index_sanity_id
2933 ORDER BY Priority, br.findings_group, br.finding, br.database_name ASC, [check_id] ASC, blitz_result_id ASC;
2934
2935
2936
2937 END; /* End @Mode=0 or 4 (diagnose)*/
2938 ELSE IF @Mode=1 /*Summarize*/
2939 BEGIN
2940 --This mode is to give some overall stats on the database.
2941 RAISERROR(N'@Mode=1, we are summarizing.', 0,1) WITH NOWAIT;
2942
2943 SELECT
2944 CAST((COUNT(*)) AS NVARCHAR(256)) AS [Number Objects],
2945 CAST(CAST(SUM(sz.total_reserved_MB)/
2946 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [All GB],
2947 CAST(CAST(SUM(sz.total_reserved_LOB_MB)/
2948 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [LOB GB],
2949 CAST(CAST(SUM(sz.total_reserved_row_overflow_MB)/
2950 1024. AS NUMERIC(29,1)) AS NVARCHAR(500)) AS [Row Overflow GB],
2951 CAST(SUM(CASE WHEN index_id=1 THEN 1 ELSE 0 END)AS NVARCHAR(50)) AS [Clustered Tables],
2952 CAST(SUM(CASE WHEN index_id=1 THEN sz.total_reserved_MB ELSE 0 END)
2953 /1024. AS NUMERIC(29,1)) AS [Clustered Tables GB],
2954 SUM(CASE WHEN index_id NOT IN (0,1) THEN 1 ELSE 0 END) AS [NC Indexes],
2955 CAST(SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
2956 /1024. AS NUMERIC(29,1)) AS [NC Indexes GB],
2957 CASE WHEN SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) > 0 THEN
2958 CAST(SUM(CASE WHEN index_id IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
2959 / SUM(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END) AS NUMERIC(29,1))
2960 ELSE 0 END AS [ratio table: NC Indexes],
2961 SUM(CASE WHEN index_id=0 THEN 1 ELSE 0 END) AS [Heaps],
2962 CAST(SUM(CASE WHEN index_id=0 THEN sz.total_reserved_MB ELSE 0 END)
2963 /1024. AS NUMERIC(29,1)) AS [Heaps GB],
2964 SUM(CASE WHEN index_id IN (0,1) AND partition_key_column_name IS NOT NULL THEN 1 ELSE 0 END) AS [Partitioned Tables],
2965 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],
2966 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],
2967 SUM(CASE WHEN filter_definition <> '' THEN 1 ELSE 0 END) AS [Filtered Indexes],
2968 SUM(CASE WHEN is_indexed_view=1 THEN 1 ELSE 0 END) AS [Indexed Views],
2969 MAX(total_rows) AS [Max Row Count],
2970 CAST(MAX(CASE WHEN index_id IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
2971 /1024. AS NUMERIC(29,1)) AS [Max Table GB],
2972 CAST(MAX(CASE WHEN index_id NOT IN (0,1) THEN sz.total_reserved_MB ELSE 0 END)
2973 /1024. AS NUMERIC(29,1)) AS [Max NC Index GB],
2974 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 1024 THEN 1 ELSE 0 END) AS [Count Tables > 1GB],
2975 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 10240 THEN 1 ELSE 0 END) AS [Count Tables > 10GB],
2976 SUM(CASE WHEN index_id IN (0,1) AND sz.total_reserved_MB > 102400 THEN 1 ELSE 0 END) AS [Count Tables > 100GB],
2977 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 1024 THEN 1 ELSE 0 END) AS [Count NCs > 1GB],
2978 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 10240 THEN 1 ELSE 0 END) AS [Count NCs > 10GB],
2979 SUM(CASE WHEN index_id NOT IN (0,1) AND sz.total_reserved_MB > 102400 THEN 1 ELSE 0 END) AS [Count NCs > 100GB],
2980 MIN(create_date) AS [Oldest Create Date],
2981 MAX(create_date) AS [Most Recent Create Date],
2982 MAX(modify_date) AS [Most Recent Modify Date],
2983 1 AS [Display Order]
2984 FROM #IndexSanity AS i
2985 --left join here so we don't lose disabled nc indexes
2986 LEFT JOIN #IndexSanitySize AS sz
2987 ON i.index_sanity_id=sz.index_sanity_id
2988 UNION ALL
2989 SELECT N'Database ' + QUOTENAME(@DatabaseName) + N' as of ' + CONVERT(NVARCHAR(16),GETDATE(),121) ,
2990 N'sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
2991 N'From Your Community Volunteers' ,
2992 N'http://FirstResponderKit.org' ,
2993 N'',
2994 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
2995 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
2996 NULL,0 AS display_order
2997 ORDER BY [Display Order] ASC
2998 OPTION (RECOMPILE);
2999
3000 END /* End @Mode=1 (summarize)*/
3001 ELSE IF @Mode=2 /*Index Detail*/
3002 BEGIN
3003 --This mode just spits out all the detail without filters.
3004 --This supports slicing AND dicing in Excel
3005 RAISERROR(N'@Mode=2, here''s the details on existing indexes.', 0,1) WITH NOWAIT;
3006
3007 SELECT database_name AS [Database Name],
3008 [schema_name] AS [Schema Name],
3009 [object_name] AS [Object Name],
3010 ISNULL(index_name, '') AS [Index Name],
3011 CAST(index_id AS VARCHAR(10))AS [Index ID],
3012 db_schema_object_indexid AS [Details: schema.table.index(indexid)],
3013 CASE WHEN index_id IN ( 1, 0 ) THEN 'TABLE'
3014 ELSE 'NonClustered'
3015 END AS [Object Type],
3016 index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
3017 ISNULL(LTRIM(key_column_names_with_sort_order), '') AS [Key Column Names With Sort],
3018 ISNULL(count_key_columns, 0) AS [Count Key Columns],
3019 ISNULL(include_column_names, '') AS [Include Column Names],
3020 ISNULL(count_included_columns,0) AS [Count Included Columns],
3021 ISNULL(secret_columns,'') AS [Secret Column Names],
3022 ISNULL(count_secret_columns,0) AS [Count Secret Columns],
3023 ISNULL(partition_key_column_name, '') AS [Partition Key Column Name],
3024 ISNULL(filter_definition, '') AS [Filter Definition],
3025 is_indexed_view AS [Is Indexed View],
3026 is_primary_key AS [Is Primary Key],
3027 is_XML AS [Is XML],
3028 is_spatial AS [Is Spatial],
3029 is_NC_columnstore AS [Is NC Columnstore],
3030 is_CX_columnstore AS [Is CX Columnstore],
3031 is_disabled AS [Is Disabled],
3032 is_hypothetical AS [Is Hypothetical],
3033 is_padded AS [Is Padded],
3034 fill_factor AS [Fill Factor],
3035 is_referenced_by_foreign_key AS [Is Reference by Foreign Key],
3036 last_user_seek AS [Last User Seek],
3037 last_user_scan AS [Last User Scan],
3038 last_user_lookup AS [Last User Lookup],
3039 last_user_update AS [Last User Update],
3040 total_reads AS [Total Reads],
3041 user_updates AS [User Updates],
3042 reads_per_write AS [Reads Per Write],
3043 index_usage_summary AS [Index Usage],
3044 sz.partition_count AS [Partition Count],
3045 sz.total_rows AS [Rows],
3046 sz.total_reserved_MB AS [Reserved MB],
3047 sz.total_reserved_LOB_MB AS [Reserved LOB MB],
3048 sz.total_reserved_row_overflow_MB AS [Reserved Row Overflow MB],
3049 sz.index_size_summary AS [Index Size],
3050 sz.total_row_lock_count AS [Row Lock Count],
3051 sz.total_row_lock_wait_count AS [Row Lock Wait Count],
3052 sz.total_row_lock_wait_in_ms AS [Row Lock Wait ms],
3053 sz.avg_row_lock_wait_in_ms AS [Avg Row Lock Wait ms],
3054 sz.total_page_lock_count AS [Page Lock Count],
3055 sz.total_page_lock_wait_count AS [Page Lock Wait Count],
3056 sz.total_page_lock_wait_in_ms AS [Page Lock Wait ms],
3057 sz.avg_page_lock_wait_in_ms AS [Avg Page Lock Wait ms],
3058 sz.total_index_lock_promotion_attempt_count AS [Lock Escalation Attempts],
3059 sz.total_index_lock_promotion_count AS [Lock Escalations],
3060 sz.data_compression_desc AS [Data Compression],
3061 i.create_date AS [Create Date],
3062 i.modify_date AS [Modify Date],
3063 more_info AS [More Info],
3064 1 AS [Display Order]
3065 FROM #IndexSanity AS i --left join here so we don't lose disabled nc indexes
3066 LEFT JOIN #IndexSanitySize AS sz ON i.index_sanity_id = sz.index_sanity_id
3067 ORDER BY [Database Name], [Schema Name], [Object Name], [Index ID]
3068 OPTION (RECOMPILE);
3069
3070
3071
3072 END /* End @Mode=2 (index detail)*/
3073 ELSE IF @Mode=3 /*Missing index Detail*/
3074 BEGIN
3075 SELECT
3076 database_name AS [Database],
3077 [schema_name] AS [Schema],
3078 table_name AS [Table],
3079 CAST((magic_benefit_number/@DaysUptime) AS BIGINT)
3080 AS [Magic Benefit Number],
3081 missing_index_details AS [Missing Index Details],
3082 avg_total_user_cost AS [Avg Query Cost],
3083 avg_user_impact AS [Est Index Improvement],
3084 user_seeks AS [Seeks],
3085 user_scans AS [Scans],
3086 unique_compiles AS [Compiles],
3087 equality_columns AS [Equality Columns],
3088 inequality_columns AS [Inequality Columns],
3089 included_columns AS [Included Columns],
3090 index_estimated_impact AS [Estimated Impact],
3091 create_tsql AS [Create TSQL],
3092 more_info AS [More Info],
3093 1 AS [Display Order]
3094 FROM #MissingIndexes
3095 /* Minimum benefit threshold = 100k/day of uptime */
3096 WHERE (magic_benefit_number/@DaysUptime) >= 100000
3097 UNION ALL
3098 SELECT
3099 N'sp_BlitzIndex (TM) v' + @Version + ' as of ' + CAST(CONVERT(DATETIME, @VersionDate, 102) AS VARCHAR(100)),
3100 N'From Your Community Volunteers' ,
3101 N'http://FirstResponderKit.org' ,
3102 100000000000,
3103 N'',
3104 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
3105 NULL, 0 AS display_order
3106 ORDER BY [Display Order] ASC, [Magic Benefit Number] DESC
3107
3108 END /* End @Mode=3 (index detail)*/
3109END
3110END TRY
3111
3112BEGIN CATCH
3113 RAISERROR (N'Failure analyzing temp tables.', 0,1) WITH NOWAIT;
3114
3115 SELECT @msg = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
3116
3117 RAISERROR (@msg,
3118 @ErrorSeverity,
3119 @ErrorState
3120 );
3121
3122 WHILE @@trancount > 0
3123 ROLLBACK;
3124
3125 RETURN;
3126 END CATCH;
3127
3128GO