Filter is push down

The filter can is pushed down directly to the base table or index, in this case, to the clustered index scan operator. It means that the filter is pushed down to the storage engine. The storage engine tests the corresponding row in database file. If the row matches the filter, the storage engine passes it along to the query processor. If the row does not match the filter, the storage engine discards it.

Code from "Excessive sort memory grant"

CREATE TABLEdbo. Test (TIDinteger IDENTITY  not NULL, Filtermeinteger  not NULL, Sortmeinteger  not NULL, Unusednvarchar(Max)NULL,    CONSTRAINTPk_dbo_test_tidPRIMARY KEY CLUSTERED(TID));GO--100,000 Example rowsINSERTDbo. Test with(Tablockx) (Filterme, Sortme)SELECT TOP( - *  +) CHECKSUM (NEWID())%  +, CHECKSUM (NEWID()) fromSys.all_columns asAC1 Cross JOINSys.all_columns asAC2;GO    --QuerySELECTT.tid, T.filterme, T.sortme, t.unused fromDbo. Test asTWHERET.filterme= 567ORDER  byT.sortme;

Why am this simple query granted so much memory? For a estimated, the optimizer reserves almost MB for the sort:

1, I perform on localhost, view execution plan, Memory Grant is 499440KB, view the properties of select operator Memorygrantinfo, what does its sub-properties mean?

Refer to "Memory Grant execution Plan Statistics"

"Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes is estimated during query compile time for Serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism. "

Desiredmemory is the memory that the optimizer expects when compiling the execution plan

Requestedmemory is the memory that is granted to SQL Server requests when the execution plan is run

Requeiredmemory is the minimum memory that a query statement must grant when it starts executing

Grantedmemory is the memory that query plan was granted to perform the sort and hashjoin operations at execution time

2,paul White's explanation, praise a

Filter is push-down to Scan operator so that when SQL Server storage engine reads data from file, the data rows that do not satisfy the filter are filtered out.

Scan operator's predicate is: [Filterme]= (567), indicating that filter is push down

The input data path property of sort Actual number of rows=38, indicating that the stream passed to the sort operator is only $ Rows;

The filtering condition is pushed down to the 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 9 Prevent the Filter from being pushed down into the scan operator. The memory granted to the sort are now correctly based on the estimated cardinality of the Filter output, not the SCAN:

SELECT     T.tid,    t.filterme,    t.sortme,    t.unusedfrom asWHERE       = 567 ORDER by t.sortme OPTION         9130--Not for production systems

Compare execution plan, find a Filter Operator, used to execute query where Clause, Select Operator memory Grant is 10240kb=10m, is reasonable.

Reference Documentation:

Excessive sort Memory grant

Filter is push down