نگهداري ايندكس‌ها در اس‌كيوال سرور

پس از مدتي كه از شروع به كار يك سيستم مي‌گذرد، همانطور كه تعريف ايندكس‌هاي مفيد سرعت جستجوها را بالا مي‌برد، ايجاد fragmentation در آن‌ها نيز تاثير منفي در كارآيي خواهد داشت. به همين منظور نياز است هر از چندگاهي بررسي شود ميزان fragmentation ايندكس‌ها چقدر است. اگر اين ميزان بيش از 30 درصد بود توصيه شده است كه از دستور DBCC INDEXDEFRAG استفاده شود يا بازسازي مجدد ( rebuild ) ايندكس‌ها صورت گيرد.

يكي ديگر از امكانات dmv هاي اس كيوال سرورهاي 2005 به بعد، ارائه آمار ميزان fragmentation ايندكس‌ها است كه كوئري آن به صورت زير مي‌تواند باشد:

USE dbName;
SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME,
      SI.NAME AS INDEX_NAME,
      avg_fragmentation_in_percent AS FRAGMENT_PERCENT,
      DMV.record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS
      DMV
      LEFT OUTER JOIN SYS.INDEXES AS SI
           ON  DMV.OBJECT_ID = SI.OBJECT_ID
           AND DMV.INDEX_ID = SI.INDEX_ID
WHERE  avg_fragmentation_in_percent > 10
      AND index_type_desc IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
      AND DMV.record_count >= 2000
ORDER BY
      TABLE_NAME DESC

بايد در نظر داشت كه اجراي اين كوئري بر روي يك ديتابيس حجيم زمان‌بر بوده و احتمالا عملكرد سيستم را تحت تاثير قرار مي‌دهد. بنابراين استفاده از آن در خارج از ساعات كاري بايد مد نظر باشد. بازسازي ايندكس‌ها نيز به همين صورت است.

براي بازسازي تمامي ايندكس‌هاي يك ديتابيس مفروض مي‌توان از كوئري زير استفاده كرد:

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR 
FOR
   SELECT OBJECT_SCHEMA_NAME([object_id]) + '.' + NAME AS TableName
   FROM   sys.tables

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql = 'ALTER INDEX ALL ON ' + @TableName +
       ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3), @fillfactor) + ')'
  
   EXEC (@sql)
   FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor