How I Use sp_BlitzIndex To Find Indexing Problems In SQL Server Databases
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Want inspiration? Deconstruct and troubleshoot where stolen memory goes.
As the Germans say, 9.
Stolen memory is the abyss that stares back at you and makes snide jokes about your ancestry. Or Canadian beer. I try to be elsewhere when that happens so I forget which.
You’ve done spooling and it’s indexing already but if you have something new to add I’m 20% ears.
I mean stolen memory is just other memory clerks that aren’t the buffer pool. Have you tried running sp_PressureDetector to look at which clerks are consuming the most memory outside of it? Most of what I see stolen memory going to is query memory grants.
“stolen memory is just other memory clerks that aren’t the buffer pool”
See, that was a whole blog post.
“running sp_PressureDetector to look at which clerks are consuming the most memory outside of it”
Now that’s a good idea and I even used that. But I’m trying to get an accounting from that and see this:
“memory_source memory_consumer memory_consumed_gb
Buffer Pool Memory MEMORYCLERK_SQLBUFFERPOOL 46.82
Non-Buffer Pool Memory: Total Stolen Server Memory (KB) 9.11
Non-Buffer Pool Memory: Top Five CACHESTORE_SQLCP 4.92
Non-Buffer Pool Memory: Top Five CACHESTORE_PHDR 0.49
Non-Buffer Pool Memory: Top Five OBJECTSTORE_SERVICE_BROKER 0.21
Non-Buffer Pool Memory: Top Five MEMORYCLERK_SOSNODE 0.20
Non-Buffer Pool Memory: Top Five MEMORYCLERK_SQLSTORENG 0.18”
******
So I see (total) Stolen Server Memory (KB) as 9.11gb and CACHESTORE_SQLCP as 4.92 which leaves roughly 4GB unaccounted for. So now it’s down to me to work through sys.dm_os_performance_counters as used in sp_PressureDetector. That explains a lot. Thank you for your time and I’ll continue my descent into the abyss and suffer through the disparagement of Molson and Moosehead.