Reorganize or Rebuild Indexes

Rédigé par M Sozezzo - - Aucun commentaire

This 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

 

 

 
 

Les commentaires sont fermés.