Three quiet servers can still overwhelm one shared host at month end. Right-sizing SQL Server starts with measurements across the business cycle, not a screenshot from lunchtime. Collect CPU, memory, and I/O together so the proposed host has room for the peaks that coincide.
Define the Business Cycle for Right-Sizing SQL Server
List normal work, nightly loads, backups, integrity checks, month-end reports, and seasonal peaks. Choose a collection interval and a retention window that cover those events. Record missing samples and service restarts. A silent collector is not a quiet workload. The sample interval also determines which brief spikes you can detect.
I ask the application owner when the expensive work happens before collecting numbers. Which workloads will peak together after consolidation? Adding separate average values does not answer that. Keep synchronized timestamps and business-event labels so you can compare simultaneous demand rather than combining unrelated maxima blindly.
Read Recent CPU From Scheduler Monitoring
The scheduler monitor ring buffer includes recent CPU observations on common SQL Server builds. Its diagnostic XML exposes SQL process utilization and system idle percentage. The query converts ring-buffer tick timestamps into approximate wall-clock times. Ring buffers are bounded diagnostic data, not a durable monitoring store, and their schema is implementation-specific.
SET QUOTED_IDENTIFIER ON;
DECLARE @ticks bigint=(SELECT ms_ticks FROM sys.dm_os_sys_info);
WITH r AS
(
SELECT [timestamp],TRY_CONVERT(xml,record) AS rec
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type=N'RING_BUFFER_SCHEDULER_MONITOR'
), samples AS
(
SELECT [timestamp],
rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS sql_cpu,
rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') AS idle_cpu
FROM r WHERE rec.exist('/Record/SchedulerMonitorEvent/SystemHealth')=1
)
SELECT TOP (60)
DATEADD(second,-CONVERT(int,(@ticks-[timestamp])/1000),SYSDATETIME()) AS sample_time,
sql_cpu,idle_cpu,100-idle_cpu-sql_cpu AS other_cpu
FROM samples ORDER BY [timestamp] DESC;The first line matters in sqlcmd, which starts with QUOTED_IDENTIFIER off. The XML methods refuse to run without it. Each row is one sample, about a minute apart, and the buffer keeps only a limited history. I confirm these samples against Windows performance monitoring on the target build. Other-process CPU matters because backup tools, antivirus, and neighboring workloads share the host. A percentage also depends on the available CPU capacity. Record core count, processor generation, VM limits, and host contention before translating percentages into a new machine size.
Observe Process and Operating-System Memory
SQL Server retaining memory is expected caching behavior. Read available operating-system memory, process memory, and pressure flags alongside max server memory configuration. Leave space for the operating system, non-buffer allocations, Agent tasks, and other services. Do not size several instances as though each owns the entire host.
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
process_physical_memory_low,process_virtual_memory_low
FROM sys.dm_os_process_memory;
SELECT total_physical_memory_kb,available_physical_memory_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
SELECT name,value_in_use FROM sys.configurations
WHERE name IN (N'min server memory (MB)',N'max server memory (MB)');A pressure flag or sustained low available memory deserves correlation with grants, paging, and workload latency. One high process-memory number alone does not justify removing RAM. I preserve the settings with every series so a configuration change does not masquerade as a workload change.
Read Page Life Expectancy in Context
PLE describes buffer-page residency behavior. It varies with memory, workload, and node distribution. Avoid one universal pass-fail threshold. Read each Buffer Node instance when NUMA is present, and compare with its own baseline. The counter object names are padded with trailing spaces, so the filter trims them before LIKE. A sharp drop during a scan is different from sustained churn that accompanies slow queries.
SELECT object_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name=N'Page life expectancy'
AND (RTRIM(object_name) LIKE N'%:Buffer Node'
OR RTRIM(object_name) LIKE N'%:Buffer Manager');
SELECT DB_NAME(database_id) AS database_name,
COUNT_BIG(*)*8.0/1024 AS cached_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id<>32767
GROUP BY database_id ORDER BY cached_mb DESC;The buffer-descriptor query shows current cached pages by database. It does not measure the entire active working set or assign every byte of SQL Server memory to a database. Scanning this DMV also has cost on a large cache. Choose a sensible sampling frequency and include tempdb and server-wide allocations in the broader review.
Calculate File I/O Over an Interval
sys.dm_io_virtual_file_stats exposes cumulative file counters. Capture two samples and calculate deltas. Separate read latency, write latency, operations, and bytes. A lifetime average can conceal a short but severe storage stall. Restarts and counter resets invalidate a simple subtraction, so reject intervals whose counters go backward.
SELECT * INTO #io_start FROM sys.dm_io_virtual_file_stats(NULL,NULL);
DECLARE @started datetime2(3)=SYSDATETIME();
WAITFOR DELAY '00:00:30';
DECLARE @seconds decimal(12,3)=DATEDIFF_BIG(millisecond,@started,SYSDATETIME())/1000.0;
SELECT DB_NAME(v.database_id) AS database_name,
SUM(v.num_of_reads-s.num_of_reads) AS read_ops,
SUM(v.num_of_writes-s.num_of_writes) AS write_ops,
SUM(v.io_stall_read_ms-s.io_stall_read_ms)*1.0 /
NULLIF(SUM(v.num_of_reads-s.num_of_reads),0) AS read_ms_per_op,
SUM(v.io_stall_write_ms-s.io_stall_write_ms)*1.0 /
NULLIF(SUM(v.num_of_writes-s.num_of_writes),0) AS write_ms_per_op,
SUM(v.num_of_bytes_read-s.num_of_bytes_read)/1048576.0/@seconds AS read_mb_per_second,
SUM(v.num_of_bytes_written-s.num_of_bytes_written)/1048576.0/@seconds AS write_mb_per_second
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
JOIN #io_start AS s ON s.database_id=v.database_id AND s.file_id=v.file_id
WHERE v.num_of_reads>=s.num_of_reads AND v.num_of_writes>=s.num_of_writes
GROUP BY v.database_id;Use a clean session for the temporary-table example. A permanent collector should preserve file identity and reject changed or reset intervals explicitly. Keep log-file and data-file metrics separately as well. Consolidated tempdb and backup traffic can make a storage design fail even when user-database averages look comfortable.
Base Right-Sizing SQL Server on Time Series Peaks
Persist samples with instance, host, timestamp, interval length, and collector version. Report peak and high-percentile CPU, available memory, pressure events, cache use, I/O throughput, and latency. Include the business events behind each peak. State the coverage and missing intervals on the sheet.
I compare aligned workload windows before estimating the destination. Peaks that never coincide require a different model from peaks driven by the same closing process. Preserve growth allowance and test headroom for a maintenance task or failover. An average is wonderfully calm. It also sleeps through the incident.
Validate the Proposed Shared Host
Run a realistic rehearsal with competing workloads, destination storage, and VM limits. Measure response time and throughput, not just resource utilization. Check tempdb, memory grants, backup windows, and recovery behavior. Different CPU generations and storage designs prevent a simple percentage-for-percentage transplant.
Permissions for these DMVs vary by SQL Server version. Confirm collector visibility before using missing data in a sizing decision. Keep collection overhead low and document its failures. The figures should describe the workload without becoming a new workload themselves.
Keep Right-Sizing SQL Server Decisions Reviewable
Keep the collector's raw units in the stored data. Milliseconds, kilobytes, pages, and bytes per interval are different quantities. Convert them in one documented reporting step. A sizing sheet that mixes megabytes with gigabytes can be remarkably confident and remarkably wrong. Record the sample interval beside every rate.
CPU and I/O can also fall after a query fix. Separate current demand from avoidable work before buying capacity or reducing it. Review the largest scans, spills, and repeated calls. Then collect another comparable cycle if a material tuning change was made. The sizing model should describe the workload that will move, not one that was already repaired.
Include a single-instance failure scenario when the destination must carry more work during recovery. Test that capacity with the same resource limits. Headroom is a requirement to validate, not an unexplained percentage added at the bottom.
Present current capacity, measured simultaneous demand, proposed capacity, and the chosen headroom separately. Include workload growth and a return plan if consolidation misses the target. Monitor the same signals after cutover so the estimate can be checked against operation.
I use right-sizing SQL Server as an evidence exercise. The collected numbers narrow the options; the rehearsal tests the chosen one. Keep the sheet and raw samples together so a later reviewer can explain why the host was sized that way.
Related reading on this blog: Navigating SQL Server CPU and Memory Usage Woes and How to Track Data Platform Service Level and Performance Before and After Consolidation?.
A sizing average is not a capacity plan, it is one summary inside a peak-aware workload record.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
