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...
xThe 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
No comments:
Post a Comment