45

Why is this simple query granted so much memory?

-- Demo table
CREATE TABLE dbo.Test
(
    TID integer IDENTITY NOT NULL,
    FilterMe integer NOT NULL,
    SortMe integer NOT NULL,
    Unused nvarchar(max) NULL,

    CONSTRAINT PK_dbo_Test_TID
    PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
    (FilterMe, SortMe)
SELECT TOP (100 * 1000)
    CHECKSUM(NEWID()) % 1000,
    CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO    
-- Query
SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe;

For an estimated 50 rows, the optimizer reserves almost 500 MB for the sort:

Estimated plan

42

This is a bug in SQL Server (from 2008 to 2014 inclusive).

My bug report is here.

The filtering condition is pushed down into the scan operator as a residual predicate, but the memory granted for the sort is erroneously calculated based on the pre-filter cardinality estimate.

To illustrate the issue, we can use (undocumented and unsupported) trace flag 9130 to prevent the Filter from being pushed down into the scan operator. The memory granted to the sort is now correctly based on the estimated cardinality of the Filter output, not the scan:

SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

Estimated plan

For a production system, steps will need to be taken to avoid the problematic plan shape (a filter pushed into a scan with a sort on another column). One way to do this is to provide an index on the filter condition and/or to provide the required sort order.

-- Index on the filter condition only
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe
ON dbo.Test (FilterMe);

With this index in place, the desired memory grant for the sort is only 928KB:

With filter index

Going further, the following index can avoid the sort completely (zero memory grant):

-- Provides filtering and sort order
-- nvarchar(max) column deliberately not INCLUDEd
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe_SortMe
ON dbo.Test (FilterMe, SortMe);

With filter and sort index

Tested and bug confirmed on the following builds of SQL Server x64 Developer Edition:

2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

This was fixed in SQL Server 2016 Service Pack 1. The release notes include the following:

VSTS bug number 8024987
Table scans and index scans with push down predicate tend to overestimate memory grant for the parent operator.

Tested and confirmed fixed on:

  • Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
  • Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

Both CE models.

5

From SQL 2012 onwards you could look for a large discrepancy between SerialRequiredMemory and SerialDesiredMemory, eg something like this:

-- Search plan cache for Memory Grant issues
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Collect more info about the plan here if required, eg usecounts, objtype etc, 
SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan
INTO #tmp
FROM sys.dm_exec_cached_plans cp WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO


;WITH cte AS
(
SELECT
    rowId,
    query_plan,
    m.c.value ('@SerialRequiredMemory', 'INT' ) AS SerialRequiredMemory,
    m.c.value ('@SerialDesiredMemory', 'INT' ) AS SerialDesiredMemory

FROM #tmp t
    CROSS APPLY t.query_plan.nodes ( '//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) m(c)
), cte2 AS (
SELECT *,
    CAST( CAST( SerialDesiredMemory AS DECIMAL(10,2) ) / CAST( SerialRequiredMemory AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) Desired_to_Required_ratio
FROM cte
)
SELECT TOP 20
    rowId,
    query_plan,
    SerialRequiredMemory SerialRequiredMemory_KB,
    SerialDesiredMemory SerialDesiredMemory_KB,
    CAST( SerialRequiredMemory / 1024. AS DECIMAL(10,2) ) SerialRequiredMemory_MB,
    CAST( SerialDesiredMemory / 1024. AS DECIMAL(10,2) ) SerialDesiredMemory_MB,
    Desired_to_Required_ratio
FROM cte2
WHERE Desired_to_Required_ratio > 100
ORDER BY Desired_to_Required_ratio DESC

Some further notes on these new attributes here. This query is a bit rough and ready but did pick up the excessive sort query from my SQL Server 2014 dev box with a ratio of 975.47 plus a couple of other eye-popping plans. The 'normal' ratio (at least from my limited testing) seems to be ~1.

HTH

3

Thanks for all the help. I thought I would send an updated version of the above query that we found helpful.

-- Search plan cache for Memory Grant issues
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Collect more info about the plan here if required, eg usecounts, objtype etc, 
SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = DB_NAME(CAST(pa.value AS int))
INTO #tmp
FROM sys.dm_exec_cached_plans cp WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa 
    WHERE pa.attribute = 'dbid' 
GO

;WITH cte AS
(
SELECT
    rowId,
    query_plan,
    m.c.value ('@SerialRequiredMemory', 'INT' ) AS SerialRequiredMemory,
    m.c.value ('@SerialDesiredMemory', 'INT' ) AS SerialDesiredMemory,
    db
FROM #tmp t
    CROSS APPLY t.query_plan.nodes ( '//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) m(c)
), cte2 AS (
SELECT *,
    CAST( CAST( SerialDesiredMemory AS DECIMAL(10,2) ) / CAST( SerialRequiredMemory AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) Desired_to_Required_ratio
FROM cte
)
SELECT TOP 20
    rowId,
    query_plan,
    SerialRequiredMemory SerialRequiredMemory_KB,
    SerialDesiredMemory SerialDesiredMemory_KB,
    CAST( SerialRequiredMemory / 1024. AS DECIMAL(10,2) ) SerialRequiredMemory_MB,
    CAST( SerialDesiredMemory / 1024. AS DECIMAL(10,2) ) SerialDesiredMemory_MB,
    Desired_to_Required_ratio,
    db
FROM cte2
WHERE Desired_to_Required_ratio > 100
ORDER BY Desired_to_Required_ratio DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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