背景

年々進化しているSQL Server/SSMSですが、使っている中で「こんな機能があったらいいな」と思うところがあったので、まとめてみました。
※実装の困難さはいったん棚に上げておきます。
※クエリストアで実現されている機能もあるかも。。

【SSMS】トランザクションをOPEN中のクエリウインドウは色が変わる

背景

例えば、以下のようなクエリを実行中に、想定より時間がかかったため中断するとします。

begin tran
    update ***
    insert ****
    select ****
commit tran

ここで、クエリの処理状況次第では、トランザクションを開いて、かつ特定のテーブルに排他ロックを書けた状態で止まってしまう可能性も考えられます。

begin tran
    update ***
    --ここでabortされた

このような状況はブロッキングを多発させる危険性があり、かつ操作を行った人物がそのままクエリウインドウを放置すると、いつまでもロックを解放することができません。

set xact_abort on

begin tran
    update top (1) member set sei = 'test2' where memberid = 18629764
    waitfor delay '00:01:00'
commit tran

↑のように、「xact_abort」オプションをONにすることで、中断した際に自動でrollbackされるようにすることも可能ですが、つけ忘れることも考えられます。

改善案

SSMSで、以下の2パターンの状況においてクエリウインドウのタブの色が変わるといいのではと思っています。

↑イメージ

①ブロッキングチェーンに巻き込まれている状況

ヘッドブロッカーであってもなくても、ブロッキングチェーンに巻き込まれている状況であれば、クエリを一時停止すべき状況が多いと思います。

②明示的にトランザクションを開きっぱなしの状況

ブロッキングチェーンに巻き込まれていないとしても、トランザクションを明示的に開きっぱなしということは今後ブロッキングを発生させる懸念があるため、色が変わって操作者が気づきやすい状況になっていると良いなと思います。

【SQL Server】DMV改善

背景

sys.dm_exec_query_statsと、sys.dm_exec_procedure_statsは、それぞれ「成功したステートメント」「成功したストアドプロシージャ」の回数をカウントアップしてくれたり、消費したCPUリソースやメモリ等の情報を格納してくれます。

大変便利ですが、タイムアウトが多発するような時間帯では、中断されたステートメントやストアドプロシージャについての情報も保存されると嬉しいことがあります。

改善案①

以下のようなカラムが追加されると嬉しいです。

exec_start_count : 実行を開始したストアドプロシージャ数
exec_end_count : 実行が完了したストアドプロシージャ数(現在のexecution_countに相当）
exec_abort_count：実行を途中囚虜うしたストアドプロシージャ数

各カラムの関係性としては
exec_start_count = exec_end_count + exec_abort_count
となります。

改善案②

もしくは、成功したものとabortしたものとでテーブルごと分かれていると都合が良いかもしれません。

dm_exec_procedure_stats

今まで通り成功したプロシージャのデータを格納。
クエリチューニングの性能評価等にはこちらを使用する。

dm_exec_abort_procedure_stats

abortしたプロシージャのデータを格納。
主に何らかの障害が発生したタイミングでの使用を想定。何らかの障害が発生している際はクエリがabortすることが多く、その状況でも各種リソース消費の寄与率を後追いしたい場合に重宝すると想定。

【SQL Server】クエリチューニングサポーター

背景

selectivityの良い検索述語がなく、多数のテーブルのJOINやサブクエリを多用しているような分析用途に近いSQLでは、チューニングの際にある程度の試行錯誤が必要になってくるかと思います。

一方で、SQL Serverは最適化フェーズで複数のプランを評価してはいるものの、コストベースで最小となったプランだけがユーザーの目に見える形でoutputされますが、AB2つのプランがあったときに「Aプランの方がコストは非常に大きいのに、実行時間はBプランよりとても短い」といったことは珍しくありません。

もともと「限られた短い時間内にできる限り高速なプランを見つける」というコンセプトがオプティマイザにはあるためこのような挙動が起こり得るのですが、「時間を十分に使っていいので、より高速なプランをみつけたい」といったニーズもあるかと思います。

改善案

特定のオプションをONにすることで、複数の実行プランで実際にクエリを実行して消費リソースを計測し、各プラン毎の「実際のパフォーマンス結果」をまとめてくれる「クエリチューニングサポーター」のような機能があると嬉しいです。

似た既存機能に「データベースチューニングアドバイザー」があります。こちらはおそらくですが、実際にクエリを各プランで実行するようなことは行っていないはずで、推奨のインデックスなどをより詳細にレコメンドしてくれます。

しかし、インデックスよりもプランを変えるだけで劇的に高速化されるケースも多々あるため、このように「複数の実行プランで実際のパフォーマンスを計測できる機能」というのはあったら使うユーザーは多いのではと思います。

【SQL Server】ノンブロッキングDDL

背景

カラム追加やカラムの型変更などのスキーマ修正はSch-Mロックを獲得するため、ブロッキングの発生リスクが非常に高いです。そのため、どうしても手動でのデプロイを選択している方も多いと思います。

改善案

TiDBなどが持っている「ノンブロッキングDDL」特性がSQL Serverに備わってくれると運用面で相当楽になります。（おそらく実現は難しいでしょうが。。）

【SQL Server】ロックに関する改善

背景

こちらの記事で紹介しているように、ロックのキューにSch-Mが入ったときに、後段のクエリ達が全てブロッキングされる挙動はかなりリスクが高いです。

改善案

ロックのキューにSch-Mが入ったときでも、後段のクエリ達がブロックされないように修正されるとありがたいです。
もしくは、こちらの記事で紹介しているように「ブロックされたらすぐにタイムアウトし、再度リトライ」をクエリ返すようなデプロイ戦略をより簡単に実現できる機能があってもとても助かるなと思います。

--イメージ
set lock_timeout 200 --既存オプション。200msecロックがとれなければタイムアウト
set retry_count 100 --オプション案。該当バッチ実行中にエラーで終了した場合に自動でリトライする回数
set retry_wait_time '00:00:01' --オプション案。リトライまでに待つ時間

alter table table1 add c2 int not null

【BCP】BULK COPYコマンド実行時のソート機能

背景

BCPでレコード数が大きいテーブルをインポートする際、インポート先のテーブルのクラスタ化インデックスキー順に並び替えていないとインポート速度が著しく低下する場合があります。
したがって、エクスポート時に事前にorder byを指定してデータを抜く必要がありますが、インポート直前に内部的にソートしたうえでインポートできるオプションが用意されていると助かります。

改善案

オプションイメージは以下の通りです。-Sort "sort_column_name"のように、「sort_solumn_nameカラムでソートした後にインポートして欲しい」ということを伝えられる機能です。

bcp table_name in file.dat -E -N -U "**" -P "**" -b 100000 -S *** -Sort "sort_column_name"

【SQL Server】レイテンシのメトリクス提供

背景

SQL ServerではパフォーマンスモニタでSQL Server\Batch Resp Statisticsというカウンタが提供されています。
このカウンタは、「1-2msecのクエリが、〇件あった」といった情報が得られます。

イメージ図

しかし、90パーセンタイル値などの情報は得ることができません。
現状は↓のような方法で、無理やり近似値を算出しています。
https://qiita.com/maaaaaaaa/items/a27045f74845c50d782c

改善案

以下のようなカウンタの提供があるととてもありがたいです。
SQL Server:Performance Statistics\90_percentile_value
SQL Server:Performance Statistics\95_percentile_value
SQL Server:Performance Statistics\99_percentile_value

【SQL Server】ヘッドブロッカーの特定を容易に

背景

SQL Serverでブロッキングチェーンが発生した場合、ヘッドブロッカーを特定するためには、目視でsys.dm_exec_requestsのblocking_session_idをたどっていくか、こちらのクエリを使うなどして、工夫してヘッドブロッカーを特定する必要があり、大変です。

改善案

以下のような情報がdmvで提供されるとありがたいです。
sys.dm_exec_blocking_chain
→ブロッキングチェーンの情報を取得できるDMV。
カラムは

ブロッキングチェーンに巻き込まれているsession_id
is_head_blockerカラム(ヘッドブロッカーなら「1」）
wait_resource
last_wait_type
wait_type
などを想定します。