Memory grant related bug caused by SQL Server Optimizer attribute

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.