I love questions! It means someone was really paying attention!
Here's the relevant blog post about the appearance of a lot of free memory when a checkdb (without tablock) completes.
Where'd all this #SQLServer Free Memory come from??!?
http://sql-sasquatch.blogspot.com/2017/04/whered-all-this-sqlserver-free-memory.html
So - what contribution to free memory on completion of checkdb is made by releasing the memory grant? YMMV, but for my 3.35 TB database... almost none. Here's the thing: checkdb doesn't get a single memory grant, rather at multiple times throughout its execution it gets memory grants. And with each of those memory grants, like other queries, it may or may not use the entire memory grant. So, the contribution of releasing the memory grant to free memory is dependent on how much used memory grant checkdb releases on completion. That's kind of confusing to describe, maybe some graphs will help!
This is the same system, same checkdb as previous post. The CPU utilization in blue clearly delineates when checkdb was active, since it was run in isolation.
As checkdb progressed, it requested varying memory grants - granted memory in the dark brown/orange below. In order for this not to be too long of a blog post, I layered "reserved memory" in a lighter orange in front of granted memory. So - the exposed dark brown granted memory is the workspace memory at any given time that is actually used. Recall that when memory is granted its just a promise - the query "steals" memory against the grant over time as needed.
So if we subtract reserved from granted... we'll be left with query memory used by checkdb. Here's what that looks like.
This is fun! Let's keep going! I said that over time, as the query needs more of its grant for sort/hash, it "steals" against the grant. Hmmm... am I implying that used query memory is a subset of "stolen memory", and that only used query memory (excluding reserved memory from the grant) is within "stolen memory"? Yessss!! Yes, I am.
Check out used query memory layered in front of stolen memory in the graph below. Stolen memory is greater than used query memory - but by a relatively fixed amount, and the greater stolen memory is during this isolated checkdb, the greater the share used query memory is.
Interesting, no? Here's the relevance to the initial question: look how small the used query memory is as checkdb nears completion and CPU utilization drops to near zero. Its pretty doggone low. That was the contribution to free memory when the last grant of checkdb was released. Not much. But, as I said, YMMV. Maybe the allocation order of HoBTs in your database is different, and a peak of used query memory like that between 20:50 and 21:50 occurs right before completion! In that case, releasing the grant would lead to a pretty big contribution to free memory.
OK. Now for a lagniappe :-)
See that dip in CPU utilization at 16:50, and at 17:50? Let's tie that in to checkdb work via tempdb.
In perfmon, we've got "SQLServer:Databases(tempdb)\Data File(s) Size (KB)" and "SQLServer:Transactions\Free Space in tempdb (KB)". Let's subtract them to get tempdb used!
And - here's my trick that I think you'll see first from me: let's stack tempdb used on top of query memory used!! Conceptually, its easiest to understand that when a single session is running in isolation, and when its a session that isn't explicitly using temp tables, row versioning, etc. In that case, tempdb use comes from spill: sort/hash has consumed the memory grant, so tempdb is leveraged to complete remaining sort/hash work.
Here's what that looks like. How nice for me; it just happened to work out that there were nice peaks in tempdb use at 16:50 and 17:50 - the same times that CPU utilization dipped. So tempdb-related waits are prime suspects. :-)
Ciao for now!!
No comments:
Post a Comment