SSAS Memory Accounting, Part I: Cleaner Memory

I had to mess around with lots of perfmon graphs in Excel before I finally understood how various SQL Server engine memory accounting fit together.

Time to start doing that for SSAS, I guess.

First stop: Cleaner Memory nonshrinkable + Cleaner Memory shrinkable = Cleaner Memory

That's intuitive - but you don't know for sure until you know... y'know? :-)

Perfmon with 30 second interval, on a server with SSAS as the only active major memory consumer.

The blue areas below form a stacked area graph of the two memory allocation components.  The red is a line graph against a secondary vertical axis (which I've hidden) that uses the exact same scale as the primary vertical axis on the left.  (That's how I often create combination stacked and line graphs to show aggregated memory accounting.)





That's it for today.  I know its not much.  I'll be back with more once I can get anything else to line up.

@sqL_handLe don't forget about trace flags 6498, 7470 and 8075

In my race to retire trace flags in SQL Server 2016 I need not forget about trace flags 6498, 7470 and 8075.  Not sure if these fixes are included by default in SQL Server 2016.  If I find out they are included by default in SQL Server 2016 I'll update this post... and retire these trace flags in my systems in tandem with adoption of SQL Server 2016 :-)

Trace flag 6498 introduced in:
SQL Server 2014 RTM SP6
SQL Server 2014 SP1 CU1
Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014
https://support.microsoft.com/en-us/kb/3024815

Trace flag 7470 introduced in:
SQL Server 2012 SP2 CU8
SQL Server 2014 RTM CU10
SQL Server 2014 SP1 CU3

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
https://support.microsoft.com/en-us/kb/3088480

Trace flag 8075 introduced in:
SQL Server 2012 SP2 CU8
SQL Server 2014 RTM CU10
SQL Server 2014 SP1 CU4

FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory
https://support.microsoft.com/en-us/kb/3074434

SQL Server 2016: Max server memory vs sqlservr private bytes

TL;DR - if trying to confine SQL Server memory consumption within a small footprint, consider setting "max workers" lower than the default.

Today a question came up about the role of "max server memory" in controlling overall SQL Server memory use on Windows.  Its important to note that memory manager changes between SQL Server 2008 R2 and SQL Server 2012 expanded the governance of "max server memory" significantly.
Additional information:
https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/
https://blogs.msdn.microsoft.com/sqlosteam/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012/
https://support.microsoft.com/en-us/kb/2663912

I've don a lot of SQL Server 2016 testing and haven't really very many of my graphs.  Time to start, I guess.

I ran a workload, and below you can see the relationship between "target server memory" and "total server memory". I included the page reads/sec just to indicate "yes, there is a reasonable amount of activity".



There are some slight fluctuations in "total server memory", but not beyond "target server memory" +/- 5 mb.

I love looking at database cache + free memory + stolen memory, so here's that(stacked area graph).



With the axis ranging all the way to zero can't really see how much fluctuation there is.  If I change the scale and move the minimum...



x

The small fluctuations in "total server memory" can be completely absorbed by free memory controlled by the manager, so I'm not too concerned.

But... what was I looking for?  Oh, yeah.  The relationship between "max server memory" and private bytes.



 Well, there's obviously a relationship between private bytes and "total server memory".  The peaks and valleys occur at the same time.   But there's a delta between them, and the delta isn't constant.

The difference between private bytes and "total server memory" is pretty well confined, too - not varying more than 10 mb in this interval.



What was changing that lead to a change in the overage of private bytes vs "total server memory"?

SQL Server thread count, for one thing.



Thread count changed by two, and the delta between private bytes and "total server memory" changed by about 4 mb.  Recall that each SQL Server worker is responsible for about 2 mb of memory consumption.  After a specified period of idle, SQL Server worker threads can deactivate.  That's what happened here, and each deactivated thread lead to a 2 mb smaller memory footprint (all other things being equal) for SQL Server.

All right - what started me on this path was SQL Server memory concerns on a laptop, so here's my big finish.  If you want to guarantee a small SQL Server memory footprint on a laptop, you may have to set "max workers" lower than its default.  Because each worker can consume 2 mb of memory and the minimum max workers in 64 bit is 512.

More details here.
https://blogs.msdn.microsoft.com/sqlsakthi/2011/03/13/max-worker-threads-and-when-you-should-change-it/  

If you do lower "max workers" to conserve memory, just watch out for waits incurred by waiting for workers once you ramp up a workload. You may want to lower maxdop from its default to 2 (or even - gasp - 1) if you want higher query concurrency than default maxdop allows before getting worker waits.  But on a laptop you probably don't want 512 active workers anyway.  Minimum "max workers" is 128.
https://technet.microsoft.com/en-us/library/ms190219%28v=sql.130%29.aspx