背景

delete from Table where Column1 = 'xxxx'

このクエリ起因でブロッキングが数分間継続して発生したため、原因について調査しました。
いつもは1分以内に完了するクエリなのですが、たまに原因不明で10分以上実行されることがあり、そのタイミングで他のクエリをブロックしているようでした。

テーブル構成

ヒープ＋Column1がキーの非クラスタ化インデックス
レコード数：約20万行

こちらの方法で1分毎にDMVのダンプをテーブルに保存していき、事象が再現した時の実行状態の遷移を確認しました。

1分毎のスナップショットの情報なので、間の状態遷移は正確にはわかりませんが、すべてstatus=runnable/last_wait_type=PAGELATCH_EXであることから、10分間ずっとこの状態であったと予想されます。

また、レコード数は20万行ほどしかなく、インデックスの容量も20MB程度しかないのですが、logical_reads（論理読み取り）のサイズが非常に大きく、最終的に10GB以上読み取っているという特徴もみられました。

そのタイミングだけレコード数が非常に多かったのでは？という気もしたので、レコード数の推移についても確認ましたが、大きな変化はみられませんでした。

テーブルサイズは常に約２０万レコードくらいで14-20MBくらいであるにもかかわらず、10GBオーバーの論理読み取りが発生しているということは、実行プランがおかしくなっていてスローダウンしている可能性が考えられます。

query_plan_hashは、実行プランが同じなら、再生成されても同じになります。そのため、はやいときと遅いときとでquery_plan_hashに違いがあるかを見てみることで、「実行プランがおかしくなったか」についてはある程度確度を高められそうだと思い、確認してみました。

select * from
dm_exec_requests_dump with(nolock)
where command_text like '%delete from Table where Column1 = %'
order by collect_date

hash値は速いときも遅いときも同じようでした。ということは、実行プランが変わったわけではなさそうです。

ここで調査は行き詰ってしまいました。
何らかの要因で、速いときと遅いときがあるようなのですが、その要因にはたどり着けませんでした。

今回のテーブルはヒープでしたが、クラスタ化インデックスで構成されているテーブルのほうがプロダクション環境で圧倒的に多く、この両者のDELETE時の論理読み取り数にどれだけ差があるか調べてみました。

その結果、「ヒープ＋非クラスタ化インデックス」テーブルのDELETEは、「クラスタ化インデックス」テーブルのDELETEよりも、論理読み取りが約30倍ほど多いという結果となりました。

一般的な傾向として、ヒープで構成されたテーブルのほうが、同一レコード数であってもDELETE時の論理読み取りページ数はかなり大きくなるようです。ただし、だからといって20万レコードのテーブルのDELETEがブロックされずに10分以上かかる理由にはなりません。

「ヒープ特有の何か」が原因でDELETEにいつもよりかなり時間がかかることがあると考え、最終的な対応としてはクラスタ化インデックスを作成しました。

これにより今のところ突発的なDELETEクエリのスローダウンは起きていません。

同じようにヒープを使ったテーブルで原因不明のスローダウンに悩まされている方はクラスタ化インデックス化すると解消される可能性があります。