今週はずっと飲み会だったので、体の調子が落ち着いた土曜日の朝に SQL Server 2012 SP3 を試してみる

　SQL Server 2012 SP3 がリリースされました。
SQL Server 2012 SP3 is now available! - SQL Server Team Blog - Site Home - TechNet Blogs

SQL Server 2012 Service Pack 3 is now Available! - Microsoft SQL Server Release Services - Site Home - MSDN Blogs

www.brentozar.com

　3つ気になるものを試してみたいと思います。

KB 3107398 – Improvements to sys.dm_exec_query_stats
This is kind of a big deal. The sys.dm_exec_query_stats DMV will now report on total, last, min and max statistics for granted workspace memory, ideal workspace memory, degree of parallelism, and reserved threads. These columns are in SQL Server 2016 CTP3, but it’s really awesome that they’ve been backported town to 2012!

　従来のレイアウトに対して、下記のカラムが追加されます。

Column	Type	Description
Last_grant_kb	bigint	The reserved memory grant (in kilobytes [KB]) that this plan received the last time that it ran
Min_grant_kb	bigint	The minimum reserved memory grant (in KB) that this plan ever received during one run
Max_grant_kb	bigint	The maximum reserved memory grant (in KB) this plan ever received during one run
Total_grant_kb	bigint	The total reserved memory grant (in KB) that this plan received after it was compiled
Last_used_grant_kb	bigint	The used memory grant (in KB) that this plan received the last time that it ran
Min_used_grant_kb	bigint	The minimum used memory grant (in KB) that this plan ever used during one run
Max_used_grant_kb	bigint	The maximum used memory grant (in KB) that this plan ever used during one run
Total_used_grant_kb	bigint	The total reserved memory grant (in KB) that this plan used after it was compiled
Last_ideal_grant_kb	bigint	The ideal memory grant (in KB) that this plan received the last time that it ran
Min_ideal_grant_kb	bigint	The minimum ideal memory grant (in KB) that this plan ever used during one run
Max_ideal_grant_kb	Bigint	The maximum ideal memory grant (in KB) that this plan ever used during one run
Total_ideal_grant_kb	Bigint	The total ideal memory grant (in KB) that this plan used after it was compiled
Last_dop	bigint	The degree of parallelism that this plan used the last time that it ran
Min_dop	bigint	The minimum degree of parallelism that this plan ever used during one run
Max_dop	bigint	The maximum degree of parallelism that this plan ever used during one run
Total_dop	bigint	The total degree of parallelism that this plan used after it was compiled
Last_reserved_threads	bigint	The number of reserved parallel threads that were used the last time that this plan ran
Min_reserved_threads	bigint	The minimum number of reserved parallel threads that this plan ever used during one run
Max_reserved_threads	bigint	The maximum number of reserved parallel threads that this plan ever used during one run
Total_reserved_threads	bigint	The total reserved parallel threads that this plan used after it was compiled
Last_used_threads	bigint	The number of used parallel threads that were used the last time that this plan ran
Min_used_threads	bigint	The minimum number of used parallel threads that this plan ever used during one run
Max_used_threads	bigint	The maximum number of reserved parallel threads that this plan ever used during one run
Total_used_threads	bigint	The total reserved parallel threads that this plan used after it was compiled

　SQL Server 2012 SP2 での実行結果は下図の通り。





　
　SQL Server 2012 SP3 での実行結果は下図の通り。

　SQL Server 2016 CTP3 で追加された機能が 2012 にフィードバックされました。これはかなり有用ですね。クエリの動作が今まで以上に詳細に把握することが可能になっています。

KB 3107397 – “Actual Rows Read” Added to Query Execution Plans
This is really exciting! It’s hard for people to read execution plans. You may have a very large index seek or scan that reads a ton of rows, and it has a hidden filter in the seek or scan. But it may be hard to diagnose because you only see the number of rows to come OUT of the seek or scan operator. This lets you know not only the rows that exit the operator, but how many it had to consume, too. It’s sorta like having a little bit of STATISTICS IO in your execution plan!

　下記のようなクエリを実行するとします。

SELECT
    *
FROM 
    Sales.SalesOrderHeader soh
	INNER JOIN Sales.SalesOrderDetail sod ON
	soh.SalesOrderID = sod.SalesOrderID
WHERE 
    soh.SalesOrderID = 43659

　SQL Server 2012 SP2 の実行プランの XML は下記の通りです。

<relop avgrowsize="95" estimatecpu="0.0001702" estimateio="0.003125" estimaterebinds="0" estimaterewinds="0" estimatedexecutionmode="Row" estimaterows="12" logicalop="Clustered Index Seek" nodeid="16" parallel="false" physicalop="Clustered Index Seek" estimatedtotalsubtreecost="0.0032952" tablecardinality="121317">
    <outputlist>
        .
        .
    </outputlist>
    <runtimeinformation>
        <runtimecountersperthread thread="0" actualrows="12" actualendofscans="1" actualexecutions="1">
        </runtimecountersperthread>
    </runtimeinformation>
</relop>

　SQL Server 2012 SP3 の実行プランの XML は下記の通りです。

<relop avgrowsize="95" estimatecpu="0.0001702" estimateio="0.003125" estimaterebinds="0" estimaterewinds="0" estimatedexecutionmode="Row" estimaterows="12" logicalop="Clustered Index Seek" nodeid="16" parallel="false" physicalop="Clustered Index Seek" estimatedtotalsubtreecost="0.0032952" tablecardinality="121317">
    <outputlist>
        .
        .
    </outputlist>
    <runtimeinformation>
        <runtimecountersperthread thread="0" actualrows="12" actualrowsread="12" actualendofscans="1" actualexecutions="1">
        </runtimecountersperthread>
    </runtimeinformation>
</relop>

　SQL Server 2012 SP3 では、ShowPlan XML に ActualRowsRead という属性が追加されます。これは、残りの述部が使用される前に、どれくらいの行がオペレーターによって読まれたかという属性です。パフォーマンス劣化時のトラブルシューティングに役立ちそうです。

KB 3107401 – New Query Hints for min_grant_percent and max_grant_percent
Previously, if you identified that a query was asking for a problematic memory grant, your options were pretty limited – code changes, index changes, resource governor, or server wide settings. These options let you shoot yourself in the foot tune this just for a single query. Check out the KB– there is a safety in the feature. If the minimum required grant is higher than max_grant_percent, it will still get the minimum.

　REQUEST_MAX_MEMORY_GRANT_PERCENT は一律で指定されてしまうため、イマイチな感じでした。(というか変更する勇気がない...変更してる人、いるのかな？いたら事例で教えてほしいかも。) KB 3107401 によって、クエリ単位にメモリ使用量の最小値および最大値を指定することが可能になっています。下記のように OPTION 句を用いて指定します。Wait Statistics(Average wait time(ms))\Memory Grant Query Waits のパフォーマンスカウンタが長時間を示している場合、メモリの消費量が多いクエリなどに適用を考えてもいいかもしれません。

SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 10, max_grant_percent = 50)

　それ以外にも、高速化を含め様々な改善がされています。
https://support.microsoft.com/en-us/kb/3072779

天職...

あなたの天職がわかる16の性格