タイトル通りですが、SQL Serverであるクエリの処理時間を計測したかったので、その方法をここに備忘録として残します。
以下の記事がとても参考になりました。
SQL Serverで処理時間を計測する - Qiita
キャッシュの削除とか、よくわかっていないので勉強しておきたいところです。。
SQL ServerのSETオプションの設定を確認する方法
SQL ServerのSETオプションの設定を確認する方法を備忘録として、残しておきます。
（といっても、MSDocの内容をそのままコピーしたものですが）
以下のSQLを実行することで、確認できます。
SELECT S.Bits, FLAGS.* FROM ( SELECT optRef, posRef, flagCheck FROM ( SELECT ones.n + tens.n * 10 FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) ones(n), ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) tens(n) ) f1(powRef) CROSS APPLY ( SELECT POWER(2, powRef) WHERE powRef <= 16 ) f2(binRef) CROSS JOIN ( VALUES (@@OPTIONS) ) f3(optRef) CROSS APPLY ( SELECT (optRef & binRef) / binRef ) f4(flagRef) CROSS APPLY ( SELECT RIGHT(CONVERT(VARCHAR(2), CAST(powRef AS VARBINARY(1)), 2), 1) [posRef], CAST(flagRef AS INT) [flagCheck] ) pref ) TP PIVOT( MAX( flagCheck ) FOR posRef IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] )) P CROSS APPLY ( SELECT CONCAT ( '', [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] ), CONCAT ( '', [F], [E], [D], [C], [B], [A], [9], [8], [7], [6], [5], [4], [3], [2], [1], [0] ) ) S (stib, Bits) CROSS APPLY ( SELECT CAST(P.[0] AS BIT) /* 1 */ [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking. , CAST(P.[1] AS BIT) /* 2 */ [IMPLICIT_TRANSACTIONS] -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections. , CAST(P.[2] AS BIT) /* 4 */ [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed. , CAST(P.[3] AS BIT) /* 8 */ [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings. , CAST(P.[4] AS BIT) /* 16 */ [ANSI_PADDING] -- Controls padding of fixed-length variables. , CAST(P.[5] AS BIT) /* 32 */ [ANSI_NULLS] -- Controls NULL handling when using equality operators. , CAST(P.[6] AS BIT) /* 64 */ [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution. , CAST(P.[7] AS BIT) /* 128 */ [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query. , CAST(P.[8] AS BIT) /* 256 */ [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression. , CAST(P.[9] AS BIT) /* 512 */ [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected. , CAST(P.[A] AS BIT) /* 1024 */ [ANSI_NULL_DFLT_ON] -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. , CAST(P.[B] AS BIT) /* 2048 */ [ANSI_NULL_DFLT_OFF] -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls. , CAST(P.[C] AS BIT) /* 4096 */ [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string. , CAST(P.[D] AS BIT) /* 8192 */ [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression. , CAST(P.[E] AS BIT) /* 16384 */ [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/ ) AS Flags;
何やらすごいSQLだなと思いますよね。とりあえず、中身を読み取ろうとはせず私の環境で実行すると以下のようになりました。いい感じですね。各SETオプションごとに、1ならONで、0ならOFFであることが分かります。
しかし、なぜこんなとてつもないSQLなのでしょうか。以下のように「@@OPTIONS」のみをSELECTしてみます。
SELECT @@OPTIONS;
正直意味が分からない結果ですね。MSDocにそれについて記載されたところを抜粋してみます。単純に「@@OPTIONS」を取得した内容は、ビットで表したSETオプションを10進数にした結果が取得できているということみたいです。それをいい感じに表したのが最初のとてつもないSQLということでした。
補足
後で気づいたのですが、以前以下の記事でも同じようにSETオプションを確認する方法を紹介させていただいてました。
SQL Serverで現在の接続でアクティブになっているSETオプションを確認する - ITエンジニアの成長ブログ
上記の記事と、今回の記事の確認方法とで何が違うのかですが、以下のような感じがします（間違ってたらすみません）。
- @@OPTIONSの内容
以下のリンクにある「user options」サーバー構成オプションの設定状況のみを表示する。
user options サーバー構成オプションの構成 - SQL Server | Microsoft Learn
これはサーバーの設定である「user options」の情報を表しているのかなと思いましたが、試しにあるセッションでSETオプションを変えてみたところ上記の確認結果にも反映されたのでそうではないようです。
- DBCC USEROPTIONSの内容
すべてのSETオプションの中で現在のセッションで設定されているものを表示する
SET ステートメント (Transact-SQL) - SQL Server | Microsoft Learn
つまり、基本的には「DBCC USEROPTIONS」を見ておけば良さそうな気がします。他にも違いがあるのかは、ちょっと読み取れませんでした。
SQL Serverデッドロックの調査方法
SQL Serverでデッドロックを調査するための方法を、自分のメモ程度に紹介しておこうと思います。
何個かあるみたいなので、いくつか確認していきたいと思います。
また、本記事ではデッドロックの調査をするために必要な情報を取得するための方法を紹介するものであり、具体的にどのようにデッドロックを解決するかまでは紹介しておりませんので、その点はご了承ください。
トレースフラグ「1204」を設定する
まずは、トレースフラグ「1204」を設定する方法を試してみます。これを設定することにより、デッドロックが発生した場合にログを出力してくれるようになります。実際に設定してみて、どのように役立つか試してみます。
以下のようにコマンドを実行して、トレースフラグ「1204」をONにします。こちらはドキュメントにも記載の通り、グローバルスコープで設定するトレースフラグのようですね。
適当に以下の通りデッドロックを発生させてみます。
デッドロックが発生すると、以下のSQL Serverのログに出力されるみたいです。SSMSの「管理」-「SQL Server ログ」から見ることができます。
以下のようにデッドロックのログを見ることができるようです。デッドロックが発生したときの実際のクエリや、ロックの種類などの情報を見ることができます。
トレースフラグを設定しないとデッドロックが発生したことくらいしかわからないので、上記の情報があれば調査の手がかりになると思います。
トレースフラグ「1222」を設定する
それでは、デッドロックの情報をログ出力するもう一つのトレースフラグ「1222」も試してみます。因みに、こちらは先ほどのトレースフラグ「1204」の後に提供された機能のようです。
以下のようにコマンドを実行して、トレースフラグ「1222」をONにします。こちらも先ほどと同様にグローバルスコープのようですね。
また同じようにデッドロックを発生させてみます。
こちらもデッドロックが発生すると、SQL Serverのログに出力されるみたいです。同様に中身を見てましょう。
字が小さくて見えないかもしれませんが、先ほどのトレースフラグ「1204」に比べると情報量がこちらの方が多いです。たとえば、ロックの原因となったクライアントの情報やインデックスの名前など、先ほどにはない情報も出力されています。
どちらかを設定するので迷うのであれば、迷わずこちらを選べば良さそうです。ただ、どちらも同時に設定することができるので通常であれば両方設定するみたいです。
拡張イベントの「system_health」で確認する
トレースフラグ「1204」「1222」をあらかじめ設定しておくことで、デッドロックの情報がログ出力されるようになりました。あともう一つだけ、紹介させていただきます。それが、拡張イベントの「system_health」で確認する方法です。
system_health セッションの使用 - SQL Server | Microsoft Learn
確認方法ですが、こちらは事前準備不要です。以下のように、SSMSで「管理」-「拡張イベント」-「セッション」に「system_health」があると思います。
上記の「system_health」-「package0.event_file」を右クリックし、「ターゲットデータの表示」を選択します。すると以下のような画面が開くと思います。
デッドロックとは関係ないたくさんの情報が出力されているので、以下のようにメニューの拡張イベントからフィルターを選択します。
フィルターで、先ほど表示されたログをデッドロックのものだけにします。以下のように、フィールドに「name」、演算子に「＝」、値に「xml_deadlock_report」を入力してOKを押下します。
すると、デッドロックが発生していれば以下のようにフィルターされると思います。
デッドロックタブを開くと以下のように、グラフを見ることができます。これは視覚的に、どのプロセスが何をロックして何をロック解放待ちしているかが分かるのでとても分かりやすい情報を取得することができます。
また、詳細タブのレコードをダブルクリックすると以下のようにXML形式でより細かい情報を取得することができます。
内容的には、トレースフラグの設定で取得するものがsystem_healthでも取得できそうですのでこちらだけ見ておけば良いかもしれません。結局ケースバイケースかとは思いますが。
SQL Serverの計算列とは？
つい最近、マイクロソフトが提供しているデータベースである「AdventureWorks2016」の定義を見ていたところ、列の定義に”計算値”と記載がありました。たとえば、以下の"StockedQty"がそれです。
これなんだろうということで、計算列を調べた結果をメモ程度に残します。
計算列とは？
計算列とは、文字通りではありますが計算した結果を保持する列です。
テーブルの計算列の指定 - SQL Server | Microsoft Learn
たとえば、単価と数量と金額がテーブルに定義してあるとします。金額は、単価×数量で計算されて登録される要件であるとします。その場合、通常であればアプリケーション側でその計算をしなくてはなりません。単価や数量が変われば、もちろん再計算する必要があります。
そこで、金額カラムを計算列として定義しその計算式を単価×数量という形であらかじめ、テーブル定義に計算ロジックを埋め込むことができます。
つまり、面倒だが必ず処理しなくてはならない計算をデータベース側に任せることができるということですね。
おわりに
簡単に計算列を確認しましたが、こんなのあるんだと正直びっくりしました。簡単で規則的な計算をアプリケーション側で毎回実施するのも、計算式を誤ったり、そもそも計算を忘れてしまったりするかもしれないのでとても便利だなと思いました。
そもそも、正規化の話では計算項目はテーブルに持たないみたいな話もあるかもしれませんが、そこは一旦置いておいて。
今後、使えるかもしれないので覚えておいて損はないかなと思いました。
今回はここまで。最後までお読みいただきありがとうございました。
参考
こちら参考にさせていただきました。
計算列（SQL Server） | DB & SQL 技術ブログ
SQLServerのComputed Column(計算列)についての備忘録 - 時が癒す
SQL Serverのデータ型「money」について
今回は、SQL Serverのデータ型「money」について簡単に調べたことをまとめたいと思います。
なぜ調べようと思ったかですが、今まで携わってきたシステムでデータ型「money」を使ったことがなかったためです。なぜ、使ってないのか。実は、便利なのでは。色々と気になりましたので調べる気分になりました。
データ型「money」とは？
SQL Serverには、データ型として「money」があります。文字通り、金額値または通貨値を表すデータ型みたいです。
money と smallmoney (Transact-SQL) - SQL Server | Microsoft Learn
実は、「money」だけでなく「smallmoney」というデータ型もあります。どちらも使い方は同じで、格納する値の大きさの要件によってどちらかを選ぶか決めれば良さそうです。
| データ型 | 内部的な使用バイト数 | 扱えるデータの範囲 |
|---|---|---|
| smallmoney | 4 | -214,747.3648 ～ 214,747.3647 |
| money | 8 | -922,337,203,685,477.5808 ～ 922,337,203,685,477.5807 |
「smallmoney」は、日本円だと約24万円まで使えて「money」は約900兆円まで扱えるみたいですね。
また、上記表の”扱えるデータの範囲”を見てわかるように小数点以下4桁まで値の保持を行うことができるみたいです。日本では、実際に小数点以下、つまり円以下の金額を扱うことはないと思うのであまり意味なさそうな感じがします。たとえば、アメリカでは1ドルの下に”セント”という単位があるので、使えそうな気がしますが実際はどうなのでしょうか。
「money」の定数は？
「money」型もしくは、「smallmoney」の定数はどのように表すかですが、基本的にはintなどと同じで、数値文字列で引用符では囲まない形で表現すれば良いとのことです。
Constants (Transact-SQL) - SQL Server | Microsoft Learn
上記のリンクにも記載ありますが、以下のように記載することで「money」型定数となるようです。通貨記号をプレフィックスとして付加できるのが通常の整数の定数とは違うところですね。ちなみに、たとえ通貨記号をプレフィックスとして付けても通貨の情報は格納しないようです。あくまで数値のみ格納するということですね。
-- ドル $12 $542023.14 $-23 -- 円 ¥12 ¥542023.14 ¥-23
データ型「money」、「smallmoney」のメリデメは？
それでは、ざっくりとデータ型の概要を把握しましたが実際にこの「money」「smallmoney」のメリット、デメリットを以下に記したいと思います。
※あくまで個人的な見解です
メリット
・お金を格納するカラムであることがデータ型でわかる
デメリット
・日本通貨では小数点以下がないので、場合によっては無駄なストレージ領域を消費する
→たとえば、「smallmoney」は「int」と同じようにストレージを4バイト使用しますが、小数点部分があるため「int」と比べると整数部の表せる範囲が4桁分少なくなります。
そのため、本来ならば「int」で問題ないケースでも「smallmoney」の上位の「money」型にすることで8バイトを使用してしまい、ややストレージの領域を消費することになります
メリット、デメリットはちょっと調べてもあまり分からなかったのでこれぐらいしか思いつきませんでした。。。
おわりに
いかがでしょうか。SQL Serverにデータ型「money」が存在することは以前から知っていましたが、使ったことがなかったので今回少し見てみました。実際に使う場面は少ないかもしれませんが、いつか役に立つかもしれないので概要だけは把握しておきたいなと思いました。
実際に金額を格納する場合は、「int」「bigint」「decimal」も使えると思うので、あまりメジャーじゃない（たぶん・・）型を使う必要はなさそうな感じがします。詳しい人にお聞きしたいところです。。
実際に「money」型を使っている日本のシステムはどのくらいあるのでしょうか。とても気になります。
今回はここまで。最後までお読みいただきありがとうございました。
SQL Serverのロックエスカレーションの発生確認方法
今回は、SQL Serverのロックエスカレーションが発生しているかどうかを確認する方法を備忘として残したいと思います。
主に、以下の方の記事を参考にさせていただきました。
SQL Server / SQL Database パフォーマンスチューニング & トラブルシューティング シリーズ : SQL Server のロックの基本的な動作 at SE の雑記
ロックエスカレーションの発生確認方法
いくつか確認方法はあるようですが、DMVで確認できる方法が一番簡単なのでこちらを紹介します。
SELECT database_id, OBJECT_NAME(object_id), index_id, index_lock_promotion_attempt_count, index_lock_promotion_count FROM sys.dm_db_index_operational_stats( DB_ID(N'データベース名'), OBJECT_ID(N'テーブル名'), NULL, NULL )
上記のなかで重要なのは、もちろん以下の2つのカラムです。
「index_lock_promotion_attempt_count」がロックエスカレーションの成否にかかわらず試行した回数で、「index_lock_promotion_count」が実際にロックエスカレーションを試行した回数と読み取れます。
因みにこの回数は、SQL Serverのサービスを最後に起動してからの累積値となるようで、再起動したらレコードごと消えていました。だいたいどのくらい発生しているのかを把握したいときには、これで十分かなと思います。
ただ、実際にいつロックエスカレーションが発生したかどうかなどがわからないので、その場合は別の方法で確認する必要がありそうです。
（冒頭の参考にさせていただいた記事には、拡張イベントで取得できる旨記載がありました）
SQL Serverのテーブルのロックエスカレーションの設定の確認方法
タイトル通りですが、テーブル単位で設定できるロックエスカレーションの設定の確認方法をこちらにメモしておきます。
ロックエスカレーションとは？
簡単にロックエスカレーションについてざっくりと説明します。
SQL Serverでは、行単位のロックが大量に行われるようなクエリが実行された場合、場合によってはたとえば行単位のロックを、テーブル単位のロックに変更します。これをロックエスカレーションと呼んでいます。これは、ちまちまと行単位にロックをするのとテーブル一つをロックするのかによって、SQL Serverの負荷が異なるためです。テーブル単位のロックにすることで、一つロックを獲得すればOKになるのでSQL Serverとしては負荷が軽いことになります。
テーブルのロックエスカレーションの設定の確認方法
それでは、さっそく設定方法の確認ですが以下で行えます。
SELECT name, lock_escalation, lock_escalation_desc FROM sys.tables WHERE name = 'テーブル名'
私の環境で実行した結果は、以下の通りです。
それぞれのカラムの意味はざっくり以下の通りです。
| カラム名 | 説明 |
|---|---|
| name | テーブル名 |
| lock_escalation | テーブルの LOCK_ESCALATION オプションの値 0 = TABLE 1 = DISABLE 2 = AUTO |
| lock_escalation_desc | lock_escalationオプションについての説明。 有効値は、TABLE、AUTO、および DISABLE |
上記の設定値の意味に関してはの説明は、今回は割愛します。以下、ALTER TABLEのドキュメントに値の意味について記載ありますので、気になる方はご参照いただければと思います。
