Memory grant related bug caused by SQL Server Optimizer attribute

We sometimes meet some pits, either to fill it or to get around it. Here is a description of the relevant SQL Server optimizer features caused by the memory grant related bugs, and related solutions, also by the way to answer the questions Jiangjian classmate.

Problem description

A simple query consumes an incredible amount of memory. (found by Jiangjian students)

Original link

Code

Create TableTest_mem (IDint Identity(1,1)Primary Key, Itemidint  not NULL, Datedatetime  not NULL, str1varchar(Max)NULL)INSERTTest_mem (itemid,date)SELECT TOP( +)        ABS(CHECKSUM (NEWID()))%  $,        DATEADD( Day, CHECKSUM (NEWID())%(3 *  the),GETDATE())     fromsys.all_columns A, Sys.all_columns BGo  -Select *  fromTest_memwhereItemid= - Order  byDate

Execute plan after executing code 1-1

Figure 1-1

It can be seen that such a small sort of data set consumes such a horrible amount of memory data, so simple querying if the amount of data is larger, it can seriously affect the throughput.

Problem analysis: By executing the plan we found just a simple clustered index scan plus a sort. The problem occurs on a clustered index scan, and through semantic analysis we find that our itemid=28 is also filtered in the clustered index scan, but the optimizer does not notice this condition when doing memory evaluation. is also evaluated based on the relative memory size of the whole table.

We can approximate the size of the data that the optimizer "thinks" based on the row size.

Select 100000.0*4051.0/1024.0/1024.0 (approximately equal to 386mb!)

The original optimizer thought he was going to sort the 386MB data ...

Summary: The optimizer does a filter filter for us while doing a clustered index scan, but ignores the filtering in the operator for the next memory evaluation. Serious problems occurred in memory evaluation.

Solution: Understanding the problem point after the resolution is simple. I mentioned the filter operator in pass sharing last June, and we just need to let him reproduce in our execution plan.

Trace Flag 9310 allows this operator to be reproduced.

Code

Select *  from  where itemid=*order by dateoption9130 )

Can be seen through the execution plan, memory grant is normal, 1-2 shows

Figure 1-2

PostScript: This issue I have reacted to Microsoft's CSS team.

Memory grant related bug caused by SQL Server Optimizer attribute