sql server hub / sizing guide

SQL Serversizing 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

Guide

Guide~13 min readUpdated 23 May 2026

LinkedIn X Email

In this guide

1. What SQL Server sizing must answer
2. SQL Server sizing calculations
3. SQL Server capacity planning inputs
4. SQL Server database size and growth
5. SQL Server CPU sizing
6. SQL Server memory sizing
7. SQL Server storage sizing
8. SQL Server tempdb sizing
9. SQL Server sizing for jobs and backups
10. SQL Server capacity planning constraints
11. SQL Server sizing decision

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

What SQL Server sizing must answer

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

SQL Server sizing examples with numbers

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.

Where SQL Server sizing matters most

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.

3 / Context

SQL Server capacity planning inputs

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.

Instance shape and uptime

Reads version, edition, uptime, CPU, scheduler, memory, and virtualization context.

SELECT
    @@SERVERNAME AS server_name,
    SERVERPROPERTY(N'MachineName') AS machine_name,
    SERVERPROPERTY(N'InstanceName') AS instance_name,
    SERVERPROPERTY(N'Edition') AS edition,
    SERVERPROPERTY(N'ProductVersion') AS product_version,
    SERVERPROPERTY(N'ProductLevel') AS product_level,
    SERVERPROPERTY(N'ProductUpdateLevel') AS product_update_level,
    SERVERPROPERTY(N'EngineEdition') AS engine_edition,
    si.sqlserver_start_time,
    DATEDIFF(hour, si.sqlserver_start_time, SYSDATETIME()) AS uptime_hours,
    si.cpu_count,
    si.scheduler_count,
    si.hyperthread_ratio,
    si.physical_memory_kb / 1024 AS physical_memory_mb,
    si.virtual_machine_type_desc
FROM sys.dm_os_sys_info AS si;

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.

CPU and memory configuration

Reads common capacity-related instance settings before judging the workload.

SELECT
    name,
    value AS configured_value,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN (
    N'max server memory (MB)',
    N'min server memory (MB)',
    N'max degree of parallelism',
    N'cost threshold for parallelism'
)
ORDER BY name;

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

SQL Server database size and growth planning

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.

Database size and recovery context

Summarizes user database size, recovery model, compatibility level, and Query Store state.

SELECT
    d.name AS database_name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_query_store_on,
    COUNT(mf.file_id) AS file_count,
    CONVERT(decimal(18,2), SUM(mf.size) / 128.0) AS total_size_mb,
    CONVERT(decimal(18,2), SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) / 128.0) AS data_size_mb,
    CONVERT(decimal(18,2), SUM(CASE WHEN mf.type = 1 THEN mf.size ELSE 0 END) / 128.0) AS log_size_mb
FROM sys.databases AS d
JOIN sys.master_files AS mf
    ON mf.database_id = d.database_id
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
GROUP BY
    d.name,
    d.state_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.is_query_store_on
ORDER BY total_size_mb DESC;

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.

File layout and growth settings

Lists data, log, and tempdb file paths, sizes, growth settings, and max size.

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.file_id,
    mf.type_desc,
    mf.name AS logical_name,
    mf.physical_name,
    CONVERT(decimal(18,2), mf.size / 128.0) AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1
            THEN CONCAT(mf.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), mf.growth / 128.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN mf.max_size = -1 THEN N'Unlimited'
        WHEN mf.max_size = 0 THEN N'No growth'
        ELSE CONCAT(CONVERT(decimal(18,2), mf.max_size / 128.0), N' MB')
    END AS max_size,
    mf.is_percent_growth
FROM sys.master_files AS mf
WHERE DB_NAME(mf.database_id) NOT IN (N'master', N'model', N'msdb')
ORDER BY DB_NAME(mf.database_id), mf.type_desc, mf.file_id;

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.

Transaction log pressure

Reads current log usage and reuse wait reason for each user database.

SELECT
    d.name AS database_name,
    d.recovery_model_desc,
    d.log_reuse_wait_desc,
    CONVERT(decimal(18,2), ls.total_log_size_mb) AS total_log_size_mb,
    CONVERT(decimal(18,2), ls.used_log_space_mb) AS used_log_space_mb,
    CONVERT(decimal(9,2), ls.used_log_space_in_percent) AS used_log_space_percent
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_stats(d.database_id) AS ls
WHERE d.name NOT IN (N'master', N'model', N'msdb', N'tempdb')
ORDER BY ls.used_log_space_in_percent DESC;

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

SQL Server CPU sizing

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.

Scheduler pressure snapshot

Shows runnable tasks, worker counts, queued work, and scheduler load for visible online schedulers.

SELECT
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count,
    load_factor
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC, work_queue_count DESC, scheduler_id;

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.

Wait stats snapshot

Shows accumulated wait patterns since the last SQL Server restart, excluding common idle waits.

SELECT TOP (40)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    CONVERT(decimal(9,2), wait_time_ms / 1000.0) AS wait_time_seconds,
    CONVERT(decimal(9,2), signal_wait_time_ms / 1000.0) AS signal_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE N'SLEEP%'
  AND wait_type NOT IN (
      N'BROKER_TASK_STOP',
      N'BROKER_TO_FLUSH',
      N'CHECKPOINT_QUEUE',
      N'CLR_AUTO_EVENT',
      N'CLR_MANUAL_EVENT',
      N'LAZYWRITER_SLEEP',
      N'LOGMGR_QUEUE',
      N'REQUEST_FOR_DEADLOCK_SEARCH',
      N'SQLTRACE_BUFFER_FLUSH',
      N'XE_DISPATCHER_WAIT',
      N'XE_TIMER_EVENT'
  )
ORDER BY wait_time_ms DESC;

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

SQL Server memory sizing

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.

Top memory clerks

Shows where SQL Server memory is currently allocated by clerk.

SELECT TOP (30)
    type,
    name,
    CONVERT(decimal(18,2), pages_kb / 1024.0) AS pages_mb,
    CONVERT(decimal(18,2), virtual_memory_committed_kb / 1024.0) AS virtual_committed_mb,
    CONVERT(decimal(18,2), awe_allocated_kb / 1024.0) AS awe_allocated_mb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

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.

Memory grants and waiting grants

Summarizes active query memory grants and shows the largest current grant requests.

SELECT
    SYSDATETIME() AS sample_time,
    COUNT(*) AS active_grants,
    SUM(CASE WHEN mg.grant_time IS NULL THEN 1 ELSE 0 END) AS waiting_grants,
    CONVERT(decimal(18,2), SUM(mg.requested_memory_kb) / 1024.0) AS requested_memory_mb,
    CONVERT(decimal(18,2), SUM(mg.granted_memory_kb) / 1024.0) AS granted_memory_mb,
    CONVERT(decimal(18,2), SUM(mg.used_memory_kb) / 1024.0) AS used_memory_mb
FROM sys.dm_exec_query_memory_grants AS mg;

SELECT TOP (25)
    mg.session_id,
    mg.requested_memory_kb / 1024 AS requested_memory_mb,
    mg.granted_memory_kb / 1024 AS granted_memory_mb,
    mg.used_memory_kb / 1024 AS used_memory_mb,
    mg.wait_time_ms,
    mg.dop,
    SUBSTRING(
        st.text,
        (er.statement_start_offset / 2) + 1,
        ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset) / 2) + 1
    ) AS running_statement
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS er
    ON er.session_id = mg.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
ORDER BY mg.requested_memory_kb DESC;

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.

Query Store top resource queries

Database-scoped Query Store query for recent CPU, logical reads, and memory-heavy query patterns.

SELECT TOP (25)
    DB_NAME() AS database_name,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    CONVERT(decimal(18,2), SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0) AS total_cpu_ms,
    CONVERT(decimal(18,2), SUM(rs.avg_logical_io_reads * rs.count_executions)) AS total_logical_reads,
    CONVERT(decimal(18,2), SUM(rs.avg_query_max_used_memory * rs.count_executions) / 128.0) AS total_memory_pages_mb
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS p
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(day, -7, SYSUTCDATETIME())
GROUP BY qt.query_sql_text
ORDER BY total_cpu_ms DESC;

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

SQL Server storage sizing and I/O latency

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.

File I/O latency

Uses sys.dm_io_virtual_file_stats to show read, write, and combined latency per database file.

SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_name,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    CONVERT(decimal(18,2), vfs.num_of_bytes_read / 1048576.0) AS read_mb,
    CONVERT(decimal(18,2), vfs.num_of_bytes_written / 1048576.0) AS written_mb,
    CONVERT(decimal(18,2), vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)) AS avg_read_latency_ms,
    CONVERT(decimal(18,2), vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)) AS avg_write_latency_ms,
    CONVERT(decimal(18,2), vfs.io_stall / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0)) AS avg_total_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON mf.database_id = vfs.database_id
   AND mf.file_id = vfs.file_id
ORDER BY avg_total_latency_ms DESC;

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

SQL Server tempdb sizing and concurrency pressure

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.

Tempdb file layout

Lists tempdb file size, growth setting, max size, and storage path.

SELECT
    df.file_id,
    df.name AS logical_name,
    df.type_desc,
    df.physical_name,
    CONVERT(decimal(18,2), df.size / 128.0) AS size_mb,
    CASE
        WHEN df.is_percent_growth = 1
            THEN CONCAT(df.growth, N'%')
        ELSE CONCAT(CONVERT(decimal(18,2), df.growth / 128.0), N' MB')
    END AS growth_setting,
    CASE
        WHEN df.max_size = -1 THEN N'Unlimited'
        WHEN df.max_size = 0 THEN N'No growth'
        ELSE CONCAT(CONVERT(decimal(18,2), df.max_size / 128.0), N' MB')
    END AS max_size
FROM tempdb.sys.database_files AS df
ORDER BY df.type_desc, df.file_id;

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.

Current tempdb space usage

Splits tempdb space into free space, user objects, internal objects, version store, and mixed extents.

USE tempdb;

SELECT
    df.file_id,
    df.name AS file_name,
    CONVERT(decimal(18,2), fs.total_page_count / 128.0) AS total_mb,
    CONVERT(decimal(18,2), fs.unallocated_extent_page_count / 128.0) AS free_mb,
    CONVERT(decimal(18,2), fs.user_object_reserved_page_count / 128.0) AS user_objects_mb,
    CONVERT(decimal(18,2), fs.internal_object_reserved_page_count / 128.0) AS internal_objects_mb,
    CONVERT(decimal(18,2), fs.version_store_reserved_page_count / 128.0) AS version_store_mb,
    CONVERT(decimal(18,2), fs.mixed_extent_page_count / 128.0) AS mixed_extents_mb
FROM sys.dm_db_file_space_usage AS fs
JOIN sys.database_files AS df
    ON df.file_id = fs.file_id
ORDER BY df.file_id;

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.

Active requests, waits, and blocking

Shows current requests with waits, blockers, CPU, reads, writes, and running statement text.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    r.reads,
    r.writes,
    r.logical_reads,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1
    ) AS running_statement
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

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

SQL Server sizing for jobs, backups, and HA

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.

Recent SQL Agent job timing

Shows recent job step duration and status so operational workload can be included in sizing.

SELECT TOP (120)
    j.name AS job_name,
    h.step_id,
    h.step_name,
    h.run_status,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_start_time,
    ((h.run_duration / 10000) * 3600)
      + (((h.run_duration % 10000) / 100) * 60)
      + (h.run_duration % 100) AS duration_seconds,
    h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = h.job_id
WHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -14, GETDATE()), 112))
  AND h.step_id > 0
ORDER BY run_start_time DESC, duration_seconds DESC;

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.

Backup duration and size

Shows recent backup duration, size, compressed size, and destination.

SELECT TOP (100)
    bs.database_name,
    bs.type AS backup_type,
    bs.backup_start_date,
    bs.backup_finish_date,
    DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS duration_seconds,
    CONVERT(decimal(18,2), bs.backup_size / 1048576.0) AS backup_size_mb,
    CONVERT(decimal(18,2), bs.compressed_backup_size / 1048576.0) AS compressed_backup_size_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.backup_start_date >= DATEADD(day, -30, GETDATE())
ORDER BY bs.backup_start_date DESC;

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, size the platform with the SQL Server failover guide and the SQL Server recovery guide in view.

10 / Platform constraints

SQL Server capacity planning for growth and virtualization

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

Turn checks into a SQL Server sizing decision

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.

Request sizing review Health audit

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.