MAX_GRANT_PERCENT

Jack Li gives an example in which MAX_GRANT_PERCENT can keep certain queries from getting runaway memory grants:

The customer has lots of waits on RESOURCE_SEMAPHORE_QUERY_COMPILE.  To troubleshoot this, we have to look from two angles.  First, did customer have many queries needing large amount of compile memory?  Secondly, was it possible that other components used too much memory, causing the threshold lowered?  In other words, if SQL Server had enough memory, those queries requiring same amount of compile memory would not have been put to wait.

We used this query and captured for several iterations of data to confirm that server didn’t have queries that required large amount of compile memory per se.

It’s nice to have this trick up your sleeve when you simply can’t get a better query in place.

Related Posts

When UNION ALL Can Beat OR

Bert Wagner compares a couple methods for writing a query: Suddenly those key-lookups become too expensive for SQL Server and the query optimizer thinks it’ll be faster to just scan the entire clustered index. In general this makes sense; SQL Server tries to pick plans that are good enough in most scenarios, and in general […]

Read More

Automatic Tuning In SQL Server 2017

Arun Sirpal shows off one of the more interesting features in SQL Server 2017: Before we begin any further let’s do a little recap. Automatic tuning in SQL Server 2017 notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems, this […]

Read More

Categories

June 2016
MTWTFSS
« May Jul »
 12345
6789101112
13141516171819
20212223242526
27282930