都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

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

『『『 SQL Server 2014 Service Pack 2 is now Available !!! ということなので使ってみる 』 の続き、まずは Performance and Scalability Improvements から書いてみる 』の続き、次に Supportability and Diagnostics Improvements について書いてみる。』 の続きを書いてみる


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


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 を参照してください。

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 で!(手抜き?)

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 で。

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 をまず理解する必要があります。下記の記事に詳しく書いてありますので、参考にしてください。

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

    AddressLine1 = N'Attaché de Presse' AND 
    PostalCode = N'75007'

 SQL Server 2014 SP2 のダウンロードはこちら。
Download Microsoft® SQL Server® 2014 Service Pack 2 (SP2) from Official Microsoft Download Center


沖縄 オトナの社会見学 R18

沖縄 オトナの社会見学 R18