Sql-server – Decision concerning RAM limit

enterprise-editionmemorymetricssql serverstandard-edition

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

Best Answer

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.
Related Question