SQL Server: 特定の状況下におけるスロットリング機能の実装について考えてみた

#背景
SQL Serverでは、特定のレコード「A」にUPDATEが集中するなどしてブロッキングが多発すると、最終的にはワーカースレッドの枯渇にいたります。
ワーカースレッドが枯渇すると、レコード「A」以外の「B」や「C」への更新要求も、ワーカースレッド獲得待ち(Wawit for the worker)の時間が増えることで処理時間が長引いたり、タイムアウトすることもあります。

このように、特定のレコードにUPDATE要求が集中する状況下において、他のレコードへの更新リクエストが悪影響をできるだけ受けずに高速に実行できる状況を作るための方法を考えてみました。

前提として、UPDATE要求が集中するレコードは事前に予測できているものとします。

#サンプルデータ作成
以下のクエリを実行してサンプルのDBとテーブルを作成し、テストデータをINSERTします。

```sql
create database stress_test
go
use stress_test

create table items (item_id bigint identity(1,1) primary key, quantity int, registered_at datetime default(getdate()))
go

insert into items (quantity) values (10000)
go 100

select * from items with(nolock)
```

↓のようなデータが入ったテーブルができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/50dfa5df-60cb-41bc-f54c-9e398f5965af.png)
...
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/c3aa8610-ef4d-6242-2d62-0aa7644389f8.png)

#ワーカースレッド数の調整
ワーカースレッドが枯渇しやすいように、ワーカースレッド数の最大値を小さくしておきます。今回は「128」に設定しました。

#負荷ツール
今回は[SqlQueryStress](https://www.microsoft.com/ja-jp/p/sqlquerystress/9n46qj5sbgkb)を使用して負荷をかけました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/aa8ab6ed-3d87-4def-41e8-2e4da3c4ef45.png)
シンプルなUI、直感的な操作で負荷をかけることができます。

#負荷試験
### シナリオ①

```sql
--クエリ① 200 threads / 100 iterations
begin tran
	update items set quantity = quantity - 1 where item_id = 1
	waitfor delay '00:00:00.100'
commit tran

--クエリ② 50 threads / 50 iterations
update items set quantity = quantity - 1 where item_id = ceiling(rand() * 100) + 1
```

クエリ①先に実行させ、ワーカースレッドを枯渇させてから、クエリ②を別のシナリオとして実行します。（クエリ①もクエリ②も同時に複数スレッドから複数回実行され続けている状況）
クエリ①は特定のレコードのUPDATE要求の競合を発生させ、結果としてワーカースレッドを枯渇させます。
そのままだと高速にクエリが完了してしまうので、ブロッキングを長引かせるために100msec待ち時間を加えています。

クエリ②はitem_id=1以外のレコードに対してランダムにUPDATEをかけます。

負荷をかけているときのperfmonメトリクスは以下のようになります。
Wait for the worker待ちが平均1-2秒発生しており、ワーカースレッドが枯渇し、クエリ②もスローダウンしていることが分かります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/c6ec7d26-7f9f-3976-cfa9-e7f396d82410.png)

以下のクエリで、実行中のクエリを抽出してみます。

```sql
set transaction isolation level read uncommitted
select 
     der.session_id as spid
    ,der.blocking_session_id as blk_spid
    ,datediff(s, der.start_time, getdate()) as elapsed_sec
    ,db_name(der.database_id) as db_name
    ,der.parallel_worker_count
    ,des.host_name
    ,des.program_name
    ,der.status -- status of the request. (background / running / runnable / sleeping / suspended)
    ,dest.text as command_text
    ,replace(replace(replace(substring(dest.text, 
    (der.statement_start_offset / 2) + 1, 
    ((case der.statement_end_offset
    when -1 then datalength(dest.text)
    else der.statement_end_offset
    end - der.statement_start_offset) / 2) + 1),char(13), ' '), char(10), ' '), char(9), ' ') as current_running_stmt
    ,datediff(s, der.start_time, getdate()) as time_sec
    ,wait_resource
    ,wait_type
    ,last_wait_type
    ,der.wait_time  as wait_time_ms
from
    sys.dm_exec_requests der
join sys.dm_exec_sessions des on des.session_id = der.session_id
outer apply sys.dm_exec_sql_text(sql_handle) as dest
where
    des.is_user_process = 1
order by
    datediff(s, der.start_time, getdate()) desc
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/8e288b96-d167-9402-5191-e5f727be11d1.png)
ブロッキングが多発してクエリが滞留している様子が確認できました。

クエリ②は、50 threads / 50 iterationsの合計2500回実行するのに、5分ほどかかりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/3d1c342b-6485-1145-4b93-a05f399f00d9.png)


### シナリオ②
シナリオ①の結果を受けて、どうやったらitem_id=1以外のレコードへのUPDATEがスムーズに実行されるかを考えてみました。
ワーカースレッドが枯渇するほどのブロッキングが発生する状況では、item_id=1への更新要求は最終的にタイムアウトすることがほとんとです。それなのにitem_id=1への更新要求がワーカースレッドを占有してしまう状況を何とかできないかと思い、簡易的なスロットリング機能を持たせるようにしました。

方法はシンプルで、「Process blocked」メトリクスが50を超えていた場合は更新要求をそもそも実行しないようにする、というものです。
UPDATE要求をスキップさせる判断基準としては他にワーカースレッド数も検討したのですが、ワーカースレッド数は一度上昇すると、すぐには値が下がらないことから今回の目的には不適当であると判断し、ブロッキング数を使うことにしました。

```sql
--クエリ① 200 threads / 100 iterations
if not exists(
	select * from sys.dm_os_performance_counters with(nolock)
	where counter_name = 'Processes blocked' and cntr_value > 50
)
begin
	begin tran
		update items set quantity = quantity - 1 where item_id = 1
		waitfor delay '00:00:00.100'
	commit tran
end

--クエリ② 50 threads / 50 iterations
update items set quantity = quantity - 1 where item_id = ceiling(rand() * 100) + 1
```

負荷をかけているときのperfmonメトリクスは以下のようになりました。
スロットリングが効いており、ブロッキングが50以下になっています。また、「Wait for the worker」のカウンタも0のままです。
先ほどと比べて状況が改善されていることが分かります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/be1d4633-2e61-af03-4dcc-c55bdfeb0740.png)


クエリ②を50 threads / 50 iterationsの合計2500回実行するのに、シナリオ①では5分ほどかかったのが、今回は約5秒で終わりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/1d2b121b-3433-82ee-0939-124be8770626.png)

#まとめと補足
特定のレコードへUPDATE要求が集中するようなワークロードにおいて、該当レコードへの更新についてブロッキング数を判断基準としてスロットリングを発生させることで、他のレコードへの更新（あるいは他のテーブルへのあらゆるクエリ）が低速になることを防ぐことができそうだ、と感じられる実験結果が得られました。

「スロットリングしたクエリはアプリ側でどういう扱いにすべきか」という議論がありますが、シンプルにエラーとして返してもいいかもしれません。

また、シナリオ②でも瞬間的にブロッキング数が50を大幅に超えたり、Wait for the workerの値が上昇するタイミングもありました。そのため、今回の手法で完璧にワーカースレッド枯渇やそれによる他のクエリへの悪影響を取り除くことができるわけではなさそうです。