Choose SQL Server hardware from measured workload requirements, not a generic rule such as “buy the most CPUs possible.” Licensing, NUMA topology, storage latency, memory pressure, virtualization, and growth can matter more than a single headline specification.
1. Establish the workload
- Peak and typical transactions, batch duration, concurrent sessions, and data growth
- Read/write mix, working-set size, recovery objectives, and maintenance windows
- Current CPU, memory, I/O latency, throughput, network, wait statistics, and Query Store history
- Expected consolidation, high availability, reporting, ETL, and backup workloads
2. CPU and NUMA
Favor strong per-core performance for latency-sensitive workloads, then size core count for measured concurrency. Check SQL Server edition limits and licensing cost. Keep virtual CPU topology aligned with the host’s NUMA design, and validate that the hypervisor is not overcommitting CPU for a critical production instance.
3. Memory
Size memory for the database working set, query workspace, operating system, drivers, agents, backup tools, and other co-located services. Configure max server memory so Windows retains headroom. Track sustained memory grants, page-life trends, paging, and plan-cache behavior rather than relying on one counter.
4. Storage
- Measure read and write latency at the file and volume layers.
- Separate performance requirements for data, transaction log,
tempdb, and backups. - Design for failure, rebuild time, queue depth, throughput, and capacity—not just advertised IOPS.
- Pre-size files, use sensible autogrowth increments, and verify instant file initialization policy.
5. Network, availability, and operations
Validate bandwidth and latency for clients, backup targets, availability replicas, and storage. Include redundant power, paths, controllers, and network links where the service level requires them. Confirm firmware, drivers, operating-system support, monitoring, and vendor support before purchase.
6. Test before committing
Replay or simulate a representative workload, including backups, restores, integrity checks, index maintenance, failover, and peak reporting. Compare Query Store, wait statistics, Performance Monitor, and storage telemetry before and after. Document the bottleneck the new design is expected to remove and keep capacity for forecast growth.
Leave a Reply