Index reorg/rebuild script
Index fragmentation may be causing more I/O than necessary for efficient query processing. Fragmentation occurs as a result of inserting items into the middle of the index instead of appending them to the end. Inserting items into an index causes the page where the items are being inserted to “split” into two pages. The resulting fragmentation can make it harder for SQL Server to find the rows required for a given query. An index reorg can fix various performance issues. Before blindly running an index reorg, there are several things to consider.
Erik Darling discussed when to consider rebuilding or reorganizing an index – take his advice – he knows what he’s talking about! For the record, doing a reorg or rebuild of data stored on a SAN, a RAID array, or an SSD disk is unlikely to have an appreciable effect on performance, but hey, you’ve been warned – rebuild away!
We’re not talking about this kind of rebuild, ok?!
https://flic.kr/p/oQ5ih – wireheadinc
If you have a lot of page splits, as is the case with using a non-sequential ID value for the index key, and you’re storing your data on a standard hard drive, you may see performance decrease inversely with index fragmentation. The script below will automatically defragment indexes that meet certain requirements. Requirements include degree of fragmentation, and size of the index. You can specify when to switch between reorganizing an index versus completely rebuilding it.
If you do decide you need to rebuild indexes on an ongoing basis, and have determined that automating statistics updates isn’t cutting it, you might consider using this script. The script only updates row-store clustered, non-clustered, and XML indexes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 |
SET NOCOUNT ON; USE master; GO IF EXISTS ( SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.name = 'dbo' AND p.name = 'alter_index_reorg_rebuild' ) BEGIN DROP PROCEDURE dbo.alter_index_reorg_rebuild; END GO CREATE PROCEDURE dbo.alter_index_reorg_rebuild ( --The cutoff between doing a reorg and switching to rebuild (percentage) @reorg_fragmentation_limit DECIMAL(10,4) = 50.0 --The minimum fragmentation required before we do a reorg (percentage) , @reorg_fragmentation_floor DECIMAL(10,4) = 25.0 --The minimum size of an index that will be reorg'd , @minimum_page_count INT = 1000 --Provides an override to prevent ONLINE = ON index rebuild operations , @no_online_operations BIT = 0 --Provides a mechanism to override the instance-wide "Fill Factor (%)" option , @fill_factor_default_override TINYINT = NULL --set to 1 to only PRINT the ALTER INDEX statements (used for debugging!) , @debug_only BIT = 0 ) AS BEGIN /* dbo.alter_index_reorg_rebuild By: Max Vernon Compatability: SQL Server 2012+ This procedure will automatically defragment user-defined clustered, non-clustered, and xml indexes that meet certain criteria, controlled by the parameters passed into the procedure. */ SET NOCOUNT ON; DECLARE @ObjectName NVARCHAR(1000); DECLARE @IndexName SYSNAME; DECLARE @PadIndex NVARCHAR(50); DECLARE @FillFactor NVARCHAR(50); DECLARE @AllowPageLocks NVARCHAR(50); DECLARE @PageLocksCmdOn NVARCHAR(1000); DECLARE @PageLocksCmdOff NVARCHAR(1000); DECLARE @AllowRowLocks NVARCHAR(50); DECLARE @DataSpace NVARCHAR(1000); DECLARE @IgnoreDupeKey NVARCHAR(50); DECLARE @CompressOption NVARCHAR(50); DECLARE @PartitionNumber INT; DECLARE @NumberOfFragments INT; DECLARE @NumberOfPages INT; DECLARE @AvgFragmentSizeInPages DECIMAL(18,4); DECLARE @AvgFragmentationInPercent DECIMAL(10,4); DECLARE @OnlineOperations BIT; DECLARE @Rebuild BIT; DECLARE @Connector NVARCHAR(50); DECLARE @FillFactorDefault INT; DECLARE @IsUnique BIT; DECLARE @IsPrimary BIT; DECLARE @IndexType NVARCHAR(60); DECLARE @IsPartitioned BIT; DECLARE @IsClustered BIT; DECLARE @OffLineOnly BIT; DECLARE @msg NVARCHAR(MAX); --online index rebuild is only supported on "Enterprise" engines, which includes "Developer" and "Trial" SET @OnlineOperations = CASE WHEN CONVERT(INT, SERVERPROPERTY('EngineEdition')) = 3 THEN 1 ELSE 0 END; IF @no_online_operations = 1 SET @OnlineOperations = 0; --get the instance-wide default fill factor SELECT @FillFactorDefault = CONVERT(INT, c.value) FROM sys.configurations c WHERE c.name = 'fill factor (%)'; IF @FillFactorDefault = 0 SET @FillFactorDefault = 100; IF @fill_factor_default_override IS NOT NULL SET @FillFactorDefault = @fill_factor_default_override; DECLARE @cmd NVARCHAR(MAX); --create a forward, local-only, static cursor to loop through the index objects -- local indicates this is not visible server-wide -- forward_only indicates we don't need to be able to move the cursor backwards or to specific rows -- static makes a copy of the data in tempdb which is used as we page through rows DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR WITH OffLineOnlyIndexes AS /* these indexes can only be rebuilt offline (ie no ONLINE = ON parameter) */ ( SELECT o.object_id , i.index_id FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.columns c ON (i.type <> 1 AND ic.object_id = c.object_id AND ic.column_id = c.column_id) /* non-clustered indexes */ OR (i.type = 1 AND o.object_id = c.object_id) /* clustered indexes, include all columns */ INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE ( ty.name = 'text' OR ty.name = 'ntext' OR ty.name = 'image' OR ty.name = 'FILESTREAM' ) GROUP BY o.object_id , i.index_id ) SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name) , IndexName = QUOTENAME(i.name) , PadIndex = CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN @FillFactorDefault ELSE i.fill_factor END) , [AllowPageLocks] = 'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END , [AllowRowLocks] = 'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END , [DataSpace] = 'ON ' + QUOTENAME(ds.name) , IgnoreDupeKey = 'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc , PartitionNumber = p.partition_number , NumberOfFragments = ips.fragment_count , NumberOfPages = ips.page_count , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent , IndexType = i.type_desc , IsUnique = i.is_unique , IsPrimary = i.is_primary_key , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END , OffLineOnly = CASE WHEN COALESCE(ooi.object_id, 0) <> 0 THEN 1 ELSE 0 END FROM sys.indexes i WITH (NOLOCK) INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id LEFT JOIN OffLineOnlyIndexes ooi ON i.index_id = ooi.index_id AND o.object_id = ooi.object_id CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'SAMPLED') ips WHERE o.is_ms_shipped = 0 AND NOT ( o.type = 'TF' -- table valued function OR o.type = 'TT' -- table type OR o.type = 'SO' -- sequence object ) AND i.index_id > 0 AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND ( ds.type = 'FG' -- filegroup OR ds.type = 'PS' -- partition stream ) AND i.type_desc IN ( --we only support rebuilding/reorganizing these index types: 'CLUSTERED' , 'NONCLUSTERED' , 'XML' ) AND ips.index_level = 0 -- leaf-levels only AND ips.page_count > @minimum_page_count AND ips.fragment_count > 1 AND ips.avg_fragmentation_in_percent > @reorg_fragmentation_floor ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC; --start with the most fragmented indexes first OPEN cur; FETCH NEXT FROM cur INTO @ObjectName , @IndexName , @PadIndex , @FillFactor , @AllowPageLocks , @AllowRowLocks , @DataSpace , @IgnoreDupeKey , @CompressOption , @PartitionNumber , @NumberOfFragments , @NumberOfPages , @AvgFragmentSizeInPages , @AvgFragmentationInPercent , @IndexType , @IsUnique , @IsPrimary , @IsPartitioned , @IsClustered , @OffLineOnly; WHILE @@FETCH_STATUS = 0 BEGIN --here we generate a dynamic T-SQL statement used to perform the actual reorg/rebuild SET @cmd = ''; SET @cmd = @cmd + '/* '; SET @cmd = @cmd + 'NumberOfFragments: ' + CONVERT(VARCHAR(50), @NumberOfFragments) + ' '; SET @cmd = @cmd + 'NumberOfPages: ' + CONVERT(VARCHAR(50), @NumberOfPages) + ' '; SET @cmd = @cmd + 'AvgFragmentSizeInPages: ' + CONVERT(VARCHAR(50), @AvgFragmentSizeInPages) + ' '; SET @cmd = @cmd + 'AvgFragmentationInPercent: ' + CONVERT(VARCHAR(50), @AvgFragmentationInPercent) + ' '; SET @cmd = @cmd + '*/ '; SET @cmd = @cmd + 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + ''; IF (@AvgFragmentationInPercent >= @reorg_fragmentation_limit) OR (@AllowPageLocks = 'ALLOW_PAGE_LOCKS = OFF') SET @Rebuild = 1 ELSE SET @Rebuild = 0; IF @Rebuild = 1 SET @cmd = @cmd + ' REBUILD' ELSE SET @cmd = @cmd + ' REORGANIZE'; IF @IsPartitioned = 1 SET @cmd = @cmd + ' PARTITION = ' + CONVERT(NVARCHAR(50), @PartitionNumber); IF @Rebuild = 1 BEGIN SET @Connector = ' WITH ( '; SET @cmd = @cmd + @Connector + 'SORT_IN_TEMPDB = ON'; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; SET @cmd = @cmd + @Connector + 'MAXDOP = 1'; --limit the degree of parallelism to prevent the new index from being fragmented at the outset SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; IF @OnlineOperations = 1 AND @IsPartitioned = 0 AND @OffLineOnly = 0 AND @AllowPageLocks LIKE '% ON' BEGIN SET @cmd = @cmd + @Connector + 'ONLINE = ON'; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END IF @IsPartitioned = 0 BEGIN SET @cmd = @cmd + @Connector + @PadIndex; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END IF @IsPartitioned = 0 BEGIN SET @cmd = @cmd + @Connector + @FillFactor; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END IF @IsPartitioned = 0 BEGIN SET @cmd = @cmd + @Connector + @AllowPageLocks; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END IF @IsPartitioned = 0 BEGIN SET @cmd = @cmd + @Connector + @AllowRowLocks; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END IF @IsPrimary = 0 AND @IsUnique = 0 AND @IsPartitioned = 0 BEGIN SET @cmd = @cmd + @Connector + @IgnoreDupeKey; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; END SET @cmd = @cmd + @Connector + @CompressOption; SET @Connector = CHAR(13) + CHAR(10) + CHAR(9) + ', '; SET @cmd = @cmd + ' )' /* SET @cmd = @cmd + CHAR(13) + CHAR(10) + @DataSpace; --data space cannot be specified in ALTER INDEX, must use CREATE INDEX ... DROP EXISTING */ END IF @AllowPageLocks LIKE '% OFF' AND @Rebuild = 0 BEGIN --temporarily alter the index to allow page locks SET @PageLocksCmdOn = 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + ' SET (ALLOW_PAGE_LOCKS = ON);'; SET @PageLocksCmdOff = 'ALTER INDEX ' + @IndexName + ' ON ' + @ObjectName + ' SET (ALLOW_PAGE_LOCKS = OFF);'; SET @msg = '/* Temporarily setting ALLOW_PAGE_LOCKS ON to allow index reorg (' + @IndexName + ' ON ' + @ObjectName + '). */'; RAISERROR (@msg, 0, 1) WITH NOWAIT; SET @cmd = CHAR(9) + REPLACE(@cmd, CHAR(13) + CHAR(10), CHAR(13) + CHAR(10) + CHAR(9)); -- indent the rebuild command SET @cmd = 'BEGIN TRANSACTION BEGIN TRY ' + @PageLocksCmdOn + CHAR(13) + CHAR(10) + @cmd + CHAR(13) + CHAR(10) + CHAR(9) + @PageLocksCmdOff + ' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH '; END ELSE BEGIN SET @cmd = @cmd + '; '; END IF @debug_only = 1 BEGIN PRINT @cmd END ELSE BEGIN SET @msg = CASE WHEN @Rebuild = 1 THEN 'REBUILD' ELSE 'REORG' END + ' ' + @ObjectName + '.' + @IndexName; RAISERROR (@msg, 0, 1) WITH NOWAIT; EXEC sp_executesql @cmd; END FETCH NEXT FROM cur INTO @ObjectName , @IndexName , @PadIndex , @FillFactor , @AllowPageLocks , @AllowRowLocks , @DataSpace , @IgnoreDupeKey , @CompressOption , @PartitionNumber , @NumberOfFragments , @NumberOfPages , @AvgFragmentSizeInPages , @AvgFragmentationInPercent , @IndexType , @IsUnique , @IsPrimary , @IsPartitioned , @IsClustered , @OffLineOnly; END CLOSE cur; DEALLOCATE cur; END; GO --mark the stored proc as a "system" object so it can be called from the context of other databases. EXEC sys.sp_MS_marksystemobject 'dbo.alter_index_reorg_rebuild'; GO |
This script should work on all versions of SQL Server since SQL Server 2012. Let me know if you found this script useful, or if you have a comment or question about it.
Also, check out the rest of our SQL Server Tools!