Buying more cores without a workload baseline is guesswork. Sizing a new SQL Server starts with measured demand. The useful inputs are transactions, query mix, data size, peak concurrency, growth, and recovery expectations. Hardware numbers become meaningful after those inputs have units and a time horizon.
Define the Service First When Sizing a New SQL Server
List databases, applications, important queries, batch jobs, and their response or completion targets. Record peak periods and how many users act at once. A reporting system with large scans needs different resources from a small transactional system with rapid commits. Availability and recovery targets also influence storage, replicas, and backup capacity.
I ask what must still work during month end, not just on a normal Tuesday. Sizing a new SQL Server while ignoring the busiest business hour will produce a machine that looks sufficient until the first close. Write the expected workload in measurable terms before discussing cores. What workload must the new server handle at month end?
Use the Existing Baseline for Sizing a New SQL Server
If replacing an existing server, capture CPU utilization, runnable tasks, memory use, page reads, log throughput, file latency, and query response times. Gather a full business cycle where possible. The old server’s specification is not the baseline. Its measured workload is. A badly tuned query can make a new server appear undersized if it is carried over unchanged.
This query provides one piece of the current instance’s memory and CPU view. Pair it with Windows performance counters and query history. Avoid taking a single quiet snapshot as a sizing answer.
SELECT cpu_count, scheduler_count,
physical_memory_kb / 1024.0 AS physical_memory_mb,
sqlserver_start_time
FROM sys.dm_os_sys_info;
SELECT total_physical_memory_kb / 1024.0 AS host_memory_mb,
available_physical_memory_kb / 1024.0
AS available_memory_mb
FROM sys.dm_os_sys_memory;Estimate CPU From Demand
Look at sustained and peak CPU, query CPU time, concurrency, and parallelism. A low average with short saturated peaks needs headroom for those peaks. More cores can increase licensing cost, so compare the gain from query tuning and indexing before adding them. CPU speed per core also matters for serial or latency-sensitive work.
For a new application with no production baseline, build a representative load test with realistic data and user actions. Extrapolation has uncertainty. State it. I present a range and the conditions behind it rather than an exact core count that pretends the unknown workload has already been measured.
Estimate Memory From Working Set
Database size is not the same as memory requirement. The working set is the data and index pages repeatedly needed during the important workload, plus plan cache and execution grants. A multi-terabyte archive can run well with modest memory if queries touch a narrow active set. A smaller database can need more memory if concurrent queries demand large grants.
Observe physical reads, buffer behavior, memory grants, and paging under load. Reserve memory for Windows, backup agents, and other processes before setting max server memory. Check edition limits for the installed version. I avoid the rule that RAM must equal database size. It confuses storage volume with active demand.
Size Data and Log Separately
Forecast current data, index space, expected annual growth, retention, and maintenance headroom. The log needs capacity for peak transaction bursts, large loads, index operations, and backup intervals. Recovery model and high availability design affect that requirement. tempdb needs room for spills, version store, and temporary work, not a copy of total database size.
This file inventory shows current allocated and used space as a starting point for an existing database. Add growth from business projections and actual history. The difference between allocated and used space is headroom inside a file, not free disk capacity.
SELECT name, type_desc,
size * 8.0 / 1024 AS allocated_mb,
FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024
AS used_mb,
physical_name
FROM sys.database_files;Include Storage Performance
Capacity in terabytes does not tell you whether the storage can meet read and write latency. Estimate random read demand, log write rate, bulk throughput, and backup or restore bandwidth. Test the proposed storage under a workload resembling SQL Server, including peak concurrency. Shared arrays and virtual disks can have noisy neighbors.
I ask for latency targets under load and for performance limits in the service agreement. A large fast-looking volume that stalls during a neighbor’s backup is not a good database path. Leave room for growth without assuming latency stays constant as the platform fills.
Plan Recovery and Growth
Backup frequency, restore time target, availability replicas, and retention add capacity and network needs. A database that grows rapidly can outpace backup windows and restore targets before it runs out of disk. Include the size of backups, log chain, and test restores in the sizing plan. If a secondary serves reports, include that workload on its resources.
Forecast at least a practical planning horizon with low, expected, and high growth scenarios. Set review triggers before the high case arrives. I prefer a plan that can be expanded predictably to a large initial purchase based on a speculative five-year peak.
Check Edition and Licensing Constraints
SQL Server editions have compute and memory limits that vary by version. Licensing can materially affect the cost of extra cores or replicas. Verify current official terms and the organization’s agreement with the licensing team before finalizing the architecture. A server with more cores than the instance can use is wasted capacity, even if the operating system sees them.
Performance sizing and license sizing belong in the same conversation. A fewer-core, faster-per-core configuration can be attractive for some workloads, but test it. I avoid turning a license constraint into a reason to underprovision a critical service. The target is a supported, affordable configuration that meets measured demand.
Validate Sizing a New SQL Server With a Pilot
Run representative transactions, reports, loads, and backups on the proposed class of hardware or VM. Compare response time, CPU, memory pressure, file latency, and throughput at expected peak and a growth scenario. Capture failures and tail latency. A vendor synthetic benchmark can supplement this, but it cannot substitute for your query and data distribution.
Sizing a new SQL Server is a forecast with evidence and stated uncertainty. Revisit it after launch using real measurements. The server should have enough headroom to absorb ordinary growth and known peaks, while the plan should show exactly when to add capacity or tune the workload.
Related reading on this blog: How to Prevent Common SQL Server Performance Problems Efficiently With Smart Capacity Planning and Collecting Server Facts Into One Table Every Night.
Sizing a new SQL Server is not copying a hardware chart, it is matching resources to measured work and growth.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
