読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL Server】ロックとラッチのトレース

SQL Serverの待機イベントの一つであるロックとラッチがどのような順番で獲得され、解放されるのかトレースしてみました。

拡張イベントの設定

トレースするにあたって拡張イベントを使います。
今回試すSQLはセッション63にて実行するので、フィルタしてあります。

CREATE EVENT SESSION [Lock_and_Latch_Mon] ON SERVER 
ADD EVENT sqlserver.latch_acquired(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.latch_released(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(63))),
ADD EVENT sqlserver.lock_released(
    ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(63)))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\Lock_and_Latch_Mon.xel')
WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
拡張イベントによるトレース

拡張イベントを作成できたら、セッションを開始します。
f:id:utiowa:20170219170926p:plain

セッション開始後、状況をリアルタイムで確認するために、「ライブデータの監視(L)」を選択します。
f:id:utiowa:20170219170939p:plain

対象のセッションで今回試したいクエリを実行してみます。
f:id:utiowa:20170219170951p:plain

先ほど「ライブデータの監視(L)」で開いたウィンドウに結果が表示されているかと思います。
f:id:utiowa:20170219171009p:plain

クエリ実行後は拡張イベントによる監視を停止します。
f:id:utiowa:20170219171000p:plain

トレース結果の確認

拡張イベントによるトレース結果から処理の流れを結果を見てみます。
①OBJECTに対するインテント共有ロック獲得
②ページ:75715に対する共有ページラッチ獲得
③ページ:5615に対する共有ページラッチ獲得
④ページ:75715に対する共有ページラッチ解放
⑤ページ:5009に対する共有ページラッチ獲得
⑥ページ:5615に対する共有ページラッチ解放
⑦ページ:5171に対する共有ページラッチ獲得
⑧ページ:5009に対する共有ページラッチ解放
⑨ページ:5171に対するインテント共有ロック獲得
⑩ページ:5171に対するインテント共有ロック解放
⑪ページ:5171に対する共有ページラッチ解放
⑫OBJECTに対するインテント共有ロック解放

実行プランは下記のようなシンプルなものです。
f:id:utiowa:20170219171139p:plain

今回のクエリはClustered Index Seekのみであり、このクラスターインデックスの奥行は4であるためページラッチの動きは以下のようなイメージになるかと思います。
f:id:utiowa:20170219171306p:plain
f:id:utiowa:20170219171311p:plain
f:id:utiowa:20170219171317p:plain
f:id:utiowa:20170219171324p:plain

まずはロックを獲得し、その次にインデックスのルートページからラッチを獲得。次のノードのページからラッチを獲得できたら、前のラッチを解放するということを繰り返して、目的のページまで到達するようなイメージでしょうか。

更新処理や複雑なクエリだとロックやラッチはもっと複雑になるかと思いますが、簡単な処理で処理構造を理解しておくことは重要かと思います。

【SQL Server】統計情報のヒストグラムと実行プランの予測行数

SQL Serverでは実行プランの決定における情報のうちの一つとして統計情報があります。
その統計情報のうちデータの分布を表すヒストグラムについて説明します。
統計情報は下記クエリで取得できます。

-- 引数は1つ目がテーブル名、2つ目が統計名
-- 結果セットをまとめて取得
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH NO_INFOMSGS

-- ヒストグラムのみ個別に結果セットを取得
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM,NO_INFOMSGS

f:id:utiowa:20170218015250p:plain

ヒストグラムとして取得できる情報は以下となります。

カラム名 説明
RANGE_HI_KEY ヒストグラム区間の上限の列値
RANGE_ROWS ヒストグラム区間内 (上限は除く) に列値がある行の予測数
EQ_ROWS ヒストグラム区間の上限と列値が等しい行の予測数
DISTINCT_RANGE_ROWS ヒストグラム区間内 (上限は除く) にある個別の列値を持つ行の予測数
AVG_RANGE_ROWS ヒストグラム区間内 (上限は除く) にある重複する列値を持つ行の平均数 (DISTINCT_RANGE_ROWS > 0 の場合 RANGE_ROWS / DISTINCT_RANGE_ROWS)

※他の結果セットとして取得したものについては詳しくはMSDNを参照してください。
https://msdn.microsoft.com/ja-jp/library/ms174384.aspx

ヒストグラムについて

SQLServerが統計情報として取得して認識している列情報のデータ分布がここからわかります。
例えばDISTINCT_RANGE_ROWSでは、頻度ヒストグラムとしての算出結果が取得されています。
試しに、DBCCコマンドで得られたDISTINCT_RANGE_ROWSを棒グラフでPythonでプロットすると次のような結果が得られます。

import pyodbc
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = r'DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM'

df = pd.read_sql(
                 query
                ,conn
                )

# create graph(BAR)
df.plot(
        x = ['RANGE_HI_KEY'],
        y = ['DISTINCT_RANGE_ROWS'],         
        kind = 'bar',
        width = 1,
        alpha = 0.5, 
        figsize = (10, 5)
        )

f:id:utiowa:20170218015305p:plain
f:id:utiowa:20170218015310p:plain

同様に本統計情報の列情報であるEMPLOYEE_IDについて実際の分布を同じビンでヒストグラムのグラフをプロットすると次のようになります。

import pyodbc
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )


query = r'SELECT EMPLOYEE_ID FROM EMPLOYEES'

df1 = pd.read_sql(
                 query
                ,conn
                )

# create graph(HISTOGRAM)
df1.plot(
        y = ['EMPLOYEE_ID'],
        kind = 'hist',
        bins = [100,115,135,159,182,205,230,242,250,276,285,293,315,355,379,407,419,443,503,539,559,619,671,699,711,747,771,795,815,823,831,903,959,999,1071,1135,1151,1175,1199,1223,1295,1367,1375,1383,1407,1447,1487,1559,1575,1595,1619,1643,1667,1691,1715,1739,1763,1787,1811,1835,1911,1975,2039,2103,2167,2231,2295,2359,2423,2487,2551,2615,2679,2743,2807,2812,2813],
        alpha = 0.5, 
        figsize = (10, 5)
        )

f:id:utiowa:20170218015326p:plain
f:id:utiowa:20170218015332p:plain

同じ結果が出力できていることがわかります。
尚、今回は統計情報のサンプリングが100%であるため一致しています。統計情報としてサンプリングしている割合が少ない場合、実際の分布とSQLServerの認識している分布が異なるため予測行数と実際の行数に乖離がでることになります。
サンプリングの件数や割合を確認するためには上記のDBCCコマンドか、もしくは上記DBCCコマンドをJOINしたコマンドで確認できます。

-- Rows Sampledがサンプリング数
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER,NO_INFOMSGS

-- SAMPLE_PCTがサンプリングの割合
DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER JOIN DENSITY_VECTOR,NO_INFOMSGS


統計情報の更新はサンプリング数を明示的に指定して行うこともできますが、対象の件数の多い場合にサンプリングの割合を多くするとその分更新に時間がかかりますので注意が必要です。

UPDATE STATISTICS EMPLOYEES(EMP_EMP_ID_PK)
	WITH SAMPLE 20 PERCENT
予測行数について

試しに用意したテーブルにてヒストグラムを確認します。
f:id:utiowa:20170218015845p:plain

この状態でcolumn3が'10005743'と'10005744'であるものを検索する場合の推定の実行プランを見てみます。

◆column3が'10005744'を検索
f:id:utiowa:20170218020051p:plain

予測行数がヒストグラムのEQ_ROWSと一致していることがわかります。

f:id:utiowa:20170218020254p:plain

◆column3が'10005743'を検索
f:id:utiowa:20170218020349p:plain

'10005743'はRANGE_HI_KEYとは一致していません。この値は、今回の統計情報のヒストグラム区間内に含まれている状態ですので、そのヒストグラム区間に含まれる重複する列値を持つ行の平均数としてAVG_RANGE_ROWS(RANGE_ROWS / DISTINCT_RANGE_ROWS)が、実行プランの予測行数として表示されています。
f:id:utiowa:20170218021009p:plain


予測行数と実際の行数に乖離があると適切な実行プランでクエリを実行することができませんので、パフォーマンスが大きく低下する恐れがありますので注意が必要です。

【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する

CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。
Python Anacondaを事前にインストール済みです。

事前にpyodbcをインストール
pip install pyodbc

f:id:utiowa:20170212222804p:plain

SQLを実行
import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )
df = pd.read_sql(
                '''SELECT * FROM [dbo].[EMPLOYEES]'''
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df.head(10)

f:id:utiowa:20170212223058p:plain

ストアドプロシージャを実行
import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test'
df1 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df1.head(10)

f:id:utiowa:20170212223158p:plain

ストアドプロシージャ(引数あり)を実行
import pyodbc
import pandas as pd

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )
print df2.head(10)

f:id:utiowa:20170212223401p:plain


resampleを使用して歯抜けなく1年ごとの各値の平均をとる等、SQLだけでやるより簡単に行えて便利です。

import pyodbc
import pandas as pd

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )

df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0)
print df_mean.head(10)

f:id:utiowa:20170212223920p:plain

集計から可視化まで
import pyodbc
import pandas as pd
import seaborn as sns

pd.set_option('line_width', 100)

conn = pyodbc.connect(
                      r'DRIVER={ODBC Driver 13 for SQL Server};'
                      r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;'
                      r'DATABASE=sales;'
                      r'UID=sa;'
                      r'PWD=system'
                      )

query = 'EXEC sp_test2 @Param = 2'
df2 = pd.read_sql(
                query
                ,conn
                ,index_col = 'HIRE_DATE'
                ,parse_dates = 'HIRE_DATE'
                )

df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0)

# create graph(SALARY)
df_mean.plot.bar(
            x= [df_mean.index],
            y= [r'SALARY'], alpha=0.5, figsize=(10,5)) 

f:id:utiowa:20170212225116p:plain

【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のクエリをチューニングするうえでの基礎となるので押さえておくとよいでしょう。
以下のようなテーブルを例に説明します。
f:id:utiowa:20170205231657p:plain

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

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

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


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

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

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

3.付加列

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

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

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


実際にクエリを実行して確認してみます。
各種インデックスは次の通り。
f:id:utiowa:20170205231841p:plain
f:id:utiowa:20170205231845p:plain
f:id:utiowa:20170205231851p:plain
f:id:utiowa:20170205231857p:plainf:id:utiowa:20170205231901p:plain

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

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

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

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

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

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

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

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

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

②左ペインの「断片化」を選択
f:id:utiowa:20170205232142p:plain

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

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

出力結果は次のようになります。
f:id:utiowa:20170205232240p:plain

【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)-「標準レポート」-「メモリ消費量」を選択
f:id:utiowa:20170205180322p:plain

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

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


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)」-「標準レポート」-「テーブルごとのディスク使用量」を選択。
f:id:utiowa:20170201011812p:plain

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

また、同様の情報は以下のような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

f:id:utiowa:20170201011847p:plain