『『 SQL Server 2014 Service Pack 2 is now Available !!! ということなので使ってみる』の続き、まずは Performance and Scalability Improvements から書いてみる』の続き、次に Supportability and Diagnostics Improvements について書いてみる。 - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　すべての更新内容に興味が沸いたわけではないのですが、なんとなく全部見てみようかと。

Supportability and Diagnostics Improvements

　次はサポータビリティと診断に関する改善です。

Tempdb supportability – A new errorlog message indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.

　SQL Server 起動時に下記のようなメッセージが表示されます。

The tempdb database has %ld data file(s).

　あと、複数の TempDB のファイルが異なるサイズ、異なる増加容量をが設定されている場合にも同様にエラーログを出力します。例えば以下のようなふざけた環境で SQL Server を再起動してみます。
f:id:koogucc11:20160719124236p:plain

　ERRORLOG には下記のように出力されます。

The tempdb database has 4 data file(s).
The tempdb database data files are not configured with the same initial size and autogrowth settings. To reduce potential allocation contention, the initial size and autogrowth of the files should be same.

　このあたりのログを見る人はそもそも TempDB の設定をミスらない気がしますが....

Database Instant File Initialization Logging – A new errorlog message indicating that Database Instant File Initialization is enabled/disabled at server startup.

　ファイルの瞬時初期化に関しても TempDB の警告と同様にログに出力されます。詳しくは下記の URL を参照のこと。(試すのが面倒な訳ではありません。)
Database Instant File Initialization

Module names in callstack – The Xevent callstack now includes modules names + offset instead of absolute addresses.

　拡張イベントの出力が変わったようですね。(試すのが面倒な訳ではありません。)

New DMF for incremental statistics – This improvement address connect feedback (797156) to enable tracking the incremental statistics at the partition level. A new DMF sys.dm_db_incremental_stats_properties is introduced to expose information per-partition for incremental stats.

　今までは、sys.dm_db_stats_properties (Transact-SQL) で統計の情報を確認していました。SP2 からは、パーティションごとに増分統計が sys.dm_db_incremental_stats_properties で参照できるようです。

　増分統計については下記の URL を参考にしてください。
blogs.technet.microsoft.com

※試すのが面倒な訳ではありません。

Index Usage DMV behavior updated – This improvement addresses connect feedback (739566) from customers where starting SQL 2014 SP2, rebuilding an index will *not* clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior now is to what it was in SQL 2008.

　SQL Server を始めたのが 2008R2 なのであまり意識していませんでした。sys.dm_db_index_usage_stats の扱いが 2008 と同じになったんですね。
※手を抜いているのではありません。

Better correlation between diagnostics XE and DMVs – This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Since SQL server does not have “unsigned bigint”, casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.

　型の統一。sys.dm_exec_requestsとか、sys.dm_exec_query_stats と同じ型の列をもっていなかった......

Support for UTF-8 in BULK INSERT and BCP – This improvement addresses connect feedback (370419) where support for export and import of data encoded in UTF-8 character set is now enabled in BULK INSERT and BCP.

　BULK INSERT で UTF-8 のサポートされました。
BULK INSERT (Transact-SQL)
※850 など、特定のコードページ番号を指定します。 Important: SQL Server はコードページ 65001 (UTF-8 エンコード) をサポートしません。

Lightweight per-operator query execution profiling – While troubleshooting query performance, although showplan provides lot of information on the query execution plan and cost of operator in the plan but it has limited information on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread). SQL 2014 SP2 introduces these additional runtime statistics per operator in the Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting query performance.

blogs.msdn.microsoft.com
　
　あ、これいいですね。今までは、

<RunTimeInformation>
   <RunTimeCountersPerThread Thread="0" ActualRows="8001" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>

　SP2 からは、実際に読み込んだ行数、CPU時間、経過時間など詳細な情報が記録されます。これは便利！

<RunTimeInformation>
   <RunTimeCountersPerThread Thread="0" ActualRows="8001" ActualRowsRead="10000000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="965" ActualCPUms="965" ActualScans="1" ActualLogicalReads="26073" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>

Change Tracking Cleanup – A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.

　手動で変更追跡のテーブルを削除することができます。
変更の追跡について (SQL Server)

f:id:koogucc11:20160719235133j:plain
　飲みながら記事書いていたので、気力が尽きてしまいました。続きはまた別の記事で書きます。残りは下記の通りです。

AlwaysON Lease Timeout Logging – Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged. Also a new message was introduced in the SQL Errorlog regarding the timeouts.

New DMF for retrieving input buffer in SQL Server – A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available. This is functionally equivalent to DBCC INPUTBUFFER.

Mitigation for underestimated and overestimated memory grant – Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT (KB3107401). This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention.

Better memory grant/usage diagnostics – A new extended event was added to the list of tracing capabilities in SQL Server (query_memory_grant_usage) to track memory grants requested and granted. This provides better tracing and analysis capabilities for troubleshooting query execution issues related to memory grants (KB3107173).

Query execution diagnostics for tempdb spill – Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used and rows affected. We also introduced a new hash_spill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400). Set statistics on now shows sort worktable statistics.

AlwaysON XEvents and performance counters to troubleshoot latency – New AlwaysON XEvents and performance counters to improve diagnostics when troubleshooting latency issues with AlwaysON. For more details on how to leverage these XEvents and performance counters please refer to this video.

DROP DDL Support for Replication – SQL Server 2014 SP2 allows a table that’s included as an article in transactional replication publication to be dropped from the database and the publication(s). A table can be dropped only if the allow_drop property is set to TRUE on all the publications that have the table(s) as an article. If the property is set to FALSE for any of the publications that contain the article, then the DROP TABLE operation will fail and report that replicated articles can’t be dropped.(KB 3170123)

Improved diagnostics for query execution plans that involve residual predicate pushdown – The actual rows read will now be reported in the query execution plans (KB3107397) to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan.

　さぁ、仕事しよう。

　秘密の沖縄スポットガイド。仕事では何回もきてるけど、まだまだ知らないことだらけの、沖縄。