Reorganize or Rebuild Indexes
Rédigé par Sozezzo - - Aucun commentaireThis script remedy index fragmentation by reorganizing or rebuilding an index.
Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy
We are talking about the guidance which is:
•if a table has less than 10000 rows, to do nothing
•if an index has less than 1000 pages, to do nothing
•if the index has:
less than 5% logical fragmentation, to do nothing
between 5% and 30% logical fragmentation, reorganize it
more than 30% logical fragmentation, rebuild it
• New FILLFACTOR = 98%
Why do we fix something we do not need it?
We should start to delete unused indexes if it is possible: [see Unused indexes]
•Unused index, delete it, when :
- Nonclustered indexes
- Non-primary keys
- Non unique
- Non-unused (#Total read < 100 and #total write > 1000 )
- Hypothetical Index.
Alternative solutions:
1. We can buy an application for index monitoring and analysis.
2. We can change the configuration. ex: MIXED_PAGE_ALLOCATION
3. We can create better indexes.
4. We can use this nice solution of Michelle Ufford [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]
Select info about tables
-- Select info about tables SELECT db_ID() as database_id ,tbSchena1.NAME AS SchemaName ,tbTable1.NAME AS TableName ,tbPartition1.rows AS RowCounts ,SUM(tbAllocation1.used_pages) AS used_pages ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables AS tbTable1 INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id AND tbIndex1.index_id = tbPartition1.index_id INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id WHERE (tbTable1.NAME NOT LIKE 'dt%') AND (tbTable1.is_ms_shipped = 0) AND (tbIndex1.object_id > 255) GROUP BY tbTable1.NAME ,tbSchena1.NAME ,tbPartition1.rows HAVING (tbPartition1.rows > 10000)
Select info about indexes
-- Select info about indexes SELECT index_physical_stats.database_id ,tbSchema2.NAME AS ShemaName ,tbTable2.NAME AS TableName ,tbTable2.object_id AS table_id ,tbIndex2.NAME AS IndexName ,tbIndex2.index_id ,tbIndex2.type ,tbIndex2.type_desc ,SUM(tbAllocation2.used_pages) AS used_pages ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb ,index_usage_stats.user_updates AS 'TotalWrites' ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads' ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference' ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) THEN 'Nonclustered' ELSE '' END AS IndexType ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) AND tbIndex2.is_unique = 0 AND tbIndex2.is_primary_key = 0 THEN CASE WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates < 1000 THEN '' WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates >= 1000 THEN 'unused' ELSE 'used' END ELSE 'keep' END AS IndexStatus ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild' ELSE '' END END AS IndexToDo ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN tbIndex2.NAME IS NULL THEN CASE WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;' ELSE '' END ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL ' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH (' + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) ) +')' ELSE '?' END END END AS IndexSqlToFix FROM sys.partitions AS tbPartition2 INNER JOIN sys.indexes AS tbIndex2 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id INNER JOIN sys.schemas AS tbSchema2 INNER JOIN sys.tables AS tbTable2 ON tbSchema2.schema_id = tbTable2.schema_id ON tbPartition2.object_id = tbTable2.object_id AND tbIndex2.object_id = tbTable2.object_id INNER JOIN sys.allocation_units AS tbAllocation2 ON tbPartition2.partition_id = tbAllocation2.container_id INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats ON tbIndex2.object_id = index_usage_stats.object_id AND tbIndex2.is_disabled = 0 AND tbIndex2.index_id = index_usage_stats.index_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats ON index_physical_stats.database_id = index_usage_stats.database_id AND index_physical_stats.object_id = tbIndex2.object_id AND index_physical_stats.index_id = tbIndex2.index_id GROUP BY tbSchema2.NAME ,tbTable2.NAME ,tbTable2.object_id ,tbIndex2.NAME ,tbIndex2.index_id ,index_usage_stats.user_seeks ,index_usage_stats.user_scans ,index_usage_stats.user_lookups ,index_usage_stats.user_updates ,tbIndex2.type ,tbIndex2.type_desc ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,tbIndex2.is_padded ,tbIndex2.ignore_dup_key ,tbIndex2.fill_factor ,tbIndex2.allow_page_locks ,tbIndex2.allow_row_locks ,index_physical_stats.database_id ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count
Now, we put together both queries.
SELECT tbTableInfo.database_id , tbTableInfo.SchemaName, tbTableInfo.TableName, tbTableInfo.RowCounts , tbTableInfo.used_pages , tbIndexInfo.IndexName , tbIndexInfo.index_id , tbIndexInfo.[type_desc] , tbIndexInfo.used_pages AS Index_pages , tbIndexInfo.IndexSizeKb, tbIndexInfo.[TotalWrites] , tbIndexInfo.[TotalReads] , tbIndexInfo.[Difference] , tbIndexInfo.index_depth , tbIndexInfo.index_level , tbIndexInfo.fragment_count , tbIndexInfo.avg_fragmentation_in_percent , tbIndexInfo.avg_fragment_size_in_pages , tbIndexInfo.page_count , tbIndexInfo.IndexType , tbIndexInfo.IndexStatus , tbIndexInfo.IndexToDo , tbIndexInfo.IndexSqlToFix FROM ( SELECT db_ID() as database_id ,tbSchena1.NAME AS SchemaName ,tbTable1.NAME AS TableName ,tbPartition1.rows AS RowCounts ,SUM(tbAllocation1.used_pages) AS used_pages ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables AS tbTable1 INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id AND tbIndex1.index_id = tbPartition1.index_id INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id WHERE (tbTable1.NAME NOT LIKE 'dt%') AND (tbTable1.is_ms_shipped = 0) AND (tbIndex1.object_id > 255) GROUP BY tbTable1.NAME ,tbSchena1.NAME ,tbPartition1.rows HAVING (tbPartition1.rows > 10000) ) tbTableInfo INNER JOIN ( SELECT index_physical_stats.database_id ,tbSchema2.NAME AS ShemaName ,tbTable2.NAME AS TableName ,tbTable2.object_id AS table_id ,tbIndex2.NAME AS IndexName ,tbIndex2.index_id ,tbIndex2.type ,tbIndex2.type_desc ,SUM(tbAllocation2.used_pages) AS used_pages ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb ,index_usage_stats.user_updates AS 'TotalWrites' ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads' ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference' ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) THEN 'Nonclustered' ELSE '' END AS IndexType ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) AND tbIndex2.is_unique = 0 AND tbIndex2.is_primary_key = 0 THEN CASE WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates < 1000 THEN '' WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates >= 1000 THEN 'unused' ELSE 'used' END ELSE 'keep' END AS IndexStatus ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild' ELSE '' END END AS IndexToDo ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN tbIndex2.NAME IS NULL THEN CASE WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;' ELSE '' END ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL ' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH (' + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) ) +')' ELSE '?' END END END AS IndexSqlToFix FROM sys.partitions AS tbPartition2 INNER JOIN sys.indexes AS tbIndex2 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id INNER JOIN sys.schemas AS tbSchema2 INNER JOIN sys.tables AS tbTable2 ON tbSchema2.schema_id = tbTable2.schema_id ON tbPartition2.object_id = tbTable2.object_id AND tbIndex2.object_id = tbTable2.object_id INNER JOIN sys.allocation_units AS tbAllocation2 ON tbPartition2.partition_id = tbAllocation2.container_id INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats ON tbIndex2.object_id = index_usage_stats.object_id AND tbIndex2.is_disabled = 0 AND tbIndex2.index_id = index_usage_stats.index_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats ON index_physical_stats.database_id = index_usage_stats.database_id AND index_physical_stats.object_id = tbIndex2.object_id AND index_physical_stats.index_id = tbIndex2.index_id GROUP BY tbSchema2.NAME ,tbTable2.NAME ,tbTable2.object_id ,tbIndex2.NAME ,tbIndex2.index_id ,index_usage_stats.user_seeks ,index_usage_stats.user_scans ,index_usage_stats.user_lookups ,index_usage_stats.user_updates ,tbIndex2.type ,tbIndex2.type_desc ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,tbIndex2.is_padded ,tbIndex2.ignore_dup_key ,tbIndex2.fill_factor ,tbIndex2.allow_page_locks ,tbIndex2.allow_row_locks ,index_physical_stats.database_id ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count ) as tbIndexInfo ON tbTableInfo.TableName = tbIndexInfo.TableName AND tbTableInfo.SchemaName = tbIndexInfo.ShemaName WHERE IndexSqlToFix <> ''
It can be dangerous but if you want to automatize.
To be a safe script we add 5 seconds between each execute of script.
SET NOCOUNT ON DECLARE @tb TABLE (IndexSqlToFix NVARCHAR(MAX), avg_fragmentation_in_percent FLOAT) INSERT INTO @tb (IndexSqlToFix, avg_fragmentation_in_percent) SELECT IndexSqlToFix, avg_fragmentation_in_percent FROM ( SELECT db_ID() as database_id ,tbSchena1.NAME AS SchemaName ,tbTable1.NAME AS TableName ,tbPartition1.rows AS RowCounts ,SUM(tbAllocation1.used_pages) AS used_pages ,SUM(tbAllocation1.total_pages) * 8 AS TotalSpaceKB ,CAST(ROUND(SUM(tbAllocation1.total_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB ,SUM(tbAllocation1.used_pages) * 8 AS UsedSpaceKB ,CAST(ROUND(SUM(tbAllocation1.used_pages) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB ,(SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 AS UnusedSpaceKB ,CAST(ROUND((SUM(tbAllocation1.total_pages) - SUM(tbAllocation1.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM sys.tables AS tbTable1 INNER JOIN sys.indexes AS tbIndex1 ON tbTable1.object_id = tbIndex1.object_id INNER JOIN sys.partitions AS tbPartition1 ON tbIndex1.object_id = tbPartition1.object_id AND tbIndex1.index_id = tbPartition1.index_id INNER JOIN sys.allocation_units AS tbAllocation1 ON tbPartition1.partition_id = tbAllocation1.container_id LEFT OUTER JOIN sys.schemas AS tbSchena1 ON tbTable1.schema_id = tbSchena1.schema_id WHERE (tbTable1.NAME NOT LIKE 'dt%') AND (tbTable1.is_ms_shipped = 0) AND (tbIndex1.object_id > 255) GROUP BY tbTable1.NAME ,tbSchena1.NAME ,tbPartition1.rows HAVING (tbPartition1.rows > 10000) ) tbTableInfo INNER JOIN ( SELECT index_physical_stats.database_id ,tbSchema2.NAME AS ShemaName ,tbTable2.NAME AS TableName ,tbTable2.object_id AS table_id ,tbIndex2.NAME AS IndexName ,tbIndex2.index_id ,tbIndex2.type ,tbIndex2.type_desc ,SUM(tbAllocation2.used_pages) AS used_pages ,8 * SUM(tbAllocation2.used_pages) AS IndexSizeKb ,index_usage_stats.user_updates AS 'TotalWrites' ,index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups AS 'TotalReads' ,index_usage_stats.user_updates - (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) AS 'Difference' ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) THEN 'Nonclustered' ELSE '' END AS IndexType ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,CASE WHEN tbIndex2.type IN ( 2, 6, 7) AND tbIndex2.is_unique = 0 AND tbIndex2.is_primary_key = 0 THEN CASE WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates < 1000 THEN '' WHEN (index_usage_stats.user_seeks + index_usage_stats.user_scans + index_usage_stats.user_lookups) < 100 AND index_usage_stats.user_updates >= 1000 THEN 'unused' ELSE 'used' END ELSE 'keep' END AS IndexStatus ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 AND tbIndex2.type > 0 THEN 'reorganize' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'rebuild' ELSE '' END END AS IndexToDo ,CASE WHEN SUM(tbAllocation2.used_pages) < 1000 THEN '' ELSE CASE WHEN tbIndex2.NAME IS NULL THEN CASE WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER TABLE ['+tbSchema2.NAME+'].['+tbTable2.NAME+'] REBUILD;' ELSE '' END ELSE CASE WHEN index_physical_stats.avg_fragmentation_in_percent < 5 THEN '' WHEN index_physical_stats.avg_fragmentation_in_percent BETWEEN 5 and 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REORGANIZE PARTITION = ALL ' WHEN index_physical_stats.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ['+tbIndex2.name+'] ON ['+tbSchema2.NAME+'].['+tbTable2.name+'] REBUILD PARTITION = ALL WITH (' + 'FILLFACTOR =' + CAST(98 AS VARCHAR(3) ) +')' ELSE '?' END END END AS IndexSqlToFix FROM sys.partitions AS tbPartition2 INNER JOIN sys.indexes AS tbIndex2 ON tbPartition2.index_id = tbIndex2.index_id AND tbPartition2.object_id = tbIndex2.object_id INNER JOIN sys.schemas AS tbSchema2 INNER JOIN sys.tables AS tbTable2 ON tbSchema2.schema_id = tbTable2.schema_id ON tbPartition2.object_id = tbTable2.object_id AND tbIndex2.object_id = tbTable2.object_id INNER JOIN sys.allocation_units AS tbAllocation2 ON tbPartition2.partition_id = tbAllocation2.container_id INNER JOIN sys.dm_db_index_usage_stats AS index_usage_stats ON tbIndex2.object_id = index_usage_stats.object_id AND tbIndex2.is_disabled = 0 AND tbIndex2.index_id = index_usage_stats.index_id INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS index_physical_stats ON index_physical_stats.database_id = index_usage_stats.database_id AND index_physical_stats.object_id = tbIndex2.object_id AND index_physical_stats.index_id = tbIndex2.index_id GROUP BY tbSchema2.NAME ,tbTable2.NAME ,tbTable2.object_id ,tbIndex2.NAME ,tbIndex2.index_id ,index_usage_stats.user_seeks ,index_usage_stats.user_scans ,index_usage_stats.user_lookups ,index_usage_stats.user_updates ,tbIndex2.type ,tbIndex2.type_desc ,tbIndex2.is_unique ,tbIndex2.is_primary_key ,tbIndex2.is_padded ,tbIndex2.ignore_dup_key ,tbIndex2.fill_factor ,tbIndex2.allow_page_locks ,tbIndex2.allow_row_locks ,index_physical_stats.database_id ,index_physical_stats.index_depth ,index_physical_stats.index_level ,index_physical_stats.avg_fragmentation_in_percent ,index_physical_stats.fragment_count ,index_physical_stats.avg_fragment_size_in_pages ,index_physical_stats.page_count ) as tbIndexInfo ON tbTableInfo.TableName = tbIndexInfo.TableName AND tbTableInfo.SchemaName = tbIndexInfo.ShemaName WHERE IndexToDo <> '' DECLARE @sql NVARCHAR(MAX) WHILE ((SELECT COUNT(*) FROM @tb as t) > 0) BEGIN SELECT TOP 1 @sql = IndexSqlToFix FROM @tb ORDER BY avg_fragmentation_in_percent DESC; DELETE FROM @tb WHERE IndexSqlToFix = @sql; RAISERROR (@sql, 0, 1) WITH NOWAIT; EXEC(@sql); WAITFOR DELAY '0:0:5' END
source :
http://sqlblog.com/
https://www.sqlservercentral.com/Forums/1873323/DMV-sysdmdbindexphysicalstats-Detailed-vs-Limited
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017
https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/tree/master
https://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer
https://www.apexsql.com/sql-tools-defrag.aspx