SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?

Question

Batch Mode Memory Grant Feedback is part of a family of features in the 2017 query processor that consists of:

• Batch Mode Adaptive Joins
• Interleaved Execution
• Batch Mode Memory Grant Feedback

So how does Batch Mode Memory Grant Feedback work?

Answer 1

What's Memory Grant Feedback All About?

Batch Mode Memory Grant Feedback attempts to right-size memory grants for queries, correcting for both over- and under-estimates.

When a query runs that requires a memory grant, the optimizer will ask for a grant of a size that it thinks will keep all row operations in memory.

Things that commonly require memory grants:

• Sorts
• Hash Joins

When too much memory is asked for and granted, concurrency may suffer as a result. Memory is a finite resource, and not everything can use all of it all the time. There's no such thing as unlimited access to such resources.

When too little memory is asked for, queries may spill to disk. Look, no one wants their queries spilling anywhere. Disk is dreadful.

How Does The Magic Work?

When a plan that requires a memory grant executes and is cached, actual memory needed to run the plan is recalculated, and the plan information is updated accordingly. Right now, it requires the presence of a ColumnStore index to achieve Batch execution mode.

What If The Magic Isn't So Magical?

This feature does have a terminating point in which it will fall back to the original memory grant. If queries run that need constant recalculating, our magical feature will give up. Eventually. As of this writing, I don't have all of the implementation details on when it will quit.

How Do I Know If It's Working?

You can use Extended Events:

You may also observe it during regular query tuning over multiple runs in the actual execution plan.

Can You Show Me An Example?

Of course! Here's a stored procedure. Under the right circumstances, it will ask for an incorrect memory grant.

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts_cx AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

The query plan has a warning on the select operator.

Sadness indeed! Our query asked for too much memory.

On second execution, the warning will disappear.

I've Heard About Some Sneaky Tricks...

Itzik Ben-Gan and Niko Neugebauer have both come up with workarounds to features and operators that require Batch Mode execution.

1. Create a filtered, non-clustered ColumnStore index with a WHERE clause that contains 0 rows
2. Join your table to a temp table with a Clustered ColumnStore index on it with no rows using a superfluous Left Join

Both methods are valid workaround to get this to work!

Creating an empty nonclustered ColumnStore index:

/*Itzik's Trizik*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_helper
    ON dbo.Posts
(
    Id,
    AcceptedAnswerId,
    AnswerCount,
    ClosedDate,
    CommentCount,
    CommunityOwnedDate,
    CreationDate,
    FavoriteCount,
    LastActivityDate,
    LastEditDate,
    LastEditorDisplayName,
    LastEditorUserId,
    OwnerUserId,
    ParentId,
    PostTypeId,
    Score,
    ViewCount,
    IsHot )
    WHERE ( Id = -2147483647 AND Id = 2147483647) -- eez impossible!

Running a different version of the proc against the table with the empty index:

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt1 (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

Using the same value:

EXEC dbo.LargeUnusedGrant_alt1 @OwnerUserId = 8672;
GO 

First run: sad memory grant

Second run: happy memory grant

Superfluous left join:

/*Niko's Triko*/
CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt2 (@OwnerUserId INT)
AS 
BEGIN

    CREATE TABLE #t1 (Id INT, INDEX cx_whatever CLUSTERED COLUMNSTORE);

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    LEFT JOIN #t1 ON 1 = 1
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

EXEC dbo.LargeUnusedGrant_alt2 @OwnerUserId = 8672;
GO 

First run: sad memory grant

Second run: happy memory grant