Archive for August, 2012

Large workspace memory grant queries

In SQL Server 2012 by setting up an extended event session based on the degree_of_parallelism event you can identify queries that are using large workspace memory grants.

As an example I ran the following test query and captured the memory grant using the actual execution plan, performance monitor, sys.dm_exec_memory_grants and the extended event session:

All four diagnostic tools show the same value for the granted memory.

You can change the definition of the extended event session to filter on the workspace memory to identify those queries requiring a large grant.

Advertisements

Leave a comment