【SQL Server】データの格納ページ位置の確認方法
テーブルに格納されているレコードがどのページにあるのか確認する方法です。
デッドロックの調査や以前説明したラッチ状況を確認する場合などに使えるかと思います。
sys.fn_PhysLocFormatter
今回確認に使うundocumentedなファンクションです。
どのような内容なのかチェックします。
sp_helptext [sys.fn_PhysLocCracker]
上記から、sys.fn_PhysLocCrackerの引数にphysical_locatorを渡して実行すればよいことがわかります。
試しに実行してみます。
SELECT * FROM dbo.EMPLOYEES CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS fPLC ORDER BY fPLC.file_id, fPLC.page_id, fPLC.slot_id
sys.dm_db_database_page_allocations
また、データだけでなくクラスターインデックスなどに存在しているルートや中間ノードがどのページにあるかを知りたい場合は、以下のようなクエリでわかるかと思います。
SELECT [dpa].[page_level] AS [page_level], [dpa].[allocated_page_page_id] AS [page_id], [i].[name] AS [index_name], [dpa].[page_type_desc], [dpa].[previous_page_page_id], [dpa].[next_page_page_id] FROM sys.dm_db_database_page_allocations( DB_ID('sales'), OBJECT_ID('dbo.EMPLOYEES'), 1, NULL, 'DETAILED' ) AS [dpa] INNER JOIN sys.indexes AS [i] ON [dpa].[object_id] = [i].[object_id] AND [dpa].[index_id] = [i].[index_id] WHERE [dpa].[page_level] IS NOT NULL ORDER BY [dpa].[page_level] DESC, [dpa].[allocated_page_page_id]
尚、sys.dm_db_database_page_allocationsの実行の仕方は次のとおりです。
Syntax :
sys.dm_db_database_page_allocations
(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)
Parameters :
@DatabaseId | database id [not null] |
@TableId | object id |
@IndexId | index id |
@PartionI | partition id |
@Mode | LIMITED or DETAILED |
例えば、以前説明したもの【SQL Server】ロックとラッチのトレース - 小物SEのメモ帳
と同じものを確かめてみると、ラッチを確保していたルート、中間ノード、リーフ(データ)のページと一致することがわかります。
※page_levelが高いほど上位のノードです。
ルートページ:75715
中間1ページ:5615、75716、109086、143394
中間2ページ:5009、5624、5958、6209…
あまり普段使用しませんが、レコードがどのページにどれだけ格納されているか、インデックスのノードがどのページに格納されているのか調べる方法となりますので把握しておいて損はないかと。
【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
拡張イベントによるトレース
拡張イベントを作成できたら、セッションを開始します。
セッション開始後、状況をリアルタイムで確認するために、「ライブデータの監視(L)」を選択します。
対象のセッションで今回試したいクエリを実行してみます。
先ほど「ライブデータの監視(L)」で開いたウィンドウに結果が表示されているかと思います。
クエリ実行後は拡張イベントによる監視を停止します。
トレース結果の確認
拡張イベントによるトレース結果から処理の流れを結果を見てみます。
①OBJECTに対するインテント共有ロック獲得
②ページ:75715に対する共有ページラッチ獲得
③ページ:5615に対する共有ページラッチ獲得
④ページ:75715に対する共有ページラッチ解放
⑤ページ:5009に対する共有ページラッチ獲得
⑥ページ:5615に対する共有ページラッチ解放
⑦ページ:5171に対する共有ページラッチ獲得
⑧ページ:5009に対する共有ページラッチ解放
⑨ページ:5171に対するインテント共有ロック獲得
⑩ページ:5171に対するインテント共有ロック解放
⑪ページ:5171に対する共有ページラッチ解放
⑫OBJECTに対するインテント共有ロック解放
実行プランは下記のようなシンプルなものです。
今回のクエリはClustered Index Seekのみであり、このクラスターインデックスの奥行は4であるためページラッチの動きは以下のようなイメージになるかと思います。
まずはロックを獲得し、その次にインデックスのルートページからラッチを獲得。次のノードのページからラッチを獲得できたら、前のラッチを解放するということを繰り返して、目的のページまで到達するようなイメージでしょうか。
更新処理や複雑なクエリだとロックやラッチはもっと複雑になるかと思いますが、簡単な処理で処理構造を理解しておくことは重要かと思います。
【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
ヒストグラムとして取得できる情報は以下となります。
カラム名 | 説明 |
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) )
同様に本統計情報の列情報である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) )
同じ結果が出力できていることがわかります。
尚、今回は統計情報のサンプリングが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
予測行数について
試しに用意したテーブルにてヒストグラムを確認します。
この状態でcolumn3が'10005743'と'10005744'であるものを検索する場合の推定の実行プランを見てみます。
◆column3が'10005744'を検索
予測行数がヒストグラムのEQ_ROWSと一致していることがわかります。
◆column3が'10005743'を検索
'10005743'はRANGE_HI_KEYとは一致していません。この値は、今回の統計情報のヒストグラム区間内に含まれている状態ですので、そのヒストグラム区間に含まれる重複する列値を持つ行の平均数としてAVG_RANGE_ROWS(RANGE_ROWS / DISTINCT_RANGE_ROWS)が、実行プランの予測行数として表示されています。
予測行数と実際の行数に乖離があると適切な実行プランでクエリを実行することができませんので、パフォーマンスが大きく低下する恐れがありますので注意が必要です。
【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する
CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。
※Python Anacondaを事前にインストール済みです。
事前にpyodbcをインストール
pip install pyodbc
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)
ストアドプロシージャを実行
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)
ストアドプロシージャ(引数あり)を実行
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)
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)
集計から可視化まで
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))
【SQL Server】【Bat】コマンドラインからのSQL呼び出しサンプル
SQLCMDを用いて、batファイルからSQLを呼び出し、結果ファイルに書き出すサンプルです。
スペースによる固定長ではなく、スペースを除いたカンマ区切りで出力したいことが多いかと思います。
システムの運用において定常業務の自動化などでちょっとしたbatから実行できるのでWindowsのタスクスケジューラで呼び出して実行したりとなにかと便利です。
構成は以下の通りです。
C:.
└─bat
│ sqlcmd_sample.bat
│
├─input
│ sql_input.sql
│
└─output
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