Update to expose per-operator query execution statistics in showplan XML and Extended Event in SQL Server 2014 SP2

It's very important to collect per-operator query execution statistics, such as actual number of rows, for query performance troubleshooting. In Microsoft SQL Server, this information is available in actual execution showplan. In SQL Server 2014 SP2 and SQL Server 2016, the actual execution showplan XML is extended to add more execution statistics in the RunTimeCountersPerThread element, including such information as elapsed time, CPU time, logical and physical reads (if applicable).

Collecting actual execution showplan, however, is an expensive operation. To provide an alternative that incurs less performance impact on query execution, new Extended Event query_thread_profile was added in SQL Server 2014 SP2. This event is triggered for each query plan operator and execution thread at the end of query execution. The node_id attribute can be used to correlate this information with the query plan, which can be obtained, such as, from query plan cache by using the plan handle and sys.dm_exec_query_plan Dynamic Management Function.

While the performance impact of enabling the query_thread_profile Extended Event is significantly less than the impact of enabling the query_post_execution_showplan Extended Event, the actual performance may vary depending on the workload.

How to get the new feature

Service pack information

Service Pack 2 for SQL Server 2014

About Service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.

How to determine the version, edition and update level of SQL Server and its components

Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 3170113 - Last Review: 07/11/2016 17:39:00 - Revision: 1.0

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3170113
Feedback