10

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 < '2010-12-26'
ORDER BY    u.CreationDate
OPTION (RECOMPILE, USE PLAN N'<xml here>'

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 plan

This seems to disprove my theory that I would see RESOURCE_SEMAPHORE waits and suggests there is some mechanism that prevents SQL Server granting more memory than is available to a query (which seems perfectly sensible!) Incidentally, if I run the query twice in simultanous sessions on the 500MB server setting, both sessions then get RESOURCE_SEMAPHORE waits for what seems indefintely.

I can see MaxQueryMemory in the plan which appears to be what is preventing SQL Server from granting more memory than it has available to a query.

Is it possible for a single query to be granted more memory than is available to the instance? If not, is MaxQueryMemory what causes SQL Server not to assign more memory than is avaiable and how is this number calculated?

NB - My StackOverflow database is in 130 compatibility level

1 Answer 1

17

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:

NUTS

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:

NUTS

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:

NUTS

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

NUTS

Wait info:

NUTS

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.