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

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

今週はずっと飲み会だったので、体の調子が落ち着いた土曜日の朝に 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 での実行結果は下図の通り。
f:id:koogucc11:20151126172514p:plain
f:id:koogucc11:20151126172520p:plain
f:id:koogucc11:20151126172525p:plain
f:id:koogucc11:20151126172531p:plain
f:id:koogucc11:20151126172538p:plain
 
 SQL Server 2012 SP3 での実行結果は下図の通り。
f:id:koogucc11:20151126173959p:plain
f:id:koogucc11:20151126174005p:plain
f:id:koogucc11:20151126174011p:plain
f:id:koogucc11:20151126174018p:plain
f:id:koogucc11:20151126174025p:plain
f:id:koogucc11:20151126174034p:plain
f:id:koogucc11:20151126174044p:plain
f:id:koogucc11:20151126174050p:plain

 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の性格

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