Begin with a slow SQL operation and determine whether it is spending time executing or waiting. A VM can have low average CPU while queries wait on locks, storage or memory grants. More vCPUs do not remove those waits automatically.
This guide uses read-only diagnostic SQL and an isolated tuning workflow. It does not apply arbitrary server configuration values or create indexes in production. Queries are source-checked examples, not lab-executed results.
1. Establish permissions and workload scope
Use a diagnostic login approved by the DBA. On SQL Server 2022, the file-I/O DMV used below requires VIEW SERVER PERFORMANCE STATE; earlier releases have different requirements. Do not grant sysadmin merely to make a query work. Other DMVs and plans can expose query text and require their own permissions. File statistics DMV reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-ver16
Select one representative query or application transaction, its expected result and its parameter distribution. Record database size, recovery model, SQL build, compatibility level, concurrent sessions and whether the observation is from a read replica or writable primary.
Identify scheduled backups, index maintenance, antivirus scans and ETL jobs overlapping the slow period. Correlate SQL, Windows and ESXi timestamps. Do not benchmark a quiet clone and claim the result covers the production concurrency pattern.
2. Capture configuration without changing it
SELECT @@VERSION AS sql_version;
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (
'max server memory (MB)', 'min server memory (MB)',
'max degree of parallelism', 'cost threshold for parallelism'
);
SELECT database_id, name, recovery_model_desc, compatibility_level
FROM sys.databases
WHERE state_desc = 'ONLINE';
SELECT name, type_desc, size * 8.0 / 1024 AS size_mb,
growth, is_percent_growth
FROM tempdb.sys.database_files;
Store the output with VM CPU/RAM, NUMA presentation, virtual controller type, datastore/policy and guest disk mapping. A SQL file on E: is not a storage diagnosis until it is mapped to the relevant VMDK and backend.
3. Decide whether the request is running or waiting
During the symptom, capture active requests:
SELECT session_id, status, command, cpu_time, total_elapsed_time,
logical_reads, reads, writes, wait_type, wait_time,
blocking_session_id, database_id
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID AND session_id > 50;
This is a request snapshot, not a complete worker-level trace. For parallel row-mode requests, several I/O counters are not updated on the coordinator row and wait columns describe that coordinator. Do not treat these rows as complete per-worker totals. SQL Server 2022 requires VIEW SERVER PERFORMANCE STATE for this DMV too. Request DMV schema and limits: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-requests-transact-sql?view=sql-server-ver16
CPU and elapsed time are not directly interchangeable for parallel queries: total worker CPU can exceed wall-clock elapsed time. Use waits, execution plans and workload context together. Microsoft's slow-query methodology: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries provides the running-versus-waiting approach.
| Observation | Investigate | Do not conclude |
|---|---|---|
| Blocking session and lock waits | Long transactions, access order, isolation and transaction design | Storage must be slow |
PAGEIOLATCH_* with physical reads |
Access pattern, buffer availability and file-read latency | Every latch wait needs a faster disk |
WRITELOG |
Log-write latency, transaction size/rate and log configuration | More data files will fix it |
RESOURCE_SEMAPHORE |
Memory grants, concurrency and query estimates | Raise memory without checking the guest budget |
| High CPU and logical reads | Plan shape, cardinality, scans and expensive operators | Add vCPUs before examining the plan |
Treat wait categories as leads. A cumulative wait since instance startup can describe an old incident; obtain a bounded delta or use an existing approved monitoring system.
4. Measure file-I/O latency over an interval
The following session-local query samples accumulated file counters twice. It writes temporary diagnostic tables in tempdb, not application tables. Run during a defined workload and avoid excessive collection frequency.
SELECT database_id, file_id, num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms
INTO #io_before
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
WAITFOR DELAY '00:01:00';
SELECT database_id, file_id, num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms
INTO #io_after
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
SELECT DB_NAME(a.database_id) AS database_name,
f.name AS logical_file, f.type_desc,
a.num_of_reads - b.num_of_reads AS reads_in_interval,
a.num_of_writes - b.num_of_writes AS writes_in_interval,
(a.io_stall_read_ms - b.io_stall_read_ms) * 1.0 /
NULLIF(a.num_of_reads - b.num_of_reads, 0) AS avg_read_ms,
(a.io_stall_write_ms - b.io_stall_write_ms) * 1.0 /
NULLIF(a.num_of_writes - b.num_of_writes, 0) AS avg_write_ms
FROM #io_after AS a
JOIN #io_before AS b
ON a.database_id = b.database_id AND a.file_id = b.file_id
JOIN sys.master_files AS f
ON a.database_id = f.database_id AND a.file_id = f.file_id
WHERE a.num_of_reads >= b.num_of_reads
AND a.num_of_writes >= b.num_of_writes
AND a.io_stall_read_ms >= b.io_stall_read_ms
AND a.io_stall_write_ms >= b.io_stall_write_ms;
Close the session or remove these temporary tables before repeating in the same session. A null latency means there were no operations in that direction; it is not zero latency. Discard an interval spanning restart, database recreation or file-identity changes. These are averages, not tail latency or a storage SLA. Compare with ESXi/vSAN measurements from the same minute.
5. Budget SQL memory instead of applying a percentage blindly
Inventory physical memory and peak requirements for Windows, non-buffer-pool SQL allocations, agents, other instances and applications. Set a candidate max server memory that leaves measured headroom, then observe both Windows pressure and SQL memory-grant waits.
max server memory is not a hard cap on every byte allocated by the SQL process. min server memory is not an instruction to preallocate that amount at startup. Microsoft documents these boundaries and the risks of setting the maximum too high or too low. Server memory configuration: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16
Prepare the candidate and previous values in a change record. Have the DBA apply one change on the test instance, rerun the workload, and restore the previous value if acceptance fails. Do not bundle memory, MAXDOP and tempdb changes into one unexplained result.
6. Align parallelism with workload and visible topology
Inspect the guest's schedulers and NUMA topology, and compare them with VM configuration and host placement. SQL's MAXDOP guidance depends on logical processors and NUMA arrangement. Apply the version-specific guidance to the topology SQL actually sees. MAXDOP configuration guidance: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16
Test throughput under concurrent work as well as the latency of one report. A setting that accelerates one parallel query can reduce total throughput by occupying workers needed by others. Cost threshold is a planning-cost control, not a time threshold in seconds.
Do not force CPU affinity, resize NUMA boundaries or enable hot-add as an automatic tuning step. Coordinate guest and hypervisor changes and keep VM topology stable across comparison runs.
7. Investigate tempdb precisely
Use the recorded file layout to identify unequal data-file sizes/growth, unexpected percentage growth and insufficient free space. Check whether observed waits indicate allocation contention, storage latency or large query spills; those are different problems.
Keep comparable tempdb data files equally sized and grow them consistently. Select file count from the SQL-version guidance and measured contention rather than creating one file for every vCPU without limit. Pre-size for a measured workload and capacity budget. tempdb documentation: https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16
File moves, file removal and service restarts are not provided as copy-and-paste repairs here. They need a DBA-reviewed maintenance and recovery procedure. Monitor whether a change reduces the original wait or spill before declaring success.
Acceptance and rollback
Compare transaction correctness, duration distribution, throughput, CPU, logical reads, waits and errors using the same dataset and concurrency. Retain actual plans where permitted and protect their contents. Do not flush the plan or buffer cache to manufacture a repeatable production test.
Record exact previous settings and reverse only the tested setting if results fail. An index or schema change has separate correctness and deployment consequences; a backup is not a substitute for a reviewed rollback plan.
Related topics: host CPU contention, storage latency and application-consistent recovery.
Learning notes
- A waiting query and a CPU-bound query need different remedies.
- Interval deltas are more useful than lifetime averages for a short incident.
- SQL memory and parallelism must fit the whole workload, not only one query.
Sources and review status
See the source ledger for dates, version scope and limits, the author self-check, and the series index. Independent review and lab validation remain pending.