SQLServerは、断片化率が高くなると、SQLが実行される際、パラレル処理されなくなり、極端に処理性能が低下します

ストレージを増設する前、12TBのストレージで、10TBのDBを運用していたので、1ヵ月に1回程度、SQLServerのDB Shrinkを実行していました。
その影響で、メインのテーブルとインデックスを中心に、断片化率が99%に達していました。

各テーブルとインデックスの断片化率は、こちらのSQLでチェックできます。


use （データベース名）
SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('（データベース名）'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10

use （データベース名）

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.fragment_count, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID('（データベース名）'), NULL, NULL, NULL, 'LIMITED') s

INNER JOIN sys.indexes i ON i.object_id = s.object_id

and i.index_id = s.index_id

WHERE s.avg_fragmentation_in_percent > 10

ビッグデータな上に、頻繁に機能に変更が加えられるシステムなので、処理コストの高いSQLが無いかは、定期的にチェックしていたのですが、断片化率はチェックしていませんでした。

断片化を解消すると、チューニングアドバイザによるインデックスと統計情報の抽出率が向上し、今まで抽出さていなかった、インデックスと統計情報が抽出されるようになりました。

また、週次処理が実行された後、特定の機能が極端に遅くなる現象が毎週発生し、その度に、チューニングアドバイザを実行し直したり、ヒント文で使用されるインデックスを固定したりしていましたが、断片化を解消してからは、週次処理後に特定のSQLが極端に遅くなるといった現象は、発生しなくなりました。

断片化を解消するには、テーブルとインデックスのリビルドが必要です。

・テーブルをリビルドするサンプルSQL


ALTER TABLE （スキーマ名）.（テーブル名） 
REBUILD PARTITION = ALL

ALTER TABLE （スキーマ名）.（テーブル名）

REBUILD PARTITION = ALL

・テーブルをPage圧縮でリビルドするサンプルSQL


ALTER TABLE （スキーマ名）.（テーブル名） 
REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE )

ALTER TABLE （スキーマ名）.（テーブル名）

REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE )

・インデックスをリビルドするサンプルSQL


ALTER INDEX all ON （スキーマ名）.（テーブル名） 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF)

ALTER INDEX all ON （スキーマ名）.（テーブル名）

REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF)

・インデックスをPage圧縮でリビルドするサンプルSQL


ALTER INDEX all ON （スキーマ名）.（テーブル名） 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION  = PAGE )

ALTER INDEX all ON （スキーマ名）.（テーブル名）

REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_PAGE_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )

Page圧縮は、I/O性能の低下はみられないのに、データ量が10分1になるので、ビッグデータを扱うDBでは必須です。

リビルドを実行しても、3割程度は断片化が解消されませんでした。
一旦、断片化してしまうと、断片化を完全に解消することは出来ない為、断片化させない工夫が必要なようです。

断片化が進んでいても、毎週1回、以下のSQLで断片化率3%以上のインデックスを抽出し、12時間ほどかけてリビルドするようになってからは、データベースのパフォーマンスは安定するようになりました。


use （対象のデータベース名）
select DB_NAME, ShemaName, TableName, name
from (
  SELECT DB_NAME(s.database_id) as DB_NAME,
  OBJECT_SCHEMA_NAME(i.[object_id]) as [ShemaName],
  t.name as TableName,
  OBJECT_NAME(s.object_id) as [IndexName], 
  i.name as name,
  s.avg_fragmentation_in_percent as avg_fragmentation_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID('（対象のデータベース名）'),
 NULL, NULL, NULL, 'LIMITED') s
  INNER JOIN sys.indexes i ON i.object_id = s.object_id
    and i.index_id = s.index_id
  INNER JOIN sys.tables t ON i.object_id = t.object_id
  WHERE s.avg_fragmentation_in_percent > 3
) as t
where name is not null
group by DB_NAME, ShemaName, TableName, name
order by DB_NAME, ShemaName, TableName, name

use （対象のデータベース名）

select DB_NAME, ShemaName, TableName, name

from (

SELECT DB_NAME(s.database_id) as DB_NAME,

OBJECT_SCHEMA_NAME(i.[object_id]) as [ShemaName],

t.name as TableName,

OBJECT_NAME(s.object_id) as [IndexName],

i.name as name,

s.avg_fragmentation_in_percent as avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID('（対象のデータベース名）'),

NULL, NULL, NULL, 'LIMITED') s

INNER JOIN sys.indexes i ON i.object_id = s.object_id

and i.index_id = s.index_id

INNER JOIN sys.tables t ON i.object_id = t.object_id

WHERE s.avg_fragmentation_in_percent > 3

) as t

where name is not null

group by DB_NAME, ShemaName, TableName, name

order by DB_NAME, ShemaName, TableName, name

【参考URL】
http://msdn.microsoft.com/ja-jp/library/ms190273(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms188388.aspx
http://msdn.microsoft.com/ja-jp/library/ms178065(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms175097(v=sql.105).aspx
http://msdn.microsoft.com/ja-jp/library/ms191528(v=sql.105).aspx