Lesson 1: Hardware and Sizing Basics (CPU, Memory, Storage Tiers)

Section 5 — Lesson 1: Hardware and Sizing Basics (CPU, Memory, Storage Tiers)

Welcome to Section 5. Up to now, you have been learning how databases behave and how DBAs write safe SQL.
Now you will build the foundation every DBA relies on: a properly sized, repeatable environment.

Most performance problems are not caused by “bad SQL” alone. They are often caused by mismatched resources:
not enough memory, slow storage for the workload, CPU bottlenecks from poor configuration,
or a system that was never sized with reality in mind.

Lesson description: You will learn the practical sizing basics DBAs use when planning or reviewing a SQL Server environment.
You will understand how CPU affects concurrency and query execution, how memory supports caching and prevents expensive disk reads,
and how storage tiers (latency and IOPS) influence every database operation. You will also learn the DBA approach to capacity planning:
establish a baseline, identify the workload pattern, size for peaks, and build headroom so the system stays stable over time.

1) The DBA Sizing Mindset (What You Are Really Trying to Achieve)

Sizing is not guessing. Sizing is risk management.
Your goal is to design an environment where:

• the system remains stable during peak load
• queries and jobs finish reliably inside expected time windows
• maintenance and backups do not fight the business workload
• there is enough headroom for growth and unexpected spikes
• failures are diagnosable (clear bottlenecks, not random chaos)

DBA rule: Size for your workload, not for a generic “recommended minimum.”

2) CPU Basics for SQL Server (What CPU Does in Real Life)

CPU is the compute engine that executes query operators (joins, sorts, aggregations),
compiles and optimizes plans, handles encryption/compression overhead,
and supports concurrency for many sessions at once.

2A) Core concepts DBAs must understand

• Cores: More cores generally support more concurrent work.
• Clock speed: Higher speed improves single-threaded performance (important for many OLTP queries).
• CPU saturation: When CPU is near 100% for sustained periods, everything slows down.
• Parallelism: SQL Server may use multiple cores for a single query (depending on settings and plan).

2B) Common CPU bottleneck signs (DBA observation)

• high CPU utilization during business workload windows
• queries with expensive sorts/hash joins (especially without supporting indexes)
• high compilation rates (many ad-hoc queries, poor parameterization)
• parallelism overhead from misconfigured MAXDOP or Cost Threshold (covered later)

2C) Practical guidance for a lab machine

• Minimum for learning: 2 cores
• Recommended for smooth lab: 4 cores
• Better for performance labs: 6–8 cores (if your host can handle it)

DBA caution: In virtualization, “more vCPUs” is not always better. Over-allocating CPU can increase scheduling delays.
Your host must have enough physical CPU resources to support the VM.

3) Memory Basics (Why RAM Is the #1 Performance Multiplier)

Memory (RAM) is where SQL Server caches data pages and plan information.
The more useful data SQL Server keeps in memory, the less it must read from disk.
Disk reads are slow compared to memory.

3A) What SQL Server uses memory for

• Buffer pool: caches data pages (most important)
• Plan cache: stores compiled query plans for reuse
• Memory grants: for sorts, hash joins, and other operators
• Columnstore / in-memory features: advanced features use additional memory (later sections)
• OS needs: Windows requires memory too (SQL Server must not steal everything)

3B) What “not enough memory” looks like

• frequent physical reads (disk I/O) for common queries
• slow queries after cache flush / server restart
• tempdb spills during sorts and hash operations (memory grant issues)
• OS paging (very bad; causes massive performance degradation)

3C) Practical guidance for a lab machine

• Absolute minimum: 8 GB RAM (you can learn, but it may feel slow)
• Recommended: 16 GB RAM (much smoother for labs)
• Ideal for advanced performance labs: 24–32 GB RAM

DBA rule: Always reserve memory for the OS and other services.
Later in the course you will learn how to set SQL Server max memory properly.

4) Storage Tiers (Latency, IOPS, Throughput — The DBA Storage Truth)

Storage is where your database files live:
data files (.mdf/.ndf), log files (.ldf), and tempdb.
Storage performance impacts everything because SQL Server ultimately reads and writes pages and log records.

4A) The three storage performance terms DBAs use

• Latency (ms): how long a read/write takes. Lower is better.
• IOPS: how many I/O operations per second can be handled (important for random access).
• Throughput (MB/s): how much data per second can be transferred (important for sequential operations like backups).

4B) Why latency is the most important first metric

Many workloads are limited by random I/O. A single slow storage layer can make the entire system feel “slow”
even if CPU and memory are healthy.

4C) Common storage tiers (conceptual)

• HDD: high latency, low IOPS (slow for databases under load)
• SATA SSD: better latency, moderate IOPS
• NVMe SSD: very low latency, high IOPS (excellent for database workloads)
• Network storage (SAN/NAS): performance depends on configuration and congestion
• Cloud storage: performance depends on the disk type and provisioned throughput/IOPS

4D) Practical guidance for your lab

• Use SSD if possible. NVMe is best.
• If your host is HDD-only, your labs will still work but tuning exercises will be less realistic.
• Ensure you have enough free disk space for backups, restores, and tempdb experiments.

5) DBAs Separate Workloads: Data vs Log vs tempdb (Concept Preview)

In professional environments, DBAs commonly separate these I/O patterns:

• Data files: mixed reads/writes, often random I/O
• Log files: mostly sequential writes (very sensitive to latency)
• tempdb: can be extremely write-heavy (sorting, hashing, temp objects)

You will configure file locations later in this section, but for sizing purposes:
this is why storage design matters.

6) Capacity Planning 101 (How DBAs Estimate “Enough”)

6A) The minimum data you should collect (in real-world jobs)

• current database size and growth rate (monthly/weekly)
• peak concurrent users / sessions
• peak transaction rate (writes)
• top query patterns (reporting vs OLTP)
• backup sizes and backup window constraints
• RPO/RTO expectations (affects HA/DR choices)

6B) Headroom rule (DBA habit)

DBAs do not size for “exactly today.” They size with headroom:

• storage free space for growth + maintenance + unexpected events
• CPU headroom for spikes and plan regressions
• memory headroom to reduce disk dependency

DBA rule: A server that runs at 90–100% capacity during normal business hours is not “fine.”
It is already in a risk state.

7) A Simple “Lab Sizing Checklist” You Can Reuse

• CPU: 4 cores recommended (2 minimum)
• RAM: 16 GB recommended (8 minimum)
• Disk: SSD recommended; ensure at least 60–100 GB free for SQL + backups + tempdb labs
• Host health: do not starve your host OS (VM must be balanced)

Practice (Sizing Notes You Should Write in Your DBA Notebook)

Answer these in your notes (this trains you to think like a DBA):

1. What is your host CPU core count and how many cores will you allocate to the VM?
2. How much RAM does your host have and how much will you allocate to the VM without starving the host?
3. Is your storage SSD or HDD? Where will you store backups?
4. How much free disk space do you have today? How much do you want reserved as headroom?
5. What is your reason for each choice (evidence-based)?

Lesson Summary

• CPU affects concurrency and query execution; clock speed and core count both matter.
• Memory is the biggest performance multiplier because it reduces disk reads and prevents spills.
• Storage performance is about latency, IOPS, and throughput; latency is often the first limiter.
• DBAs size with workload awareness and headroom, not minimum requirements.
• In real environments, data/log/tempdb I/O patterns are different and often separated.

Next Lesson: Virtualization Options (VMware/VirtualBox/Hyper-V) — Choose One

Next, you will learn how DBAs build lab environments using virtualization,
how to choose the right platform, and how to avoid common VM mistakes that cause slow and unstable labs.