これは ZOZO Advent Calendar 2023 カレンダー Vol.9 の 5日目の記事です。
はじめに
DBのパフォーマンスを安定させるためには、CPUやメモリへのリソースアクセスの管理が重要です。
SQL Serverでは、OPTION句を指定することで、SQLごとにCPUやメモリ使用量に関する制限を設けることができます。
今までふわっとした理解でOPTION句を使っていたため、
改めて以下２つのOPTION句について調べてみました。
- MAXDOP（Maximum Degree of Parallelism）
- MAX_GRANT_PERCENT
1.MAXDOPによる並列数の制御
概要
SQLを複数コアで並列実行することを並列クエリ(パラレルクエリ)と呼びます。
MAXDOPは、クエリ実行に利用できるCPUコアの最大数を指定します。
本来はSQLServer全体の設定で定義していますが、OPTION句によってSQL単位での指定が可能です。
検証
以下ような単純な集計クエリで検証します。
クエリの最後にOPTION (MAXDOP n)をつけることで DOPを制限できます。
SELECT hogeID,COUNT(*)
FROM [dbo].[Table]
GROUP BY hogeID
OPTION (MAXDOP 1)
DMV(sys.dm_exec_query_stats)でクエリの実行状況を確認しました。
| MAX DOP | elapsed_time(s) | worker_time(s) | avg_logical_reads(gb) | max_used_grant(mb) |
|---|---|---|---|---|
| 1 | 107 | 107 | 53 | 3 |
| 2 | 55 | 109 | 53 | 1 |
| 4 | 31 | 109 | 53 | 3 |
| 8 | 18 | 117 | 53 | 17 |
並列数に比例して実行時間が1/nになっていることが確認できます。
実行プラン DeepDive
調べてみたところ、実行プランに登場する重要な内部処理は大きく3つです。
Distribute Streams
データを並列処理するにあたり、一つのデータストリームを複数のプロセッサに分配する処理です。
上記クエリではIndex Scanの時点でパラレルで処理されデータが各プロセッサに分配されているため、この処理は発生していないようです。
Repartition Streams
データを複数のプロセッサ間で適切に分配する処理です。
上記のクエリでは、Index Scanしたデータをソートをするために各プロセッサに再分配を行なっているように見えます。
Gather Streams
複数のデータストリームを一つに統合する処理です。
最終的な結果を返すために、各プロセッサでソートした結果を集約しています。
分かったこと
並列処理ができるのは、CPUを使った集計、結合、ソートの処理のみだと思っていました。
このSQLは、対象テーブルが53GBと非常に大きくIOがボトルネックになっています。
並列数を上げることでインデックスのスキャンやシークなどIO処理も並行して行うことができ、データの読み込みも早くなることが分かりました。
2.MAX_GRANT_PERCENTによるメモリ制御
概要
MAX_GRANT_PERCENTは、同じくSQLに指定できるOPTION句の1つです。
そのクエリが使用できるメモリの最大割合を指定します。
この設定により、メモリリソースの過剰利用を防ぐことができます。
検証
クエリは割愛しますが、複雑なJOINや大量データのソートを行う重いクエリで検証します。
クエリの最後にOPTION(MAX_GRANT_PERCENT = n)を指定して、100, 50, 10で比較します。(nはパーセント)
DMV(sys.dm_exec_query_stats)でクエリの実行状況を確認します。
各結果は3回実行した平均値です。
| MAX_GRANT_PERCENT(%) | elapsed_time(s) | worker_time(s) | logical_reads(gb) | max_grant(gb) | max_used_grant(gb) | max_ideal_grant(gb) |
|---|---|---|---|---|---|---|
| 100 | 10 | 69 | 5.94 | 5.01 | 3.85 | 17.25 |
| 50 | 13 | 93 | 6.18 | 2.56 | 2.42 | 17.25 |
| 10 | 11 | 69 | 6.65 | 0.51 | 0.41 | 17.25 |
メモリ割り当て量について
max_grant_gb
クエリに割り当てられた最大メモリ量
指定したパーセンテージに比例して割り当て量が減っています。
max_used_grant_gb
割り当てられたメモリのうち、実際に使用されたメモリ量
指定したパーセンテージに比例して割り当て量が減っています。
max_ideal_grant_gb
理想的なメモリ割り当て量
今回のクエリだと17.23GBのメモリが理想的な割り当て量のようです。
分かったこと
MAX_GRANT_PERCENTの値は、本来割り当てられるはずのmax_grant_gbのうち何パーセント割り当てるかという指定でした。
このmax_grant_gbは、あくまでSQLの実行に使うワーキングメモリとしての割り当て量です。
データをメモリに読み込むためのバッファキャッシュ領域はまた別で、max_grant_gbは影響しません。
基本的にSQLServerが使うメモリ領域の大半はバッファキャッシュであり、データがメモリに乗っていることで高速にデータアクセスができるようになるため重要です。
ワーキングメモリを大量に使用するとこのバッファキャッシュが圧迫される恐れがあるため、MAX_GRANT_PERCENTを設定することで間接的にバッファキャッシュを安定して確保できることに繋がると思います。
今回はワーキングメモリを絞っても実行時間には影響しませんでしたが、
そもそもmax_grant_gbが5.01GBに対して3.85GBと余剰がありました。
これを基にMAX_GRANT_PERCENTでさらに絞り込んでも良いという判断はできそうです。
おわりに
データベースの内部構造はブラックボックスな感じがしますが、実際の動きを見ると理解が深まりますね。