Monitoring · Performance · SQL Server

Capacity Planning for SQL Server: From Dashboards to Decision Time

2 min read

A dashboard can show that CPU is 70 percent today. Capacity planning must answer a harder question: under expected growth and seasonality, when will the platform leave its safe operating envelope, and which action has enough lead time?

[company_article_image src=”articles/capacity-signals.svg” alt=”SQL Server workload trend approaching a safe operating limit and decision horizon” caption=”Capacity planning converts telemetry into the time available to tune, scale or change the workload.”]

Define service demand first

Infrastructure usage without workload context is incomplete. Track transactions, batch requests, active users, data ingestion, database size and business cycles. A CPU increase may be healthy growth; unchanged CPU with falling throughput may be degradation.

Use distributions, not isolated averages

Daily averages hide peak windows. Capture percentiles, maximum sustained periods and concurrency alongside averages. Separate business hours, batch windows, month-end processing and maintenance so unlike workloads are not blended into one baseline.

Measure the full resource path

  • CPU: utilization, runnable queue, compilation and query efficiency.
  • Memory: grants, grant waits, cache pressure and operating-system headroom.
  • Data I/O: latency, IOPS, throughput and query logical reads.
  • Transaction log: generation rate, flush latency, backup throughput and AG send/redo queues.
  • Storage: used space, autogrowth, backup footprint, TempDB peak and free-space horizon.

Calculate a decision horizon

Fit growth against a defined safe limit rather than against absolute exhaustion. Preserve room for failure modes, maintenance, failover and forecast error. Use more than one forecast scenario when demand is uncertain.

SELECT DB_NAME(database_id) AS database_name,
       type_desc,
       SUM(size) * 8.0 / 1024 AS allocated_mb,
       SUM(FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS used_mb
FROM sys.master_files
WHERE database_id > 4
GROUP BY database_id, type_desc;

This query is a point-in-time allocation view, not a forecast. Store consistent snapshots in a monitoring repository and evaluate rate of change over representative periods.

Choose the response in the right order

  1. Remove avoidable demand: inefficient queries, redundant indexes and uncontrolled retention.
  2. Reduce peaks through scheduling, batching or workload isolation.
  3. Validate configuration and platform limits.
  4. Scale resources or redesign before the procurement and change window closes.

Attach capacity to ownership

Every forecast needs an owner, safe limit, review cadence and action date. The useful output is not “disk grows 4 percent per month”; it is “archive or expand by this date, with this rollback and validation plan.”

Related Articles