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
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: