Size for memory, not for cores: SQL Server cost and performance on AWS

Most instance-selection advice starts from compute and treats memory as a follow-on. For SQL Server that order is backwards. SQL Server is licensed per core, so the number of cores you run is the number of cores you pay Microsoft for, every month, regardless of how busy they are. Memory carries no such charge. That asymmetry should drive the whole sizing exercise, and it points to a conclusion that surprises people the first time they hear it: for most SQL Server workloads, you want the highest-memory, lowest-core instance that still meets your compute need.

The counterintuitive rule

SQL Server core licensing has two properties that matter here. First, it is charged on the physical cores you run, with a minimum of four cores per instance, so very small instances buy you no licence saving below that floor. Second, the licence is by far the largest line item in the total cost of running SQL Server in the cloud, often dwarfing the compute and storage underneath it.

On premises you could not do much about this. The server had the cores it had, and the ratio of cores to memory was whatever you bought. In the cloud that ratio is a menu choice. You can pick an instance with eight cores and modest memory, or one with eight cores and several times the memory, for a similar licence cost, because the licence follows the cores, not the RAM. So the rule writes itself: choose fewer, higher-memory cores. Every core you avoid is a recurring licence cost you never pay, and the memory you gain in its place is free of that charge.

Choose memory-optimised instances

This is where instance families stop being interchangeable. The right place to start for SQL Server is the memory-optimised families, which are built to deliver high RAM per core rather than high core counts. As illustrative examples, the R family (such as R6i or R5) offers a generous memory-to-core ratio for general database use, and the X family (such as X2iedn or X2idn) pushes that ratio much further again for memory-hungry workloads. The exact members shift over time, so treat the names as examples and check the current generation, but the principle is stable: these families let you license fewer cores while still giving SQL Server the memory it needs.

The instances to be wary of are the general-purpose families. They are balanced for a broad range of applications, which means they carry more cores per unit of memory than a database wants. For a stateless web tier that is fine. For SQL Server it means you are licensing cores you bought to reach a memory target you could have reached with fewer cores on a memory-optimised instance. The compute-optimised families are worse again on this axis: lots of cores, little memory, exactly the wrong shape.

The buffer pool is the lever

The reason this works so well is that SQL Server performance is, to a first approximation, a memory story. SQL Server keeps the data pages it is working with in the buffer pool, an in-memory cache of the working set. When the pages a query needs are already in the buffer pool, the query is served from RAM. When they are not, SQL Server has to fetch them from storage, and a read from disk is orders of magnitude slower than a read from memory.

So the single most effective thing you can do for SQL Server performance is keep the working set resident in memory. More RAM means a larger buffer pool, a higher cache hit rate, and fewer trips to storage. This is why memory-optimised instances pull double duty: the same RAM that lets you license fewer cores is the RAM that holds your working set. The licensing win and the performance win come from the same decision. You rarely get to optimise two things with one lever; here you do.

Storage: provision to the measured workload

Memory carries the working set, but storage still has to serve the cache misses, the writes, and the transaction log, and getting it wrong shows up as latency the buffer pool cannot hide. A few defaults hold for most SQL Server deployments:

• gp3 for most workloads. Its key advantage is that you provision IOPS and throughput independently of volume size, so you are not forced to over-buy capacity just to reach a performance target, as you were with older volume types.
• io2 Block Express where you need high, consistent IOPS with predictable low latency, typically the busiest production OLTP databases. You pay more for provisioned IOPS, so reserve it for the volumes that genuinely need it.
• On EC2, place tempdb (and sometimes the transaction log) on local NVMe instance store. Instance store is ephemeral, which suits tempdb exactly because it is rebuilt on restart anyway, and the local NVMe gives very low-latency IO for the heavy scratch activity tempdb attracts.

The discipline that ties these together: size IOPS and throughput to the workload you have measured, not to a guess. Over-provisioning storage is quieter than over-provisioning cores because it carries no licence penalty, but it is still waste. Measure the real read and write rates, then provision to them with headroom, rather than reaching for the largest numbers out of caution.

The x86-only reality

There is one constraint that quietly removes an option many teams now reach for by default. SQL Server runs on x86-64 only. It does not run on AWS Graviton, the Arm-based processors that deliver strong price/performance for many other engines.

This matters most in mixed estates. If your platform team has standardised on Graviton for its other databases and built that into reference architectures and cost models, SQL Server is the exception that must be carved out by hand. The memory-optimised families discussed above all have x86-64 members, so the strategy holds; you simply choose from the x86-64 side of the menu and ignore the Graviton variants entirely.

Right-size before you migrate

All of this points at the migration itself as the moment of leverage. The most common and most expensive mistake is to carry the on-premises core count straight across: the old server had thirty-two cores, so the new instance gets thirty-two cores. That number was set years ago, often sized for a peak that no longer happens, on hardware that has since been outgrown or outlived. Replicating it into the cloud replicates a licence bill you may have been overpaying for a long time.

Migration is the rare chance to reset that. Before choosing an instance, measure the real workload: actual CPU utilisation across a representative period, the memory footprint the buffer pool genuinely needs, and the IOPS and throughput the storage actually serves. Those three measurements, not the legacy spec sheet, should drive instance selection. In practice teams that measure honestly almost always find they can shed cores they have been licensing for years, which turns the migration from a like-for-like lift into a cost reset.

Right-sizing is also safer in the cloud than it was on premises, because the decision is not permanent. If you trim too far, you resize. That reversibility is exactly why caution should not default to over-provisioning: you can start lean, watch the real workload, and adjust, rather than buying for a worst case that may never arrive.

Bring it together

Pulled into one move, the method is simple to state and worth stating plainly. Choose fewer, higher-memory cores on a memory-optimised, x86-64 instance; provision storage to the measured workload; and size from real metrics rather than the legacy core count. Each part reinforces the others. Fewer cores lower the licence bill. More memory per core both enables those fewer cores and feeds the buffer pool that drives performance. Right-sizing makes sure you are paying for the workload you have rather than the one you had years ago.

The reason this is worth getting right is that for SQL Server the licence usually outweighs the infrastructure beneath it. An instance choice that looks like a small technical detail is in fact the largest recurring cost lever you have, and it is one of the few places where the cheaper option is also the faster one. Size for memory, not for cores, and you get both.