or
Paul White
sql-server
Why is this simple query granted so much memory?

```sql
-- 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][1]


  [1]: https://i.stack.imgur.com/5De1C.png
Top Answer
Paul White
This is a **bug** in SQL Server (from 2008 to 2014 inclusive).

My bug report is **[here][1]**.

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:

```sql
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][2]

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.

```sql
-- 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][3]

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

```sql
-- 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][4]

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)
~~~

The bug was **fixed** in [SQL Server 2016 Service Pack 1][5]. 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`

...under both cardinality estimation (CE) models.

  [1]: https://feedback.azure.com/forums/908035-sql-server/suggestions/32909110-cardinality-estimation-error-with-pushed-predicate
  [2]: https://i.stack.imgur.com/DgEfd.png
  [3]: https://i.stack.imgur.com/upDb8.png
  [4]: https://i.stack.imgur.com/U2aXO.png
  [5]: https://support.microsoft.com/en-us/kb/3182545
Excessive sort memory grant

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.