『『『 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

　関東涼しいですね。夏とは思えない気温です。来週もそんなに気温は上がらなそうです。
f:id:koogucc11:20160723131017p:plain

　さて、今回はSQL Server 2014 SP2 のアップデート概要の最終回です。(なんでこうなったのかは不明です。)
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com

　涼しい午前中を利用して、記事を書いてしまいます。

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.

　リースのタイムアウト・メッセージの新しいロギング機能を追加されました。エラーログに出力されます。

　ちなみに、下記のバージョンで対応されています。

　追加されるメッセージは下記の通りです。

Error	Error Message	Cause	Corrective Action
19419	The renewal of the lease between availability group ‘%.*ls’ and the Windows Server Failover Cluster failed because the existing lease is no longer valid.	The lease worker on the SQL Server side did not get scheduled on time to process event signal from the cluster.	Check the CPU utilization on the server as SQL Server lease worker seems to be starving.
19420	The availability group ‘%.*ls’ is explicitly asked to stop the lease renewal.	The lease renewal is stopping as a part of bringing the availability group offline. This is informational only.
19421	The renewal of the lease between availability group ‘%.*ls’ and the Windows Server Failover Cluster failed because renewal didn’t happen within lease interval.	The lease helper on the cluster side did not signal the SQL Server lease worker on time.	Check corresponding availability group resource in WSFC cluster to see if it reported any error.
19422	The renewal of the lease between availability group ‘%.*ls’ and the Windows Server Failover Cluster failed because of a windows error with Error code (‘%d’).	The lease worker on SQL Server side failed to renew the lease because of a windows error.	Check windows error code and take the corrective action.
19423	The lease of availability group ‘%.*ls’ lease is no longer valid to start the lease renewal process.	When the lease worker started processing the excess lease time provided by online call the lease was already expired. This might happened because of scheduling issues.	Check the CPU utilization on the server as SQL Server lease worker seems to be starving.
19424	The lease worker of availability group ‘%.*ls’ is now sleeping the excess lease time (%u ms) supplied during online. This is an informational message only. No user action is required.	Informational. Extra online time allotted to starting the lease renewal thread and as part of the availability group online routine.

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.

　DBCC INPUTBUFFER と同様の機能を持つ DMV である、sys.dm_exec_input_buffer が追加されました。プロセスが実行しているコマンドを確認するものです。似たようなものに、sys.fn_get_sql (Transact-SQL) があります。けど、これはもう使っちゃいけません。

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.

　新しいクエリヒントとして、MIN_GRANT_PERCENT および MAX_GRANT_PERCENT が追加されました。下記のように記述することで使用することができます。

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

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_memory_grant_usage が追加されました。詳しくは下記の URL を参照してください。
https://support.microsoft.com/en-us/kb/3107173

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.

　TempDB の拡張イベントに関する改善です。詳しくは Web で！(手抜き？)
https://support.microsoft.com/en-us/kb/3107173

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.

　AlwaysOn に関する拡張イベントの改善です。詳しくは Web で。
https://support.microsoft.com/en-us/kb/3173156

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)

　トランザクションレプリケーションでテーブルを複数削除することができます。コマンドは下記の通りです。

exec sp_changepublication @publication = '【Publication Name】', @property = 'allow_drop', @value = 'true'

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.

　これ、重要です。Predicate と Seek Predicate をまず理解する必要があります。下記の記事に詳しく書いてありますので、参考にしてください。
ryuchan.hatenablog.com

　それでは、実際に試してみましょう。下記のクエリを SQL Server 2014 SP2、データベースは AdventureWorks2014 で実行します。

SELECT
    AddressID,
    AddressLine1,
    AddressLine2,
    City,
    StateProvinceID,
    PostalCode,
    SpatialLocation,
    rowguid,
    ModifiedDate
FROM
    Person.Address
WHERE
    AddressLine1 = N'Attaché de Presse' AND 
    PostalCode = N'75007'

f:id:koogucc11:20160723124447p:plain
　
　さらに、オペレータの内容をみてみましょう。『読み取った行数』が表示されているがわかるかと思います。これは、Predicate(述語)が発生しているときに表示されます。インデックスを使っているのになぜか遅い、とよく質問されるのですが、このパターンはよく見かけます。実際の行数と読み取った行数が一度に表示されるのは、かなりいいですね。これはいい改善です。
f:id:koogucc11:20160723124514p:plain
　
　SQL Server 2014 SP2 のダウンロードはこちら。
Download Microsoft® SQL Server® 2014 Service Pack 2 (SP2) from Official Microsoft Download Center