SQL Server Production Installation Checklist

Goal: Treat the installation as a complete platform build, not just "install SQL Server and create databases". Every decision must support predictable performance, easy troubleshooting, workload separation, recoverability, security hardening, and operational readiness.

1.1 Server Role Decision

1.2 Pre-Planning Checklist

2.1 OS Patching (Before SQL Installation)

Install the latest Windows cumulative updates, security updates, firmware updates, storage controller drivers, and NIC drivers. Verify with winver.

2.2 Windows Features

Ensure required features are installed:

3.1 Disable Unnecessary Services

3.2 Power Plan

Set Windows power plan to High Performance. The default Balanced plan allows the CPU to idle and throttle down cores, which causes erratic SQL Server latency — queries that should complete in milliseconds may sporadically take seconds when the CPU ramps back up.

Activate via command line (more reliable than GUI for Server Core or remote management):

powercfg /list                       -- list all plans with their GUIDs
powercfg /setactive <GUID>           -- e.g., 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c (High Perf)
powercfg /getactivescheme            -- verify the active plan

For SQL Server VMs running on Hyper-V or VMware, also verify that the hypervisor host and the physical BIOS/UEFI have power management set to High Performance / Maximum Performance, not power-saving or balanced. Windows inside the guest cannot override a host or BIOS that is capping frequency.

3.3 Windows Defender / Antivirus Exclusions

3.4 Windows Firewall

3.5 Time Zone & NTP

Critical: A production SQL Server must NOT have data, logs, or TempDB on the C: drive.

5.1 File System & Allocation Unit

Use NTFS with 64 KB allocation unit size (65536 bytes) for all SQL volumes. On Windows Server 2022/2025, consider ReFS (Resilient File System) with 64 KB cluster size for Data and Backup volumes — ReFS leverages block cloning to accelerate file creation, instant file initialization for transaction logs, and storage-level checkpoints.

6.1 Verify Accounts

6.2 Windows Permissions Granted to SQL Engine Account

Both permissions must be assigned via Local Security Policy (secpol.msc) → Local Policies → User Rights Assignment. The exact service account name (DOMAIN\svc_sqlengine) must be explicitly added — group membership inheritance is not sufficient.

VERY IMPORTANT. Change defaults on the Data Directories tab during setup.

SQL Server will consume all available memory unless constrained. Always set max server memory.

-- Example: 64 GB RAM server — reserve 8 GB for OS
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 57344;  -- 56 GB
RECONFIGURE;

-- Verify
SELECT name, value_in_use FROM sys.configurations
WHERE name = N'max server memory (MB)';

Follow Microsoft guidance (KB 2806535). For NUMA systems, also consider max degree of parallelism together with cost threshold for parallelism.

-- Check logical CPUs
SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info;

-- Set MAXDOP (example: 8-core server or cap at 8 for larger hosts)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

SQL Server auto-configures max worker threads based on CPU count. For high-core production servers with heavily concurrent workloads, the default can sometimes lead to thread starvation before SQL scales. Evaluate and adjust if needed.

-- Check current setting
SELECT max_workers_count FROM sys.dm_os_sys_info;

-- Current configured value
EXEC sp_configure 'max worker threads';

The default formula (SQL 2016+): 256 + ((cpu_count - 4) * 8) for x64 systems. For servers with > 64 cores, verify the calculated value matches your workload concurrency. Only adjust upward if you observe thread waits (THREADPOOL wait type) under normal load.

17.1 Verify TempDB Configuration (Post-Install or Post-Migration)

-- File count, sizes, and growth uniformity
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       growth, is_percent_growth, physical_name
FROM sys.master_files
WHERE database_id = 2
ORDER BY type_desc, file_id;

-- Active TempDB PAGELATCH contention (allocation pages)
SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%' AND resource_description LIKE '2:%';

Remediation: Add equal-sized data files (up to 8) on a dedicated fast volume. Set identical fixed MB growth on every file. Pre-size files to expected peak — autogrowth during peak load causes PAGELATCH waits. Restart is not required after adding TempDB files.

-- Example: add a TempDB data file (repeat until file count matches target)
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev3,
    FILENAME = N'F:\SQLTempDB\tempdev3.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);

Multi-TB databases: For databases over 500 GB, set autogrowth to at least 2048–4096 MB. A small autogrowth (e.g., 100 MB) on a large database causes excessive VLF fragmentation and frequent growth events during heavy data loads, leading to IO stalls.

25.1 Ola Hallengren Maintenance Solution

25.2 Index Fragmentation — Detect High Fragmentation

After migration or bulk load, index fragmentation often spikes. Use Microsoft thresholds: 5–30% → reorganize; >30% → rebuild (online when edition supports it). Skip small indexes (<1000 pages).

-- Fragmentation report across current database (run per DB or use physical_stats_and_heaps.sql)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    ps.page_count,
    ps.avg_fragmentation_in_percent,
    CASE
        WHEN ps.avg_fragmentation_in_percent > 30 THEN 'REBUILD'
        WHEN ps.avg_fragmentation_in_percent > 5  THEN 'REORGANIZE'
        ELSE 'OK'
    END AS recommended_action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps
INNER JOIN sys.tables AS t ON ps.object_id = t.object_id
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.index_id > 0
  AND ps.page_count >= 1000
  AND ps.avg_fragmentation_in_percent > 5
ORDER BY ps.avg_fragmentation_in_percent DESC;

Repository script: 05_Index_Statistics/physical_stats_and_heaps.sql (read-only report). To apply maintenance online: 05_Index_Statistics/index_maintenance_online.sql — set @ExecuteMaintenance = 0 first for a dry run.

25.3 Remediate Fragmentation (Online)

-- Reorganize (5–30% fragmentation, always online)
ALTER INDEX [IX_OrderDate] ON [dbo].[Orders]
    REORGANIZE WITH (LOB_COMPACTION = ON);

-- Rebuild (>30% fragmentation — Enterprise/Developer/Azure for ONLINE)
ALTER INDEX [IX_OrderDate] ON [dbo].[Orders]
    REBUILD PARTITION = ALL WITH (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    );

25.4 Statistics Freshness — Detect Outdated Stats

Stale statistics cause bad cardinality estimates and plan regressions after migration. Flag stats where modifications exceed 20–30% of row count since last update.

-- Stale statistics in current database
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    CAST(sp.modification_counter AS DECIMAL(18,2))
        / NULLIF(sp.rows, 0) * 100 AS modification_pct
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND CAST(sp.modification_counter AS DECIMAL(18,2))
        / NULLIF(sp.rows, 0) * 100 >= 20
ORDER BY modification_pct DESC;

Repository script: 05_Index_Statistics/statistics_freshness.sql (all user databases).

25.5 Update Statistics (Remediation)

-- Single statistic (preferred for large tables during business hours)
UPDATE STATISTICS [dbo].[Orders] ([IX_OrderDate]) WITH FULLSCAN;

-- Entire table (off-peak; can be expensive on large tables)
UPDATE STATISTICS [dbo].[Orders] WITH FULLSCAN;

-- Large table alternative — sampled update
UPDATE STATISTICS [dbo].[Orders] WITH SAMPLE 30 PERCENT;

-- All user databases: schedule via Ola Hallengren IndexOptimize
-- or run statistics_freshness.sql and update flagged objects only

With transaction log backups every 5–15 minutes, the SQL Server Error Log fills rapidly with thousands of "Backup succeeded" messages, making it difficult to find real errors during an outage.

Recommendation: Add Trace Flag 3226 as a startup parameter. It suppresses successful backup messages while still logging failures.

To add: Open SQL Server Configuration Manager → SQL Server Services → right-click SQL Server → Properties → Startup Parameters. Add -T3226 and restart.

-- Verify the trace flag is active
DBCC TRACESTATUS(3226);

Context: Most SQL Server deployments are now virtualized — on Hyper-V, VMware vSphere, or Azure. A VM that inherits default virtualization settings will leave significant performance on the table. This section covers the additional steps required when SQL Server runs inside a virtual machine.

36.1 vCPU Configuration

• Do not overallocate. Total vCPUs across all VMs on a host should not exceed the number of host logical processors (minus 1–2 for the root partition / hypervisor). Overcommitment causes CPU ready / co-stop contention.
• Use whole sockets. Assign vCPUs in socket multiples (e.g., 4 vCPUs = 1 socket × 4 cores, not 4 sockets × 1 core). SQL Server licenses per core — socket alignment avoids licensing confusion and NUMA oddities.
• Avoid oversubscription for SQL Server. Unlike web servers, SQL Server does not handle CPU overcommitment gracefully. A 1:1 or at-most 1:2 vCPU-to-pCPU ratio is recommended.

36.2 Memory — Dynamic Memory Must Be OFF

Critical: Disable Dynamic Memory (Hyper-V) or Memory Ballooning (VMware) for all SQL Server VMs. SQL Server manages its own buffer pool and expects a fixed amount of memory. Ballooning/dynamic memory causes SQL to be paged out, leading to severe performance degradation or even the SQL Server process being killed by the OS memory manager.

36.3 Storage — VM Disk Configuration

36.4 Hyper-V Enlightenments & Integration Services

Windows Server VMs running on Hyper-V have access to "enlightenments" — optimized code paths that detect the hypervisor and use hypercalls instead of emulated instructions. These are enabled by default on modern Windows Server but should be verified:

• Hyper-V Integration Services: all critical components enabled (Heartbeat, Shutdown, Time Sync, Data Exchange, Key-Value Pair)
• Time sync: If the VM domain-joined with its own NTP source, disable host time sync to avoid clock drift conflicts

36.5 NUMA & Virtual NUMA (vNUMA)

For VMs with more than 64 vCPUs, enable Virtual NUMA so SQL Server can properly schedule across processor groups. For VMs with fewer than 64 vCPUs, let the hypervisor and SQL Server auto-manage NUMA — no manual intervention needed beyond the MAXDOP guidance in Section 15.

36.6 Network — SR-IOV & VMQ

For high-throughput SQL workloads (Always On synchronous replication, backup streaming), use SR-IOV (Single Root I/O Virtualization) to bypass the virtual switch and reduce latency. If SR-IOV is not available, ensure VMQ (Virtual Machine Queue) is enabled on the host for the physical NIC.

36.7 Host-Level Resource Governance

• Hyper-V Root Partition: Reserve at least 2 GB RAM + 2 CPU cores for the root partition. On NUMA systems, ensure the root partition owns at least one NUMA node.
• VM Reservations: Set CPU and memory reservations equal to the VM's configured allocation to guarantee resource availability.
• Weight / Priority: Set the SQL Server VM to the highest relative weight so the hypervisor prioritizes it during contention.

36.8 Anti-Virus Exclusions on the Host

Extend antivirus exclusions to the host level as well. If the host scans VHDX files while SQL Server is writing to them, it causes IO latency spikes inside the VM. Exclude .vhdx, .vhd, .avhdx (snapshots/checkpoints), and the VM storage path from host-side real-time scanning.

36.9 VMware-Specific (if applicable)

• Paravirtual SCSI (PVSCSI): Use PVSCSI adapters instead of LSI Logic for better throughput and lower CPU overhead.
• VMXNET3: Use VMXNET3 network adapter (not E1000) for lower latency and higher throughput.
• CPU Hot Add / Memory Hot Plug: Disable — not supported for SQL Server; prevents proper NUMA detection.
• Reservation: Set CPU and memory reservations equal to the VM size to avoid ready-time contention.

When to use this section: The instance is online after migration but queries are slower than the source server — especially with high elapsed time, low CPU, and moderate logical reads. Work through these checks in order before chasing query hints or compatibility level changes.

37.1 Virtual Log Files (VLF) Fragmentation

Excessive VLFs in the transaction log slow down log backups, recovery, replication, and AG sync. Common after many small autogrowth events during migration restore.

-- VLF count per database (SQL Server 2016+)
SELECT
    d.name AS database_name,
    COUNT(*) AS vlf_count,
    CAST(SUM(vlf_size_mb) AS DECIMAL(18,2)) AS total_log_size_mb
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS v
WHERE d.database_id > 4 AND d.state = 0
GROUP BY d.name
ORDER BY vlf_count DESC;

Remediation (maintenance window):

1. Take a log backup (FULL recovery) or switch to SIMPLE temporarily if policy allows.
2. Shrink log to minimum: DBCC SHRINKFILE (N'LogLogicalName', TRUNCATEONLY);
3. Grow back in large fixed increments (e.g., 4–8 GB per step) — not 10% autogrowth.
4. Set fixed MB FILEGROWTH going forward.

-- Example: grow log in controlled steps after shrink
ALTER DATABASE [YourDB] MODIFY FILE (NAME = N'YourDB_log', SIZE = 8192MB, FILEGROWTH = 2048MB);

Repository script: 03_Storage_Engine/vlf_fragmentation.sql

37.2 Disk Latency (I/O Stalls)

Post-migration slowness is frequently storage-related — especially cold buffer pool, shared VM storage, or logs on slow volumes. sys.dm_io_virtual_file_stats is cumulative since instance start; note sqlserver_start_time before comparing.

-- Per-file read/write latency
SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS logical_file,
    mf.physical_name,
    mf.type_desc,
    CAST(vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS DECIMAL(10,2)) AS avg_read_ms,
    CAST(vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS DECIMAL(10,2)) AS avg_write_ms,
    CAST(vfs.io_stall / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0) AS DECIMAL(10,2)) AS avg_io_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY avg_io_ms DESC;

-- Pending I/O backlog
SELECT COUNT(*) AS pending_io, MAX(io_pending_ms_ticks) AS max_pending_ms
FROM sys.dm_io_pending_io_requests;

Remediation:

• Move data/log files to faster dedicated volumes (SSD/NVMe); keep logs separate from data.
• Verify VM storage: fixed VHDX (not dynamic), host caching off for data/log, Storage QoS minimum IOPS.
• Confirm antivirus exclusions on SQL paths (guest and hypervisor host).
• Correlate with waits: PAGEIOLATCH_*, WRITELOG, IO_COMPLETION.

Repository scripts: 01_Server_OS/disk_latency.sql, Prod_Migration/08_Storage_OS/01_io_latency_deep_dive.sql

37.3 Database File Growth Settings

Percent-based autogrowth and tiny growth increments cause repeated growth events, VLF proliferation, and I/O pauses during peak load — especially on multi-TB databases after migration.

-- Autogrowth and space utilization (all databases)
SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_name,
    type_desc,
    size * 8 / 1024 AS size_mb,
    CASE WHEN is_percent_growth = 1
         THEN CAST(growth AS VARCHAR(10)) + '%'
         ELSE CAST(growth * 8 / 1024 AS VARCHAR(10)) + ' MB'
    END AS autogrowth,
    is_percent_growth,
  physical_name
FROM sys.master_files
WHERE database_id > 4
ORDER BY database_name, type_desc;

Remediation:

• Switch all files to fixed MB growth — never percent.
• Pre-size data and log files to planned capacity; autogrowth is a safety net, not a sizing strategy.
• Multi-TB databases: use 2048–4096 MB growth increments minimum.
• Disable Auto Shrink on all user databases.

-- Fix percent-based autogrowth
ALTER DATABASE [YourDB] MODIFY FILE (NAME = N'YourDB', FILEGROWTH = 1024MB);
ALTER DATABASE [YourDB] MODIFY FILE (NAME = N'YourDB_log', FILEGROWTH = 2048MB);

Repository script: 03_Storage_Engine/database_files_growth.sql

37.4 TempDB Configuration Review

TempDB contention (PAGELATCH on PFS/GAM/SGAM) affects every workload using sorts, hashes, and spools. After migration, TempDB often retains default file count, uneven sizes, or percent growth.

Checklist:

• 1 data file per logical core, maximum 8 (add only if PAGELATCH contention persists).
• All data files: equal size, equal fixed MB growth, on a dedicated fast volume.
• Pre-size to expected peak — do not rely on autogrowth under load.
• Trace flag 1118 (SQL 2014 and earlier) and 1117 are unnecessary on SQL 2016+.

Repository scripts: 03_Storage_Engine/tempdb_configuration.sql, Prod_Migration/08_Storage_OS/03_tempdb_autogrowth_audit.sql

37.5 Post-Migration Triage Order

1. Instance config audit — memory, MAXDOP, cost threshold, compatibility level.
2. Storage I/O latency and file growth settings (this section).
3. Wait statistics delta during slow workload reproduction.
4. Index fragmentation and stale statistics (Section 25).
5. Query Store / plan regressions if still unresolved.

Repository playbook: Prod_Migration/01_Quick_Triage/00_RUN_FIRST_triage_playbook.sql