What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

My subjective feeling is that we are somehow on the edge and I am looking for strong arguments to do the right decision to the future.

We are now running EVALUATION edition (so in fact ENTERPRISE). That gave me the space for testing the influence of having RAM limit (Max Server Memory) set to 128 GB and more (in fact set to 169 GB). Let's call the states before (Max Server Memory = 128 GB) and after (Max Server Memory = 169 GB). While testing I was collecting wait stats and several Memory based counters.

In general, wait statistics didn't change much while monitoring before and after the RAM increase. Not even in buffer latch and memory category of wait types.

Memory counters provides more interesting values but are they strong enough indicators of lack of memory?

Memory Grant Pending: value stays = 0 before and after

Total & Target server memory: keeps close to each other before and after - no spikes

System memory state: before - it's mostly in "Physical memory state us steady" state after - it goes to "Available physical memory is hight" state, not suprisingly

Page life expectancy: varies much over time (before and after) before: sometime goes under 300 but in average it is more after: sometimes goes under 300 (but less time)

Batch Reguests/sec & SQL Compilations / sec: the ratio between those two counters didn't change (before / after) - so there seems to be enought space for plans in the buffer, unfortunately the ratio is still higher than 10%.


What steps would you do to be sure that STANDARD edition with its 128GB RAM limit is enough for your production?

To be "exactly" sure you have to test your application on standard edition, there is no other absolute way. Testing your application on enterprise and assuming it would give same performance result on standard will not work always. Enterprise edition has better way of handling things as compared to standard for example the Read Aheads are better in enterprise as compared to standard edition. This is just one example. MS has not documented such small things and I believe it is not possible to document all such small things.

Coming to memory, like you saw, there is hardly any change in memory related counters when moving from 169 to 128 GB of memory, which only indicates that at that particular time extra memory which was taken by changing max server memory was not actually needed much anywhere.

I normally feel if you have RAM 1/4th of total size of database combined on your system it should do fine. Please note this is generalized statement and comes out from my experience and does not holds everywhere. And it definitely does not means that if you have 600GB database altogether you would start facing memory pressure, it all depends on workload.

Your question is bit open ended but I have tried to answer it as good as I can

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.