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%.