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