The database in question was in pretty bad shape; missing, unnecessary, and extremely fragmented indexes, missing primary and foreign keys, bad normalisation, and (the main reason I decided to look into it) undesired table locks during ETL operations. It's a 24/7 system, and live lookups are performed around the clock - data is imported early in the morning (when usage is at a minimum), which is when most of errors occur.
Amongst many other pre-emptive actions I've recently performed on the database, I decided to set up regular table index maintenance job to keep queries as quick as possible. Unfortunately the database is SQL Server Standard edition, and with it being a 24/7 system, we had to schedule some downtime to do a REBUILD of the indexes (the vast majority of the indexes on the primary application tables are at 90% or greater fragmentation), but I wanted to try to eliminate, or at least reduce any future downtime.
Some of you will know that an index can be rebuilt (on-line or offline, depending on the version of SQL Server you have installed) or reorganized (which can only be performed on-line). You might also know that you shouldn't just run these commands willy-nilly, and shouldn't rebuild or reorganize at all under certain conditions. This article on MSDN provides a decent overview of when you should perform a rebuild or reorganize operation, but I also found quite a bit of evidence out there that says performing a REBUILD or REORGANIZE against small indexes yields no noticeable performance improvement.
Armed with this information, I set out to find an already written table maintenance script, but failed to find anything that was robust enough for my needs. I wanted to be able to specify a number of parameters that determine which indexes are rebuilt, determine if an index can be rebuilt online or not, and perform optional logging.
The script below has been tested on SQL Server 2008 R2 and SQL Server 2012, and although I'm sure it'll work on previous versions of SQL Server, I would do some testing before running it.
---------------------------------------------------------------------------------------------------- -- Developer: Chris Sebok -- Date: 10th May 2013 -- Description: A stored procedure that rebuilds or reorganizes indexes intelligently depending on -- the SQL Server version. ---------------------------------------------------------------------------------------------------- /* -- Pre-run server configuration. Required for logging. EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'master..xp_cmdshell', 1 GO RECONFIGURE GO */ /* DECLARE @Database VARCHAR(100) SET @Database = 'MyDatabase' DECLARE @PageCountThreshold INT SET @PageCountThreshold = 1000 DECLARE @ReorganiseThreshold DECIMAL(5,2) SET @ReorganiseThreshold = 5.00 DECLARE @RebuildThreshold DECIMAL(5,2) SET @RebuildThreshold = 30.00 DECLARE @Debug BIT SET @Debug = 0 DECLARE @SafeMode BIT SET @SafeMode = 0 DECLARE @DefaultFillFactorIfZero INT SET @DefaultFillFactorIfZero = 90 DECLARE @LogPath VARCHAR(1000) SET @LogPath = 'D:\logs\MyDatabase\reindex_logs\' EXEC spReindex @Database, @LogPath, @Debug, @SafeMode, @PageCountThreshold, @ReorganiseThreshold, @RebuildThreshold, @DefaultFillFactorIfZero */ IF OBJECT_ID('spReindex') IS NOT NULL DROP PROC spReindex GO CREATE PROC spReindex ( @Database VARCHAR(100), -- Database to reindex @LogPath VARCHAR(1000), -- The path to store log files in. Must be prefixed with a back slash. @Debug BIT = 1, -- 0 = Index work is carried out. 1 = Index statements are not executed, but everything is logged as normal (for analysis) @SafeMode BIT = 1, -- 0 = Rebuilds indexes regardless of SQL Server version. 1 = Only rebuilds indexes if they can be rebuilt online. @PageCountThreshold INT = 1000, -- Number of paged in the index before a rebuild / reorganize occurs @ReorganiseThreshold DECIMAL(5,2) = 5.00, -- The lower percentage threshold for a REORGANIZE @RebuildThreshold DECIMAL(5,2) = 30.00, -- The lower percentage threshold for a REBUILD @DefaultFillFactorIfZero INT = 90 -- The default fillfactor percentage for an index if it's currently set to 0 (100%) ) AS -- Validation IF RTRIM(LTRIM(ISNULL(@Database, ''))) = '' BEGIN PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@Database can not be null or empty.' RETURN END IF ISNULL(@ReorganiseThreshold, 0) >= ISNULL(@RebuildThreshold, 0) OR ISNULL(@ReorganiseThreshold, 0) = 0 OR ISNULL(@RebuildThreshold, 0) = 0 BEGIN PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@ReorganiseThreshold must be less than @RebuildThreshold, and neither can be null or zero.' RETURN END IF RIGHT(@LogPath, 1) != '\' BEGIN PRINT CONVERT(VARCHAR, GETDATE(), 113) + ': ' + '@LogPath must end with a backslash (\), and be a valid path.' RETURN END -- Sproc variables DECLARE @OnlineRebuildEnabled BIT SET @OnlineRebuildEnabled = CASE WHEN CONVERT(VARCHAR, SERVERPROPERTY('Edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END DECLARE @LogFile VARCHAR(2000) SET @LogFile = @LogPath + CONVERT(VARCHAR, DATEPART(yyyy, GETDATE())) + '_' + RIGHT('00' + CONVERT(VARCHAR, DATEPART(MM, GETDATE())), 2) + '_spReindex.log' DECLARE @StartTime DATETIME DECLARE @EndTime DATETIME DECLARE @Difference INT DECLARE @Prefix VARCHAR(500) DECLARE @Instruction VARCHAR(8000) DECLARE @IndexStatement NVARCHAR(4000) -- Cursor variables DECLARE @Table VARCHAR(200), @Schema VARCHAR(200) DECLARE @IndexName VARCHAR(500), @Fragmentation DECIMAL(5, 2), @Fillfactor INT, @Pages INT, @Type VARCHAR(30) -- Manage log directory SET @Instruction = 'md ' + @LogPath EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; -- Start cursor operations DECLARE cTables CURSOR READ_ONLY FAST_FORWARD FOR SELECT s.name AS SchemaName, t.name AS TableName FROM sys.tables (nolock) t INNER JOIN sys.schemas (nolock) s ON t.schema_id = s.schema_id WHERE t.[type] = 'u' OPEN cTables FETCH NEXT FROM cTables INTO @Schema, @Table WHILE @@FETCH_STATUS = 0 BEGIN DECLARE cIndexes CURSOR READ_ONLY FAST_FORWARD FOR SELECT I.name, ROUND(S.avg_fragmentation_in_percent, 2), i.fill_factor, s.page_count, i.type_desc FROM sys.dm_db_index_physical_stats (DB_ID(@Database), OBJECT_ID(@Table), NULL, NULL, NULL) S INNER JOIN sys.indexes I (nolock) ON ( S.object_id = I.object_id AND S.index_id = I.index_id ) WHERE name IS NOT NULL -- Ignore heap indexes OPEN cIndexes FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type WHILE @@FETCH_STATUS = 0 BEGIN SET @Prefix = @Database + '.' + @Schema + '.' + @Table + '.' + @IndexName SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + ':: Index info: Type = ' + @Type + ', No. of Pages = ' + CONVERT(VARCHAR, @Pages) + ', Fragmentation = ' + CONVERT(VARCHAR, @Fragmentation) + '%, Fill Factor = ' + CONVERT(VARCHAR, @Fillfactor) + '%) >> ' + @LogFile EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; -- Make sure the index should be rebuilt or reorganized IF @Pages >= @PageCountThreshold AND @Fragmentation >= @ReorganiseThreshold BEGIN -- Rebuild or reorganize? IF @OnlineRebuildEnabled = 1 OR @SafeMode = 0 OR (@SafeMode = 1 AND @OnlineRebuildEnabled = 1) BEGIN -- Compile reindex command IF @Fragmentation >= @RebuildThreshold BEGIN -- REBUILD SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REBUILD WITH (' + CASE WHEN @OnlineRebuildEnabled = 1 THEN 'ONLINE = ON, ' ELSE '' END + 'FILLFACTOR = ' + CONVERT(VARCHAR, CASE WHEN @Fillfactor = 0 THEN @DefaultFillFactorIfZero ELSE @Fillfactor END) +');' END ELSE BEGIN -- REORGANIZE SET @IndexStatement = 'ALTER INDEX ' + @IndexName + ' ON ' + @Schema + '.' + @Table + ' REORGANIZE;' END SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Running: ' + @IndexStatement + ' >> ' + @LogFile EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; -- Perform reindex and time it SET @StartTime = GETDATE() IF @Debug = 0 BEGIN EXEC sp_executesql @IndexStatement END SET @EndTime = GETDATE() SET @Difference = DATEDIFF(ss, @StartTime, @EndTime) SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Finished reindex. Time elapsed ' + CONVERT(VARCHAR, @Difference) + ' seconds (' + CONVERT(VARCHAR, @Difference / 60) + ' minutes) >> ' + @LogFile EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; END ELSE BEGIN SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; unable to rebuild index ONLINE, SQL Enterprise required, or server must be taken offline. >> ' + @LogFile EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; END END ELSE BEGIN -- No need to index SET @Instruction = 'echo ' + CONVERT(VARCHAR, GETDATE(), 113) + ': ' + @Prefix + '; Index does not require reorganizing or rebuilding. >> ' + @LogFile EXEC master..xp_cmdshell @Instruction, 'NO_OUTPUT'; END FETCH NEXT FROM cIndexes INTO @IndexName, @Fragmentation, @Fillfactor, @Pages, @Type END CLOSE cIndexes DEALLOCATE cIndexes FETCH NEXT FROM cTables INTO @Schema, @Table END CLOSE cTables DEALLOCATE cTables GO
