ITエンジニアの成長ブログ

ITエンジニアとして行う勉強の発信&日々の生活で体験した楽しいことをゆるく発信

SQL Serverでブロッキングを検出するクエリ

今回は、SQL Serverでロック関連の調べ物をしていたときに今後使えそうなクエリを見つけましたので自分のためにここにメモしておきます。

以下のリンクにあるクエリがタイトルのとおり、ブロッキングを検出するクエリです。こちらは特に条件変更もしないでそのまま実行すればOKのようです。
https://raw.githubusercontent.com/MasayukiOzawa/SQLServer-Util/master/Lock/%E3%83%96%E3%83%AD%E3%83%83%E3%82%AD%E3%83%B3%E3%82%B0%E3%83%81%E3%82%A7%E3%83%BC%E3%83%B3%E3%81%AE%E5%8F%96%E5%BE%97.sql

私の環境でブロッキングを再現させた後に、上記のリンクのクエリを実行してみたのが以下です。

ブロッキングを検出するクエリ実行結果

とてもいい感じの情報がたくさん出力されるので、もし「このSQLが終わらない・・・これは何かしらでブロッキングが発生してそう」といったときに、このクエリを実行すれば何がブロッキングしているのか一目でわかりそうです。

今回はただリンクを紹介しただけですが、この辺で失礼いたします。最後までお読みいただきありがとうございました。

SQL Serverで長時間のブロッキングを確認する方法

SQL Serverにて長時間のブロッキングが行われていたSQLなどを検出する方法がないかを知りたくて、ネットで色々と調べてみたのでその内容を備忘としてここに記載したいと思います。

基本的には、以下のブログを参照させていただきました。こちらで紹介されていた、後追いで長時間のブロッキングが行われていたSQLを調査する方法を実際に自分の環境で試してみたいと思います。
ブロッキングとデッドロックを後追い確認する方法 at SE の雑記

blocked process threshold サーバー構成オプションを設定する

まずは事前準備として、blocked process threshold サーバー構成オプションを設定します。これを設定しておくことで、ある閾値(秒)を超えてブロッキングが発生した場合にイベントを生成することができます。

blocked process threshold サーバー構成オプション - SQL Server | Microsoft Docs


以下のとおり、blocked process threshold サーバー構成オプションを設定変更します。今回は、10秒間ブロッキングが発生した場合にイベントを生成する設定としています。

sp_configure 'show advanced options', 1 
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 10
GO
RECONFIGURE


上記の変更が正しく行うことができていたか以下の通り確認します。上記で指定したとおり、値が10秒となっていますね。

blocked process threasholdサーバー構成オプション変更

Blocked Process Report イベントクラスを使用して、ブロッキングの情報を取得する

上記までで、blocked process threshold サーバー構成オプションを設定しました。上記でイベントが発生するようになりましたが、それをキャプチャするためには別途設定が必要です。

Blocked Process Report イベントクラスと呼ばれるものを使用してブロッキングの情報を取得します。今回は拡張イベントを使って、Blocked Process Report イベントクラスを使用し、ブロッキングのイベントを取得してみます。

Blocked Process Report イベント クラス - SQL Server | Microsoft Docs


SSMSのオブジェクトエクスプローラーから、「管理」-「拡張イベント」-「セッション」を右クリックし、「新規セッション」を選択します。
以下のような画面が開きますので、セッション名を任意で入力し、「セッションの作成後すぐにイベントセッションを開始する」をチェックしておきます。

拡張イベント設定①


次は、ページの選択で「イベント」を選択し、イベントライブラリから「blocked_process_report」を選択します。今回はこれで、「OK」ボタンを押下して作成完了です。

拡張イベント設定②

ブロッキングを発生させて、拡張イベントで確認してみる

上記手順まででほぼ準備は完了です。これからブロッキングを発生させて確認していきますが、その前に拡張イベントのセッション欄に追加で表示されたセッションを右クリックして、「ライブデータの監視」を選択します。

拡張イベント設定③

以下のように情報を取得する画面が表示されます。これで準備はすべて完了しました。ブロッキングを発生させましょう。

拡張イベント設定④


新しいクエリでSQLシートを作成し、以下の通り更新SQLを実行します。

ブロッキングを発生させる①

もう一度新しいクエリでSQLシートを作成し、以下の通り更新SQLを実行します。同じ行を更新しているので、あとから実行しているこちらのUPDATE文はブロッキングされます。このまま、10秒程度待ちます。

ブロッキングを発生させる②


すると、先ほど「ライブデータの監視」で表示した画面に何やら行が追加で表示されました。これが今回色々と設定したおかげで取得できたブロッキングの情報になります。

拡張イベントでブロッキング情報を確認する①

中身を簡単に見てみましょう。「bloked_process」フィールドをダブルクリックするとXMLブロッキングの細かい情報を見ることができます。

拡張イベントでブロッキング情報を確認する②

上記の情報を確認することで、ブロックしているSQLブロッキングされているSQLどちらも確認することができます。

おわりに

いかがでしたでしょうか。私自身作業してみて、とても簡単にブロッキング情報を取得することができたと思いました。今回はリアルタイムに確認しましたが、拡張イベントはファイル出力することができるので、後追いで問題のあるSQLなどを確認することができると思います。

ここらへん上手く活用して、実際の運用作業に役立てることができそうです。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

SQL ServerでセッションIDから実行中のSQLを確認する方法

先日、以下の記事でリアルタイムにブロッキングの状態を見るコマンドを紹介しました。
SQL Serverでリアルタイムにブロッキングの状態を見る - ITエンジニアの成長ブログ

上記は、ブロッキングが起きていてかつそのセッションIDまで知ることができるのですが実際にそれがどのようなSQLによって、ブロッキングが発生しているのかという中身を知ることができませんでした。そこで、その中身を知るためのコマンドを調査しましたので、今回もここにメモ程度に残します。

早速ですが、コマンドは以下の通りです。「DBCC INPUTBUFFER」と呼ばれるデータベース管理コマンドを使います。
DBCC INPUTBUFFER (Transact-SQL) - SQL Server | Microsoft Docs

たとえば、あるセッションで以下のようなSELECT文を実行したとします。このセッションのセッションIDは、56になりますね。

あるセッションのSELECT実行結果

そこで別のセッションで、以下のように上記で取得したセッションIDを引数に「DBCC INPUTBUFFER」コマンドを実行します。

DBCC INPUTBUFFER実行結果

上記のとおり、EventInfoカラムにセッションID=56のSQL実行結果を取得することができました。とても手軽に確認することができるので、今度何かしらで調査する際には使えるかもしれません。

今回はこの辺で失礼いたします。最後までお読みいただきありがとうございました。

SQL Serverでリアルタイムにブロッキングの状態を見る

先日、SQL Serverデッドロック関連について調べていたところブロッキングの状態を簡単に見ることができる便利なコマンドを見つけましたのでここにメモをしておきます。

そのコマンドとは、システムストアドプロシージャのsp_whoです。
sp_who (Transact-SQL) - SQL Server | Microsoft Docs

実行すると、現在のSQL Serverインスタンス内の現在のプロセスの状態を確認することができるようです。

実際に私のローカル環境で試してみたところ、以下のようにいくつかのレコードが出力されました。

sp_who実行結果

ユーザーの操作とは関係ないものが大多数ですが、この情報からたとえばあるプロセスがどのプロセスによってブロッキングされているかが分かります。

若干分かりづらいですが、上記の図だとspid=68のstatusは「suspended」になっており、何らかのイベントを待っている状態です。

さらに、spid=68のblkカラムが「56」となっていますが、これはブロッキングされている場合はセッションIDを表しており、このspid=68のセッションは、spid=56のセッションにブロッキングされていると解釈できます。

使い方はざっとこのような形で、引数を使ってあるログインやセッションIDでフィルターすることもできます。詳細は冒頭のリンクからご確認いただければと思います。

あまり、リアルタイムにブロッキングの状態を見ることは少ないかもしれませんが、もしもの時に役立つと思います。
因みに今回のsp_whoのコマンドの実行結果は、以前トランザクションのロックを解放する方法を記載した記事で使用した利用状況モニターと似たような結果を取得することができます。よろしれければ以下ご参考までにリンクを張っておきます。
SQL Serverでトランザクションのロックを解放する方法② - ITエンジニアの成長ブログ


今回も最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

SQL Serverで実行中のトランザクションを確認する方法

SQL Serverで実行中のトランザクションを確認する方法を知りましたので、ここにメモします。

確認方法はとても簡単で以下のコマンドを実行すればOKです。

DBCC OPENTRAN(‘データベース名’)

私の環境で実行した結果は以下の通りです。

DBCC OPENTRAN実行結果1

もし、実行中のトランザクションが存在しない場合は以下のように表示されます。

DBCC OPENTRAN実行結果2

オプション、WITH TABLERESULTSを付けるとテーブル形式で表示することもできます。

DBCC OPENTRAN実行結果3


因みに、DBCC OPENTRANコマンドで表示されるトランザクションは、指定したデータベースのトランザクション ログ内に存在する最も古い トランザクションのようです。

そのため、実行中のすべてのトランザクションを表示したいといった要件では使えないということですね。私がネットで見た記事では、トランザクションログが圧縮されないケースにおいて、アクティブなトランザクションが原因といった場合にこのコマンドを使って調べていました。

今日は簡単にコマンドの紹介まで。最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

SQL Serverのトランザクションログの使用状況を確認する

SQL Serverトランザクションログの使用状況を確認する方法を知りましたので、ここにメモします。

以下のコマンドで各データベースのトランザクションログの使用状況を確認することができます。

DBCC SQLPERF(LOGSPACE)


私の環境で実行した結果は以下の通りです。

DBCC SQLPERFコマンド実行結果

Log Size(MB)が、現在ログに割り当てられているファイルサイズで、Log Space Used(%)がログの使用済み領域の割合となるようです。

たとえば、sampleDBでは7.992188がログに割り当てられているサイズ(MB)で、実際の使用済み領域は、15.83578(約15%)となりますね。

因みに、トランザクションログの使用状況の確認方法ですが他にもあるようで、SQL Server 2012以降では上記のDBCC SQLPERF(LOGSPACE)の代わりにsys.dm_db_log_space_usageを使用する方法を推奨しているようです。

私の環境で実行した結果は以下の通りです。

sys.dm_db_log_space_usageの参照結果

実行結果の詳細の説明は割愛しますが、DBCC SQLPERF(LOGSPACE)と同様の情報に加えて、前回のログ バックアップ以降に使用された領域の量も確認することができるようです。

詳細は、本記事の下部の参考欄にあるMicrosoftの公式サイトをご参照ください。

今回は簡単ですが、コマンドの紹介まで。
最後までお読みいただきありがとうございました。今回はこの辺で失礼いたします。

SQL Serverの論理バックアップデバイスについて

最近SQL Serverのバックアップ・復元周りを勉強しているのですが、その中で論理バックアップデバイスなるものを知りましたので、備忘としてここに残したいと思います。

論理バックアップデバイスとは?

簡単に説明すると、バックアップファイルへのエイリアスというイメージで良さそうです。

通常であれば、たとえば完全バックアップを行う場合のクエリは以下のようなものになると思います。バックアップ先のファイル名を物理パスとして記載しています。

BACKUP DATABASE [testdb]
TO DISK = N'c:\tmp\testdb.bak';


論理バックアップデバイスを作成することで、以下のようにスマートにクエリを書くことができます。つまり、ここでは論理バックアップデバイスとしてtestdbBackupsを指定することで、たとえば「c:\tmp\testdb.bak」にバックアップすることができます。

BACKUP DATABASE [testdb]
TO [testdbBackups];

長くて間違えやすい物理パス名の代わりに、論理バックアップデバイスを使うことでクエリも非常に簡潔で見やすくなると思います。

論理バックアップデバイスの作り方

それでは、論理バックアップデバイスの作り方をSSMSで簡単に説明します。

以下のキャプチャのとおり、SSMSのオブジェクトエクスプローラーの「サーバーオブジェクト」-「バックアップデバイス」を選択して右クリックし、「新しいバックアップデバイス」を選択します。

SSMSプロジェクトエクスプローラーのサーバーオブジェクト

以下のようなダイアログが開くので、「デバイス名」とバックアップ先の「ファイル」を指定します。
ここでは、「デバイス名」にtestdbBackups、「ファイル」にC:\tmp\testdb.bakを指定しました。

論理バックアップデバイスの作成ダイアログ

上記2項目を指定して、「OK」ボタン押下で論理バックアップデバイスの完成です。以下のように表示されていたら問題なく作成されています。これで記事冒頭で紹介したように論理バックアップデバイスを指定して、バックアップをすることができます。

バックアップデバイスに作成したものが追加されている

他にも

因みに既存の論理バックアップデバイスは、sys.backup_devicesビューを参照することで確認することができます。以下のような結果が表示されます。各カラムの意味は本記事下部の公式サイトリンクからご確認ください。

sys.backup_devicesビュー確認

あと、論理バックアップデバイスですがSSMSだけではなくてTransact-SQLでも、もちろん作成できます。以下のようなクエリになります。こちらも詳細は、本記事下部の公式サイトリンクからご確認ください。

USE AdventureWorks2016 ;  
GO  
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak' ;  
GO

おわりに

いかがでしたでしょうか。あまり知っていてもいなくても正直変わらなさそうですが、バックアップクエリをシンプルに出来たりするので使ってみるのも良さそうですね。

今回は簡単ですがここまで。最後までお読みいただきありがとうございました。