【SQL Server】【Bat】コマンドラインからのSQL呼び出しサンプル

SQLCMDを用いて、batファイルからSQLを呼び出し、結果ファイルに書き出すサンプルです。
スペースによる固定長ではなく、スペースを除いたカンマ区切りで出力したいことが多いかと思います。

システムの運用において定常業務の自動化などでちょっとしたbatから実行できるのでWindowsのタスクスケジューラで呼び出して実行したりとなにかと便利です。

構成は以下の通りです。

C:.
└─bat
　　│　　　sqlcmd_sample.bat
　　│
　　├─input
　　│　　　sql_input.sql
　　│
　　└─output

実際に実行したいSQLは次のような簡単なSQLとします。

SET NOCOUNT ON
GO

SELECT * FROM dbo.EMPLOYEES;

次のようなbatで呼び出すことができます。

set user=sa
set password=system
set server=YUUSUKE-VAIO\INS_NISHI2016
set database=sales
set input=.\input\sql_input.sql
set output=.\output\sqlcmd_result.csv


@echo off
cd /d %~dp0%

sqlcmd -U %user% -P %password% -S %server% -d %database% -i %input% -o %output% -W -s ,

オプションの内容としては、よく使うのは以下くらいでしょうか。

オプション	説明
-U	ユーザ名
-P	パスワード
-S	サーバ名
-d	データベース名
-i	インプットとなるSQLファイルのパスとファイル名
-o	アウトプットとなるパスとファイル名
-s	区切り文字

出力結果は以下の通り。

EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
-----------,----------,---------,-----,------------,---------,------,------,--------------,----------,-------------
100,Steven,King,J,515.123.4567,1987-06-17,AD_PRES,24000,NULL,NULL,90
101,Neena,Kochhar,N,515.123.4568,1989-09-21,AD_VP,17000,NULL,100,90
102,Lex,De Haan,Z,515.123.4569,1993-01-13,AD_VP,17000,NULL,100,90
103,Alexander,Hunold,N,590.423.4567,1990-01-03,IT_PROG,9000,NULL,102,60
104,Bruce,Ernst,J,590.423.4568,1991-05-21,IT_PROG,6000,NULL,103,60
105,David,Austin,DAUSTIN,590.423.4569,1997-06-25,IT_PROG,4800,NULL,103,60
106,Valli,Pataballa,J,590.423.4560,1998-02-05,IT_PROG,4800,NULL,103,60
107,Diana,Lorentz,L,590.423.5567,1999-02-07,IT_PROG,4200,NULL,103,60
108,Nancy,Greenberg,NGREENBE,515.124.4569,1994-08-17,FI_MGR,12000,NULL,101,100
109,Daniel,Faviet,Q,515.124.4169,1994-08-16,FI_ACCOUNT,9000,NULL,108,100
110,John,Chen,Q,515.124.4269,1997-09-28,FI_ACCOUNT,8200,NULL,108,100
111,Ismael,Sciarra,N,515.124.4369,1997-09-30,FI_ACCOUNT,7700,NULL,108,100
112,Jose Manuel,Urman,O,515.124.4469,1998-03-07,FI_ACCOUNT,7800,NULL,108,100

「-s」のオプションをつけないと扱いにくいスペースが混じるのでCSVとして扱いたい場合はつけておくとよいでしょう。

【SQL Server】クラスター化インデックスと非クラスター化インデックス

SQLserverのインデックスのうちよく使われる基本的なインデックスであるクラスター化インデックス、非クラスター化インデックス、付加列について解説します。これらはSQLserverのクエリをチューニングするうえでの基礎となるので押さえておくとよいでしょう。
以下のようなテーブルを例に説明します。

1.クラスターインデックス

クラスターインデックスは以下図のような構成となっています。
例)EMPLOYEE_ID = '1002'を検索した場合

ポイントとしては、クラスターインデックスのリーフノードにはテーブルのデータ部分が含まれていることでしょうか。
クラスターインデックスがないテーブルの場合ヒープになりますが、ここではヒープの説明は割愛させていただきます。

2.非クラスターインデックス

非クラスターインデックスは以下図のような構成となっています。
例)FIRST_NAME = 'Arizabeth' を検索した場合

非クラスターインデックスのリーフには、実データへの参照先として、ヒープの場合は行識別子、クラスターインデックスが存在する場合はクラスターインデックスのキー列が含まれています。
非クラスターインデックスのリーフだけで参照列が不足している場合には、行識別子やクラスターインデックスのキー列を使用してデータを取得します。

3.付加列

非クラスターインデックスに付加列を追加した場合は以下図のような構成となります。
例)FIRST_NAME = 'Arizabeth' を検索し、FIRST_NAME、LAST_NAME,EMAILを取得したい場合

WHERE句に使用される列を付加列を追加しても効果はありませんが、取得したい列が追加である場合に有効かと思います。

尚、複合インデックスの場合は中間リーフにもその列は含むことになります。WHERE句に使っていないが、参照したい列がある場合は付加列のインデックスを使うとよいでしょう。
特にその列のデータ長が大きい場合、複合インデックスにすると中間リーフの1ページあたりのデータ量が多くなってしまうので、付加列を使用することで中間リーフの1ページあたりのデータの格納効率がよくなります。

実際にクエリを実行して確認してみます。
各種インデックスは次の通り。

複合インデックスに含まれない列を参照した場合は、下記のようにキー参照(Key Lookup)が発生しています。

非クラスターインデックス(NCIX_1)にはEMAIL列を含んでいないため、先に非クラスターインデックスを参照した後、キー参照にてクラスターインデックスを参照しています。
イメージ的には以下のような形でしょうか。

実際にページの読み取り数を確認すると、論理読み取り数が4であることが確認できます。

複合インデックスに含まれる列だけを参照した場合は、下記のようにキー参照(Key Lookup)が発生していないことがわかります。

実際にページの読み取り数を確認すると、論理読み取り数が2であり、非クラスターインデックスのみでデータを取得できていることがわかります。

ちなみに今回は、下記のとおりインデックスの奥行はどちらも2であるため、各インデックスのページ読み取りは2でしたが、奥行が深くなると読み取り数はそれに応じて増えるかと思います。

キー参照が発生するとその分参照するページ数が増加するので、極力減らすことが基本となります。

同様に複合インデックスと付加列で参照できる場合は、下記のようにキー参照(Key Lookup)が発生していないことがわかります。

ちなみにインデックスの奥行を調べたい場合は以下のような手順で確認できます。
①オブジェクトエクスプローラーで調べたいインデックスを選択して、右クリックを押下し、「プロパティ（R)」を選択

②左ペインの「断片化」を選択

インデックスのページ数やリーフレベルの行、奥行をクエリで調べるなら下記のようなクエリで検索できます。
※他の調査用に使っていたものの使いまわしであまりイケてないですが。

SELECT 
    DB_NAME() AS [db_name]
	, SCHEMA_NAME(so.schema_id) AS [schema_name]
	, OBJECT_NAME(si.object_id) AS [Table_name]
	, si.name
	, si.type_desc
	, ips.alloc_unit_type_desc
	, ips.page_count
	, SUBSTRING(idxcolinfo.idxcolname,1,LEN(idxcolinfo.idxcolname) -1) AS [INDEX_Columns]
	, SUBSTRING(idxinccolinfo.idxinccolname,1,LEN(idxinccolinfo.idxinccolname) -1) AS [INDEX_Columns_Include]
	, dps.row_count
FROM		sys.dm_db_index_physical_stats (DB_ID(), NULL , NULL , NULL , 'DETAILED' ) AS ips,	
			sys.indexes AS si
LEFT JOIN	sys.dm_db_index_usage_stats AS ius
	ON	ius.object_id = si.object_id
	AND	ius.index_id = si.index_id
	AND	ius.database_id = DB_ID()
LEFT JOIN	sys.dm_db_partition_stats AS dps
	ON	si.object_id = dps.object_id
	AND	si.index_id = dps.index_id
LEFT JOIN	sys.objects so
	ON	si.object_id = so.object_id
LEFT JOIN	sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios
	ON	ios.object_id = si.object_id
	AND	ios.index_id = si.index_id
	AND	ios.partition_number = dps.partition_number
CROSS APPLY
	(SELECT sc.name + ','
		FROM			sys.index_columns AS sic
		INNER JOIN	sys.columns AS sc
		ON	sic.object_id = sc.object_id
		AND	sic.column_id = sc.column_id
		WHERE	
				sic.object_id = si.object_id
		AND	sic.index_id = si.index_id
		AND	sic.is_included_column = 0
	FOR XML PATH('') ) AS idxcolinfo(idxcolname)
CROSS APPLY
	(SELECT sc.name + ','
		FROM			sys.index_columns AS sic	
		INNER JOIN	sys.columns AS sc
		ON	sic.object_id = sc.object_id
		AND	sic.column_id = sc.column_id
	WHERE
				sic.object_id = si.object_id
		AND	sic.index_id = si.index_id
		AND	sic.is_included_column = 1
	FOR XML PATH('')) AS idxinccolinfo(idxinccolname)
WHERE
			(ius.database_id = DB_ID() OR ius.database_id IS NULL) 
	AND (ips.database_id = DB_ID() OR ius.database_id IS NULL)
	AND	so.schema_id <> SCHEMA_ID('sys')
	AND	ips.object_id	= si.object_id 
	AND ips.index_id	= si.index_id 
	AND ips.object_id	= so.object_id
	AND ips.object_id	= ios.object_id
	AND ips.index_id	= ios.index_id
	AND ips.partition_number = ios.partition_number
ORDER BY
	Table_name,
	name,
	page_count

出力結果は次のようになります。

【SQL Server】Plan Cacheのサイズと内訳

SQLServerでは実行プランに関する情報は、プランキャッシュ(Plan Cache)に格納してあります。
プランキャッシュはPerfmonやDMVにて以下の情報を取得することが可能です。

カウンター	説明
Object Plans	ストアドプロシージャ、ファンクション、トリガーのクエリプラン
Bound Trees	ビュー、規則、計算済みの列、およびCHECK制約のための正規化ツリー
Extended Storedd Procedures	拡張ストアドプロシージャのカタログ情報
SQL Plans	自動化パラメータ化クエリ、アドホッククエリのクエリプラン
Temporary Tables & Table Variables	一時テーブルおよびテーブル変数に関連するキャッシュ情報

#詳細は以下MSDNを参照してください。
https://msdn.microsoft.com/ja-jp/library/ms177441.aspx

またその時点の詳細な内訳をみる場合は以下のような手順でSSMSから確認できます。
①左ペインのオブジェクトエクスプローラー上のインスタンス名を選択して右クリックを押下し、「レポート(P)-「標準レポート」-「メモリ消費量」を選択

②表示されたレポートの最下部にある「コンポーネント別のメモリ消費量」を開きます

③コンポーネント別のメモリ使用量が表示されます
#CACHESTORE_[xxx]というコンポーネントでそれぞれ確認できます。

SQL Serverはメモリ負荷が高い場合に、コスト値の低いプランを削除してプランキャッシュのサイズを管理しています。その削除までのメモリ負荷の制限値はSQL Server2012であれば以下のように算出されます。

ターゲットメモリに対し、0GB～4GBのうち75% + 4GB～64GBのうち10% + 64GB～のうち5%

例えばターゲットメモリが40GBであれば、
プランキャッシュの制限値 = 4 GBの75％(3GB) + 4 GBを超えた分の10％（3.6GB）= 6.6GB
となるかと。

上記はSQL Server2012の場合ですが、それ以前の情報やプランキャッシュの仕組みについて、以下のMSDNにかなり詳細な記載がありますので一読しておくとよいでしょう。
https://msdn.microsoft.com/en-us/library/cc293624.aspx?f=255&MSPPError=-2147217396

【SQL Server】テーブルごとのレコード件数およびディスク使用量を出力する

前回に引き続き、こちらもよく使うので備忘録です。
レコード件数はクエリの性能に影響しますので、取得の仕方を把握しておくとよいかと思います。

左ペインのオブジェクトエクスプローラのデータベースを選択して、右クリックを押下し、「レポート(P)」-「標準レポート」-「テーブルごとのディスク使用量」を選択。

下記のように選択したデータベース内のすべてのテーブルのレコード件数やデータ容量、インデックス容量を取得することができます。

また、同様の情報は以下のようなSQLでも取得可能です。

DECLARE @table TABLE( 
		[TableName] SYSNAME, 
		[rows] BIGINT,
		[reserved] VARCHAR(25),
		[data] VARCHAR(25),
		[index_size] VARCHAR(25),
		[unused] VARCHAR(25)
		 )
INSERT INTO @table
EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"
SELECT * FROM @table
GO

【SQL Server】接続しているDB内のすべてのテーブルの定義情報を抽出する

SQLServerにおいて、すべてのテーブルの定義情報をまとめて取得する際にお手軽に取得できるクエリです。
sp_Msforeachtableはundocumentedなストアドプロシージャですが、すべてのテーブルに対し、処理を実行してくれるため何かと便利です。

sp_MSforeachtable @command1 = "sp_help '?'"

尚、事前に以下手順で出力をファイル形式かつタブ区切りにしておくとエクセルに貼り付けやすくてよいかと思います。

①クエリエディタ上で右クリックを押下して、「結果の出力（R)」-「結果をファイルに出力」を選択。

②クエリエディタ上で右クリックを押下し、「クエリオプション」を選択。

③左ペインの「結果」配下の「テキスト」を選択し、出力形式(O)で”タブ区切り”を選択して「OK」を押下。

あとはクエリを実行すると出力先を決めることができるので、保存すればよいかと。

ちなみにテーブルおよびインデックスのDDLをすべて出力したいこともあるかと思います。
こちらに関しては、GUI操作に出力が簡単です。

①左ペインのオブジェクトエクスプローラでデータベースを選択し、右クリックを押下し、「タスク（T)」-「スクリプトの生成（E)」を選択。

②次へを押下。

③「テーブル」にチェックをいれます。
※今回はテーブル情報の出力ですが、ここでは下記のようにストアドプロシージャなども出力可能です。

④以下に応じて選択します。
全テーブルで1ファイルとしたければ、「生成するファイル」を単一ファイル
1テーブルごとに1ファイルとしたければ、「生成するファイル」をオブジェクトごとに1つのファイル

また、詳細設定(A)を選択すると下記のような出力オプションが選ぶことができます。
今回は、インデックスのスクリプトを作成をFalse⇒Trueとして実行してみます。

出力したファイルは下記のようなものとなります。

【SQL Server】再コンパイル時の原因調査

バッチでステートメントレベルの再コンパイルが発生した際の原因調査として、拡張イベントが利用できます。

下記のようにsql_statement_recompileイベントを拡張イベントとして追加すれば確認できます。

実際に当該イベントが発生した場合は以下のようにイベントが取得されます。

実行プランを再作成する条件として以下がありますが、後から調査する際にどれであったかわかるのは便利かと思います。
(※拡張イベントを設定することによる負荷増加は念頭に入れておく必要はあります。)

【実行プランを再作成する条件】
・クエリ(ALTER TABLE および ALTER VIEW)によって参照されるテーブルまたはビューに変更を加えた場合
・実行プランで使用されるインデックスに加えた場合
・UPDATE　STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
・実行プランで使用されるインデックスを削除した場合
・sp_recompile を明示的に呼び出した場合
・クエリによって参照されるテーブルを変更する他のユーザが、INSERT ステートメントまたはDELETE ステートメントを使用して大量の変更をキーに加えた場合
・トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
・WITH RECOMPILE オプションを使用してストアドプロシージャを使用して実行する場合

【SQL Server】リレーション図の出力

データベース内の各テーブルのER図の出力方法の備忘録です。

「データベースダイアグラム」を選択して右クリックを押下、「新しいデータベース ダイアグラム(N)」を選択します。

リレーション図に表示したいテーブルを選択します。

下記のように出力されます。
右クリックで「リレーションシップ ラベルの表示(L)」を選択すると、FK制約のラベルが表示できます。
右クリックで「ダイアグラムをクリップボードにコピー(P)」を選択すると、ダイアグラムのみをきれいにコピペすることができます。

このダイアグラムに表示されているテーブルにカラム追加等があった場合は、自動で反映されるので、SSMS上でサッと確認するには便利かと思います。