Improved memory grant diagnostics using Extended Events in SQL Server 2012 and 2014

Applies to: Microsoft SQL Server 2012 Analysis ServicesSQL Server 2012 DeveloperSQL Server 2012 Enterprise More

Microsoft distributes Microsoft SQL Server 2012 fixes and Microsoft SQL Server 2014 fixes as downloadable files. Because the fixes is cumulative, each new release contains all the hotfixes and all the security fixes that were included in the previous SQL Server 2012 and 2014 fix release.

Summary


This update adds a new extended event: query_memory_grant_usage. This extended event contains the following columns (event fields) and is intended to help detect inaccurate or insufficient memory grant performance.
 
Column Type Description
Sql_handle Varbinary(64) Hash map of the SQL text of the request. Is nullable.
Plan_handle Varbinary(64) Hash map of the plan for SQL execution. Is nullable.
Query_hash Binary(8) Binary hash value calculated on the query and used to identify queries that have similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.
Query_plan_hash Binary(8) Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use the query plan hash to find the cumulative cost of queries that have similar execution plans.
Ideal_memory_kb UInt(64) Ideal memory grant size in KB.
Granted_memory_kb UInt(64) Granted memory in KB.
Used_memory_kb UInt(64) Used memory in KB.
Usage_percent UInt(32) Used/granted memory for easy filtering.
Dop UInt(32) Degree of parallelism.
Granted_percent UInt(32) Granted/ideal memory for easy filtering.

Resolution


This issue is fixed in the following service packs for SQL Server:

       Service Pack 3 for SQL Server 2014

       Service Pack 4 for SQL Server 2012  

More Information


You can define a memory limit as a filter for this new extended event so that the extended event fires only when the memory grant of a query exceeds the limit.

References


Learn about the terminology that Microsoft uses to describe software updates.