High boss ' SQL Server optimizer attribute caused by memory grant related bug ' learning notes

Today, senior boss has a good article. It was a bit confusing for me to take this opportunity to learn.

Just use the high-boss script. You need to go directly to him to find it, here is saved.

Plus query optimization tag before and after comparison

You can see that grantedmemory is 504928KB, about 213096/1024=208.101562MB (the value here seems to be different every time, but the gap is not too big.) Does not affect the effect)

After you add the trace flag

4560/1024 is probably only 4MB.

The difference is very big ~

In fact, these values may also be seen in the query plan XML, which contains more detailed information.

Such as

<queryplan degreeofparallelism= "1" memorygrant= "216568" cachedplansize= "" Compiletime= "2" CompileCPU= "2" compilememory= "168" >
<memorygrantinfo serialrequiredmemory= "serialdesiredmemory=" 504928 "requiredmemory=" DesiredMemory= " 504928 "requestedmemory=" 216568 "grantwaittime=" 0 " grantedmemory=" 216568 "maxusedmemory="/> "
<optimizerhardwaredependentproperties estimatedavailablememorygrant= "196572" EstimatedPagesCached= "7482" estimatedavailabledegreeofparallelism= "2"/>

Memorygrant This is the evaluation value that begins with the serial* field, as if the evaluation value when this query is serialized (Serial required/desired memory attributes is estimated during Query compile Itme for serial execution).

Highly Recommended articles:

Understanding SQL Server Memory Grant

Troubleshooting SQL Server Resource_semaphore Waittype Memory issuesmemory Grant execution Plan Statistics

To tell the truth, there are a few parameters in it or not very clear the exact meaning. In the actual work how to use, I hope someone can give some guidance.

High boss ' SQL Server optimizer attribute caused by memory grant related bug ' learning notes