SQLServerでインデックスエラーの対応をしたら副産物を得た

業務で少しハマったので、備忘録として記述。

はじめに

業務でいつも通り開発を行っていると見慣れないエラーが。
内容をみると、どうやらテーブルに張ったインデックスが悪さしているみたい。

内容

インデックスが存在しているのに、存在しないもしくは無効やでみたいな感じのエラー内容。
ちゃんと読むとsqlserverの8951と酷似しているエラーっぽい。が対応当時はよくわからず。

対応

とりあえず問題にぶちあたったらそれっぽい対応をしてみようということで、、、

ssmsで該当インデックスを再構築してみた。そのあと処理を行うと動作した。めでたい。

このときインデックスの断片化が進みすぎて動作しないんじゃないかなあと推測。
なので断片化が進んでいるインデックスは悪だという自身の偏見の元、調査することに。
他の断片化したインデックスを確認するため以下のクエリ実行しました。

断片化したインデックスの取得クエリ

SELECT
  DB_NAME(s.database_id) dbName --DB名
  , OBJECT_NAME(s.object_id) tableName -- テーブル名
  , s.index_type_desc --インデックスの種類
  , i.name -- インデックス名
  , i.index_id --ヒープ:0, クラスター化インデックス:1, 非クラスター化インデックス:>=1
  , s.page_count -- インデックスのページ数
  , s.fragment_count --リーフレベルでのフラグメントの数
  , s.avg_fragmentation_in_percent --断片化率,高いとイケてない
  , s.index_level --インデックスのレベル, ヒープとかリーフレベルなら0
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 --10％以上断片化が進んでいることを条件
ORDER BY
  s.avg_fragmentation_in_percent DESC

すると何十件かのインデックスで断片化が進んでいることを確認。
どれもindex_levelが0のものなのでリーフレベルで断片化が進んでいると推測。

ただ、僕はとても面倒くさがりなので何十件もssmsで再構築をぽちぽちするのはやりたくない、、、
なのでカーソルを使って動的に断片化の解消を行うことにしました。

上記のクエリを参考に作成した再構成、再構築用クエリ

/** 
*   インデックスの断片化の再構成、再構築クエリ.
**/


DECLARE @avgPercent DECIMAL (17, 2)
DECLARE @sqlStr VARCHAR (max) 
DECLARE @indexNm VARCHAR (255)
DECLARE @schemaNm VARCHAR (255) 
DECLARE @objectNm VARCHAR (255) 

DECLARE index_cursor 
CURSOR FOR 
SELECT   
    QUOTENAME(s.name) AS schemaNm,
    QUOTENAME(o.name) AS objectNm,
    QUOTENAME(i.name) AS indexNm,
    ips.avg_fragmentation_in_percent AS avgPercent
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS ips
        INNER JOIN sys.objects AS o
            ON ips.object_id = o.object_id
        INNER JOIN sys.schemas AS s
            ON o.schema_id = s.schema_id
        INNER JOIN sys.indexes AS i
            ON ips.object_id = i.object_id
            AND ips.index_id = i.index_id
WHERE    
    ips.avg_fragmentation_in_percent > 10.0
    AND ips.index_id > 0 --クラスター化、もしくは非クラスターインデックスを対象

OPEN index_cursor FETCH NEXT 
FROM
  index_cursor 
INTO @schemaNm
, @objectNm
, @indexNm
, @avgPercent

while @@FETCH_STATUS = 0
begin

IF @avgPercent <= 30
    /** 30％以下は再構成. */
    BEGIN 
        SET
        @sqlStr = N'ALTER INDEX ' + @indexNm + N' ON ' + @schemaNm + N'.' + @objectNm + N' REORGANIZE'; 
    END 
ELSE
    /** 30％超えは再構築. */
    BEGIN 
        SET
        @sqlStr = N'ALTER INDEX ' + @indexNm + N' ON ' + @schemaNm + N'.' + @objectNm + N' REBUILD'; 
    END
EXEC (@sqlStr)
FETCH NEXT 
FROM
  index_cursor 
INTO @schemaNm
, @objectNm
, @indexNm
, @avgPercent
END 

CLOSE index_cursor
DEALLOCATE index_cursor

再構成、再構築の違いは、ありがたいことに先駆者がまとめているのでこちらを参考にしてください。

クエリについてですが、公式の断片化の検出によると大体5~30%が再構成、それ以上断片化が進んでいれば再構築してねとのことだったのでそれを参考にしつつ組みました。

というわけでいざ実行、、、。
しかし、、、！

何件かは断片化が解消されず残っているではありませんか、悲しい。

どうやら対象のインデックスはいずれも30-50％くらい断片化しているみたいです。
ただ、どれも最初のクエリでインデックスがリーフレベルであることを確認しているので、ダメ元で残りの数件を再構築ではなく再構成してみました。

すると全て断片化が解消されているではありませんか、、、！
(厳密には上記クエリ上断片化率10%以下であることを解消としています)

これにて対応としては終戦。

要因とかあとがき

対応終えた後に、あんまり自身の対応が腑に落ちなかったのでネットを漁りました。が、断片化で8951のようなエラーが発生する旨の記事は見当たらないので直接的原因ではなさそうでした。

あとから開発環境を整えてくださった方に聞くと、本番環境のデータを開発環境に持ってくる際にバージョンとかで差異があったので、エラーが起きていたインデックス等を削除したとのこと。
その時に削除すべきでないデータも吹っ飛ばした可能性があると聞いたのでそれかなと思っています。
まあ副産物としてクエリパフォーマンスの改善ができたのでそれは良かったのかなあと。
疑問点は再構築でうまくいかなかったのに再構成でうまくいったことですが、再構築に割くためのエクステントが枯渇したのかなあとか思いつつ、でもしっくり来ていないのが感想です。誰か教えてすごい人

参考

カーソルクエリで参考にさせて頂きました。
https://sql55.com/query/reorganize-rebuild-index.php