【SQL server】トランザクション分離レベルについて

SQL serverで設定できるトランザクション分離レベルについて整理しました。
※デフォルトはREAD COMMITTEDになります。

トランザクション分離レベル	ロックの種類	ダーティリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	悲観的ロック	○	○	○
READ COMMITTED	悲観的ロック	×	○	○
REPEATABLE READ	悲観的ロック	×	×	×
SERIALIZABLE	悲観的ロック	×	×	×
SNAPSHOT	楽観的ロック	×	×	×
READ COMMITTED SNAPSHOT	楽観的ロック	×	○	○

SQL serverのREAD COMMITTED SNAPSHOTについては、更新時の断面をTempdbにSNAPSHOTをとってから処理する動きになりますのでOracleのREAD COMMITTEDに似たような処理になるかと思います。

尚、悲観的ロックと楽観的ロックについては一般的には以下のような理解でよいかと思います。

悲観的ロック：
更新処理時に更新対象のデータを参照してから更新が完了するまでの間、他のトランザクションからの参照をブロックします。

楽観的ロック：
更新対象のデータを参照した時点ではロックをかけず、更新直前に他のトランザクションによって更新されていないことを確認してから対象をロックします。すでに更新されてしまっていた場合は、エラーとなります。

悲観的ロックについては上記の通りデフォルトの設定となります。SQL serverにおいては更新時に排他ロックを取得しますので、参照するための共有ロックがブロックされ待たされるような形になります。複数のトランザクションを順番に処理できますので、トランザクション時間が短く、頻繁に同時更新が発生するような処理（例えば金額計算処理）に向いているとされています。ただし、更新完了までのロック解放待ちが発生しますのでパフォーマンスは悪くなりますし、ロックを保持する時間が長い分デッドロックの危険も増えます。

一方、楽観的ロックについては更新頻度が少なく同時に更新されにくいようなデータに対する処理に向いています。ただし更新が競合してしまった場合のロールバック後処理も考慮する必要があります。

現状の設定を確認・変更するSQLは以下の通りとなります。

-- 設定確認
DBCC USEROPTIONS

-- Sessionレベルの設定の変更
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- DatabaseレベルのREAD COMMITTED SNAPSHOT 設定の有効化

ALTER DATABASE Database_name SET READ_COMMITTED_SNAPSHOT ON

【SQL server】利用状況モニターの情報取得について

個人的にパフォーマンス障害時によく利用する「利用状況モニター」ですが、いまいち情報取得の方法や周期について理解が不足しておりましたので、ここで整理をしてみます。
（※プロファイラーの取得結果SQLをそのまま記載してありますので、内容問題がある場合はSQL部分の削除を致しますのでご一報願います。）

SQL server2016のSSMSで利用状況モニターを開くと以下のようになっています。
※SQL server2014までですと、一番下の「アクティブなコストの高いクエリ」はありません。
以下、ペインを開いている間は、開いているペインそれぞれに対し、一定の周期で内部でSQLを発行して情報を採取する仕組みとなっています。

◆概要
「概要」ペインでは、全体のサマリのような形でグラフィカルに内部を確認できます。
「最新のコストの高いクエリ」ペインを除くそれぞれの情報採取周期は、ここの「概要」ペインで指定します。

情報採取の周期は右クリック⇒更新間隔を選択して、任意の時間を選択します。
尚、デフォルトでは、10秒に設定されております。

上記で指定した更新間隔に基づき、「概要」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

EXEC #am_generate_waitstats;

SELECT 
    SUM (interval_wait_time_per_sec) / 1000 AS avg_waiting_task_count
FROM #am_resource_mon_snap;



SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_total_io_mb numeric (28, 1);
DECLARE @current_collection_time datetime;
DECLARE @current_total_io_mb numeric (28, 1);
DECLARE @mb_per_sec numeric (20, 1);

-- Get the previous snapshot's total I/O
SELECT TOP 1 @previous_collection_time = collection_time, @previous_total_io_mb = total_io_bytes 
FROM #am_dbfileio
ORDER BY collection_time DESC;

-- Get the current total I/O.  
SET @current_collection_time = GETDATE();
SELECT @current_total_io_mb = SUM(num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0
FROM sys.dm_io_virtual_file_stats(default, default);

-- Calc the total I/O rate (MB/sec) for the just-completed time interval. 
-- Round values larger than 2MB/sec to the nearest MB.
SET @mb_per_sec = (@current_total_io_mb - @previous_total_io_mb) / DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) * 1000;
IF @mb_per_sec > 2 
BEGIN
  SET @mb_per_sec = ROUND (@mb_per_sec, 0);
END; 

-- Save off current total I/O
INSERT INTO #am_dbfileio (collection_time, total_io_bytes) 
VALUES (@current_collection_time, @current_total_io_mb);

-- Return the I/O rate for the just-completed time interval. 
SELECT ISNULL (@mb_per_sec, 0) AS mb_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_dbfileio WHERE collection_time < @current_collection_time;



SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_request_count bigint;
DECLARE @current_collection_time datetime;
DECLARE @current_request_count bigint;
DECLARE @batch_requests_per_sec bigint;
DECLARE @interval_sec bigint;

-- Get the previous snapshot's time and batch request count
SELECT TOP 1 @previous_collection_time = collection_time, @previous_request_count = request_count 
FROM #am_request_count
ORDER BY collection_time DESC;

-- Get the current total time and batch request count
SET @current_collection_time = GETDATE();
SELECT @current_request_count = cntr_value 
FROM sys.sysperfinfo
WHERE counter_name = 'Batch Requests/sec' COLLATE Latin1_General_BIN;

SET @interval_sec = 
    -- Avoid divide-by-zero
    CASE
        WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) = 0 THEN 1
        ELSE DATEDIFF (second, @previous_collection_time, @current_collection_time)
    END;

-- Calc the Batch Requests/sec rate for the just-completed time interval. 
SET @batch_requests_per_sec = (@current_request_count - @previous_request_count) / @interval_sec;

-- Save off current batch count
INSERT INTO #am_request_count (collection_time, request_count) 
VALUES (@current_collection_time, @current_request_count);

-- Return the batch requests/sec rate for the just-completed time interval. 
SELECT ISNULL (@batch_requests_per_sec, 0) AS batch_requests_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_request_count WHERE collection_time < @current_collection_time;

◆プロセス
「プロセス」ペインでは、セッションID毎の実行プロセスが確認できます。

さらに右クリックを押下することで、以下を確認することができます。
詳細 ⇒実行されているSQLテキスト
ライブ実行プランの表示 ⇒後述
強制終了 ⇒kill によるセッションkill

「概要」ペインで指定した更新間隔に基づき、「プロセス」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

WITH profiled_sessions as (
	SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT 
   [Session ID]    = s.session_id, 
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,   
   [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end, 
   [Task State]    = ISNULL(t.task_state, N''), 
   [Command]       = ISNULL(r.command, N''), 
   [Application]   = ISNULL(s.program_name, N''), 
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''), 
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  = 
        CASE 
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END, 
   [Total CPU (ms)] = s.cpu_time, 
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024, 
   [Memory Use (KB)]  = s.memory_usage * (8192 / 1024), 
   [Open Transactions] = ISNULL(r.open_transaction_count,0), 
   [Login Time]    = s.login_time, 
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''), 
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N''),
   [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as 
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows 
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, 
    -- and use it as representative of the other wait relationships this thread is involved in. 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;

◆リソースの待機
「リソースの待機」ペインでは、待機イベント毎に待機している時間および待機数を表示できます。

「概要」ペインで指定した更新間隔に基づき、「リソースの待機」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

EXEC #am_generate_waitstats;

SELECT 
    category_name AS [Wait Category], 
    SUM (interval_wait_time_per_sec) AS [Wait Time (ms/sec)], 
    SUM (weighted_average_wait_time_per_sec) AS [Recent Wait Time (ms/sec)], 
    SUM (interval_avg_waiter_count) AS [Average Waiter Count], 
    SUM (resource_wait_time_cumulative) /1000 AS [Cumulative Wait Time (sec)]
FROM #am_resource_mon_snap
GROUP BY category_name 
ORDER BY SUM (weighted_average_wait_time_per_sec) DESC;

◆データファイル I/O
「データファイル I/O」ペインでは、各データベースのデータファイルおよびログファイルなどにおけるI/O情報を確認することができます。

「概要」ペインで指定した更新間隔に基づき、「データファイル I/O」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT 
    @current_collection_time AS collection_time, 
    d.name AS [Database], 
    f.physical_name AS [File], 
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read], 
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written], 
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count], 
    fs.io_stall AS [Total I/O Wait Time (ms)], 
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id; 

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time 
FROM #am_dbfilestats 
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time); 

-- Return the diff of this snapshot and last
SELECT 
    cur.[Database], 
    cur.[File] AS [File Name], 
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read], 
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written], 
    -- protect from div-by-zero
    CASE 
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)]) 
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time 
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

◆最新のコストの高いクエリ
「最新のコストの高いクエリ」ペインでは、クエリ毎のCPUTimeや平均実行時間などのパフォーマンス情報を確認することができます。

右クリックを押下すると以下を確認できます。

クエリテキストの編集　　　⇒SQLテキストを一部出力
実行プランの表示　　　　　⇒推定の実行プランを表示

「概要」ペインで指定した更新間隔ではなく、こちらは「最新のコストの高いクエリ」ペインを開いている間のみ30秒ごとに情報を採取します。

※SQLプロファイラーでトレースした結果、20秒ごとに採取しているように見えましたが、公式には30秒ごととのこと。
情報採取で内部で実行されるSQLは以下となります。

EXEC #am_get_querystats

◆アクティブなコストの高いクエリ
「アクティブなコストの高いクエリ」ペインでは、現在実行中のクエリのみに対して、セッション毎のSQLのCPUTimeや平均実行時間などのパフォーマンス情報を確認することができます。

右クリックを押下すると以下を確認できます。

クエリテキストの編集　　　⇒SQLテキストを一部出力
ライブ実行プランの表示　　⇒後述
実行プランの表示　　　　　⇒推定の実行プランを表示

「概要」ペインで指定した更新間隔に基づき、「アクティブなコストの高いクエリ」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

WITH profiled_sessions as (
	SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT TOP 10 SUBSTRING(qt.TEXT, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2)+1) as [Query],
er.session_id as [Session Id],
er.cpu_time as [CPU (ms/sec)],
db.name as [Database Name],
er.total_elapsed_time as [Elapsed Time],
er.reads as [Reads],
er.writes as [Writes],
er.logical_reads as [Logical Reads],
er.row_count as [Row Count],
mg.granted_memory_kb as [Allocated Memory],
mg.used_memory_kb as [Used Memory],
mg.required_memory_kb as [Required Memory],
/* We must convert these to a hex string representation because they will be stored in a DataGridView, which can't handle binary cell values (assumes anything binary is an image) */
master.dbo.fn_varbintohexstr(er.plan_handle) AS [sample_plan_handle], 
er.statement_start_offset as [sample_statement_start_offset],
er.statement_end_offset as [sample_statement_end_offset],
profiled_session_id as [Profiled Session Id]
FROM 
sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg 
	ON er.session_id = mg.session_id
LEFT OUTER JOIN profiled_sessions
	ON profiled_session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt,
sys.databases db
WHERE db.database_id = er.database_id
AND er.session_id  <> @@spid

ライブ実行プランの表示は、拡張イベントでquery_post_complilation_showplanイベントを取得している場合、SQL server2016の新機能であるライブ実行プランの表示をさせることができます。
現在実行中のプランに対し、実際の実行プラン(actualなplan)のどのアクセスパスを実行中か確認することができる機能となっております。

利用状況モニターは、ペインを閉じている間は情報採取しないようですので、特にパフォーマンス障害中などは不要なペインは閉じて必要なもののみ開いておくことが必要です。
また、「最新のコストの高いクエリ」ペインは30秒ごとに更新とのことですので、リアルタイムで見る際には更新間隔に注意が必要ようそうです。
以下まとめ。

ペイン名	採取周期	採取トリガー
概要	"更新間隔"の設定値	ペインを開いているとき
プロセス	概要ペインと同じ	ペインを開いているとき
リソースの待機	概要ペインと同じ	ペインを開いているとき
データファイル I/O	概要ペインと同じ	ペインを開いているとき
最新のコストの高いクエリ	30秒毎	ペインを開いているとき
アクティブなコストの高いクエリ	概要ペインと同じ	ペインを開いているとき

MSの解説も以下に貼っておきます。
https://technet.microsoft.com/ja-jp/library/cc879320(v=sql.105).aspx

長時間リアルタイムで実行中の高負荷SQLを監視するのに、「アクティブなコストの高いクエリ」を監視するのは、非常に効果が高いと思います。
以下は、長時間化しているSQLを監視しているときの画面キャプチャですが、「最新のコストの高いクエリ」「アクティブなコストの高いクエリ」ペインそれぞれで同じSQLを監視しているはずなのに監視結果に大きな差が出てしまっています。
これは「アクティブなコストの高いクエリ」がセッション毎に情報を取得しているため、実質そのSQL単体をリアルタイムで監視できていることに対し、「最新のコストの高いクエリ」は、過去分や他セッション分を含めて実行回数で割っているので、結果がサマられてしまっているのではないかと考えられます。

同SQLを複数セッションで実行した場合が以下です。

【SQL server】sp_who2について

SQL Serverの性能情報を取得するうえで、簡単にいろいろな情報を収集できるシステムストアドプロシージャであるsp_who2について、改めて内部を確認してみました。
※sp_who2はundocumentedなシステムストアドプロシージャであるため、変更されている恐れがあります。

まずは、内容を以下クエリで確認します。

sp_helptext 'sp_who2'

基本的にはsysprocesses_exから情報を取得しているようです。
このような名前のテーブルやビューは見つからないため、内部用のテーブルでしょうか。。。
同じようなストアドを作ることもできなさそうに見受けられるため、システムストアドプロシージャからのみ参照できるのでしょうか。。

似たようなテーブルとしてsysprocessesが存在し、こちらとほぼ同様な内容を取得していると思われます。
ちなみにsysprocessesに関しても、SQL server2000までの下位互換ビューのため、直接情報採取する場合は、以下を使用してください。
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests

※詳しくは以下を参照してください。
https://technet.microsoft.com/ja-jp/library/ms187997.aspx

また、環境によっては本番環境で自由に実行できるクエリに制限があり難しいかもしれませんが、
MSのtechnetにはさらに有益な情報を出力できるsp_who3も存在します。（MS標準で用意されているストアドとは異なるため、使用は自己責任で）
こちらはさらに、SQLのステートメントなども出力できるため、使用できる方は使ってみてもいいと思います。
https://gallery.technet.microsoft.com/SPWHO3-74fb1c35

話がそれてしまいましたが、sp_sho2の実行結果サンプルは以下となります。

どのような実行内容であったか詳細を知りたい場合は、上記の結果からSPIDを選び、
以下で検索します。

DBCC INPUTBUFFER(<SPID>)

以上のようなことはSSMSの利用状況モニターでも確認できます。
「プロセス」ペインを開きます。

対象を選択して、右クリック⇒詳細を選択します。

実行しているSQLが確認できます。また、強制終了を実行できます。
(コマンドで実行する場合は、Kill でしょうか。)

以上のような内容は、パフォーマンス低下時の緊急時に確認が必要だったりするので、頭に入れておくと安心ですね。

ファイルグループへのテーブルの新規追加の手順

ファイルグループへのテーブルを新規で追加する手順の理解があいまいだったため、簡単に確認してみました。
ファイルグループを分けた場合、違うディスクに配置することで、IO負荷の分散が期待できます。

⓵DBに新規のファイルグループを追加します

-- ================================================
-- DBにファイルグループを新規追加
-- ================================================
ALTER DATABASE [SalesDB] ADD FILEGROUP [Customer_FG]

⓶DBにファイルを追加します

-- ================================================
-- ファイルグループにファイルを新規追加
-- ================================================
ALTER DATABASE [SalesDB] ADD FILE
	(NAME = N'SalesDB_Custmor'
	,FILENAME = N'C:\temp\SalesDB_Custmor.ndf'
	) TO FILEGROUP [Customer_FG]

⓷テーブル作成時にON FILEGROUPを指定して実行します

-- ================================================
-- ファイルグループにテーブルを新規追加
-- ================================================
USE [SalesDB]

	CREATE TABLE [CustomerTBL](
		id INT				,
		name NVARCHAR(20)
	) ON [Customer_FG]

【SQL server】クエリ統計の履歴レポートについて

データコレクションの機能で、「クエリ統計の履歴」を実行し、
レポート表示させる場合の元スクリプトとテーブルについて
確認してみました。
内容に誤りがある場合は、ご指摘いただけると幸いです。
※変数の値はそれぞれの対象となるSQLによって異なります。
レポートに出力されている各項目におけるinputデータやモジュールから
把握することで、レポート内容をより正確に読み取ることを趣旨と
しています。

◆「クエリ統計の履歴」の初期表示時のスクリプト

exec snapshots.rpt_top_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016', -- インスタンス名
@end_time='2016-07-13 16:30:02.500', -- データコレクション収集最終時間
@time_window_size=240, -- レポートの期間（デフォルトは240分）
@order_by_criteria=N'CPU', -- レポートのTOP10のソート（デフォルトはCPUtime）
@database_name=NULL

◆「クエリ統計の履歴」のTOPxの特定のクエリを選択した場合のスクリプト
※レポート名は「クエリの詳細」
ここでは、対象SQLで使用されたの全ての実行プランの場合の以下値を合計して出力しているようです。
【出力項目】CPU、実行時間、物理読み取り、論理書き込み、実行回数
exec snapshots.rpt_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116

ここでは、対象SQLで使用されたそれぞれの実行プランの場合の以下値を合計してレポート期間で割った
1分当たりの各値を出力しているようです。
exec snapshots.rpt_query_plan_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,
@order_by_criteria=N'CPU',
@plan_handle_str=NULL,
@plan_creation_time=NULL

◆「クエリの詳細」レポートにおけるSQLステートメント全文出力スクリプト

exec sp_executesql @stmt=N'DECLARE @sql_handle varbinary(64)
SET @sql_handle = snapshots.fn_hexstrtovarbin (@sql_handle_str)
SELECT TOP 1 sql.*
FROM core.snapshots AS snap
CROSS APPLY snapshots.fn_get_query_text (snap.source_id, @sql_handle, @statement_start_offset, @statement_end_offset) AS sql
WHERE
snap.instance_name = @instance_name
AND snap.collection_set_uid = @collection_set_uid
AND sql.query_text IS NOT NULL',
@params=N'@sql_handle_str NVarChar(max),
@statement_start_offset Int,
@statement_end_offset Int,
@instance_name NVarChar(max),
@collection_set_uid NVarChar(max)',
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@collection_set_uid=N'2DC02BD6-E230-4C05-8516-4E8C0EF21F95'

◆各プランの番号を押下して、「クエリプランの詳細」レポートを出力
「グラフィカルなクエリ実行プランの表示」を押下した場合のスクリプト
exec sp_executesql N'SELECT [query_plan]
FROM [snapshots].[notable_query_plan]
WHERE [plan_handle]=[snapshots].[fn_hexstrtovarbin](@PlanHandle)
AND [sql_handle]=[snapshots].[fn_hexstrtovarbin](@SqlHandle)
AND [statement_start_offset]=@StartOffset
AND [statement_end_offset]=@EndOffset
AND [creation_time]=@CreationTime',
N'@PlanHandle varchar(90),
@SqlHandle varchar(90),
@StartOffset int,
@EndOffset int,
@CreationTime datetimeoffset(7)',
@PlanHandle=N'0x06000B0031E46020B0EE6D667001000001000000000000000000000000000000000000000000000000000000',
@SqlHandle='0x0200000031E4602070C1E890AFDFEAF91D35396449B4F4A60000000000000000000000000000000000000000',
@StartOffset=0,
@EndOffset=1116,
@CreationTime='2016-07-13 15:36:44.4730000 +00:00'

【SQL server2012】特定の実行プランのみキャッシュクリアする方法

SQL server では、キャッシュクリアしたい実行プランのプランハンドルがわかれば、特定の実行プランのみをクリアすることができます。

何らかの原因で実行プランが変わり、急激なパフォーマンス低下が発生してしまった際等に、他の処理への影響を減らしつつ実行プランを変更する手法として利用できるかと思います。

削除テスト用のSQLを実行して、削除するための実行プランを作成します。

次に削除する実行プランのPlan_handleを取得します。

DBCCコマンドの引数として先ほどの削除する実行プランのPan_handleを渡して実行します。
【コマンド】DBCC FREEPROCCACHE(<plan_handle>)
*尚、ここでは試しておりませんが、MSDNライブラリを見る限り、Plan_handleだけでなく、sql_handleも指定できるため、sql_handleに紐づく実行プランも削除出来るようです。

プランキャッシュから先ほどの実行プランが削除されていることが確認できます。

プランキャッシュ上にはそのほかの実行プランがキャッシュクリアされずに、残っていることが確認できます。

SQL server 2016の新機能クエリストアについて

◆クエリストアでできること

SQL server の特定のクエリの実行プランは時間の経過とともに変化します。
プロシージャキャッシュ（プランキャッシュ）には、最新の実行プランのみが格納され、
メモリ負荷によるキャッシュアウトで実行プランがプランキャッシュから削除される場合があります。。

クエリストアを利用すると、1つのクエリにつき複数の実行プランをDMV情報と共に保持されるようになります。実行プラン毎の差分をグラフィカルに確認したり、クエリの特定の実行プランを使用するよう強制するプラン強制が可能になります。
ヒント句を用いるプラン強制と似ていますが、クエリの変更を伴わない（ユーザアプリケーションの変更を必要としない）ことがポイントですね。

実行プランの変更による急激なパフォーマンス低下時の暫定対処等で活躍が期待できそうです。

◆クエリストアの有効化

SSMS上のオブジェクトエクスプローラで、データベースのプロパティから操作モードをオフから変更する。

T-SQLの場合は以下を実行すれば、SQL server 2016以前でも使用できる模様ですが未検証です。。
ただしSSMSは2016である必要あり。
ALTER DATABASE <DB_name> SET QUERY_STORE = ON;

◆使用が想定されるシナリオ
・以前の推定の実行プランを強制して適用し、実行プランの変更に伴うパフォーマンス低下を修正
・特定のクエリのデータベースリソース使用パターンを分析

◆低下したクエリでは、以下のようなメトリックごとにソートしたDMV情報及び実行プランを確認することができます。

◆全体のリソース消費量では、データベース全体における累積のクエリ実行状況が確認できます。

◆追跡したクエリでは、クエリIDを使用して、特定のクエリについてクエリストアに保存されている実行プランをそれぞれ確認できるようです。

また、上部メニューの「プランの比較」では、それぞれの実行プランの差分を強調表示したり、予想行数やサイズの比較も行えます。