Created
June 13, 2022 08:49
-
-
Save brovish/36292e35cd58bec537f1f717f3c647b4 to your computer and use it in GitHub Desktop.
Index Defragmantion Script Template from Expert Performance Indexing in SQL Server
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --Listing 9-27. Index Defragmantion Script Template | |
| DECLARE @MaxFragmentation TINYINT=30 | |
| ,@MinimumPages SMALLINT=1000 | |
| ,@SQL nvarchar(max) | |
| ,@ObjectName NVARCHAR(300) | |
| ,@IndexName NVARCHAR(300) | |
| ,@CurrentFragmentation DECIMAL(9, 6) | |
| DECLARE @FragmentationState TABLE | |
| ( | |
| SchemaName SYSNAME | |
| ,TableName SYSNAME | |
| ,object_id INT | |
| ,IndexName SYSNAME | |
| ,index_id INT | |
| ,page_count BIGINT | |
| ,avg_fragmentation_in_percent FLOAT | |
| ,avg_page_space_used_in_percent FLOAT | |
| ,type_desc VARCHAR(255) | |
| ) | |
| INSERT INTO @FragmentationState | |
| --Listing 9-28. Script to Collect Fragmenation Data | |
| SELECT | |
| s.name as SchemaName | |
| ,t.name as TableName | |
| ,t.object_id | |
| ,i.name as IndexName | |
| ,i.index_id | |
| ,x.page_count | |
| ,x.avg_fragmentation_in_percent | |
| ,x.avg_page_space_used_in_percent | |
| ,i.type_desc | |
| FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'SAMPLED') x | |
| INNER JOIN sys.tables t ON x.object_id = t.object_id | |
| INNER JOIN sys.schemas s ON t.schema_id = s.schema_id | |
| INNER JOIN sys.indexes i ON x.object_id = i.object_id AND x.index_id = i.index_id | |
| WHERE x.index_id > 0 | |
| AND alloc_unit_type_desc = 'IN_ROW_DATA' | |
| DECLARE INDEX_CURSE CURSOR LOCAL FAST_FORWARD FOR | |
| --Listing 9-29. Script to Identify Fragmented Indexes | |
| SELECT QUOTENAME(x.SchemaName)+'.'+QUOTENAME(x.TableName) | |
| ,CASE WHEN x.type_desc = 'CLUSTERED' THEN 'ALL' | |
| ELSE QUOTENAME(x.IndexName) END | |
| ,x.avg_fragmentation_in_percent | |
| FROM @FragmentationState x | |
| LEFT OUTER JOIN @FragmentationState y ON x.object_id = y.object_id AND y.index_id = 1 | |
| WHERE ( | |
| x.type_desc = 'CLUSTERED' | |
| AND y.type_desc = 'CLUSTERED' | |
| ) | |
| OR y.index_id IS NULL | |
| ORDER BY x.object_id | |
| ,x.index_id | |
| OPEN INDEX_CURSE | |
| WHILE 1=1 | |
| BEGIN | |
| FETCH NEXT FROM INDEX_CURSE INTO @ObjectName, @IndexName | |
| ,@CurrentFragmentation | |
| IF @@FETCH_STATUS <> 0 | |
| BREAK | |
| --Listing 9-30. Script to Build Index Defragmentation Statements | |
| SELECT @SQL='ALTER INDEX ' | |
| +@IndexName+' ON '+@ObjectName | |
| +CASE WHEN @CurrentFragmentation <= 30 | |
| THEN ' REORGANIZE;' | |
| ELSE ' REBUILD' | |
| +CASE WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Data Center%' | |
| OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Enterprise%' | |
| OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Developer%' | |
| THEN ' WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) ' | |
| END+';' | |
| END | |
| RAISERROR (@SQL, 10, 1) WITH NOWAIT; | |
| EXEC sp_ExecuteSQL @SQL | |
| END | |
| CLOSE INDEX_CURSE | |
| DEALLOCATE INDEX_CURSE |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment