Is it possible for SQL Server to grant more memory to a query than is available to the instance

I was asked the other day what would happen if SQL Server wanted to run a single query that was granted more memory than is available to the instance. My initial thoughts were that I may see RESOURCE_SEMAPHORE waits and the query would never start.

I did some testing to attempt to find out.

My instance starts on 4000MB RAM:

EXEC sys.sp_configure N'max server memory (MB)', N'4000'
GO
RECONFIGURE WITH OVERRIDE
GO

If I then run my (delibarately horrible) query:

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate;

The execution plan says granted memory is 732,008KB.

I then set the memory available to my instance below this number and then restart the instance:

EXEC sys.sp_configure N'max server memory (MB)', N'500' /* a value lower than the previous memory grant */
GO
RECONFIGURE WITH OVERRIDE
GO

I ran the query again and found that it is granted less memory than before (93,176KB) but the plan is actually a different shape.

I then ran the query again and used a query hint to force the original plan to see what memory is granted:

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate '

I found that the query now uses the original plan but gets a very similar memory grant to the plan it compiled (93,168KB) - forced actual pla

By default, SQL Server will let any query use up to 25% of max server memory for a memory grant, but in practice it's often closer to 20%.

Resource Governor has something to say about this:

But on my demo VM with Max Server Memory set to 90GB, the highest single-query memory grant I can get is 17GB, not 22GB.

WITH
    c AS 
(
SELECT
    c.*,
    n = 
        ROW_NUMBER() OVER 
        (
            PARTITION BY 
                c.PostId 
            ORDER BY 
                c.Score DESC
        )
FROM dbo.Comments AS c
)
SELECT
    c.*
FROM c
WHERE n = 0;

Here's the partial plan for it:

Using sp_PressureDetector, you can get a bunch of information about memory usage for a SQL Server. To keep it to memory grants, this is what happens when I run the above query:

It's not until I run four copies of the query that one waits in a queue on resource semaphore:

Wait info:

A general way to think about it is that SQL Server will give out about 75% of max server memory to queries requesting grants.

In this case, 68GB of memory is available for grants out of 90GB. Since the fourth query would have gone over that ~75% mark, it has to wait.

In general, the total grant plus 50% has to be available for memory to be granted immediately. If not, it will wait, sometimes until a lower grant is forced (review the forced_grant_count column in sys.dm_exec_query_resource_semaphores)

You can learn in more detail about how SQL Server prioritizes semaphore queues and memory grants in general from these sources:

• Query Memory Grants and Resource Semaphores in SQL Server

• Enabling Resource Governor To Fix Memory Grants

• Query Tuning Mastery: Zen and the Art of Workspace Memory

Keep in mind, my answer is only in reference to traditional row mode and row store workloads. Adding in batch mode and column store indexes will change some behaviors, particularly around respect for max server memory, and what requires a memory grant.

With row mode, the most common places you'll see memory grants given to queries is when query plans feature:

• Sorts

• Hash joins or aggregates

• Optimized nested loops

Inserts to column store indexes will ask for memory even when no DML Request Sort is in the query plan to perform compression.

StackExchange Database Administrators Q#319327, answer score: 17