8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ZOZOAdvent Calendar 2023

Day 5

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

Last updated at Posted at 2023-12-05

これは ZOZO Advent Calendar 2023 カレンダー Vol.9 の 5日目の記事です。

はじめに

DBのパフォーマンスを安定させるためには、CPUやメモリへのリソースアクセスの管理が重要です。
SQL Serverでは、OPTION句を指定することで、SQLごとにCPUやメモリ使用量に関する制限を設けることができます。

今までふわっとした理解でOPTION句を使っていたため、
改めて以下2つの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つです。

image.png

Distribute Streams

image.png
データを並列処理するにあたり、一つのデータストリームを複数のプロセッサに分配する処理です。
上記クエリではIndex Scanの時点でパラレルで処理されデータが各プロセッサに分配されているため、この処理は発生していないようです。

Repartition Streams

image.png

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

Gather Streams

image.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_gb5.01GBに対して3.85GBと余剰がありました。
これを基にMAX_GRANT_PERCENTでさらに絞り込んでも良いという判断はできそうです。

おわりに

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

8
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?