Decision concerning RAM limit

Question

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

Answer 1

Memory Utilization

I would also run the following statement to see how SQL Server memory is being used:

SELECT 
  physical_memory_in_use_kb/1024            AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024          AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024         AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024  AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count                        AS sql_page_fault_count,
    memory_utilization_percentage           AS sql_memory_utilization_percentage, 
    process_physical_memory_low             AS sql_process_physical_memory_low, 
    process_virtual_memory_low              AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

At some point when you keep on increasing memory, the sql_memory_utilization_percentage column will start moving away from 100%. This is because SQL Server may not require more memory.

SQL Server Edition Features

Depending on your SQL Server Edition there are some features that are available or not. Two being the Read-Ahead and Advanced Scanning capabilities which are described in the article Reading Pages (Microsoft | SQL Docs).

SQL Server 2016 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2017 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

SQL Server 2019 Scalability and Performamnce

Feature           | Enterprise | Standard
------------------+------------+----------
Read-Ahead        | Yes        | Yes (possible limitations; see Correction)
Advanced Scanning | Yes        | No

Read Ahead

The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.

Correction Apparently the Read Ahead feature is available in all editions of SQL Server. I found the article When are read ahead reads issued (Microsoft Social MSDN - Paul White) which answers this question. However, there can still be internal limitations depending on the edition that would affect the amount of read-aheads performed in the Standard Edition.

i.e. Enterprise can read further ahead, if the circumstances are right. The exact numbers might change between versions (that second quote is taken from Bob Dorr's SQL Server 2000 I/O Basics paper - http://technet.microsoft.com/en-us/library/cc966500.aspx). It might be 2,048 pages these days - who can keep up? ;c)

Advanced Scanning

In SQL Server Enterprise, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.

^{(Taken from the linked Microsoft article Reading Pages)}

Because you are testing on "Enterprise Edition" you may be observing a performance boost just because of these additional features.

Caution
There are features which may or may not be available in the version of your SQL Server Standard Edition. Some features became available within the same version but with different Service Packs!

Please ensure you are comparing Apples (Standard) with Apples (Standard) and not Apples (Standard) with Pears (Enterprise).

Rare Cases & Trace Flags

In some rare cases having too much memory can be counter-productive. This situation is described in the Knowledge Base article regarding the Trace Flag 2335.

When you configure max server memory sp_configure option in Microsoft SQL Server to a large value, you may notice that a particular query may run slowly. But if you lower the value for this option, the same query may run much faster.

Answering Your Question

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

1. Compare Standard Edition with Standard Edition
2. Keep an eye on your baseline counters
   • If your counters show a decrease in performance and users are complaining, consider switching to Enterprise Edition if only for the benefits of the various features available in Enterprise Edition. Please test thoroughly before switching.
   • If your counters don't show a decrease in performance and users are happy, then 128 GB might be just right.

Answer 2

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