SQL 使用指令重建index

當少了SQL admin的時候，還是有些方法是可以執行我想要做到的事情

所以當我必須重作index的時候，可以利用指令的方式，搭配夜間排程來執行

1.首先將下面script存成檔案，命名為Execute_RebulidIndex.sql

  
USE [你的DB名稱]
GO
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE
              WHEN ps.avg_fragmentation_in_percent > 15
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END +
       CASE
              WHEN pc.partition_count > 1
              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
              ELSE ''
       END,
       avg_fragmentation_in_percent
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id                   ,
                      index_id                    ,
                      avg_fragmentation_in_percent,
                      partition_number
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id              = pc.object_id
          AND ix.index_id              = pc.index_id
   AND ix.name IS NOT NULL
order by avg_fragmentation_in_percent desc
GO

2.接著另存一個批次檔，命名為RebuildIndex.bat

呼叫SQL執行批次檔，並且LOG紀錄結果

    sqlcmd -i D:\Execute_RebulidIndex.sql -o D:\Log\RebulidIndex_Log.txt
    

3. 最後一步，排程設定固定每天夜間執行即可