Index Defrag Script Updates – Beta Testers Needed
Filed under: Miscellaneous, Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
Update: Wow! I’ve received a ton of responses to my request for beta testers. Thank you all! The SQL Community is really amazing. I’ll hopefully have the new version online in just a few days. 
Over the last few months, I’ve received many great comments and suggestions regarding my Index Defrag Script v3.0. I’ve just recently had time to implement most of these suggestions, plus some other things that I thought would be useful.
Here’s some of what you can look forward to shortly:
- Probably the single most requested feature, the new version of the script allows you to set a time limit for index defrags.
- There’s now a static table for managing the status of index defrags. This way, when your time limit is reached, you can pick up where you left off the next day, without the need to rescan indexes.
- There’s now an option to prioritize defrags by range scan counts, fragmentation level, or page counts.
- For those using partitioning, there is now an option to exclude the right-most populated partition from defrags (in theory, the one you’re writing to in a sliding-window scenario).
- Options such as page count limits and SORT_IN_TEMPDB are now parameterized.
- I’ve enhanced error logging.
- … and more!
Right now, I’m looking for a few folks who are willing to beta test the script. If you’re interested, please send me an e-mail at michelle at sqlfool dot com with the editions of SQL Server you can test this on (i.e. 2005 Standard, 2008 Enterprise, etc.).
Thank you!
Index Defrag Script Update
A couple of people pointed out to me that the stats rebuild feature in my defrag script will only complete for one database. Whoopsies! I’ve fixed the bug and updated the script in my previous post, so if you’re using my defrag script, please update it. Thanks to Derick and SuperCoolMoss for letting me know about the bug.
I’ve also received a couple of other requests for feature enhancements. I’m currently swamped at work and outside of work, but as soon as I get time, I will release the latest version. Realistically, it’ll probably be sometime after summer ends.
Thank you to everyone for their comments and suggestions!
Index Defrag Script, v3.0
Filed under: Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
I’ve just completed the latest version of my index defrag script! Here’s a brief list of the updates:
- Fixed a bug with the LOB logic. In the previous version, after a LOB was encountered, all subsequent indexes would be reorganized.
- Added support for stat rebuilds after the index defrag is complete (@rebuildStats)
- Added an exclusion list table (dba_indexDefragExclusion) to support index scheduling
- Modified logging to show which defrags are “in progress”; added columns to dba_indexDefragLog
- Added support for the defrag of the model and msdb databases
- Added @scanMode as a configurable parameter
So what can this index defrag script do? Well, for starters, you can:
- Schedule it to run with the default settings; it works “right out of the box” with no additional configuration necessary
- Run this one script from a centralized database for all databases on a server
- Run this script for a specific database or table
- Configure custom threshold limits and the point at which a rebuild should be performed (instead of a reorganize)
- Defrag individual partitions
- Log its actions and the duration of the defrag
- Run in “commands only” mode (@executeSQL = 0, @printCommands = 1)
- Customize performance parameters such as @maxDopRestriction and @defragDelay to minimize impact on the server
- Schedule specific indexes to only be defragged on weekends, or every other day
To use this last option, you need to add a record to the dba_indexDefragExclusion table. I think all of the columns are pretty self-explanatory except the [exclusionMask] column. The way this works is each day of the week is assigned a value:
1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday
Take a SUM of the values for the days that you want excluded. So if you want an index to only be defragged on weekends, you would add up Monday through Friday (2+4+8+16+32) and use a value of 62 for the exclusionMask column. For a little more information on how this works, check out my blog post on Bitwise Operations.
Please note: if you don’t insert any records into the dba_indexDefragExclusion table, by default all indexes will be defragged every run-time if they exceed the specified thresholds. This is normal behavior and may be perfectly fine in your environment. However, if the dba_indexDefragExclusion table does not exist, the script will fail.
I try to document each parameter within the code, so check the comments section in the script for a full list of parameters and what they do.
Special thanks to everyone who helped beta test this script!
Without further ado, the script:
/* Drop Table Scripts: Drop Table dbo.dba_indexDefragLog; Drop Table dbo.dba_indexDefragExclusion; */ IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE [name] In (N'dba_indexDefragLog', 'dba_indexDefragExclusion')) BEGIN CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , partitionNumber SMALLINT Not Null , fragmentation FLOAT Not Null , page_count INT Not Null , dateTimeStart DATETIME Not Null , dateTimeEnd DATETIME Null , durationSeconds INT Null CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ); PRINT 'dba_indexDefragLog Table Created'; CREATE TABLE dbo.dba_indexDefragExclusion ( databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , exclusionMask INT Not Null /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */ CONSTRAINT PK_indexDefragExclusion PRIMARY KEY CLUSTERED (databaseID, objectID, indexID) ); PRINT 'dba_indexDefragExclusion Table Created'; END ELSE RAISERROR('One or more tables already exist. Please drop or rename before proceeding.', 16, 0); IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 5.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold FLOAT = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @scanMode VARCHAR(10) = N'LIMITED' /* Options are LIMITED, SAMPLED, and DETAILED */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @printCommands BIT = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation BIT = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay CHAR(8) = '00:00:05' /* time to wait between defrag commands */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ , @rebuildStats BIT = 1 /* option to rebuild stats after completed index defrags */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for the current database Notes: CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @scanMode Specifies which scan mode to use to determine fragmentation levels. Options are: LIMITED - scans the parent level; quickest mode, recommended for most cases. SAMPLED - samples 1% of all data pages; if less than 10k pages, performs a DETAILED scan. DETAILED - scans all data pages. Use great care with this mode, as it can cause performance issues. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay Time to wait between defrag commands; gives the server a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments @rebuildStats Affects only statistics that need to be rebuilt 1 = rebuild stats 0 = do not rebuild stats Called by: SQL Agent Job or DBA Date Initials Version Description ---------------------------------------------------------------------------- 2007-12-18 MFU 1.0 Initial Release 2008-10-17 MFU 1.1 Added @defragDelay, CIX_temp_indexDefragList 2008-11-17 MFU 1.2 Added page_count to log table , added @printFragmentation option 2009-03-17 MFU 2.0 Provided support for centralized execution , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic 2009-06-18 MFU 3.0 Fixed bug in LOB logic, added @scanMode option , added support for stat rebuilds (@rebuildStats) , support model and msdb defrag , added columns to the dba_indexDefragLog table , modified logging to show "in progress" defrags , added defrag exclusion list (scheduling) ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; SET Quoted_Identifier ON; BEGIN IF @debugMode = 1 RAISERROR('Undusting the cogs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage VARCHAR(128) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000) , @rebuildStatsID INT , @rebuildStatsSQL NVARCHAR(1000) , @indexDefrag_id INT; /* Create our temporary tables */ CREATE TABLE #indexDefragList ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , defragStatus BIT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null ); CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) , scanStatus BIT , statsStatus BIT ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* Just a little validation... */ IF @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 10.0; IF @rebuildThreshold Not Between 0.00 And 100.0 SET @rebuildThreshold = 30.0; IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' SET @defragDelay = '00:00:05'; IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED') SET @scanMode = 'LIMITED'; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name , 0 -- not scanned yet for fragmentation , 0 -- statistics not yet updated FROM sys.databases WHERE name = IsNull(@DATABASE, name) And [name] Not In ('master', 'tempdb')-- exclude system databases And [STATE] = 0; -- state must be ONLINE IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList WHERE scanStatus = 0) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList WHERE scanStatus = 0; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO #indexDefragList SELECT database_id AS databaseID , QUOTENAME(DB_NAME(database_id)) AS 'databaseName' , [OBJECT_ID] AS objectID , index_id AS indexID , partition_number AS partitionNumber , avg_fragmentation_in_percent AS fragmentation , page_count , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */ , Null AS 'schemaName' , Null AS 'objectName' , Null AS 'indexName' FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode) WHERE avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent And index_level = 0 -- leaf-level nodes only, supports @scanMode OPTION (MaxDop 2); /* Keep track of which databases have already been scanned */ UPDATE #databaseList SET scanStatus = 1 WHERE databaseID = @databaseID; END CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(databaseID, objectID, indexID, partitionNumber); /* Delete any indexes from our to-do that are also in our exclusion list for today */ DELETE idl FROM #indexDefragList AS idl Join dbo.dba_indexDefragExclusion AS ide ON idl.databaseID = ide.databaseID And idl.objectID = ide.objectID And idl.indexID = ide.indexID WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) > 0; SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM #indexDefragList; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM #indexDefragList WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*) From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF @fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart; SET @indexDefrag_id = SCOPE_IDENTITY(); /* Execute our defrag! */ EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Update our log with our completion time */ UPDATE dbo.dba_indexDefragLog SET dateTimeEnd = @dateTimeEnd , durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd) WHERE indexDefrag_id = @indexDefrag_id; /* Just a little breather for the server */ WAITFOR Delay @defragDelay; /* Print if specified to do so */ IF @printCommands = 1 PRINT N'Executed: ' + @sqlCommand; END ELSE /* Looks like we're not executing, just printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!'); END IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait; /* Update our index defrag list so we know we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* Do we want to rebuild stats? */ IF @rebuildStats = 1 BEGIN WHILE Exists(SELECT TOP 1 * FROM #databaseList WHERE statsStatus = 0) BEGIN /* Build our SQL statement to update stats */ SELECT TOP 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 'Execute sp_updatestats;' , @rebuildStatsID = databaseID FROM #databaseList WHERE statsStatus = 0; SET @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Execute our stats update! */ EXECUTE SP_EXECUTESQL @rebuildStatsSQL; /* Keep track of which databases have been updated */ UPDATE #databaseList SET statsStatus = 1 WHERE databaseID = @rebuildStatsID; END; END; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; DROP TABLE #databaseList; DROP TABLE #processor; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END Go SET Quoted_Identifier OFF SET ANSI_Nulls ON Go
Primary Key vs Unique Constraint
Recently, I encountered a table that needed to have the definition of a clustered index altered. It just so happens that the clustered index and the primary key were one and the same, a pretty common occurrence. However, when we went to modify the index, it failed.
The following entry in Books Online for CREATE INDEX explains why:
If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.
Let’s test this, shall we?
/* Create a table with a clustered primary key */ CREATE TABLE dbo.myTable ( myID INT IDENTITY(1,1) Not Null , myDate SMALLDATETIME Not Null , myNumber INT Not Null CONSTRAINT CIX_myTable PRIMARY KEY CLUSTERED (myDate, myID) ); /* Insert some data */ INSERT INTO myTable SELECT '2009-01-01', 100 UNION All SELECT '2009-02-01', 200 UNION All SELECT '2009-01-05', 300; /* Try to alter the index - FAIL */ CREATE CLUSTERED INDEX CIX_myTable ON myTable(myID, myDate, myNumber) WITH (Drop_Existing = ON); /* Drop the clustered primary key */ ALTER TABLE dbo.myTable DROP CONSTRAINT CIX_myTable; /* Add a unique clustered index */ CREATE UNIQUE CLUSTERED INDEX CIX_myTable ON myTable(myDate, myID); /* Add a unique constraint */ ALTER TABLE myTable ADD CONSTRAINT Unique_myTable UNIQUE (myDate); /* Try to alter the index - SUCCESS */ CREATE CLUSTERED INDEX CIX_myTable ON myTable(myID, myDate, myNumber) WITH (Drop_Existing = ON); /* Add a primary key constraint */ ALTER TABLE myTable ADD CONSTRAINT PK_myTable PRIMARY KEY (myID, myDate); /* Try to alter the index - SUCCESS */ CREATE CLUSTERED INDEX CIX_myTable ON myTable(myID, myDate) WITH (Drop_Existing = ON); /* Clean-Up */ DROP TABLE myTable;
The only instance that actually fails is the PRIMARY KEY constraint. The unique clustered index is able to be modified successfully, even when a unique constraint is applied to the table. So either I’m misunderstanding BOL, or BOL is mistaken. Either way, I’m then left with the following question: is there any reason to actually use a primary key when a unique index serves the same purpose and offers greater flexibility?
Questions, comments, and explanations are welcome.
Overhead in Non-Unique Clustered Indexes
Filed under: Internals, Performance & Tuning, SQL 2008, Syndication
I’ve received a couple of questions regarding my article, Performance Considerations of Data Types, and the overhead associated with non-unique clustered indexes. I started to respond via e-mail, but my response was so long I decided to turn it into a blog post instead. 
I should start by clarifying that non-unique clustered indexes do not necessarily consume more space and overhead; it depends on the data stored. If you have duplicate clustered key values, the first instance of the value will be handled as though it were unique. Any subsequent values, however, will incur overhead to manage the uniquifier that SQL Server adds to maintain row uniqueness. This same overhead is also incurred in non-clustered indexes, too, adding to the overall expense of this approach.
I think it helps to actually look at the data, so let’s walk through a few different common scenarios. We’ll create a table with a unique clustered index, a table with a non-unique clustered index but no duplicates, and a table with duplicate key values.
Also, a little warning that I started to write this in SQL Server 2008, and since I’m on a 2008 kick, I decided to leave it that way. You can modify this pretty easily to work in 2005, if necessary.
USE sandbox; Go /* Unique, clustered index, no duplicate values */ CREATE TABLE dbo.uniqueClustered ( myDate DATE Not Null , myNumber INT Not Null , myColumn CHAR(995) Not Null ); CREATE UNIQUE CLUSTERED INDEX CIX_uniqueClustered ON dbo.uniqueClustered(myDate); /* Non-unique clustered index, but no duplicate values */ CREATE TABLE dbo.nonUniqueNoDups ( myDate DATE Not Null , myNumber INT Not Null , myColumn CHAR(995) Not Null ); CREATE CLUSTERED INDEX CIX_nonUniqueNoDups ON dbo.nonUniqueNoDups(myDate); /* Non-unique clustered index, duplicate values */ CREATE TABLE dbo.nonUniqueDuplicates ( myDate DATE Not Null , myNumber INT Not Null , myColumn CHAR(995) Not Null ); CREATE CLUSTERED INDEX CIX_nonUniqueDuplicates ON dbo.nonUniqueDuplicates(myDate);
I’m going to use the date data type in 2008 for my clustered index key. To ensure uniqueness for the first two tables, I’ll iterate through a few years’ worth of dates. This is typical of what you may see in a data mart, where you’d have one record with an aggregation of each day’s data. For the table with duplicate values, I’m going to insert the same date for each row.
/* Populate some test data */ SET NOCOUNT ON; DECLARE @myDate DATE = '1990-01-01' , @myNumber INT = 1; WHILE @myDate < '2010-01-01' BEGIN INSERT INTO dbo.uniqueClustered SELECT @myDate, @myNumber, 'data'; INSERT INTO dbo.nonUniqueNoDups SELECT @myDate, @myNumber, 'data'; INSERT INTO dbo.nonUniqueDuplicates SELECT '2009-01-01', @myNumber, 'data'; SELECT @myDate = DATEADD(DAY, 1, @myDate) , @myNumber += 1; END;
After running the above script, each table should have 7,305 records. This is obviously pretty small for a table, but it’ll serve our purposes. Now let’s take a look at the size of our tables:
/* Look at the details of our indexes */ /* Unique, clustered index, no duplicate values */ SELECT 'unique' AS 'type', page_count, avg_page_space_used_in_percent, record_count , min_record_size_in_bytes, max_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'uniqueClustered'), Null, Null, N'Detailed') WHERE index_level = 0 UNION All /* Non-unique clustered index, but no duplicate values */ SELECT 'non-unique, no dups', page_count, avg_page_space_used_in_percent, record_count , min_record_size_in_bytes, max_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'nonUniqueNoDups'), Null, Null, N'Detailed') WHERE index_level = 0 UNION All /* Non-unique clustered index, duplicate values */ SELECT 'duplicates', page_count, avg_page_space_used_in_percent, record_count , min_record_size_in_bytes, max_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'nonUniqueDuplicates'), Null, Null, N'Detailed') WHERE index_level = 0;
Here’s the results:
type page_count avg_page_space_used_in_percent record_count min_record_size_in_bytes max_record_size_in_bytes ------------------- -------------------- ------------------------------ -------------------- ------------------------ ------------------------ unique 914 99.8055102545095 7305 1009 1009 non-unique, no dups 914 99.8055102545095 7305 1009 1009 duplicates 1044 88.066036570299 7305 1009 1017
I want to point out a couple of things. First, there is no difference in the number of pages between the non-unique clustered index with no duplicates ([nonUniqueNoDups]) and the unique clustered index ([uniqueClustered]). The table with duplicate clustered key values, however, requires 14% more pages to store the same amount of data. Secondly, the [max_record_size_in_bytes] of the [nonUniqueDuplicates] table is 8 bytes more than that of the other two. We’ll discuss why in a minute.
Now let’s take a look at the actual data pages. For this, I’m going to use my page internals proc.
Execute dbo.dba_viewPageData_sp @databaseName = 'sandbox' , @tableName = 'sandbox.dbo.uniqueClustered' , @indexName = 'CIX_uniqueClustered';
I’m not going to post the entire results here, but I want to draw your attention to “m_slotCnt = 8″, which is near the top of the page. That means 8 records are stored on this page. Also, when you look near the end of the first record (Slot 0), you should see the following results:
Slot 0 Offset 0x60 Length 1009 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 1009 Memory Dump @0x00A9C060 00000000: 1000ee03 c3150b01 00000064 61746120 †..î.Ã......data [...] 000003F0: 00†††††††††††††††††††††††††††††††††††. Slot 0 Column 1 Offset 0x4 Length 3 Length (physical) 3 myDate = 1990-01-01 Slot 0 Column 2 Offset 0x7 Length 4 Length (physical) 4 myNumber = 1 Slot 0 Column 3 Offset 0xb Length 995 Length (physical) 995 myColumn = data
Now let’s look at the table that has a non-unique clustered index but no duplicates:
EXECUTE dbo.dba_viewPageData_sp @databaseName = 'sandbox' , @tableName = 'sandbox.dbo.nonUniqueNoDups' , @indexName = 'CIX_nonUniqueNoDups';
The m_slotCnt count is also 8 for this page. This time, let’s glance at the first and second records (Slot 0 and 1 respectively):
Slot 0 Offset 0x60 Length 1009 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 1009 Memory Dump @0x62FDC060 00000000: 1000ee03 c3150b01 00000064 61746120 †..î.Ã......data [...] 000003F0: 00†††††††††††††††††††††††††††††††††††. Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER = 0 Slot 0 Column 1 Offset 0x4 Length 3 Length (physical) 3 myDate = 1990-01-01 Slot 0 Column 2 Offset 0x7 Length 4 Length (physical) 4 myNumber = 1 Slot 0 Column 3 Offset 0xb Length 995 Length (physical) 995 myColumn = data Slot 1 Offset 0x451 Length 1009 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 1009 Memory Dump @0x62FDC451 00000000: 1000ee03 c4150b02 00000064 61746120 †..î.Ä......data [...] 000003F0: 00†††††††††††††††††††††††††††††††††††. Slot 1 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER = 0 Slot 1 Column 1 Offset 0x4 Length 3 Length (physical) 3 myDate = 1990-01-02 Slot 1 Column 2 Offset 0x7 Length 4 Length (physical) 4 myNumber = 2 Slot 1 Column 3 Offset 0xb Length 995 Length (physical) 995 myColumn = data
We now see a new addition to the row, “UNIQUIFIER = 0.” This is SQL Server’s way of managing row uniqueness internally. You’ll notice that, because the clustered key values are unique, the UNIQUIFIER is set to 0 and the row size is still 1009; for all intents and purposes, the UNIQUIFIER is not consuming any space.
Update: The DBCC God himself, Paul Randal, explained that non-dupes actually have a NULL UNIQUIFIER, which DBCC PAGE displays as a 0. Thanks for explaining, Paul! I wondered about that but chalked it up to SQL voodoo.
Now let’s look at our final case, a non-unique clustered index with duplicate key values:
EXECUTE dbo.dba_viewPageData_sp @databaseName = 'sandbox' , @tableName = 'sandbox.dbo.nonUniqueDuplicates' , @indexName = 'CIX_nonUniqueDuplicates';
Here’s where things get interesting. The m_slotCnt value is now 7, which means we’re now storing 1 record less per page. Let’s look at the details:
Slot 0 Offset 0x60 Length 1009 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 1009 Memory Dump @0x00A9C060 00000000: 1000ee03 df300b01 00000064 61746120 †..î.ß0.....data [...] 000003F0: 00†††††††††††††††††††††††††††††††††††. Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER = 0 Slot 0 Column 1 Offset 0x4 Length 3 Length (physical) 3 myDate = 2009-01-01 Slot 0 Column 2 Offset 0x7 Length 4 Length (physical) 4 myNumber = 1 Slot 0 Column 3 Offset 0xb Length 995 Length (physical) 995 myColumn = data Slot 1 Offset 0x451 Length 1017 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS Record Size = 1017 Memory Dump @0x00A9C451 00000000: 3000ee03 df300b02 00000064 61746120 †0.î.ß0.....data [...] 000003F0: 000100f9 03010000 00†††††††††††††††††...ù..... Slot 1 Column 0 Offset 0x3f5 Length 4 Length (physical) 4 UNIQUIFIER = 1 Slot 1 Column 1 Offset 0x4 Length 3 Length (physical) 3 myDate = 2009-01-01 Slot 1 Column 2 Offset 0x7 Length 4 Length (physical) 4 myNumber = 2 Slot 1 Column 3 Offset 0xb Length 995 Length (physical) 995 myColumn = data
The first record, Slot 0, looks exactly the same as in the previous table; the UNIQUIFIER is 0 and the row size is 1009. The second record (Slot 1), however, now has a UNIQUIFIER value of 1 and the row size is 1017. If you notice, the “Record Attributes” of Slot 1 are also different, with the addition of “VARIABLE_COLUMNS.” This is because the UNIQUIFIER is stored as a variable column. The extra 8 bytes of overhead break down to 4 bytes to store the UNIQUIFIER, 2 bytes to store the variable column offset, and 2 bytes to store the variable count. The tables we created used all fixed-length columns; you may notice some minor overhead differences if your table already contains variable columns.
To summarize, there is indeed a difference in the page structure between a unique clustered index and a non-unique clustered index; however, there’s only a possible performance and space impact when storing duplicate clustered key values. So there you go, more detail than you ever wanted to know about clustered indexes and uniqueness!
A Look at Missing Indexes
Filed under: Performance & Tuning, Syndication, T-SQL Scripts
Tim Ford (@SQLAgentMan) recently blogged about his Top 5 SQL Server Indexing Best Practices. I thought it was a good list, and it inspired this blog post. I’ve recently been doing a little index spring cleaning, and I thought some people may be interested in the process I go through. So, here it is… a journey through madness an overview of my general missing index process.
I start with my trusty dba_missingIndexStoredProc table. If this table sounds completely foreign to you, check out my post, Find Missing Indexes In Stored Procs. Basically, I have a process that runs every night, scanning the XML of every query plan on the server to find procs that are possibly missing indexes. I then log the details for later action.
So I take a look at my table, and I find 8 stored procedures that are possibly missing indexes. Clicking on the XML link will show me the logged query plan:
Procs With Missing Indexes
Right clicking on the “Missing Index” description will give me the details of the recommended index:
Missing Index Details
Here’s an example of what SQL Server will return for you:
/* Missing Index Details from ExecutionPlan2.sqlplan The Query Processor estimates that implementing the following index could improve the query cost by 85.7327%. */ /* USE [msdb] GO CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[sysjobhistory] ([job_id]) INCLUDE ([instance_id],[step_id],[sql_message_id],[sql_severity], [run_status],[run_date],[run_time],[run_duration],[operator_id_emailed], [operator_id_netsent],[operator_id_paged],[retries_attempted],[server]) GO */
I now compare the details of this proposed index to the missing index DMV suggestions, using this query:
SELECT t.name AS 'affected_table' , 'Create NonClustered Index IX_' + t.name + '_missing_' + CAST(ddmid.index_handle AS VARCHAR(10)) + ' On ' + ddmid.STATEMENT + ' (' + IsNull(ddmid.equality_columns,'') + CASE WHEN ddmid.equality_columns IS Not Null And ddmid.inequality_columns IS Not Null THEN ',' ELSE '' END + IsNull(ddmid.inequality_columns, '') + ')' + IsNull(' Include (' + ddmid.included_columns + ');', ';' ) AS sql_statement , ddmigs.user_seeks , ddmigs.user_scans , CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) AS 'est_impact' , ddmigs.last_user_seek FROM sys.dm_db_missing_index_groups AS ddmig INNER JOIN sys.dm_db_missing_index_group_stats AS ddmigs ON ddmigs.group_handle = ddmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS ddmid ON ddmig.index_handle = ddmid.index_handle INNER JOIN sys.tables AS t ON ddmid.OBJECT_ID = t.OBJECT_ID WHERE ddmid.database_id = DB_ID() --AND t.name = 'myTableName' ORDER BY CAST((ddmigs.user_seeks + ddmigs.user_scans) * ddmigs.avg_user_impact AS INT) DESC;
I usually find the data in both places, but not always. One reason why is because the missing index DMV will only store data since your last reboot. So if I’m taking a look at this DMV on Monday and I just rebooted on Sunday, I may not have enough history to give me meaningful recommendations. This is just something to be aware of.
What I’m looking for in this DMV is the number of user_seeks and the est_impact. Also, if I haven’t rebooted my server in a while, I take a look at last_user_seek so I can determine whether or not it’s still accurate.
Next, I take a look at my existing indexes using Kimberly Tripp’s sp_helpindex2 system stored proc. I use her proc instead of sp_helpindex because I need to see included columns.
If you’re wondering why I’m looking at existing indexes, the reason is because I’m looking for indexes that can be modified slightly to accommodate my missing index needs. By “modified slightly,” I mean that I’d only want to make a change to an existing index if it did not drastically change the size or composition of an index, i.e. adding one or two narrow columns as included columns. I do NOT mean making changes that double the size of your index; in those cases, you’d probably be better off creating a brand new index.
Looking at existing indexes is actually a pretty critical part of the puzzle. If I have a proc that only gets called a few times an hour and could benefit from a better index, I may not create that index if it means adding a wide, expensive index to a busy table. If I can make a small modification to an existing index, then there’s a greater chance I’ll make the change and cover my query.
At this point, I should have enough information to start making decisions. I was going to write out the path I normally take when making decisions, but I thought, “Hey! What a great time for a diagram.” So here you go:
Decision Path
Disclaimer: I’m *not* a Visio wizard, so if I butchered the use of certain symbols in my diagram, please let me know so I can a) fix it, and b) learn from it!
It’s hard to really put all of the decision paths into a single, small diagram like this. There’s a lot of variables that I’m not even touching here. But I think this is a fairly good “generic” representation of the path I take. When I hit an “end” process, it means I don’t create the missing index at this time. Maybe in the future, it’ll become necessary, but I prefer to err on the side of less indexes.
So there you have it, a brief look at my missing index process. Hopefully someone finds it helpful.
Filtered Indexes: What You Need To Know
Filed under: Performance & Tuning, SQL 2008, Syndication
Filtered indexes are probably my favorite feature in 2008. That’s saying a lot, since there are so many great new features to choose from. In this post, I want to explore a little about how filtered indexes work, how they can be applied, and some of the “gotchas” to be aware of.
First, for those of you who may not yet know about filtered indexes, allow me enlighten you. In short, filtered indexes allow you to create an index on a subset of data using a filtering predicate. Filters can only be applied to non-clustered indexes. The general syntax of a filtered index is:
CREATE NONCLUSTERED INDEX [index_name] ON [table_name] ([column_list]) Include ([column_list]) WHERE [filtered_criteria];
For our purposes, we’re going to be working with the Sales.SalesOrderDetail table in the AdventureWorks database. Let’s look at a specific example. Suppose we have a query that regularly searches on the [SpecialOfferID] column.
SELECT SalesOrderID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID;
We notice that there’s no covering index for this query by looking at the actual execution plan:
Query Plan - Clustered Scan
If this is a commonly executed query, then we’d probably want to toss an index on it. Before we get started, let’s take a look at what the distribution of values are on that column:
SELECT SpecialOfferID , COUNT(*) AS 'rows' FROM Sales.SalesOrderDetail GROUP BY SpecialOfferID ORDER BY COUNT(*) DESC;
Our distribution of values is:
SpecialOfferID rows -------------- ----------- 1 115884 2 3428 3 606 13 524 14 244 16 169 7 137 8 98 11 84 4 80 9 61 5 2
As you can see, [SpecialOfferID] = 1 accounts for 96% of our values. In 2005, we’d create an index that may look something like this:
CREATE NONCLUSTERED INDEX IX_Sales_SalesOrderDetail_SpecialOfferID ON Sales.SalesOrderDetail(SpecialOfferID) Include (SalesOrderID, LineTotal);
Now if we re-run our original query, this is what we see:
Indexed Query Plan
So we’re now performing a non-clustered index seek instead of a clustered index scan. Already this results in some pretty significant performance improvements. To see this, we’re going to use the INDEX query hint to force an index scan. We’re also going to use the DBCC command DROPCLEANBUFFERS, which will allow us to clear the buffer cache and better examine what’s happening with our IO.
SET STATISTICS IO ON; DBCC DropCleanBuffers; SELECT SalesOrderID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WITH (INDEX(PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID)) WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID; DBCC DropCleanBuffers; SELECT SalesOrderID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID; SET STATISTICS IO OFF;
Clustered Index Scan: Table 'SalesOrderDetail'. Scan count 1, logical reads 1240, physical reads 17, read-ahead reads 1242... NonClustered Index Seek: Table 'SalesOrderDetail'. Scan count 2, logical reads 30, physical reads 4, read-ahead reads 480...
As you can see, the non-clustered (NC) index seek performs quite a bit better. Now let’s create a filtered index and explore what happens:
CREATE NONCLUSTERED INDEX FIX_Sales_SalesOrderDetail_SpecialOfferID_Filtered ON Sales.SalesOrderDetail(SalesOrderID) Include (LineTotal) WHERE SpecialOfferID <> 1;
First, let’s look at the pages consumed by each index:
SELECT i.name, ddips.index_depth, ddips.index_level , ddips.page_count, ddips.record_count FROM sys.indexes AS i Join sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'Sales.SalesOrderDetail'), Null, Null, N'Detailed') AS ddips ON i.OBJECT_ID = ddips.OBJECT_ID And i.index_id = ddips.index_id WHERE i.name In ('IX_Sales_SalesOrderDetail_SpecialOfferID' , 'FIX_Sales_SalesOrderDetail_SpecialOfferID_Filtered' , 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID') AND ddips.index_level = 0;
name index_depth index_level page_count record_count ---------------------------------------------------------- ----------- ----------- ----------- -------------------- PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 3 0 1234 121317 IX_Sales_SalesOrderDetail_SpecialOfferID 3 0 480 121317 FIX_Sales_SalesOrderDetail_SpecialOfferID_Filtered 2 0 19 5433
If you scroll over, you’ll see that the clustered index consumes the most pages, naturally. The non-filtered NC index consumes less pages than the clustered index because it’s narrower; however, it still consumes more pages than the filtered index because it’s storing every data row. The filtered index, with only 5433 rows stored, is by far our smallest index, consuming 96% less space than our non-filtered NC index.
Because we’re using less space to store this index, we should also see an equivalent performance boost. Let’s verify that this is the case:
SET STATISTICS IO ON; DBCC DropCleanBuffers; SELECT SalesOrderID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WITH (INDEX(IX_Sales_SalesOrderDetail_SpecialOfferID)) WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID; DBCC DropCleanBuffers; SELECT SalesOrderID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID; SET STATISTICS IO OFF;
NonClustered Index Seek: Table 'SalesOrderDetail'. Scan count 2, logical reads 30, physical reads 4, read-ahead reads 480 Filtered Index Scan: Table 'SalesOrderDetail'. Scan count 1, logical reads 24, physical reads 2, read-ahead reads 22
Filtered Query Plan
As expected, we get the best results with our filtered index scan.
You’ll notice that I did *not* create the index on the [SpecialOfferID] column like I did in [IX_Sales_SalesOrderDetail_SpecialOfferID]. This is because my query doesn’t care what my [SpecialOfferID] value is, just as long as it’s not equal to 1. My non-filtered NC index was created on [SpecialOfferID] because it needed to navigate the B-TREE to find the records where [SpecialOfferID] <> 1. With my filtered index, the query optimizer knows that all of my records already meet the criteria, so doesn’t need to navigate through the index to find the matching results.
We could choose to include the [SpecialOfferID] data in our filtered index, but we’d most likely want to make it an included column rather than part of the index key. In fact, it’s important to note that, if I don’t add [SpecialOfferID] as an included column and I want to return it in the results, i.e.
SELECT SalesOrderID , SpecialOfferID , COUNT(*) AS 'CountOfLineItem' , SUM(LineTotal) AS 'SumOfLineTotal' FROM Sales.SalesOrderDetail WHERE SpecialOfferID <> 1 GROUP BY SalesOrderID , SpecialOfferID;
my filtered index will not be used and I will instead scan on the clustered index once more (assuming [IX_Sales_SalesOrderDetail_SpecialOfferID] does not exist). This is because the filtering criteria is not included anywhere on the actual index page. This is actually good news, in my opinion, since it allows you to create even leaner indexes. And like I already mentioned, if you do need the data returned, you can always add the filtering criteria as included columns.
What if you’re trying to find out whether or not an index is filtered, and what it’s filtered on? The sys.indexes catalog view has been updated in 2008 to include this information:
SELECT name, has_filter, filter_definition FROM sys.indexes WHERE name In ('IX_Sales_SalesOrderDetail_SpecialOfferID' , 'FIX_Sales_SalesOrderDetail_SpecialOfferID_Filtered' , 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID');
name has_filter filter_definition ------------------------------------------------------ ---------- ------------------------- FIX_Sales_SalesOrderDetail_SpecialOfferID_Filtered 1 ([SpecialOfferID]<>(1)) IX_Sales_SalesOrderDetail_SpecialOfferID 0 NULL PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 0 NULL
I personally recommend Kimberly Tripp’s system stored proc, sp_helpindex2. It returns a lot of good information about your indexes, such as included columns and filtering criteria.
That’s all I have for today. Hopefully, you now understand how powerful filtered indexes can be. When used properly, filtered indexes can use less space, consume less IO, and improve overall query performance.
Automated Index Defrag Script
Filed under: Performance & Tuning, SQL 2008, SQL Tips, T-SQL Scripts
Note: This script has been updated. You can find the latest version here: http://sqlfool.com/2009/06/index-defrag-script-v30/
So after much delay, here’s the latest and greatest version of my index defrag script.
A summary of the changes:
- Added support for centralized execution. Option to defrag indexes for a single database from another database, or for all non-system databases.
- Consolidated Enterprise and Standard versions of the script with new @editionCheck variable.
- Added parametrization for MaxDop restrictions during rebuilds; validates that the value does not exceed the actual number of processors on the server.
- Changed minimum fragmentation default value from 10 to 5 to match BOL recommendations.
- Limited defrags to objects with more than 8 pages.
- Added a debug option to give a little more insight into what’s happening and to assist with troubleshooting.
- Updated logic for handling partitions and LOBs.
And a couple of notes and explanations:
Don’t you know you can just pass NULL to sys.dm_db_index_physical_stats for the DatabaseID?
Yes, I realize you can do this. But I don’t want to defrag the system databases, i.e. tempdb, so I opted to handle it this way instead.
How long will this take?
It depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.
Where should I put this?
It’s up to you. If you have a database for items like centralized maintenance or scratch tables, that may be a good place for it. If you prefer, you could also put this in each individual database and call it locally, too. I would not put this in the master or msdb databases.
This is pretty close to a complete rewrite, so please let me know if you encounter any bugs. And now… the code!
IF Not Exists(SELECT [OBJECT_ID] FROM sys.tables WHERE name = N'dba_indexDefragLog') BEGIN -- Drop Table dbo.dba_indexDefragLog CREATE TABLE dbo.dba_indexDefragLog ( indexDefrag_id INT IDENTITY(1,1) Not Null , databaseID INT Not Null , databaseName NVARCHAR(128) Not Null , objectID INT Not Null , objectName NVARCHAR(128) Not Null , indexID INT Not Null , indexName NVARCHAR(128) Not Null , partitionNumber SMALLINT Not Null , fragmentation FLOAT Not Null , page_count INT Not Null , dateTimeStart DATETIME Not Null , durationSeconds INT Not Null CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id) ) PRINT 'dba_indexDefragLog Table Created'; END IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1 BEGIN DROP PROCEDURE dbo.dba_indexDefrag_sp; PRINT 'Procedure dba_indexDefrag_sp dropped'; END; Go CREATE PROCEDURE dbo.dba_indexDefrag_sp /* Declare Parameters */ @minFragmentation FLOAT = 5.0 /* in percent, will not defrag if fragmentation less than specified */ , @rebuildThreshold FLOAT = 30.0 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */ , @executeSQL BIT = 1 /* 1 = execute; 0 = print command only */ , @DATABASE VARCHAR(128) = Null /* Option to specify a database name; null will return all */ , @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName /* Option to specify a table name; null will return all */ , @onlineRebuild BIT = 1 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */ , @maxDopRestriction TINYINT = Null /* Option to restrict the number of processors for the operation; only in Enterprise */ , @printCommands BIT = 0 /* 1 = print commands; 0 = do not print commands */ , @printFragmentation BIT = 0 /* 1 = print fragmentation prior to defrag; 0 = do not print */ , @defragDelay CHAR(8) = '00:00:05' /* time to wait between defrag commands */ , @debugMode BIT = 0 /* display some useful comments to help determine if/where issues occur */ AS /********************************************************************************* Name: dba_indexDefrag_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Defrags all indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING. @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @executeSQL 1 = execute the SQL generated by this proc; 0 = print command only @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @tableName Specify if you only want to defrag indexes for a specific table, format = databaseName.schema.tableName; if not specified, all tables will be defragged. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @printCommands 1 = print commands to screen; 0 = do not print commands @printFragmentation 1 = print fragmentation to screen; 0 = do not print fragmentation @defragDelay time to wait between defrag commands; gives the server a little time to catch up @debugMode 1 = display debug comments; helps with troubleshooting 0 = do not display debug comments Called by: SQL Agent Job or DBA Date Initials Description ---------------------------------------------------------------------------- 2008-10-27 MFU Initial Release for public consumption 2008-11-17 MFU Added page-count to log table , added @printFragmentation option 2009-03-17 MFU Provided support for centralized execution, , consolidated Enterprise & Standard versions , added @debugMode, @maxDopRestriction , modified LOB and partition logic ********************************************************************************* Exec dbo.dba_indexDefrag_sp @executeSQL = 0 , @minFragmentation = 80 , @printCommands = 1 , @debugMode = 1 , @printFragmentation = 1 , @database = 'AdventureWorks' , @tableName = 'AdventureWorks.Sales.SalesOrderDetail'; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Quoted_Identifier ON; BEGIN IF @debugMode = 1 RAISERROR('Dusting off the spiderwebs and starting up...', 0, 42) WITH NoWait; /* Declare our variables */ DECLARE @objectID INT , @databaseID INT , @databaseName NVARCHAR(128) , @indexID INT , @partitionCount BIGINT , @schemaName NVARCHAR(128) , @objectName NVARCHAR(128) , @indexName NVARCHAR(128) , @partitionNumber SMALLINT , @partitions SMALLINT , @fragmentation FLOAT , @pageCount INT , @sqlCommand NVARCHAR(4000) , @rebuildCommand NVARCHAR(200) , @dateTimeStart DATETIME , @dateTimeEnd DATETIME , @containsLOB BIT , @editionCheck BIT , @debugMessage VARCHAR(128) , @updateSQL NVARCHAR(4000) , @partitionSQL NVARCHAR(4000) , @partitionSQL_Param NVARCHAR(1000) , @LOB_SQL NVARCHAR(4000) , @LOB_SQL_Param NVARCHAR(1000); /* Create our temporary tables */ CREATE TABLE #indexDefragList ( databaseID INT , databaseName NVARCHAR(128) , objectID INT , indexID INT , partitionNumber SMALLINT , fragmentation FLOAT , page_count INT , defragStatus BIT , schemaName NVARCHAR(128) Null , objectName NVARCHAR(128) Null , indexName NVARCHAR(128) Null ); CREATE TABLE #databaseList ( databaseID INT , databaseName VARCHAR(128) ); CREATE TABLE #processor ( [INDEX] INT , Name VARCHAR(128) , Internal_Value INT , Character_Value INT ); IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait; /* Just a little validation... */ IF @minFragmentation Not Between 0.00 And 100.0 SET @minFragmentation = 5.0; IF @rebuildThreshold Not Between 0.00 And 100.0 SET @rebuildThreshold = 30.0; IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]' SET @defragDelay = '00:00:05'; /* Make sure we're not exceeding the number of processors we have available */ INSERT INTO #processor EXECUTE XP_MSVER 'ProcessorCount'; IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor) SELECT @maxDopRestriction = Internal_Value FROM #processor; /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */ IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310) SET @editionCheck = 1 -- supports online rebuilds ELSE SET @editionCheck = 0; -- does not support online rebuilds IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait; /* Retrieve the list of databases to investigate */ INSERT INTO #databaseList SELECT database_id , name FROM sys.databases WHERE name = IsNull(@DATABASE, name) And database_id > 4 -- exclude system databases And [STATE] = 0; -- state must be ONLINE IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait; /* Loop through our list of databases */ WHILE (SELECT COUNT(*) FROM #databaseList) > 0 BEGIN SELECT TOP 1 @databaseID = databaseID FROM #databaseList; SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...'; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Determine which indexes to defrag using our user-defined parameters */ INSERT INTO #indexDefragList SELECT database_id AS databaseID , QUOTENAME(DB_NAME(database_id)) AS 'databaseName' , [OBJECT_ID] AS objectID , index_id AS indexID , partition_number AS partitionNumber , avg_fragmentation_in_percent AS fragmentation , page_count , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */ , Null AS 'schemaName' , Null AS 'objectName' , Null AS 'indexName' FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, N'Limited') WHERE avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > 8 -- ignore objects with less than 1 extent OPTION (MaxDop 1); DELETE FROM #databaseList WHERE databaseID = @databaseID; END CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList(databaseID, objectID, indexID, partitionNumber); SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!' FROM #indexDefragList; IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait; /* Begin our loop for defragging */ WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0 BEGIN IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait; /* Grab the most fragmented index first to defrag */ SELECT TOP 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count FROM #indexDefragList WHERE defragStatus = 0 ORDER BY fragmentation DESC; IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait; /* Look up index information */ SELECT @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexDefragList As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + ' And i.type > 0 And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10)); EXECUTE SP_EXECUTESQL @updateSQL; /* Grab our object names */ SELECT @objectName = objectName , @schemaName = schemaName , @indexName = indexName FROM #indexDefragList WHERE objectID = @objectID And indexID = @indexID And databaseID = @databaseID; IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait; /* Determine if the index is partitioned */ SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + ' And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT; IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait; /* Determine if the table contains LOBs */ SELECT @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT; IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait; /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */ IF @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1 BEGIN SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ IF @partitionCount > 1 SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10)); END; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 BEGIN /* Set online rebuild options; requires Enterprise Edition */ IF @onlineRebuild = 1 And @editionCheck = 1 SET @rebuildCommand = N' Rebuild With (Online = On'; ELSE SET @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ IF @maxDopRestriction IS Not Null And @editionCheck = 1 SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')'; ELSE SET @rebuildCommand = @rebuildCommand + N')'; SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; END; /* Are we executing the SQL? If so, do it */ IF @executeSQL = 1 BEGIN IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait; /* Grab the time for logging purposes */ SET @dateTimeStart = GETDATE(); EXECUTE SP_EXECUTESQL @sqlCommand; SET @dateTimeEnd = GETDATE(); /* Log our actions */ INSERT INTO dbo.dba_indexDefragLog ( databaseID , databaseName , objectID , objectName , indexID , indexName , partitionNumber , fragmentation , page_count , dateTimeStart , durationSeconds ) SELECT @databaseID , @databaseName , @objectID , @objectName , @indexID , @indexName , @partitionNumber , @fragmentation , @pageCount , @dateTimeStart , DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd); /* Just a little breather for the server */ WAITFOR Delay @defragDelay; /* Print if specified to do so */ IF @printCommands = 1 PRINT N'Executed: ' + @sqlCommand; END ELSE /* Looks like we're not executing, just printing the commands */ BEGIN IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait; IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!'); END IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait; /* Update our index defrag list so we know we've finished with that index */ UPDATE #indexDefragList SET defragStatus = 1 WHERE databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; END /* Do we want to output our fragmentation results? */ IF @printFragmentation = 1 BEGIN IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait; SELECT databaseID , databaseName , objectID , objectName , indexID , indexName , fragmentation , page_count FROM #indexDefragList; END; /* When everything is said and done, make sure to get rid of our temp table */ DROP TABLE #indexDefragList; DROP TABLE #databaseList; DROP TABLE #processor; IF @debugMode = 1 RAISERROR('DONE! Thank you for taking care of your indexes! :)', 0, 42) WITH NoWait; SET NOCOUNT OFF; RETURN 0 END Go
Thanks to my beta testers, @scoinva, @davidmtate, @jdanton, and @SuperCoolMoss!
Special thanks to SCM for keeping on me to finish this.
Happy Defragging!
Michelle
Source: http://sqlfool.com/2009/03/automated-index-defrag-script/
Index Fragmentation PotW Webcast – Now Available
Filed under: Miscellaneous, Performance & Tuning, Presentations, Syndication
The Pain-of-the-Week webcast I did last Thursday is now online! You can find it here: http://www.quest.com/common/registration.aspx?requestdefid=23092.
The topic of the webcast is index fragmentation: what is it, how to identify it, and how to fix it. You can find the materials for this webcast in my previous blog post. I’ll also be updating SQLServerPedia with the materials, hopefully tonight.
Thanks to the great feedback I’ve received, I’ll be doing another Pain-of-the-Week webcast, this time with Brent Ozar. More info to follow as we hammer out the specifics.
In a related note, I have updated my index defrag script. I’m just waiting for my awesome beta testers to let me know everything works for them. It works just fine on my servers (lol) but I don’t have access to any Standard editions, etc., so I just want to be extra sure before I unleash it on the SQL world. I should have it online tomorrow.
Source: http://sqlfool.com/2009/03/index-fragmentation-potw-webcast/
Find Missing Indexes in Stored Procs with T-SQL
Filed under: Performance & Tuning, SQL Tips, Syndication, T-SQL Scripts
This post should probably be called “The Power of Twitter.” I’ve said it before, I’ll say it again: I love Twitter. I mostly follow SQL Server people, so it’s become a great source of new information and help when I feel like banging my head against the wall when I get stumped.
So last week, Jonathan Kehayias (@jmkehayias) posted a link to a missing index script on the MSDN forums. Jonathan’s script is modified from something he put together for a question posed by Jeremiah Peschka (@peschkaj).
Jonathan’s script intrigued me. I’d never tried to search a query plan’s XML before, and it certainly presents some interesting possibilities. After dealing with a missing index in production a few weeks ago (caused by an index change), I thought it’d be a great idea to put a regular monitor in place.
Everything was going well until I ran into a problem where I couldn’t get the proc name returned. The results spanned numerous databases; I had the object_id and database_id, but I wanted to store the proc name instead. I tried several different methods, including sp_msforeachdb and sp_executeSQL, and while I had a working solution, it was a little more clunky than I liked. So I asked my awesome followers on Twitter for any tips and within minutes I had half a dozen responses. In the end, @MladenPrajdic solved my problem with a pretty simple solution: put ‘Use ?;’ at the start of my sp_msforeachdb statement. Thanks again, Mladen!
I’ve now had this process running on my server for a few days now, with good success. The stored procedure below will return the database name, proc name, and query plan XML for any stored proc with a missing index. This is a centralized proc that will store the results in a table for later action. If you click on the XML, you should see the actual query plan with the missing index details. Because this looks at cached query plans, your best bet is to run it fairly regularly (maybe daily) to increase your chances of catching any problem procs.
/* Create a stored procedure skeleton */ IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') IS Null BEGIN EXECUTE ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''') RAISERROR('Procedure dba_missingIndexStoredProc_sp created.', 10, 1); END; Go /* Drop our table if it already exists */ IF Exists(SELECT OBJECT_ID FROM sys.tables WHERE [name] = N'dba_missingIndexStoredProc') BEGIN DROP TABLE dbo.dba_missingIndexStoredProc PRINT 'dba_missingIndexStoredProc table dropped!'; END /* Create our table */ CREATE TABLE dbo.dba_missingIndexStoredProc ( missingIndexSP_id INT IDENTITY(1,1) Not Null , databaseName VARCHAR(128) Not Null , databaseID INT Not Null , objectName VARCHAR(128) Not Null , objectID INT Not Null , query_plan xml Not Null , executionDate SMALLDATETIME Not Null CONSTRAINT PK_missingIndexStoredProc PRIMARY KEY CLUSTERED(missingIndexSP_id) ); PRINT 'dba_missingIndexStoredProc Table Created'; /* Configure our settings */ SET ANSI_Nulls ON; SET Quoted_Identifier ON; Go ALTER PROCEDURE dbo.dba_missingIndexStoredProc_sp /* Declare Parameters */ @lastExecuted_inDays INT = 7 , @minExecutionCount INT = 7 , @logResults BIT = 1 , @displayResults BIT = 0 AS /********************************************************************************* Name: dba_missingIndexStoredProc_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Retrieves stored procedures with missing indexes in their cached query plans. @lastExecuted_inDays = number of days old the cached query plan can be to still appear in the results; the HIGHER the number, the longer the execution time. @minExecutionCount = minimum number of executions the cached query plan can have to still appear in the results; the LOWER the number, the longer the execution time. @logResults = store results in dba_missingIndexStoredProc @displayResults = return results to the caller Notes: This is not 100% guaranteed to catch all missing indexes in a stored procedure. It will only catch it if the stored proc's query plan is still in cache. Run regularly to help minimize the chance of missing a proc. Called by: DBA and/or SQL Agent Job Date User Description ---------------------------------------------------------------------------- 2009-03-02 MFU Initial Release for public consumption ********************************************************************************* Exec dbo.dba_missingIndexStoredProc_sp @lastExecuted_inDays = 30 , @minExecutionCount = 5 , @logResults = 1 , @displayResults = 1; *********************************************************************************/ SET NOCOUNT ON; SET XACT_Abort ON; SET Ansi_Padding ON; SET Ansi_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Null ON; SET Numeric_RoundAbort OFF; BEGIN /* Declare Variables */ DECLARE @currentDateTime SMALLDATETIME; SET @currentDateTime = GETDATE(); DECLARE @plan_handles TABLE ( plan_handle VARBINARY(64) Not Null ); CREATE TABLE #missingIndexes ( databaseID INT Not Null , objectID INT Not Null , query_plan xml Not Null CONSTRAINT PK_temp_missingIndexes PRIMARY KEY CLUSTERED ( databaseID, objectID ) ); BEGIN Try /* Perform some data validation */ IF @logResults = 0 And @displayResults = 0 BEGIN /* Log the fact that there were open transactions */ EXECUTE dbo.dba_logError_sp @errorType = 'app' , @app_errorProcedure = 'dba_missingIndexStoredProc_sp' , @app_errorMessage = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.' , @forceExit = 1 , @returnError = 1; END; BEGIN TRANSACTION; /* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */ INSERT INTO @plan_handles SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats WHERE last_execution_time > DATEADD(DAY, -@lastExecuted_inDays, @currentDateTime) And execution_count > @minExecutionCount; WITH xmlNameSpaces ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ) /* Retrieve our query plan's XML if there's a missing index */ INSERT INTO #missingIndexes SELECT deqp.[dbid] , deqp.objectid , deqp.query_plan FROM @plan_handles AS ph Cross Apply sys.dm_exec_query_plan(ph.plan_handle) AS deqp WHERE deqp.query_plan.exist('//MissingIndex') = 1 And deqp.objectid IS Not Null; /* Do we want to store the results of our process? */ IF @logResults = 1 BEGIN INSERT INTO dbo.dba_missingIndexStoredProc EXECUTE sp_msForEachDB 'Use ?; Select ''?'' , mi.databaseID , Object_Name(o.object_id) , o.object_id , mi.query_plan , GetDate() From sys.objects As o Join #missingIndexes As mi On o.object_id = mi.objectID Where databaseID = DB_ID();'; END /* We're not logging it, so let's display it */ ELSE BEGIN EXECUTE sp_msForEachDB 'Use ?; Select ''?'' , mi.databaseID , Object_Name(o.object_id) , o.object_id , mi.query_plan , GetDate() From sys.objects As o Join #missingIndexes As mi On o.object_id = mi.objectID Where databaseID = DB_ID();'; END; /* See above; this part will only work if we've logged our data. */ IF @displayResults = 1 And @logResults = 1 BEGIN SELECT * FROM dbo.dba_missingIndexStoredProc WHERE executionDate >= @currentDateTime; END; /* If you have an open transaction, commit it */ IF @@TRANCOUNT > 0 COMMIT TRANSACTION; END Try BEGIN Catch /* Whoops, there was an error... rollback! */ IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; /* Return an error message and log it */ EXECUTE dbo.dba_logError_sp; END Catch; /* Clean-Up! */ DROP TABLE #missingIndexes; SET NOCOUNT OFF; RETURN 0; END Go
Not know what “EXECUTE dbo.dba_logError_sp;” is doing? Check out my blog post on Error Handling in T-SQL.
HTH!
Michelle
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
