Friday 28 June 2013

Changing the Max Server Memory setting

Of late I have encountered a number of servers with no Max Server Memory setting, in fact all around me I see SQL Server 2008R2 servers which are straight out the box installs.

A couple of these machines have had memory challenging - I was looking at one today, a virtual, with 30Gb of RAM allocated, 100% adhoc queries (and no "optimize for adhoc workloads" but duly sorted that) where SQL is reporting as using 29.7Gb of RAM and 805mb of ram outside of the buffer cache, needless to say paging was quite heavy as SQL alone was using 30.5Gb of 30Gb.

However, a slight catch, the Max Server Memory setting is dynamic and can simply be changed but very recent experience has shown me that regardless of whether the max setting is above or below current usage for certain the procedure cache is dumped, I haven't checked buffer cache but I suspect that the setting change is a blunt instrument so be warned.