SQL Server では インテリジェントなクエリ処理 として、バージョン / 互換性レベルに応じてクエリ最適化が自動的に行われる機能が実装されています。
この中で、「互換性レベル 150 以上」で有効化される「行ストアでのバッチモード」があります。この最適化機能に起因して、クエリの実行効率が極端に悪化する可能性があるのかについて、確認した情報をまとめておきたいと思います。
Archive for the ‘SQL Server’ Category
SSMS 22.10.0 で SQL Server のアップグレード評価の精度が向上しているようです
以前、SQL Server の互換性レベル変更に伴う構文解析ツールを作成しました という投稿をしました。
この投稿の検証をした理由の一つとして、SQL Server の互換性レベル毎の構文評価が動作していないというものがありました。
この事象については、フィードバックをしており開発チームにも事象の共有ができていたのですが、 SSMS 22.10.0 で改善したようです。
本投稿時点では、リリースノート には記載されていないのですが、以前のアップグレード評価では検知されなかった互換性の問題が検知されるようになっています。
Windows Server 2025 で SQL Server を動作させた場合の LPIM の問題についての情報を整理する
先日、Microsoft: Windows Server 2025 changes causing app crashes という記事が公開されました。
日本語で公開されている記事もあるようですが、元は上記の記事となるようなので私は上記の記事の参照をお勧めします。
この記事では Windows Server 2025 で SQL Server 2019 / 2022 / 2025 を使用しており、LPIM (Lock Pamge in Memory: メモリ内のページロック) を有効化している場合に、Access Violation が発生するというものです。
今回のような問題が発生している場合、どのように情報を見つければよいのかをまとめておきたいと思います。
SQL Server 2022 で実装された PSP optimization の挙動を把握するために参考にする情報
SQL Server 2022 の Intelligent query processing では、PSP optimization というクエリ最適化の処理が実装されました。
この機能はパラメーター スニッフィングに対する最適化として期待されるものとなり、パラメーターによって実行プランが大きく異なるケースで、パラメーターの値範囲に応じた実行プランを生成してくれるものとなります。
この機能が使用されているかを確認する際には、どの情報を参照すればよいのかを調べる機会がありましたのでまとめておきたいと思います。
SQL Server の互換性レベル変更に伴う構文解析ツールを作成しました
SQL Server では 互換性レベル という概念があります。
新しいバージョンで廃止された機能については新しいバージョンで継続して利用することはできませんが、T-SQL の構文の差異であれば互換性レベルを調整することで、新しいバージョンの SQL Server でも古いバージョンの T-SQL の構文解析で動作させることが可能となります。
今回、互換性レベルの変更に伴う構文解析ツールを Codex で作成して、MSSQLCompatibilityLevelQueryChecker として公開しました。
SQL Server のレプリケーションで最後のサブスクライバーを削除する際の挙動の違い
SQL Server のレプリケーションで、パブリケーション内の最後のサブスクライバーを削除する場合、それまでのサブスクライバーを削除するときとは異なる挙動がありましたので、調査した内容や方法をまとめておきたいと思います。
SQL Server の変更の追跡のクリーンアップの挙動を確認する際の参考情報
SQL Server では、変更の追跡 (Change Tracking: CT) の機能を使用することで、変更があった行のトラッキングを行うことができます。
CT では行の変更は次のテーブル (サイドテーブル) で管理が行われています。
- sys.syscommittab
- sys.change_tracking_<Object Id>
これらのテーブルは クリーンアップ により、定期的にデータの削除が行われます。
クリーンアップは、上述のリンクと Change Tracking の自動クリーンアップに関する問題のトラブルシューティング から挙動を確認することができます。
更新頻度が高いテーブルでは、サイドテーブルに格納されている行数が膨大になり、これらのクリーンアップのドキュメントに記載されている内容だけではトラブルシューティングが難しいことがあります。
本投稿では、変更の追跡のクリーンアップについてドキュメント外の情報について、参考情報をまとめておきたいと思います。
SQLServer 2019 CU19 / 2022 CU1 で追加された変更の追跡のクリーンアップ用トレースフラグによるクエリの変化
SQL Server の 変更の追跡 では、クリーンアップ プロセス で変更の内容を記録するサイドテーブル (syscommittab / change_tracking_<オブジェクト ID>) のクリーンアップが実行されます。
このクリーンアップはバックグラウンド タスクとして実行されるため、ユーザー側では実行されるクエリの制御ができないのですが、トレースフラグ TF8286 / 8287 を有効にすることでヒント句を追加することができます。
このトレースフラグを有効化することで、クリーンアップで実行されるクエリがどのように変化をするのかを確認してみました。
SQL Server のメモリサイズによるロックエスカレーションについて
SQL Server でロックエスカレーションが発生する要因としては次の 2 種類があります。
- ロックのメモリが閾値に達した場合
- ロック数が閾値に達した場合
閾値については、ロックのエスカレーションのしきい値 に記載されています。
それぞれの閾値に達した場合に、ロックエスカレーションが発生し、ロックの粒度がテーブルにエスカレーションされ確保されます。
この動作により、取得されているロックの数を最小限にすることで、ロックで過剰なメモリが使用されないようにします。
TF1211 を有効にすることで、「1.」「2.」の両方のロックエスカレーションを無効にし、TF1224 を有効にすることで「2.」についてのロックエスカレーションを無効にします。
これが、ロックエスカレーションの基本的な考え方となりますが、「1.」のケースについて、きちんと理解できていなかったことが分かったので、情報をまとめておきたいと思います。
sys.dm_tran_locks を参照せずに取得されているロック数を把握できるかを検証する
SQL Server で取得されているロック数を把握する際には、sys.dm_tran_locks を参照することが多いかと思います。
ロック数が数 10 万 / 数 100 万となっている環境では、この DMV を参照して COUNT(*) をするだけでも数分かかってしまい、定期的にロック数を取得して推移を把握するということが難しいケースがあります。
この DMV を使用せず、類似のロック数を把握することができるかを検証してみました。