sql server hub / sizing guide
SQL Server sizing is not just checking database size. The goal is to find what is tight now, what will be tight next, and what the server needs before the next big change.
Use this guide to collect read-only SQL Server output for instance shape, database files, CPU pressure, memory grants, storage latency, tempdb pressure, Agent jobs, backups, growth, and platform constraints. If high availability is part of the same decision, keep the SQL Server failover guide nearby.
workload
headroom
decision
In this guide
Use this when
Size from workload output, not database size alone.
Separate capacity, latency, concurrency, and growth before choosing hardware.
Use read-only checks first; do not change settings to prove a sizing theory.
Document the current bottleneck, headroom, risky inputs, and next review date.
1 / Sizing target
A sizing answer should say more than how large the databases are. It should explain which resource is tight now, which resource will become tight next, and how much headroom the setup needs for peak work, maintenance, backups, failover behavior, and growth.
The useful output is a small decision package: CPU shape, memory fit, storage capacity and latency, tempdb needs, concurrency risk, operational load, growth allowance, platform constraints, and the assumptions that still need proving.
| Input | What it proves | What it does not prove |
|---|---|---|
| Database size | How much space exists now. | Whether CPU, memory, latency, or concurrency will hold. |
| CPU count | The configured compute shape. | Whether schedulers are under pressure at peak. |
| Memory setting | The instance memory ceiling. | Whether the workload fits in memory well enough. |
| Storage capacity | How much room the files have. | Whether writes, reads, backups, and tempdb are fast enough. |
| Average utilization | The normal day shape. | Whether month-end, batch overlap, or reporting peaks are safe. |
| Growth rate | How fast the footprint changes. | Whether the workload, maintenance, and backup windows will still fit. |
2 / Calculations
People usually want a number: how much disk, memory, CPU, tempdb, backup space, or throughput. The exact number depends on the workload, but the calculation should be visible. These examples show the shape of the math before the DMV checks refine it.
| Sizing question | Example input | Example calculation |
|---|---|---|
| Data file runway | Current data size 180 GB, growth 8 GB/month, planning window 18 months, headroom 25%. | (180 + (8 x 18)) x 1.25 = 405 GB data capacity target. |
| Backup storage | Full backup 110 GB compressed, keep 14 daily fulls, add 30% for diffs, logs, and job overlap. | (110 x 14) x 1.30 = 2002 GB backup target, before offsite retention. |
| Memory starting point | VM has 64 GB RAM and only SQL Server runs there. Leave 8 GB for Windows, backup tools, AV, drivers, and headroom. | 64 - 8 = 56 GB max server memory starting point, then validate with grants, reads, and OS pressure. |
| Tempdb working space | Observed tempdb peak 70 GB during ETL, normal peak 25 GB, add 50% incident headroom. | 70 x 1.5 = 105 GB tempdb space target, plus predictable growth and storage latency checks. |
| Maintenance throughput | CHECKDB, backup, or migration copy must process 600 GB inside a 4-hour window. | 600 / 4 = 150 GB/hour, about 42 MB/sec sustained before concurrency, latency, and other jobs. |
| CPU pressure | 8 visible schedulers and 4 runnable tasks sustained during the business peak. | Runnable work equals 50% of scheduler count. That points to CPU pressure unless one bad query or job is causing it. |
Best practice is not one magic server shape. The useful practice is knowing which part of the system matters for this workload, then sizing that part from measured peaks and realistic growth.
| Area | Why it matters | Practical sizing rule |
|---|---|---|
| Memory | Starting point: leave 4 GB for Windows on small servers, 8 GB on 32-64 GB servers, 10-15% on larger dedicated servers. | Example: 64 GB RAM - 8 GB reserve = 56 GB max server memory. For 256 GB RAM, start around 220-230 GB, then validate. |
| Storage latency | Useful targets: data reads often need < 20 ms, log writes often need < 5 ms, tempdb should usually stay < 10-15 ms under load. | If a data file averages 45 ms reads or a log file averages 25 ms writes during peak, storage is part of the sizing problem. |
| Tempdb | Start with observed peak tempdb usage x 1.5. Use 4-8 equally sized data files for many busy OLTP systems; do not jump past 8 without evidence. | Example: peak tempdb use 70 GB x 1.5 = 105 GB. With 8 data files, start around 13 GB each plus a separate log file. |
| CPU | Watch sustained runnable_tasks_count. More than 1 runnable task per 4 schedulers during peak is worth investigating; 1 per 2 schedulers is serious. | Example: 8 schedulers with 4 runnable tasks means 50% scheduler pressure. Do not choose fewer cores for the next platform. |
| Transaction log | Size the log for the largest normal write burst plus 25-50% headroom. For full recovery, log backup cadence must fit RPO. | Example: nightly load writes 35 GB to log. Start log around 50 GB, not 5 GB with repeated autogrowth. |
| Backups and restore | Backup storage = compressed full size x retained copies, plus diff/log space, plus 20-30% working headroom. | Example: 110 GB full x 14 days x 1.3 = about 2 TB. Restore speed for 600 GB in 4 hours needs about 42 MB/sec sustained. |
| Peak concurrency | Size from the busiest real 15-60 minute window, not the daily average. Track active requests, waits, CPU, and file latency in that window. | Example: average CPU 25% is irrelevant if month-end holds 85-95% CPU for 45 minutes with runnable tasks and slow requests. |
| Growth runway | Capacity target = current size + monthly growth x months, then add 20-30% headroom. Review again when 70-80% of target is used. | Example: 180 GB + 8 GB/month x 18 months = 324 GB. Add 25% headroom = 405 GB target. |
Use this for a reviewable starting point. Reserve memory outside the instance first, then compare the result with operating-system pressure, SQL Server memory clerks, other instances, and the actual NUMA layout.
The formulas follow Microsoft's starting guidance for server memory and MAXDOP. They are starting values to test, not automatic production settings.
Host and instances
max server memory, then enter the combined maximum assigned to the other SQL Server instances.Calculated baseline
Available to SQL Server
104.0 GB
This instance envelope
72.0 GB
Average other-instance max
32.0 GB
Across 1 other instance
Max server memory
55,296 MB
Processors per NUMA node
16
MAXDOP starting value
8
Review the current settings, workload peaks, and every instance budget before running this SQL.
Generated SQL Server configuration
T-SQL · 18 lines
3 / Context
Start with instance identity, version, edition, uptime, CPU count, scheduler count, physical memory, and virtualization state. Many DMV readings reset when SQL Server restarts, so a five-hour uptime and a five-month uptime should not be interpreted the same way.
Reads version, edition, uptime, CPU, scheduler, memory, and virtualization context.
Instance shape and uptime
T-SQL · 17 lines
uptime_hours tells you how much history the DMV counters can represent. scheduler_count is the SQL Server scheduler shape visible to the instance. virtual_machine_type_desc helps separate assigned hardware from virtualized resource behavior. Edition can affect capacity limits and feature choices.
Reads common capacity-related instance settings before judging the workload.
CPU and memory configuration
T-SQL · 13 lines
value and value_in_use can differ when a setting is configured but not active yet. max server memory should be read with OS and workload context, not as a standalone rule. Parallelism settings affect CPU pressure interpretation. Do not change these during collection just to see what happens.
4 / Files and growth
File size is still important. It affects storage, backups, restores, CHECKDB, migration timing, and growth runway. But file size is an input. It does not prove that CPU, memory, tempdb, latency, or concurrency are safe.
Summarizes user database size, recovery model, compatibility level, and Query Store state.
Database size and recovery context
T-SQL · 21 lines
Large databases affect backup, restore, CHECKDB, migration, and storage planning. Recovery model changes log behavior and backup requirements. Compatibility level can matter during upgrade or workload-change sizing. Query Store state tells you whether richer workload history may be available.
Lists data, log, and tempdb file paths, sizes, growth settings, and max size.
File layout and growth settings
T-SQL · 21 lines
Percent growth can create unpredictable growth events as files get larger. Physical paths show whether files share storage that should be treated separately. Unlimited max size still depends on real disk capacity and alerting. Growth settings explain some incidents but do not replace workload sizing.
Reads current log usage and reuse wait reason for each user database.
Transaction log pressure
T-SQL · 11 lines
log_reuse_wait_desc can point to backup, replication, availability, or transaction issues. High used_log_space_percent is a point-in-time signal, not a full trend. Large log files affect recovery, backup, restore, and storage planning. Requires SQL Server versions that support sys.dm_db_log_stats.
5 / CPU
CPU sizing should not be based only on average utilization. Look for runnable tasks, signal waits, query shapes, parallelism behavior, and peak-window pressure. Adding cores may help, but it can also hide poor query shape or make licensing more expensive without fixing the cause.
Shows runnable tasks, worker counts, queued work, and scheduler load for visible online schedulers.
Scheduler pressure snapshot
T-SQL · 11 lines
runnable_tasks_count points to work waiting for CPU at the sample moment. work_queue_count can point to queued scheduler work. One sample is not a trend; capture during known peak windows. CPU pressure should be compared with waits, top queries, and job timing.
Shows accumulated wait patterns since the last SQL Server restart, excluding common idle waits.
Wait stats snapshot
T-SQL · 23 lines
High signal waits can support a CPU-pressure story. Wait stats are cumulative since startup; uptime changes the meaning. Do not treat one wait type as a diagnosis by itself. Capture before and after peak periods when possible.
6 / Memory
Memory fit is not proven by one counter. Review the configured memory ceiling, memory clerks, grants, waiting grants, cache behavior, file reads, and the queries asking for large grants. A workload can have enough total RAM and still suffer because one report, ETL step, or bad estimate asks for too much at the wrong time.
Shows where SQL Server memory is currently allocated by clerk.
Top memory clerks
T-SQL · 8 lines
Memory clerks show allocation shape, not a complete health verdict. Large clerks should be interpreted with workload, uptime, and configuration. Compare with storage reads and grants before deciding memory is the bottleneck. Some memory use is normal and beneficial cache behavior.
Summarizes active query memory grants and shows the largest current grant requests.
Memory grants and waiting grants
T-SQL · 26 lines
waiting_grants can point to RESOURCE_SEMAPHORE-style pressure. Large grants often need query-plan and estimate review, not only more RAM. Run this during slow periods or known reporting windows. No rows in the detail query means no active grants at that moment.
Database-scoped Query Store query for recent CPU, logical reads, and memory-heavy query patterns.
Query Store top resource queries
T-SQL · 19 lines
Run this inside a user database with Query Store enabled. The time window is the last seven days in Query Store runtime stats. High CPU, reads, or memory signals should lead to plan review. If Query Store is off, use monitoring data or cached-plan analysis instead.
7 / Storage
Storage sizing has at least three separate questions: enough room, enough throughput, and acceptable latency. A server can have free disk space and still be undersized because writes stall, reads wait, backups overrun, or tempdb shares a path with other busy files.
Uses sys.dm_io_virtual_file_stats to show read, write, and combined latency per database file.
File I/O latency
T-SQL · 17 lines
Latency is cumulative since database open or instance start. Separate data, log, and tempdb behavior before blaming one storage tier. High latency during maintenance may mean a scheduling problem rather than constant undersizing. Compare with job timing, backup timing, and wait stats.
| Symptom area | What it can look like | Sizing response |
|---|---|---|
| CPU | High runnable tasks, SOS scheduler waits, plan regressions, parallelism pressure. | Check workload shape and top queries before adding cores. |
| Memory | Grants pending, RESOURCE_SEMAPHORE, heavy clerks, repeated reads from storage. | Check memory settings, grants, cache fit, and query shape together. |
| Storage | High file latency, backup windows drifting, CHECKDB or maintenance overruns. | Separate data, log, tempdb, and backup paths where possible. |
| Tempdb | Version store growth, internal object growth, PAGELATCH waits, spill-heavy requests. | Use the tempdb guide before changing file count or moving storage. |
| Concurrency | Blocking, worker pressure, peak-only slowdowns, job/user overlap. | Review scheduling, transaction scope, and workload overlap before hardware. |
8 / Tempdb and concurrency
Tempdb pressure is capacity, workload, and concurrency at the same time. It can come from user objects, internal worktables, version store, spills, row-versioning isolation, maintenance, reporting, or overlapping jobs. Use the checks here to decide whether tempdb is part of the sizing answer or a symptom of query and workload shape.
Lists tempdb file size, growth setting, max size, and storage path.
Tempdb file layout
T-SQL · 18 lines
File layout matters, but file count should not be changed from a generic rule alone. Growth settings should be predictable enough not to join the incident. Physical paths show whether tempdb has the intended storage. Some tempdb changes require restart and rollback planning.
Splits tempdb space into free space, user objects, internal objects, version store, and mixed extents.
Current tempdb space usage
T-SQL · 15 lines
user_objects_mb points toward temp tables and explicit tempdb objects. internal_objects_mb often points toward worktables, sorts, hashes, and spills. version_store_mb matters when row-versioning or long transactions are involved. Free space alone does not prove tempdb health.
Shows current requests with waits, blockers, CPU, reads, writes, and running statement text.
Active requests, waits, and blocking
T-SQL · 23 lines
Use this during the slow window, not hours later. blocking_session_id connects capacity symptoms to lock chains. Logical reads and writes help connect query shape to capacity pressure. For deeper lock chains, continue with the blocking guide.
9 / Operational load
User workload is not the whole workload. Backups, CHECKDB, index maintenance, ETL, reporting, Availability Group synchronization, failover behavior, and monitoring can all change the sizing answer. If these jobs are ignored, the server can look correctly sized until the first real maintenance window.
Shows recent job step duration and status so operational workload can be included in sizing.
Recent SQL Agent job timing
T-SQL · 16 lines
Long-running jobs can explain peak pressure and maintenance windows. Failed or retried steps can hide real capacity or scheduling problems. Compare run_start_time with monitoring, waits, and file latency. The query reads msdb job history only.
Shows recent backup duration, size, compressed size, and destination.
Backup duration and size
T-SQL · 14 lines
Backup duration affects maintenance windows and storage throughput needs. Large compressed_backup_size changes restore and offload planning. Physical device paths can reveal shared storage or network bottlenecks. A backup that exists is not the same as a tested restore.
HA note
Availability Groups, failover clustering, log shipping, replication, and recovery targets can all change CPU, storage, network, licensing, and maintenance requirements. If HA design is still open, use the SQL Server failover guide for the technical design and the passive node licensing guide for the second-node licensing boundary.
10 / Platform constraints
Growth planning should name which thing is growing: data footprint, transaction volume, reporting load, user concurrency, retention, job duration, backup size, or HA complexity. Those do not grow at the same rate, and they do not put pressure on the same resource.
Virtualization and licensing should be treated as design constraints, not footnotes. Assigned CPU is not always dependable CPU. A smaller licensing footprint can be sensible, but not if it leaves the workload permanently constrained. Treat licensing notes here as technical planning inputs, not legal advice.
| Platform shape | Useful when | Sizing caution |
|---|---|---|
| Physical | Cleaner ownership of CPU, memory, and storage behavior. | Can still be wrong if storage, growth, HA, or licensing are guessed. |
| Virtual | Flexible placement and easier resizing in some shops. | Assigned resources are not the same as dependable resources under host load. |
| Cloud or managed | Elastic options and managed platform features. | Sizing depends on tier limits, storage latency model, IO caps, and cost behavior. |
| Consolidated estate | Can reduce operational sprawl. | One busy workload can distort the sizing story for every neighbor. |
11 / Decision output
The final output should be short enough to use. List the current bottleneck, the headroom needed, the checks that support the conclusion, the weak inputs, the near-term change, and when the sizing should be reviewed again.
| Output item | What to write down |
|---|---|
| Current bottleneck | Name the main limiting resource and the checks that support it. |
| Required headroom | State the buffer for peak workload, maintenance, backups, and growth. |
| Risky inputs | List missing baselines, short uptime, untested failover, weak job history, or unknown growth. |
| Near-term change | Recommend the smallest useful platform, workload, or operational change. |
| Next review date | Set a review trigger based on time, growth, migration, upgrade, or repeated incidents. |
What not to change blindly
Do not buy more CPU before checking scheduler pressure, top queries, and licensing impact.
Do not raise memory without checking grants, clerks, OS needs, and query shape.
Do not move storage from a single latency sample without checking job and backup timing.
Do not change tempdb file count or paths without restart and rollback planning.
Do not size from a quiet week when month-end or batch peaks are the real workload.
When review helps
Ask for a sizing review before procurement, consolidation, migration target selection, HA design, a large growth period, or a repeated performance problem that might be workload shape rather than hardware.
Send instance output, file inventory, waits, file latency, tempdb output, job timing, backup history, monitoring data, and the business event that is forcing the capacity decision.
Next step
If the capacity question needs a workload-based answer instead of a hardware guess, use SQL Server consulting or the SQL Server health audit page.
Next useful reads: the SQL Server monitoring guide for baseline capture, the SQL Server indexing guide for query-shape pressure, the SQL Server tempdb guide for tempdb pressure, and the SQL Server performance review page when slow queries and capacity are mixed together.