Home About Resume Services Articles Client Results Schedule Contact
Michael Paycer — SQL Server Standard vs Enterprise: What You Give Up
SQL Server Guide

SQL Server Standard vs Enterprise: What You Give Up

SQL Server Standard vs Enterprise past the feature matrix: memory ceilings, offline rebuilds, columnstore, CHECKDB, and the numbers that make the call.

SQL Server Services All Articles Contact

Someone in finance has seen the renewal quote and asked whether Standard would do instead of Enterprise. You have a week to answer, and the comparison pages you find are either a reprint of Microsoft's feature matrix with no judgment attached, or they are years out of date and still claiming Standard cannot do partitioning.

The answer turns on four things: how much memory your working set needs, how you maintain indexes, what your failover story is, and whether your reporting leans on columnstore. The rest of the matrix is noise for most shops. This article covers capability; what the editions cost is a separate question.

What 2016 SP1 changed, and what it did not

In November 2016, Microsoft opened the programmability surface of the engine. Columnstore indexes, In-Memory OLTP, table and index partitioning, and row and page compression stopped being Enterprise features and became available in Standard, Web, and Express.

That change is why a lot of published advice is wrong. It is also narrower than people remember.

The SP1 announcement drew the line: a consistent programmability surface between editions, so a developer could write one set of DDL and have it run anywhere. Operational and scale features stayed put. Asked in that post's comment thread about online index rebuild, Microsoft answered that online index operations are an operational requirement rather than a programmability one. Resource Governor got the same answer. The post is explicit: the scale and high availability limits do not change.

SP1 gave Standard the syntax. It did not give Standard the horsepower to run that syntax at speed, and the distinction matters more than the headline did.

The hard ceilings: memory, cores and database size

Three numbers decide most of these arguments, and two of them moved in SQL Server 2025.

Limit Standard (2017, 2019, 2022) Standard (2025) Enterprise
Compute capacity, one instance Lesser of 4 sockets or 24 cores Lesser of 4 sockets or 32 cores OS maximum
Buffer pool memory, per instance 128 GB 256 GB OS maximum
Columnstore segment cache, per instance 32 GB 32 GB Unlimited
Memory-optimized data, per database 32 GB 32 GB Unlimited
Maximum relational database size 524 PB 524 PB 524 PB

Database size is not a differentiator. Standard will hold 524 petabytes, the same as Enterprise. If someone tells you Standard caps database size, they are thinking of Express, which caps at 10 GB through 2022 and 50 GB in 2025.

The memory cap is per instance, and it applies to the buffer pool. That last word carries weight. The columnstore segment cache and the memory-optimized data quota sit outside the cap and stack on top of it. Microsoft's post on the SP1 limits gives the example: a Standard instance limited to 128 GB of buffer pool can hold an additional 32 GB of columnstore segment cache for the instance, plus an additional 32 GB of memory-optimized data for each database. Aaron Bertrand ran the same arithmetic at SQLPerformance and confirmed the columnstore and In-Memory OLTP quotas are not subtracted from the buffer pool limit when the server has memory to spare.

One qualifier from the same post: a max server memory value set below the edition ceiling is honored. The edition limit is a ceiling, not a floor, and you can still starve the instance by hand.

Find out where you stand:

SELECT
    SERVERPROPERTY('Edition')            AS edition,
    SERVERPROPERTY('EngineEdition')      AS engine_edition,   -- 2 = Standard, Standard Developer, Web, BI
                                                              -- 3 = Enterprise, Enterprise Developer,
                                                              --     Developer, Evaluation
    SERVERPROPERTY('ProductVersion')     AS product_version,
    SERVERPROPERTY('ProductLevel')       AS product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS cu_level,
    si.cpu_count                         AS logical_cpus_visible,
    si.socket_count,
    si.cores_per_socket,
    si.numa_node_count,
    si.physical_memory_kb / 1048576.0    AS physical_memory_gb,
    si.committed_target_kb / 1048576.0   AS committed_target_gb,
    (SELECT COUNT(*)
       FROM sys.dm_os_schedulers
      WHERE status = 'VISIBLE ONLINE'
        AND parent_node_id < 64)         AS schedulers_in_use
FROM sys.dm_os_sys_info AS si;

socket_count, cores_per_socket and numa_node_count arrived in SQL Server 2016 SP2. Drop those three columns if you are running anything older.

schedulers_in_use tells you whether the core cap is biting. On a 48-core box running Standard 2022, that count stops at 24 and the other 24 cores sit idle. The OS sees them and SQL Server will not schedule a worker on them.

Now check whether you are pressed against the memory ceiling:

SELECT
    mc.type,
    SUM(mc.pages_kb) / 1048576.0 AS allocated_gb
FROM sys.dm_os_memory_clerks AS mc
WHERE mc.type IN ('MEMORYCLERK_SQLBUFFERPOOL',
                  'CACHESTORE_COLUMNSTOREOBJECTPOOL',
                  'MEMORYCLERK_XTP')
GROUP BY mc.type
ORDER BY allocated_gb DESC;

If MEMORYCLERK_SQLBUFFERPOOL is parked at 127-and-change on a 2022 Standard instance and page life expectancy is in the hundreds, the cap is your problem and index tuning will not fix it. If it sits at 60 GB on a box with 512 GB of RAM, the cap is not your problem and edition is the wrong argument.

For the in-memory quota, which is per database rather than per instance:

SELECT
    DB_NAME()                                         AS database_name,
    SUM(memory_allocated_for_table_kb
      + memory_allocated_for_indexes_kb) / 1048576.0  AS xtp_allocated_gb
FROM sys.dm_db_xtp_table_memory_stats;

The columnstore trap

This is the one that catches people, because the matrix says Yes. Columnstore is available in Standard. Most of what makes columnstore fast is not.

Microsoft's columnstore documentation names three enhancements as reserved for Enterprise edition: aggregate pushdown (computing MIN, MAX, SUM, COUNT, and AVG during the scan for non-string types of 8 bytes or less), string predicate pushdown (which covers varchar, char, nvarchar, and nchar comparisons including LIKE with bitmap filters), and SIMD, the use of AVX2 and SSE4 vector hardware extensions.

Then there is the parallelism footnote, carried on the columnstore row and the batch mode row in every editions matrix from 2017 forward: the degree of parallelism for batch mode operations is limited to 2 for SQL Server Standard edition, and 1 for Express. Through 2022 the footnote names Web as well. SQL Server 2025 dropped Web edition, and the 2025 footnote names Express alone.

DOP 2. On a 24-core Standard instance.

Erik Darling tested this on SQL Server 2019 CU9 and published the numbers. A batch mode columnstore query running at the Standard DOP limit of 2 took 3.8 seconds, while the same data scanned in row mode at DOP 8 took 1.4 seconds. The nonclustered columnstore build ran about a minute in a serial plan on Standard against 28 seconds on Enterprise, because index creation runs serial there. His conclusion on the restrictions was blunt, and his practical point is the one to take away: on Standard, adding a columnstore index can make a query slower than leaving it on a rowstore index.

If the pitch for Standard is "we will put columnstore on the reporting tables and it will be fine," test it first. Build the index, run the ten queries that matter, and compare against the rowstore plan at your normal MAXDOP.

Availability groups and failover on Standard

Standard does not get Always On availability groups. It gets basic availability groups, which replaced database mirroring, and the restrictions are severe enough to read as a different feature rather than a smaller one.

A basic availability group supports two replicas. One database. The secondary is not readable, you cannot back up from it, and you cannot run integrity checks against it. It cannot join a distributed availability group, and it cannot be upgraded to an advanced availability group. Moving up means dropping the group and building a new one.

Read that list against what people want from an AG. Offload backups: no. Offload reporting: no. Run CHECKDB on the secondary: no. Protect twelve databases that fail over together as an application unit: no, you get twelve separate groups with no coordinated failover. The basic AG protects one database and gives you automatic failover. That is the whole feature. Whether that clears the bar depends on the objectives somebody signed, which is the question behind what you give up protecting a single database.

Failover cluster instances work on both editions. Enterprise supports a maximum of 16 nodes, Standard two. For an active-passive pair on shared storage, Standard is enough and Enterprise buys nothing.

One row surprises people: basic availability groups are Standard-only. The Enterprise column reads No, because Enterprise gets the full feature. Enterprise supports one to eight secondary replicas, and SQL Server 2019 raised the maximum synchronous-commit replicas to five, up from three in 2017. The availability group overview counts that five as including the primary; the 2025 matrix footnote counts five synchronous secondaries. Check which a colleague means before sizing the cluster.

Index maintenance is where the operational pain lives

Three separate rows, all Enterprise-only in 2019, 2022, and 2025:

On Standard, ALTER INDEX ... REBUILD WITH (ONLINE = ON) returns error 1712: online index operations can only be performed in Enterprise edition of SQL Server. Resumable requires ONLINE, so resumable is out for the same reason. And parallel index maintenance being Enterprise-only means the rebuild is serial. Passing MAXDOP does not change that.

Combine those three and you get the real cost. A 200 GB clustered index on Standard rebuilds offline, single-threaded, and the table is unavailable throughout. You cannot pause it at 4 a.m. when the window closes and resume tomorrow, because resumable is gated behind online. The same index on Enterprise rebuilds online at DOP 8, and if it overruns you pause it and pick it up the next night.

Find the indexes that will hurt:

SELECT TOP (25)
    s.name  AS schema_name,
    t.name  AS table_name,
    i.name  AS index_name,
    i.type_desc,
    SUM(ps.used_page_count) * 8 / 1048576.0 AS size_gb,
    SUM(ps.row_count)                    AS rows_in_index
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i
  ON  i.object_id = ps.object_id
  AND i.index_id  = ps.index_id
JOIN sys.tables AS t  ON t.object_id = i.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE i.index_id > 0
GROUP BY s.name, t.name, i.name, i.type_desc
ORDER BY size_gb DESC;

Anything over 50 GB there is a conversation. On Standard you reorganize it rather than rebuild it, accept that REORGANIZE does not update statistics, and run UPDATE STATISTICS behind it by hand. That swap has consequences past the maintenance window, because reorganize plus a statistics update is the Standard pattern and it changes what you are measuring when a plan regresses afterward.

Note what Standard keeps: table and index partitioning, and partitioned table parallelism. Both read Yes for Standard in the 2019, 2022, and 2025 matrices. Partitioning your way around a rebuild window is a sound Standard strategy, and it is the most common thing people get wrong in the other direction.

CHECKDB has no parallel checking on Standard

Parallel consistency check is Enterprise-only in the 2017, 2019, 2022, and 2025 matrices. Erin Stellato at SQLskills chased the behavior down with the SQL Server development team: DBCC CHECKDB does no parallel checking of objects on Standard at all, and is always single-threaded there. Her post dates from November 2012. The matrix row has held in every version since, but Microsoft has never restated the single-thread fallback in current docs, so time the check yourself.

You also cannot run the check on a basic AG secondary. A large database on Standard leaves you with WITH PHYSICAL_ONLY on weeknights, a full check at the weekend, and DBCC CHECKTABLE batched across nights for the largest tables.

Intelligent query processing is an Enterprise feature set

Enterprise-only in the 2019 and 2022 matrices: automatic tuning, batch mode on rowstore (which also needs compatibility level 150), batch mode adaptive joins, batch mode memory grant feedback, row mode memory grant feedback. In 2022, cardinality estimation feedback, degree of parallelism feedback, and memory grant feedback persistence and percentile are Enterprise-only as well. Query Store on secondary replicas is Enterprise-only.

Available on Standard: Query Store itself, interleaved execution for multi-statement table-valued functions, scalar UDF inlining, table variable deferred compilation, approximate count distinct, and Query Store hints from 2022 forward.

The pattern is consistent: you get the compile-time rewrites, not the feedback loops that watch execution and correct themselves, and not FORCE_LAST_GOOD_PLAN. On Standard you notice a plan regression in Query Store and do the work the automatic tuning feature would have done: forcing the plan by hand when nothing does it for you.

What Standard gets that people assume it does not

Stop arguing for Enterprise on any of these:

All of these read Yes for Standard in 2019 and later unless the bullet names a different version.

Peer-to-peer transactional replication and Oracle publishing stay Enterprise-only. So do Resource Governor and I/O resource governance, both through 2022 and both covered below for 2025, along with distributed partitioned views, automatic use of an indexed view by the optimizer without NOEXPAND, memory-optimized tempdb metadata, NUMA-aware large page allocation, hot add memory and CPU, mirrored backups, online page and file restore, and fast recovery.

2025 moved the line

SQL Server 2025 raised Standard to 256 GB of buffer pool and the lesser of 4 sockets or 32 cores. Resource Governor now reads Yes for Standard, the I/O resource governance row is gone, and a new tempdb space resource governance row reads Yes for Standard. Web edition is gone too, and Express moved to 50 GB.

The rest held. Columnstore segment cache and memory-optimized quotas stayed at 32 GB. Online, resumable, and parallel index operations stayed Enterprise-only, as did read-ahead, advanced scanning, AVX-512 support, and hardware acceleration offload. 2025 raised the ceiling without moving index maintenance across the line.

Doubling the memory cap removes the most common reason to leave Standard. If your case for Enterprise was "we have 300 GB of hot data," recheck it against 2025 before signing.

Making the call

Standard fails, and you should pay for Enterprise, when any of these is true:

  1. Your hot working set exceeds the buffer pool cap for your version and you have the RAM to use. Confirm with the buffer pool clerk query above plus page life expectancy per NUMA node, not a guess about database size. A slow server is not proof of a ceiling, so start from measuring whether the ceiling is your actual problem before the edition argument gets made for you.
  2. You have indexes large enough that an offline single-threaded rebuild will not fit the maintenance window, and partitioning will not decompose them.
  3. Your reporting depends on columnstore performance. DOP 2 with no aggregate pushdown, no string predicate pushdown, and no SIMD is not a smaller version of Enterprise columnstore.
  4. You need a readable secondary, backups off a secondary, more than two replicas, more than one database failing over as a unit, or more than two cluster nodes.
  5. The box has more cores than the edition cap and the workload is CPU-bound across all of them.
  6. CHECKDB on the primary does not fit in the window, and you were counting on a secondary to take it.

Enterprise buys nothing over Standard when all of these are true:

  1. The working set fits under the cap with headroom and steady-state page life expectancy is healthy.
  2. The server has 24 cores or fewer (32 on 2025), or the workload does not saturate what Standard can schedule.
  3. Maintenance runs in a window you control, and reorganize plus explicit statistics updates is acceptable.
  4. Availability means an active-passive FCI pair, one database in a basic AG, or log shipping.
  5. The reporting workload is rowstore with well-chosen indexes.

The procedure, when you have a week and a question from finance:

  1. Run the edition and scale query. Write down cores visible, schedulers in use, physical memory, and committed_target_kb.
  2. Run the memory clerk query at peak. Record buffer pool allocation and compare against your edition ceiling.
  3. Pull page life expectancy per NUMA node from sys.dm_os_performance_counters across a full business cycle, month-end included.
  4. Run the index size query. List every index over 50 GB and time one offline rebuild of the largest in a test restore.
  5. Time DBCC CHECKDB against a restored copy on a Standard instance. That is your real number.
  6. Write down the recovery time and recovery point objectives the business signed off on, then check them against the basic AG restriction list.
  7. Bring the six numbers to the meeting. The decision makes itself, and you will not be the person who moved to Standard and found out in March that the nightly rebuild no longer finishes.

If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.