FIX: Memory grant that's required to run optimized nested loop join isn't reflected in Showplan XML in SQL Server 2014 or 2016

Summary
The optimized nested loop join operator uses memory grant during query execution. This information isn't available in the queryplan. This makes troubleshooting of problems related to query execution memory usage more difficult.


Resolution

Service pack information

The issue was first fixed in the following Service Packs for SQL Server.

SQL Server 2016 Service Pack 1 (SP1). See the SQL Server 2016 build versions.

Service Pack 2 for SQL Server for SQL Server 2014

Note In SQL Server 2014 SP2, the MemoryFractions showplan XML attribute is updated for the optimized nested loop join operator to reflect the memory that's required to run it.

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 don't 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: 3170116 - Last Review: 11/16/2016 09:40:00 - Revision: 2.0

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard

  • kbqfe kbfix kbsurveynew kbexpertiseadvanced KB3170116
Feedback