SQL Server

The feature matrix is not what constrains a SQL Server design. The scale limits are. Standard edition will not use more than a fixed amount of memory for its buffer pool and will not schedule work across more than a fixed number of cores, no matter what hardware you put underneath it, and the gap between Standard and Enterprise is a licensing decision measured in cores, not a configuration change. Get those numbers wrong at the design stage and every later tuning effort is spent working around a ceiling that was chosen for you.

Edition scale limits

These are per instance of the Database Engine, and they are hard: exceeding the hardware simply wastes it. The figures changed in 2025, which matters if you are sizing a migration.

The buffer pool cap is not the instance’s total memory. Columnstore segment cache and memory-optimised (In-Memory OLTP) data are capped separately on Standard, at 32 GB each, outside the buffer pool figure. A Standard instance therefore has a working set larger than 256 GB, but you cannot spend the columnstore allowance on row-store pages, or the reverse.

Compute is capped by sockets and cores, whichever binds first. Standard on a two-socket server with 24 physical cores per socket uses 32 of the 48 cores; the other 16 are bought and idle. In a virtualised environment the limit applies to logical processors instead of cores, because the processor architecture is not visible to the guest. Deploying multiple instances on one server is the documented way to consume capacity beyond a single instance’s limit - hence the 50-instance figure.

Express is a distribution format, not a small database. The size cap rose from 10 GB to 50 GB in SQL Server 2025 (17.x), which matters for shipped appliances, but 1,410 MB of buffer pool and four cores mean anything with a working set worth caching reads from disk continuously. Express is also excluded from most of the high-availability list below.

Enterprise’s only compute limit is a legacy one. Under Server + Client Access License (CAL) licensing (not available for new agreements) Enterprise is capped at 20 cores per instance; under core-based licensing there is no limit. If you inherit an Enterprise instance that refuses to use more than 20 cores, this is why.

One ceiling is independent of edition: SQL Server does not support more than 64 logical cores per NUMA node. From SQL Server 2022 (16.x) CU11 the Database Engine refuses to start if it detects a configuration that can exceed this, and from CU15 Setup warns about it. The fix is BIOS-level: sub-NUMA clustering on Intel, Nodes per Socket on AMD, or disabling SMT on Azure Mv3-series VMs.

Always On: availability groups versus failover cluster instances

These two things share a brand and protect against different failures. Deploying the wrong one is a common and expensive mistake, because you only find out during the incident it does not cover.

An FCI is one instance installed across cluster nodes and presented on the network as a single virtual network name. Exactly one node owns the resource group (network name, IP address, shared disks, the Database Engine and Agent services) at a time. On failover, dirty pages are flushed if the failure allows it, services stop on the old node, ownership transfers, and clients reconnect to the same name with no reconfiguration. Because there is one copy of the data, an FCI does nothing for storage failure, and failover time is bounded by the number of dirty pages in the buffer cache. Indirect checkpoints throttle that, at the cost of more I/O during normal operation.

An availability group replicates at the database level. The primary sends transaction log records to every secondary, each of which hardens the log and applies it independently. Failover happens at the level of the replica, not the database: a suspect database does not trigger it. Secondaries are not backups, and Microsoft is explicit about that. You still need a backup regime, although log backups and copy-only full backups can be offloaded to a secondary. On Windows an HA availability group requires a Windows Server Failover Cluster, with each replica on a different node; on Linux the cluster manager is Pacemaker. Quorum is computed across all WSFC nodes, whether or not they host replicas, and there is no witness role as there was in database mirroring. A read-scale availability group has no cluster manager and consequently no automatic failover.

The Enterprise edition matrix says up to eight secondary replicas including five synchronous ones; the availability groups overview describes the same topology as a primary plus four synchronous-commit secondaries. Either way, plan for a synchronous set of five and asynchronous replicas beyond that.

The Standard edition restriction

Standard does not get availability groups. It gets basic availability groups, and the restriction is severe: two replicas, and one database. Not one availability group with several databases. One database. Contained availability groups, distributed availability groups, and automatic read/write connection rerouting are all Enterprise-only. So is online index create and rebuild, resumable online index rebuilds, and online page and file restore, all of which are what make maintenance on a 24/7 system possible in the first place.

The practical effect: on Standard, an application with six databases that must fail over together has no supported availability-group answer. The options are an FCI (two nodes, shared storage, so no protection against storage loss), log shipping, or Enterprise.

Combining the two is legitimate: an FCI can host a replica of an availability group for remote disaster recovery. One caveat applies. Automatic failover from an FCI to other nodes within an availability group is not supported.

Isolation levels and RCSI

The default isolation level is READ COMMITTED, and its behaviour depends entirely on one database option most instances never change.

With READ_COMMITTED_SNAPSHOT off, a SELECT takes shared locks: it is blocked by uncommitted writers and it blocks writers in turn. On a contended OLTP database that single fact produces most of the blocking chains you will ever investigate. Turning the option on switches the same READ COMMITTED sessions to row versioning. Each statement sees a transactionally consistent view of the data as it stood when the statement began, taking only schema stability locks. No application code changes. It is the default on Azure SQL Database, and it is also the setting Microsoft names as the one that gives the most benefit from optimized locking.

ALTER DATABASE [Orders] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Two things to know before running it. First, when you set the option only the connection executing ALTER DATABASE may be in the database; there must be no other open connection until it completes, though the database does not have to be in single-user mode. WITH ROLLBACK IMMEDIATE is how you get there on a live system, and it kills in-flight transactions.

Second, the semantics change. Readers no longer wait for writers, so a query that used to block until a writer committed now returns the previous committed value instead. Code that relied on blocking as an accidental serialisation mechanism will behave differently. The READCOMMITTEDLOCK table hint restores shared locking for individual statements where you actually need it.

tempdb and the version store

Row versions live in a version store. If accelerated database recovery (ADR) is enabled the version store is in the user database; otherwise it is in tempdb. Every data modification in the database is versioned once either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION is on, including modifications made by transactions that never read a version.

Versions are retained as long as any active transaction might need them. A single long-running transaction therefore pins the whole version store, which is the mechanism behind almost every “tempdb filled up after we enabled RCSI” incident. Row versions created inside a trigger are held to the end of the transaction even though nothing needs them after the trigger finishes.

The failure modes are specific and each has its own message:

There is a cost in the data rows too. Up to 14 bytes per row are appended for row versioning information: the transaction sequence number and a pointer to the versioned row, added the first time a row is modified or inserted while versioning is on. On a table with 100-byte average rows, Microsoft’s own example, that is up to 14 percent growth, plus page splits as the extra bytes fail to fit on existing pages. Lower the fill factor before you enable it on a large, densely packed table.

Presize tempdb on its own storage and monitor sys.dm_db_file_space_usage and sys.dm_tran_active_snapshot_database_transactions. RCSI converts a blocking problem into a space problem, and the space problem is caused by long transactions.

Query Store

Query Store records a history of queries, plans and runtime statistics per database and retains them in time windows, so a regression can be tied to the plan change that caused it. The plan cache holds only the latest plan for a query and evicts under memory pressure; Query Store does not.

It is enabled by default in READ_WRITE mode for new databases from SQL Server 2022 (16.x). It is not enabled by default on 2016, 2017 or 2019, and a database restored or upgraded in place keeps its previous setting, so a 2022 or 2025 instance full of migrated databases may have it off everywhere. It cannot be enabled on master or tempdb.

What it captures: plans for DML, meaning SELECT, INSERT, UPDATE, DELETE, MERGE and BULK INSERT. By design it does not capture DDL such as CREATE INDEX, though it does capture the internal DML those statements generate. Natively compiled stored procedures are excluded unless you enable them with sys.sp_xtp_control_query_exec_stats. From SQL Server 2017 (14.x) it also tracks per-query wait statistics, which is the only convenient way to attribute instance-level waits to a specific query.

Defaults worth knowing before you rely on it:

The size limit being checked only at flush time is the operational trap: Query Store can breach MAX_STORAGE_SIZE_MB between checks and then flip itself to READ_ONLY, silently stopping collection at exactly the moment the workload got interesting. Size-based cleanup then frees space and it switches back. If you find a database in READ_ONLY mode, raise the size or clear the store, then fix the cause. An unparameterised ad hoc workload is the usual one. Microsoft’s guidance is blunt: if you think you need more than 10 GB, fix the workload’s plan reuse instead, with forced parameterisation or QUERY_CAPTURE_MODE = CUSTOM.

A configuration that survives a real workload:

ALTER DATABASE [Orders] SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Those CUSTOM thresholds are the documented AUTO values from SQL Server 2019 (15.x) onwards, set explicitly so that changing one is a deliberate act.

Plan forcing is the reason to have Query Store on before you need it. Because multiple plans per query are retained, the Query Processor can be directed to use a specific one, using a mechanism similar to a query hint but requiring no application change. When a plan regresses after a statistics refresh, a new index, or a parameter-sensitive recompile, the “Regressed Queries” and “Queries With Forced Plans” views in SSMS identify it and force the earlier plan in seconds. Treat a forced plan as an incident mitigation with an expiry date, not a fix: it pins the optimiser to a decision made against data that will keep changing.

Two global trace flags trade data for availability: 7745 stops Query Store flushing to disk before shutdown, losing up to DATA_FLUSH_INTERVAL_SECONDS of data, and 7752 loads it asynchronously at startup so the database comes online before the store has recovered.

Licensing, and why “add cores” is the wrong reflex

Under the per-core model, the number of licences equals the number of physical cores on the server, subject to a minimum of four licences per physical processor, or, when licensing a virtual machine, the number of virtual cores, subject to a minimum of four per VM. Licences are sold in two-core packs, so an order is always the licence count divided by two.

Three design implications:

• Vertical scaling has a linear licence cost with no volume relief. Doubling cores doubles the licence count in the same edition. The cheapest performance work is nearly always the work that reduces CPU demand: indexing, plan stability, RCSI to remove blocking. More cores come after that.
• The four-licence-per-processor minimum penalises wide, low-core-count hardware. Four sockets with two cores each still costs 16 licences. Fewer sockets with more cores per socket is the licence-efficient shape, up to the edition’s core cap.
• The Standard-to-Enterprise decision is usually driven by an HA requirement, not throughput. Full availability groups, online index rebuilds and resource governor are Enterprise features. If you need them, you pay the Enterprise per-core rate on every core in the server.

Developer and Evaluation editions include all Enterprise functionality but are licensed for development and test only; Evaluation expires after 180 days. SQL Server 2025 adds a Standard Developer edition, which is the right choice for a test environment that must reproduce Standard’s ceilings. Verify entitlements against the licensing guidance and Product Terms, not this page.

Diagnostics

SQL Server exposes diagnostics through Dynamic Management Views (DMVs), the error log, and Extended Events.

Blocking and deadlocks. Identify blocked sessions:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

For deadlocks, enable the system_health Extended Events session (on by default in 2012+) and query the deadlock graph:

SELECT XEvent.query('.') AS deadlock_graph
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent);

High CPU from query compilation. Check plan cache pressure:

SELECT
    objtype,
    COUNT(*) AS plan_count,
    SUM(size_in_bytes) / 1024 / 1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

Thousands of Adhoc plans indicate missing parameterisation. Enable forced parameterization at the database level or fix the application to use parameterised queries.

TempDB contention. Visible as PAGELATCH_* waits on pages in database 2:

SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

Fix: add more TempDB data files (one per logical CPU core, up to 8), and enable trace flag 1118 (uniform extents) on versions before 2016.

Availability Group failover not completing. Check the AG dashboard in SSMS or:

SELECT
    ag.name,
    ars.role_desc,
    ars.synchronization_health_desc,
    ar.availability_mode_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ar.replica_id = ars.replica_id
JOIN sys.availability_groups ag ON ag.group_id = ar.group_id;

If synchronization_health_desc is NOT_HEALTHY, the secondary is behind. Check the error log on the secondary for redo thread errors, and verify network connectivity between replicas.

Log file growing unbounded. The transaction log does not truncate if a long-running transaction or replication holds it open:

SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = '<db>';

Common values: ACTIVE_TRANSACTION (find and kill or commit it), REPLICATION (the log reader job is behind or a subscriber is disconnected), AVAILABILITY_REPLICA (secondary is behind on redo).

Where SQL Server is the wrong choice

When the licence cost buys nothing you use. A workload that fits in Standard’s ceilings, needs no availability group, and uses no Enterprise feature is paying for an engine whose distinguishing capabilities are switched off. PostgreSQL covers that ground with no per-core cost, and its concurrency model is the RCSI behaviour by default rather than an option you must enable.

When you need more than one writable node. Neither an FCI nor an availability group gives you multi-primary writes. Secondaries are readable at best. If the requirement is write scale-out, the answer is sharding at the application layer or a different class of system entirely.

When you need automatic failover without a cluster. AGs on Windows require a WSFC; on Linux they require Pacemaker. A read-scale availability group has no cluster manager and therefore no automatic failover at all. There is no self-contained failover mechanism inside the database.

Embedded, below Express’s ceilings. Express with LocalDB is a reasonable developer convenience, but a 1,410 MB buffer pool and four cores make it a poor production choice for anything with a real working set. SQLite or PostgreSQL are usually better answers for embedded and small-server cases.