Improved memory grant diagnostics when you use DMV in SQL Server 2012 and 2014


This update adds the following new columns to the Dm_exec_query_stats dynamic management views (DMV). These columns provide statistics on memory grant and parallel threads of cached query plans. They can be useful when you investigate the query plan.

Note These columns will have a value of 0 (zero) for querying a memory-optimized table.

ColumnTypeDescription
Last_grant_kbbigintThe reserved memory grant (in kilobytes [KB]) that this plan received the last time that it ran
Min_grant_kbbigintThe minimum reserved memory grant (in KB) that this plan ever received during one run
Max_grant_kbbigintThe maximum reserved memory grant (in KB) this plan ever received during one run
Total_grant_kbbigintThe total reserved memory grant (in KB) that this plan received after it was compiled
Last_used_grant_kbbigintThe used memory grant (in KB) that this plan received the last time that it ran
Min_used_grant_kbbigintThe minimum used memory grant (in KB) that this plan ever used during one run
Max_used_grant_kbbigintThe maximum used memory grant (in KB) that this plan ever used during one run
Total_used_grant_kbbigintThe total reserved memory grant (in KB) that this plan used after it was compiled
Last _ideal_grant_kbbigintThe ideal memory grant (in KB) that this plan received the last time that it ran
Min_ideal_grant_kbbigintThe minimum ideal memory grant (in KB) that this plan ever used during one run
Max_ideal_grant_kbBigintThe maximum ideal memory grant (in KB) that this plan ever used during one run
Total_ideal_grant_kbBigintThe total ideal memory grant (in KB) that this plan used after it was compiled
Last_dopbigintThe degree of parallelism that this plan used the last time that it ran
Min_dopbigintThe minimum degree of parallelism that this plan ever used during one run
Max_dopbigintThe maximum degree of parallelism that this plan ever used during one run
Total_dopbigintThe total degree of parallelism that this plan used after it was compiled
Last_reserved_threadsbigintThe number of reserved parallel threads that were used the last time that this plan ran
Min_reserved_threadsbigintThe minimum number of reserved parallel threads that this plan ever used during one run
Max_reserved_threadsbigintThe maximum number of reserved parallel threads that this plan ever used during one run
Total_reserved_threadsbigintThe total reserved parallel threads that this plan used after it was compiled
Last_used_threadsbigintThe number of used parallel threads that were used the last time that this plan ran
Min_used_threadsbigintThe minimum number of used parallel threads that this plan ever used during one run
Max_used_threadsbigintThe maximum number of reserved parallel threads that this plan ever used during one run
Total_used_threadsbigintThe total reserved parallel threads that this plan used after it was compiled

Resolution
This functionality was fist introduced in the following Service Packs for SQL Server. 
For more information about SQL Server 2012 Service Pack 3 (SP3), see bugs that are fixed in SQL Server 2012 Service Pack 3.

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.
Vlastnosti

ID článku: 3107398 - Poslední kontrola: 07/11/2016 17:08:00 - Revize: 3.0

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

  • kbqfe kbsurveynew kbfix kbexpertiseadvanced KB3107398
Váš názor