SQLServerのOPTION句でリソース使用量を制御する

:::note info
これは 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を複数コアで並列実行することを並列クエリ(パラレルクエリ)と呼びます。

https://learn.microsoft.com/ja-jp/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#parallel-query-processing

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つです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/79236/70b8f542-09cd-ba05-1c4a-ebc43ca6287c.png)

### Distribute Streams
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/79236/8eb07f9a-c683-a4b0-5279-3220ed30a29f.png)
データを並列処理するにあたり、一つのデータストリームを複数のプロセッサに分配する処理です。
上記クエリではIndex Scanの時点でパラレルで処理されデータが各プロセッサに分配されているため、この処理は発生していないようです。

### Repartition Streams
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/79236/e3048192-5582-3a9f-9e47-f44d5b82f1ea.png)

データを複数のプロセッサ間で適切に分配する処理です。
上記のクエリでは、Index Scanしたデータをソートをするために各プロセッサに再分配を行なっているように見えます。

### Gather Streams
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/79236/3d445d53-1e0c-f536-976a-e4c1456271b3.png)
複数のデータストリームを一つに統合する処理です。
最終的な結果を返すために、各プロセッサでソートした結果を集約しています。


## 分かったこと

並列処理ができるのは、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`でさらに絞り込んでも良いという判断はできそうです。

# おわりに

データベースの内部構造はブラックボックスな感じがしますが、実際の動きを見ると理解が深まりますね。