SE の雑記

タイトルはホッテントリメーカーで作りました。
一度やってみたかっただけです。ごめんなさい…。

SQL Database における動作傾向の取得について少しまとめてみたいと思います。
@IT の Dr.K が連載をされている 内部動作を知らずしてチューニングは語れない (1/3) の SQL Database を SQL Database 向けに書いたものになります。

JAZUG では、Azureブログを書いてSurfaceRTを貰おう という企画を実施しており、JAZUG の Facebook ページ の投稿 (https://www.facebook.com/jazug.jp?fref=ts&filter=2) でいいねを使用した記事の投票をしておりますので、皆さんが書かれている記事 でこれはというものがありましたら、いいねを押していただければと思います。
ハイライトで表示されて、過去の投稿がまとめられている場合は、最近の投稿の [すべて見る] から投稿されている記事をすべて確認することができます。
また、Azure に関する投稿がありましたらこちらの企画にご参加いただけると幸いです。

さて本題です。

システムが稼働しているときには「動作に待ちがなくリソースを最大限に活用する」というのが理想的になるかと思います。

この待ちは

• ロックによるレコードの排他制御
• ラッチによるページの管理
• ディスクからの I/O 待ち
• CPU の処理待ち
• ネットワークの I/O 待ち

というような様々な事象があります。
# メモリ不足によるディスク I/O や CPU の処理能力不足による処理待ちなどはあくまでも要因の一つとなります。

これらが可能な限り 0 に近ければ待ちが発生することなく処理をすることができるのですが、実際にはそうはいきません。

処理は以下のように遷移していきます。

実行状態が実際に処理が実行されている状態となり、ロック等の待ちが発生すると待ち状態になり、リソースが使用できるようになると実行可能状態から実行状態に遷移します。
# 直接実行状態になるときもあったかと思いますが。

SQL Database の場合、待ち状態は Waiter List として待ちの原因単位に管理がされており、どの待ちがどれくらい発生したかを確認することができます。

この待ちの状態を確認するための動的管理ビューが sys.dm_db_wait_stats になります。
# 最新のヘルプだと sys.dm_database_wait_stats になりますが、この DMV の名称では本投稿を書いている時点では認識しないため sys.dm_db_wait_stats を使用する必要があります。ヘルプの内容は共通ですので内容自体は最新のものを確認するとよいかと。

この DMV では以下のような各 Waiter List の待ちの情報が取得することができます。

count は発生回数、max は最大の待ち時間になります。
wait_time_ms と signal_wait_time_ms の関係は以下のようになります。

wait_time_ms は処理を実行するまでにかかった全体の待ち時間となり、signal_wait_time_ms は実行可能状態から実行状態に移るまでにかかった時間となり、signal_wait_time_ms は wait_time_ms の内数となります。

signal_wait_time_ms が高い場合は CPU リソースが不足しているということが言われることもあります。
# この辺はケースバイケースになるはずですが。

SQL Server の場合は、発生していない待ちの情報も取得することができるのですが、SQL Database の場合には発生した待ちの情報のみが DMV から取得できるようになっているようです。
また、SQL Server の場合はインスタンス全体の待ち事象として累計がされますが、SQL Database の場合はデータベース単位で累計されています。
# SQL Database はデータベース単位で異なるサーバーに配置される可能性があり、TDS ゲートウェイを介して等価的に一つのサーバーとしてみるようなアーキテクチャになっているためだと思います。

各 Wait_type の情報はこの DMV の情報か、SQL Server の sys.dm_os_wait_stats を参考にするとよいかと思います。

具体的に値をどのように使用するか例を出してみていきたいと思います。

SQL Database で以下のクエリを実行してみます。

INSERT INTO BaseTable VALUES(1, NEWID())

実行前の DMV の状態は以下のようになります。

実行後がこちらに。

実行前後で待ち事象の差を見てみると以下のようになります。

SQL Server は先行ログ書き込み (Write Ahead Log : WAL) になるため、ログへの書き込みが完了しないとトランザクションは完了しません。

そのため、ログの書き込みが完了するまで待ちが発生するため WRITE_LOG が発生し、ログの書き込みに 1ms の待ちが発生しています。

また、SQL Database ではデータベースが三重化されており、セカンダリレプリカへのレプリケーションが必須となります。この際の待ちが [SE_REPL_COMMIT_ACK] になり、この同期待ちにより 1ms の待ちが発生しています。

大量のデータを検索した場合などは以下のようになります。

ASYNC_NETWORK_IO のみ差が発生しています。

これはすでにメモリ上にデータがキャッシュされているため、ディスクとの I/O の待ちは発生していないのですが、実行の要求があったクライアント (今回の場合は SSMS) にデータを返す際に非同期ネットワーク I/O による待ちが発生しているため、この待ち事象が上昇しているものと考えることができます。

以下のようなクエリで待ちの発生回数単位の平均の値を取得してみます。

select *, wait_time_ms / waiting_tasks_count from sys.dm_db_wait_stats

この情報ですと、発生状況回数ごとの町としては非同期ネットワーク I/O が高いことが確認できますね。

今回は 5,000 件のデータを取得しているのですが、ネットワークのレイテンシも影響しているかもしれないです。

データは

• 差分を算出し特定の時間帯の情報を取得
• 全体の発生回数ごとの平均値を確認し、全体的な傾向を取得

というような見方をすることができます。

SQL Server 2012 以降であれば、拡張イベントでセッション単位の待ちを取得することもできるのですが、SQL Database では、拡張イベントが使用できないため、クエリの前後で DMV を検索するためのクエリを実行して差を取得するとよいかもしれませんね。

select *, wait_time_ms / waiting_tasks_count from sys.dm_db_wait_stats
SELECT * FROM BaseTable
select *, wait_time_ms / waiting_tasks_count from sys.dm_db_wait_stats

SQL Database の DB が他のデータベースに移動された場合は値はクリアされるかもしれませんので、基本的にはサーバー内で最後に起動してからの累計値と考えるとよいかと。

待ちの情報を確認することで、システムの動作傾向 (どのような待ちが多いか) を確認し、待ちが多いものを減らすためにはどうすればよいかというアプローチをすることができますので、レスポンスについてどこから手を付けるかの検討をする際には有効な手段だと思います。

• WRITE_LOG
• SE_REPL_xxxx (三重化するための同期による待ち)
• ASYNC_NETWORK_IO

については環境依存する避けられない箇所があるかもしれませんので待ちを減らすのは難しいかもしれませんが。

SQL Database では、READ COMMITTED SNAPSHOT が既定のトランザクション分離レベルとなっており、MAXDOP が 1 になっていたはずですので、LCK_M_S や CXPACKET 等については発生の頻度が少ないという特徴があるかもしれません。