ITエンジニアの成長ブログ (original) (raw)

SQL Serverで最初から含まれている拡張イベントのsystem_healthセッションを簡単に紹介しておこうと思います。

system_healthセッションの公式のページは以下の通りです。
system_health セッションを使用する - SQL Server | Microsoft Learn

system_healthセッションは、既定で含まれている拡張イベントのセッションの1つです。パフォーマンスの問題をトラブルシューティングするときに役立つデータを収集しています。

そのため、何も設定しなくてもこのsystem_healthセッションから特定の情報を取得することができます。

実際に、system_healthセッションをSSMSで見てみましょう。以下の通り、拡張イベントがいくつかありますがその一つがsystem_healthセッションとなります。少し分かりづらいかもしれませんが、緑色になっているのでセッションは開始されていることがわかります。

system_healthセッション

先ほど、system_healthセッションは「パフォーマンスの問題をトラブルシューティングするときに役立つデータを収集しています」と言いましたが、具体的にどのようなデータを収集しているのかは冒頭でご紹介した公式のページに記載されています。

system_healthセッションで取得する情報

いくつかありますが、ロックによって長時間待機しているセッションの情報や、デッドロックの情報など取得していることがわかります。

実際にsystem_healthセッションで取得する情報を参照するには、SSMSであれば簡単に行えます。

「system_health」セッションを開き、「package0.event_file」を右クリックし、「ターゲットデータの表示」を押します。

ターゲットデータの表示

そうすると、以下のようにタブが開き、実際に収集されたデータを参照することができます。

system_healthセッションで取得したデータ

因みに、system_healthセッションは2つのターゲットにデータを格納します。「ターゲット」とは、拡張イベントのイベントデータを格納する保存先を定義する情報のことです。system_healthセッションは、「event_file」、「ring_buffer」ターゲットにそれぞれ格納します。

「event_file」は、実際にイベントデータをXELファイルという専用のファイルに格納していきます。「ring_buffer」はメモリ内にイベントデータを格納します。

基本的には、XELファイルを参照することになるかと思いますが、このファイルはSQL Serverのログディレクトリに格納されるように設定されています。XELファイルに関しては、SSMSにドラッグアンドドロップして簡単に開くことができます。

XELファイル格納場所

また、このXELファイルはローテーションされるようになっているので、ある程度イベントデータが出力されると、欲しい情報が消えてしまうので注意が必要です。デフォルトでは、ファイルの最大サイズは100MBで10世代管理しています。

因みに、公式のページでは、5MBで4世代管理という記載がされていたのですが、以下の機能強化により設定が変更されたようです。system_healthのページがまだ修正されていないようですね。
KB4541132 - 機能強化: SQL Server 2016、2017、および 2019 の既定の XEvent トレース system_health でサイズと保持ポリシーが増加しました - Microsoft サポート

system_healthはこのようにデフォルトで設定されており、簡単に参照することができるので万が一のときに役に立つ時がくるかもしれません。さらに詳しい情報は、公式ページをご参照いただければと。

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

先日、以下の記事でロック待ちのレコードを特定する方法を紹介しました。
SQL Serverでロック待ちのレコードを特定する - ITエンジニアの成長ブログ

記事内では、ロックリソースを取得することができる**%%lockres%%**と呼ばれる仮想列を紹介しました。

自分の環境でこの仮想列を色々と参照しているうちに、取得することができる情報の形式が異なる場合があることに気づきました。

実際にその違いを見ていきたいと思います。以下のTableAとTableBの違いは、主キーが設定してあるかどうか。TableAは主キーの設定なし、TableBは主キーをCol1に設定しています。

%%lockres%%で取得することができる情報の違い

上記の抽出結果を見てすぐにわかると思いますが、%%lockres%%で取得した情報の形式が異なります。今回はこの情報の形式の違いについて考えてみます。

まず、テーブルAですがこちらは主キーを設定していないため、クラスター化インデックスなしとなるため、ヒープになります。

以下のページに記載されておりますが、テーブルをヒープとして格納している場合は個々の行は、（ファイル番号:ページ番号:ページのスロット）という8バイトの行識別子である、「RID」で識別されます。
ヒープ (クラスター化インデックスなしのテーブル) - SQL Server | Microsoft Learn

TableAの%%lockres%%で取得することのできる情報は、確かに上記の説明通りの情報の形式であることがわかります。

そして、TableBについては、「(f1acef56732c)」という一見するとよくわからない情報が取得できていますが、これはマジックハッシュと呼ばれる値のようで、インデックスの行を指定する値らしいです。

この値は、sys.dm_tran_locksで、resource_typeが"KEY"の場合にresource_description列で取得できる値と同じです。

sys.dm_tran_locksの取得例

また、非クラスター化インデックスを参照してデータを取得する場合に、%%lockres%%の値はどうなるのかも試してみました。

クラスター化インデックスと非クラスター化インデックス

上がクラスター化インデックスで下が非クラスター化インデックスを使用してデータを抽出しているのですが、同じような情報の形式ではあるものの、%%lockres%%の値が異なっていますね。このことから、データを取得するアクセスパスによって異なる値を取得することがわかりました。

ヒープかそうでないかの違いで、%%lockres%%の値が異なることになると思っていたのですが、これは改めて勉強になりました。

あまり、まとまりがありませんが今回はこのへんで。最後までお読みいただきありがとうございました。

今回は、SQL Serverにおけるロック待ちのレコードを特定する方法を紹介したいと思います。

SQL Serverでロックの情報は、sys.dm_tran_locksというDMVを使用すれば取得可能です。

sys.dm_tran_locksの使用例

リソースの種類（resource_type）ごとにロックの情報を取得することができます。今回は、リソースの種類として「KEY」に着目したいと思います。以下の赤枠の部分ですね（4行目）。ちなみに、「KEY」はインデックス内の行を示します。

リソースの種類「KEY」に着目する

上記の4行目は、”request_mode”が"X"なので排他アクセスとなっていますが、"request_status"は"WAIT"なのでロック待ちの状態となっています。

ロック待ちの状態となっていますが、実際にどのテーブルの、どのレコードでロック待ちをしているのかを特定する場合は以下の2ステップで行います。

  1. ロックを保持している「KEY」のテーブルを確認する
  2. %%lockres%%によって、該当のロック待ちのレコードを特定する

まずは、”ロックを保持している「KEY」のテーブルを確認する”ですが、上記の例であれば"resource_type"が"OBJECT"のレコード（2行目）の"resource_associated_entity_id"を確認します。このカラムは、リソースの種類によって異なる値になるのですが、"OBJECT"の場合はオブジェクトIDとなります。そのため、以下のようなクエリで該当のテーブルを確認することができます。

SELECT * FROM sys.tables WHERE object_id = 1586104691;

実際に上記クエリを実行した結果は以下の通りです。テーブル名「SalesOrderHeader」を確認することができました。

「sys.tables」から該当のテーブル情報を取得する

上記以外にも以下のクエリでも確認することができます。お好きな方法で確認可能です。

SELECT OBJECT_NAME(1586104691);

OBJECT_NAME関数を使用してオブジェクト名を取得する

そして、2ステップ目の"%%lockres%%によって、該当のロック待ちのレコードを特定する”ですが、**%%lockres%%**は、ドキュメントには記載されていないものらしいのですが、これを使用することで該当のロック待ちのレコードを特定することができます。

以下のように、ロックを保持しているテーブルの抽出条件として**%%lockres%%**という仮想列を使用して、sys.dm_tran_locksの”KEY”のレコード（4行目）の"resource_description"の値を引数にして実行します。

SELECT * FROM Sales.SalesOrderHeader (NOLOCK) WHERE %%lockres%% = '(39fa2db6f94b)';

実際に上記クエリを実行してみると、以下の通り１つのレコードを取得することができました。これがロック待ちとなっている該当のレコードということになります。これで特定できましたね。

ロック待ちのレコードを特定

おわりに

いかがでしたでしょうか。

このように実際にどのレコードでロックが発生してしまっているかを確認することで、実際に問題のあるクエリや処理を見つけることができるかもしれません。

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

先日、uniqueidentifier型について調べた際に、合わせてNEWID関数についても調べました。
SQL Serverのuniqueidentifier型とは？ - ITエンジニアの成長ブログ

公式のNEWID関数ページの使い方の例で以下の記述がありました。

NEWID() 関数を使用してランダムなデータのクエリを実行する

NEWID関数がランダムな一意な値を生成するという仕組みを利用して、ORDER BY句でNEWID関数を指定してデータをランダムな並び順で取得するクエリです。

もしかしたらいつ使うかもしれないので、メモ程度にこちらに残しておきます。

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

お恥ずかしながら、今まで「uniqueidentifier」と呼ばれるデータ型の存在を知らなかったので簡単にまとめます。

uniqueidentifier型とは？

16バイトのGUIDを格納するためのデータ型です。ちなみに、GUIDはGlobally Unique Identifierの略でグローバル一意識別子と呼ばれている値です。
uniqueidentifier (Transact-SQL) - SQL Server | Microsoft Learn

一意識別子なので、重複しない値を表現する場合にこのデータ型を使用します。GUIDを格納するためのデータ型とはいえ、データ型自体が一意であることを保証することはありません。つまり、格納されているデータが一意であることを保証したければ、UNIQUE 制約を使用するか、後述するNEWID関数やNEWSEQUENTIALID関数を使用する必要があります。

uniqueidentifier型に格納する値について

uniqueidentifier型のカラムに格納するための値は、以下の2つの方法で初期化することができます。以下は、そのまま公式サイトを引用しています。

NEWID()関数について

上述した通り、uniqueidentifier値はNEWID()関数で初期化することができます。私の環境で試してみます。

簡単に試すために、SELECT句で使ってみます。

SELECT NEWID();

以下の通り、NEWID()関数は一意なuniqueidentifier値を出力することができます。

NEWID()の使用

今度は、以下のように変数に代入して出力してみます。これはNEWID()の公式ページの例を試しています。

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

変数に代入したuniqueidentifier値を出力できています。

NEWID()を変数に代入して出力する

ちなみに、uniqueidentifier値は+演算子で文字列と結合することはできないようです。

NEWID()を変数に代入して出力する（文字列結合でエラー）

NEWSEQUENTIALID()関数について

上述した通り、uniqueidentifier値はNEWSEQUENTIALID()関数でも初期化することができます。
NEWSEQUENTIALID (Transact-SQL) - SQL Server | Microsoft Learn

しかし、NEWID()関数と異なり使用箇所が限定されています。以下のように、CREATE TABLEもしくは、ALTER TABLEでuniqueidentifier型の列の既定の制約でのみ使用できます。こちらも、NEWID()関数と同様に一意なuniqueidentifier値を出力することができます。

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID());

試しに、既定の制約ではないSELECT句で使用すると以下の通りエラーになりました。

SELECT句でNEWSEQUENTIALID()関数を使用するとエラー

NEWID()とNEWSEQUENTIALID()の違いについて

どちらもGUIDを生成するという目的は同じですが、大きな違いは以下の2つでしょうか。

おわりに

いかがでしたでしょうか。uniqueidentifier型をそもそも知らなかった私にとっては、良い勉強になりました。

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

以前、バッチについて記事を書いた時にSQL Serverのステートメント「GO」についても簡単に紹介しました。
SQL Serverのバッチについて - ITエンジニアの成長ブログ

しかし、最近「GO」の後に何やら数字を指定しているステートメントを見かけました。最初は気にも止めてなかったのですが、実際はかなり重要な意味を持つことを知りました。

「GO」を紹介している公式ページにもその数字の意味は書いてあったのですが、前回は読み飛ばしてしまったようです。
SQL Server のユーティリティのステートメント - GO - SQL Server | Microsoft Learn

以下の通り、GOの後に数字（ここでは[count]）を入力すると、そのGOの前のバッチを指定した数字の回数実行します。

GO [count]

最初は、まさかそんなことありえないと思いましたが、以下の通りです。

GO 2の実行結果

意外に知らない方も多いのではないでしょうか。

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

SQL Serverのプロセス関連のDMVとして、ドキュメントで比較的よく見かけるものは以下の3つがあります。

これらのDMVでは、それぞれどのような情報を取得することができるのか理解していなかったので、改めて簡単に調べてみました。

「sys.dm_exec_sessions」と「sys.dm_exec_connections」の違い

個人的には、「sys.dm_exec_sessions」と「sys.dm_exec_connections」の違いが分かりにくいと感じましたが、前述した通り、OSI参照モデルに当てはめて考えるとそれぞれのビューで取得することのできる情報のだいたいのイメージはつくかなと思います。

また、それぞれの関係性ですが、それを紹介する図が公式のページに記載されているので、これを見ると理解が深まりそうです。
sys.dm_exec_connections (Transact-SQL) - SQL Server | Microsoft Learn

sys.dm_exec_sessionsとsys.dm_exec_connectionsのリレーションシップ

それぞれのビューが持つ、session_idというカラムで結合することができ、「sys.dm_exec_sessions」と「sys.dm_exec_connections」は一対ゼロまたは一対多の関係のようです。通常は、1対1の場合が多いけど、システムの内部セッション等の場合は「sys.dm_exec_sessions」に対応する「sys.dm_exec_connections」は存在しないということのようです。

また、MARSが使われていると1つの「sys.dm_exec_sessions」に複数の「sys.dm_exec_connections」が存在することになるそうです。

おわりに

いかがでしたでしょうか。まだまだ理解が足りなさそうなので今後これらのDMVを使いながら、知識を深めていきたいと思います。

補足ですが、今回調べていく中でセッションとコネクションの違いが分からず（今もあまり分かっていない部分がありますが）苦労しました。

「MARSが使われていると、1つのコネクションで複数のセッションが作成される」という説明が多かったのですが、上述した通り、DMVの関係としては、「sys.dm_exec_sessions」（セッション）が1で「sys.dm_exec_connections」（コネクション）が多なので、逆なのでは？と思いましたが、私の理解がやや違うのかもしれません。

今日はこのへんで。最後までお読みいただきありがとうございました。