SQL Server Instance Setup and Best Practices (SQL Server 2016 and Later)

---

This guide covers how to configure a SQL Server instance correctly from the ground up — covering parallelism, memory, TempDB, file layout, security, backup strategy, and database settings. Each section explains what the setting does, why the default is wrong or incomplete, and gives you the exact T-SQL to apply the correct value.

1 Max Server Memory

The single most impactful configuration change on any new SQL Server installation. The default value is 2,147,483,647 MB — effectively unlimited. Left at default, SQL Server will consume as much RAM as it wants, starving the operating system of the memory it needs for basic functions, driver I/O buffers, and network stack operations. The result is OS paging, instability, and unpredictable performance under load.

As of September 2025, Microsoft’s own documentation now recommends setting max server memory to 75% of total RAM as a baseline starting point for dedicated SQL Server machines — an update from prior guidance. The SQLYARD tiered formula below gives more precise values for machines at both the low and high end of the RAM range, which a flat 75% underserves on smaller servers and overserves on very large ones.

SQLYARD Tiered OS Reserve Formula

Total RAM	OS Reserve	SQL Server Max Memory
16 GB	4 GB	12 GB
32 GB	8 GB	24 GB
64 GB	12 GB	52 GB
128 GB	14.4 GB	~113 GB
256 GB	27.2 GB	~229 GB
512 GB	52.8 GB	~459 GB

The Formula Is a Starting Point — Not a Permanent Setting

Any memory formula applied at build time is an educated estimate based on hardware specs and no workload data. Once the server is in production and the real workload is established — typically 2 to 4 weeks after go-live — both the SQL Server max memory and the OS headroom need to be reviewed against actual evidence.

This is especially true on virtual machines. On a VM, the guest OS does not have exclusive access to the physical RAM assigned to it. The hypervisor can reclaim memory pages through ballooning or swapping when other VMs on the same host compete for resources. The VMware balloon driver in particular runs inside the guest OS and inflates by allocating pages from the guest, forcing the OS to page out its own memory to make room — which directly competes with SQL Server’s buffer pool. The formula has no way to account for this at build time because host-level memory pressure is dynamic and invisible from inside the guest.

Post-Go-Live Memory Review Queries

Run these 2 to 4 weeks after go-live, and again whenever a significant new workload is added to the server. The goal is to confirm that both SQL Server and the OS have sufficient memory under real production load.

-- 1. OS memory state -- the most direct signal
-- You want: "Available physical memory is high"
-- Warning:  "Available physical memory is low" or "running low" means OS needs more headroom
--           Lower max server memory if you see this sustained
SELECT
    total_physical_memory_kb / 1024          AS [Total RAM (MB)],
    available_physical_memory_kb / 1024      AS [OS Available (MB)],
    total_page_file_kb / 1024                AS [Page File Limit (MB)],
    available_page_file_kb / 1024            AS [Page File Available (MB)],
    system_memory_state_desc                 AS [OS Memory State]
FROM sys.dm_os_sys_memory;
------
-- Possible states:
-- "Available physical memory is high"         → OS has enough headroom
-- "Physical memory usage is steady"           → Acceptable
-- "Available physical memory is low"          → OS is under pressure -- act now
-- "Available physical memory is running low"  → Critical -- lower max server memory
-- "Physical memory state is transitioning"    → Monitor closely

-- 2. SQL Server internal memory pressure signals
-- process_physical_memory_low = 1 means SQL Server itself is being squeezed
-- If both this AND the OS state show pressure simultaneously,
-- the server does not have enough physical RAM for the current workload
SELECT
    physical_memory_in_use_kb / 1024         AS [SQL Memory In Use (MB)],
    locked_page_allocations_kb / 1024        AS [Locked Pages (MB)],
    page_fault_count,
    memory_utilization_percentage,
    process_physical_memory_low,             -- 1 = SQL Server under pressure
    process_virtual_memory_low               -- 1 = virtual address space pressure
FROM sys.dm_os_process_memory;

-- 3. Page Life Expectancy -- the buffer pool health indicator
-- PLE measures how long (seconds) a page stays in the buffer pool before being evicted
-- A declining trend or sustained low value means the buffer pool is too small
-- for the active working set
-- Rule of thumb: PLE below 300 seconds warrants investigation
--                PLE consistently dropping over hours = buffer pool pressure
SELECT
    TRIM([object_name])                      AS [Object],
    instance_name,
    cntr_value                               AS [Page Life Expectancy (sec)],
    GETDATE()                                AS [Captured At]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE N'%Buffer Node%'
AND counter_name = N'Page life expectancy'
ORDER BY instance_name;
------
-- On NUMA systems this returns one row per NUMA node
-- Look at each node individually -- a low PLE on one node indicates
-- uneven memory pressure across NUMA nodes

-- 4. Memory grants pending -- query memory pressure
-- Any sustained value above 0 means queries are queuing for memory grants
-- before they can execute -- a direct performance impact
SELECT
    TRIM([object_name])                      AS [Object],
    cntr_value                               AS [Memory Grants Pending]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE N'%Memory Manager%'
AND counter_name = N'Memory Grants Pending';

-- 5. Current max server memory vs what SQL Server is actually using
-- If SQL is consistently using close to its max, and PLE is low,
-- SQL Server needs more memory -- raise max server memory
-- If OS memory state shows pressure, SQL Server has too much -- lower it
SELECT
    c.value_in_use / 1024.0                  AS [Max Server Memory (GB)],
    p.physical_memory_in_use_kb / 1024.0 / 1024.0 AS [SQL Currently Using (GB)],
    m.total_physical_memory_kb / 1024.0 / 1024.0  AS [Total Machine RAM (GB)],
    m.available_physical_memory_kb / 1024.0 / 1024.0 AS [OS Available RAM (GB)],
    m.system_memory_state_desc               AS [OS Memory State]
FROM sys.configurations c
CROSS JOIN sys.dm_os_process_memory p
CROSS JOIN sys.dm_os_sys_memory m
WHERE c.name = 'max server memory (MB)';

How to Interpret and Act

What You See	What It Means	Action
OS state = low or running low	OS is starved — max server memory is too high for this workload on this host	Lower max server memory by 2–4 GB increments until OS state stabilizes
PLE declining trend or below 300 sec	Buffer pool too small for working set — SQL Server needs more memory	Raise max server memory if OS has available headroom; otherwise add RAM
Memory Grants Pending > 0 sustained	Query execution is queuing for memory — buffer pool or grant pool pressure	Review max server memory, check for memory-hungry queries, consider Resource Governor
process_physical_memory_low = 1	SQL Server is under physical memory pressure from outside	OS needs more headroom — lower max server memory; investigate VM host memory overcommit
All signals healthy	Current balance is working for this workload	Document the current values as your baseline; re-review after any major workload change

-- Step 1: Calculate the recommended value (from the SQLYARD Health Check Toolkit, Query 14)
-- Step 2: Apply it
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 24576;  -- replace with your calculated value in MB
RECONFIGURE;

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

2 Cost Threshold for Parallelism

This setting determines the minimum estimated cost at which SQL Server considers using a parallel execution plan for a query. The default is 5, a value unchanged since SQL Server 7.0 in 1998. Modern hardware is orders of magnitude faster than it was in 1998, which means thousands of queries that get parallel plans today would complete faster running single-threaded, because the overhead of spawning and synchronizing parallel threads exceeds the benefit.

The practical problem: a parallel query can hold many CPU threads simultaneously (up to MAXDOP threads per parallel branch). When too many low-cost queries go parallel, thread pool saturation becomes the actual bottleneck rather than query complexity — the exact opposite of what parallelism is supposed to fix.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

-- Verify
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';

Note: changing this setting clears the plan cache. Apply it during a low-traffic window and monitor for plan regressions over the following 24–48 hours.

3 Max Degree of Parallelism (MAXDOP)

MAXDOP limits how many CPU threads a single query can use during parallel execution. The default of 0 means unlimited — SQL Server can use every available logical processor for a single query. This is almost never appropriate for a production OLTP system. A single runaway parallel query can consume all CPU threads and prevent other sessions from getting scheduled.

Microsoft’s MAXDOP Guidance (SQL Server 2016+)

Server Configuration	Recommended MAXDOP
≤ 8 logical processors, single NUMA node	Equal to the number of logical processors
> 8 logical processors, single NUMA node	8
Multiple NUMA nodes, ≤ 16 logical per NUMA node	Equal to logical processors per NUMA node
Multiple NUMA nodes, > 16 logical per NUMA node	Half the logical processors per NUMA node (min 8)

-- Set instance-level MAXDOP
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 8;   -- replace with your calculated value
RECONFIGURE;

-- Override at database level for specific databases
-- (e.g. a vendor application that requires MAXDOP 1)
ALTER DATABASE [VendorAppDB]
SET MAXDOP = 1;

-- Override at query level
SELECT * FROM dbo.LargeTable
OPTION (MAXDOP 4);

-- Verify instance setting
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'max degree of parallelism';

4 Optimize for Ad Hoc Workloads

By default, SQL Server caches the full execution plan the first time any query runs — even one-time ad hoc queries that will never run again. On servers with many distinct queries, the plan cache fills with single-use plans that consume buffer pool memory while providing zero reuse benefit.

Enabling this option changes behavior so that the first time a query runs, only a small plan stub is stored. If the exact same query runs a second time, the full plan is then cached. The memory savings on ad hoc-heavy workloads can be substantial — some environments see their plan cache shrink by 50% or more.

There is no downside to enabling this on any instance. It does not affect plan reuse for stored procedures, functions, or queries that actually repeat.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- Check current plan cache composition before and after
SELECT objtype, COUNT(*) AS [Plan Count],
       SUM(size_in_bytes)/1024/1024 AS [Total Size (MB)]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total Size (MB)] DESC;

5 Instant File Initialization (IFI)

By default, whenever SQL Server creates or extends a data file, Windows writes zeros to every byte of the new space before SQL Server can use it. This is a security measure to prevent reading leftover disk data, but it causes significant stalls — a 10 GB autogrowth event means writing 10 GB of zeros before the operation completes. During that time, user activity is held up waiting for the file to be ready.

IFI grants the SQL Server service account the Perform Volume Maintenance Tasks privilege, allowing SQL Server to skip the zero-write step for data files and allocate space immediately. The space is overwritten with real data pages as they are written, so the security exposure is narrow and acceptable for most production environments.

What Changed in SQL Server 2022

SQL Server 2022 extended IFI-like behavior to transaction log autogrowth events up to 64 MB — the same as the new default log autogrowth increment. Log files above 64 MB still require full zero-initialization. This means that on SQL 2022, if your log autogrowth is set to 64 MB or less, you get near-instant log growth as well. Another good reason to keep log autogrowth at 64 MB or below on this version.

-- Verify IFI status
SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%';

-- How to enable:
-- 1. Run: secpol.msc
-- 2. Navigate to: Local Policies → User Rights Assignment
-- 3. Open: "Perform volume maintenance tasks"
-- 4. Add the SQL Server service account (or service SID — preferred)
-- 5. Restart the SQL Server service

-- Using service SID is preferred over named account (survives service account changes):
-- Service SID format: NT SERVICE\MSSQLSERVER (default instance)
--                     NT SERVICE\MSSQL$InstanceName (named instance)

6 Lock Pages in Memory (LPIM)

Without LPIM, the Windows memory manager can page portions of SQL Server’s buffer pool to disk under memory pressure — even if SQL Server’s memory usage is within the configured max server memory limit. When SQL Server later needs those pages back, it reads them from the pagefile. For a database engine whose entire performance model is built on keeping hot data in RAM, this is catastrophic. The result is random I/O stalls that look like storage problems but are actually memory management problems.

LPIM grants the SQL Server service account the Lock Pages in Memory privilege, which causes SQL Server to allocate buffer pool memory using the AWE API. Those pages are locked into physical RAM and cannot be paged out by Windows under any circumstances.

-- Verify LPIM status
SELECT sql_memory_model_desc
FROM sys.dm_os_sys_info;
-- CONVENTIONAL = LPIM not active
-- LOCK_PAGES   = LPIM active
-- LARGE_PAGES  = TF 834 or 876 active (implies LPIM)

-- How to enable:
-- 1. Run: secpol.msc
-- 2. Navigate to: Local Policies → User Rights Assignment
-- 3. Open: "Lock pages in memory"
-- 4. Add the SQL Server service account or service SID
-- 5. Restart the SQL Server service

-- You can enable LPIM and IFI in the same local policy edit and restart once

7 Backup Compression and Checksums

Both of these settings should be enabled on every production SQL Server instance. Neither is the default.

-- Enable backup compression and checksums at instance level
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- SQL Server 2022: set ZSTD as default compression algorithm
-- Options: MS_XPRESS (legacy default), QAT_DEFLATE (Intel QAT hardware), ZSTD (recommended)
EXEC sp_configure 'backup compression algorithm', 2;   -- 2 = ZSTD
RECONFIGURE;

-- Enable backup checksum default
-- Note: this is a startup parameter, not sp_configure
-- Add -T3023 as a startup parameter in SQL Server Configuration Manager
-- OR: specify WITH CHECKSUM explicitly in every backup job (preferred for explicit control)

-- Verify current backup settings
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('backup compression default','backup compression algorithm');

-- Verify a backup has checksums:
RESTORE VERIFYONLY FROM DISK = 'D:\Backups\MyDB_FULL.bak'
WITH CHECKSUM;

8 Remote Admin Connection (DAC)

The Dedicated Administrator Connection provides an emergency back-channel to SQL Server when the main connection pool is saturated and normal connections are being refused. Without the DAC enabled for remote access, you can only use it from the server console — which is often impractical in a data center or cloud environment when the server is unresponsive.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;

-- Connect via DAC from SSMS:
-- Connection string prefix: ADMIN:ServerName
-- Or in SSMS query window: ADMIN:ServerName\InstanceName

-- Verify
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'remote admin connections';

9 TempDB File Count and Sizing

TempDB is a shared system database used by every database on the instance for temporary tables, table variables, sort operations, hash joins, index builds, version stores, and spills from insufficient memory grants. Contention on TempDB allocation pages (PFS, GAM, SGAM) has historically been one of the most common sources of PAGELATCH_UP and PAGELATCH_EX waits under high concurrency.

SQL Server 2022 made further improvements to GAM and SGAM concurrent allocation beyond what was introduced in SQL Server 2016 and 2019. Even with these improvements, multiple equally-sized data files remain the correct configuration.

File Count Rules

• If the server has 8 or fewer logical processors: create the same number of TempDB data files as logical processors.
• If the server has more than 8 logical processors: start with 8 TempDB data files.
• If you still see PAGELATCH_UP or PAGELATCH_EX waits on TempDB pages after configuring 8 equal files: add more in increments of 4, up to the number of logical processors.
• Only ever have one TempDB log file — SQL Server does not parallelize writes across multiple log files.

-- Check current TempDB configuration
SELECT
    name,
    type_desc,
    CAST(size/128.0 AS DECIMAL(10,2))         AS [Current Size (MB)],
    CAST(max_size/128.0 AS DECIMAL(10,2))     AS [Max Size (MB)],
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS VARCHAR(5)) + '%'
        ELSE CAST(growth/128 AS VARCHAR(10))  + ' MB'
    END                                       AS [Growth Setting],
    physical_name
FROM tempdb.sys.database_files
ORDER BY type, file_id;

-- Add equally-sized TempDB data files (example: adding files 2-8)
-- Replace paths and sizes to match your environment
USE [master];
GO
ALTER DATABASE tempdb ADD FILE
    (NAME = N'tempdev2', FILENAME = N'T:\TempDB\tempdb2.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev3', FILENAME = N'T:\TempDB\tempdb3.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev4', FILENAME = N'T:\TempDB\tempdb4.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev5', FILENAME = N'T:\TempDB\tempdb5.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev6', FILENAME = N'T:\TempDB\tempdb6.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev7', FILENAME = N'T:\TempDB\tempdb7.ndf', SIZE = 1024MB, FILEGROWTH = 256MB),
    (NAME = N'tempdev8', FILENAME = N'T:\TempDB\tempdb8.ndf', SIZE = 1024MB, FILEGROWTH = 256MB);
GO

-- Resize the original file to match if needed
ALTER DATABASE tempdb MODIFY FILE
    (NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 256MB);

10 TempDB Placement and Autogrowth

TempDB should always be on its own dedicated storage volume, separate from user database data files, user database log files, and backups. This has two benefits: it isolates the I/O load from your business-critical databases, and if TempDB ever runs out of space (which happens), it does not take down the volume that holds your user data and logs with it.

Pre-size TempDB generously. A common starting guideline is to allocate at least 25% of the instance’s total user data size to TempDB storage. This is a starting point, not a ceiling — monitor actual usage over time using sys.dm_db_session_space_usage and adjust accordingly.

-- Monitor current TempDB space consumption
SELECT
    DB_NAME(database_id)            AS [Database],
    reserved_page_count             AS [Version Store Pages],
    reserved_space_kb / 1024        AS [Version Store (MB)]
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_space_kb DESC;

-- Per-session TempDB usage
SELECT
    session_id,
    (internal_objects_alloc_page_count  * 8) / 1024 AS [Internal Objects (MB)],
    (user_objects_alloc_page_count      * 8) / 1024 AS [User Objects (MB)],
    (internal_objects_dealloc_page_count * 8) / 1024 AS [Internal Dealloc (MB)]
FROM sys.dm_db_session_space_usage
WHERE internal_objects_alloc_page_count + user_objects_alloc_page_count > 0
ORDER BY internal_objects_alloc_page_count DESC;

-- TempDB PAGELATCH contention check
-- If these waits are high, add more data files in groups of 4
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms / NULLIF(waiting_tasks_count,0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGELATCH_UP','PAGELATCH_EX','PAGELATCH_SH')
ORDER BY wait_time_ms DESC;

11 Recovery Model and Log Backups

Every production database that cannot afford data loss should be in Full recovery model. Simple recovery model truncates the transaction log at every checkpoint — it is appropriate only for development, test, or databases where losing all changes since the last full backup is acceptable. Bulk-Logged recovery is a narrow middle option for specific ETL scenarios, not a general-purpose setting.

A database in Full recovery model with no log backups scheduled is in the worst of both worlds: it gets none of the benefits of Full recovery (point-in-time restore), but the transaction log grows indefinitely because it cannot be truncated without a log backup. Check the log_reuse_wait_desc column in sys.databases — a value of LOG_BACKUP on a Full recovery database with no log backup job is a problem waiting to happen.

-- Check recovery models and log reuse wait for all databases
SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc,
    CAST(CAST(lu.cntr_value AS FLOAT)/CAST(ls.cntr_value AS FLOAT)*100 AS DECIMAL(5,1))
        AS [Log Used %]
FROM sys.databases db
LEFT JOIN sys.dm_os_performance_counters lu
    ON db.name = lu.instance_name AND lu.counter_name LIKE 'Log File(s) Used Size%'
LEFT JOIN sys.dm_os_performance_counters ls
    ON db.name = ls.instance_name AND ls.counter_name LIKE 'Log File(s) Size (KB)%'
WHERE db.name NOT IN ('tempdb','master','model','msdb')
AND ls.cntr_value > 0
ORDER BY recovery_model_desc, [Log Used %] DESC;

-- Set a database to Full recovery
ALTER DATABASE [YourDatabase] SET RECOVERY FULL;

-- Log backup frequency guidance:
-- RPO 15 minutes → log backup every 15 minutes
-- RPO 1 hour     → log backup every hour
-- RPO = 0        → Availability Groups with synchronous commit
-- Ola Hallengren's solution handles log backups correctly for AG environments

12 File Growth Settings and VLF Management

Percentage-based file autogrowth is one of the most common and most damaging SQL Server misconfigurations. A 10% growth event on a 100 GB database is a 10 GB growth stall. A 10% event on a 1 TB database is a 100 GB stall that blocks all user activity while zeros are written to disk (or IFI kicks in for data files). For transaction log files, percentage growth generates a number of new Virtual Log Files (VLFs) proportional to the growth increment size — percentage growth on a large log produces hundreds of new VLFs per event and leads to VLF counts in the thousands.

VLF Count Impact

High VLF counts slow every operation that needs to scan the log: full database restores, crash recovery after an unexpected shutdown, log shipping, and Availability Group redo operations. A database with 10,000 VLFs may take minutes to come online after a restart where one with 50 VLFs takes seconds.

VLF Count	Assessment
Under 200	Good for most databases
200–500	Monitor — investigate if log is large
500–1,000	Elevated — consider log file resizing
Over 1,000	High — plan remediation
Over 5,000	Critical — impacts restore and crash recovery time

-- Check VLF counts across all databases
SELECT db.[name] AS [Database], li.[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
    SELECT COUNT(*) AS [VLF Count]
    FROM sys.dm_db_log_info(db.database_id)
) AS li
ORDER BY li.[VLF Count] DESC;

-- Fix percentage-based growth (change ALL files — check each database)
USE [YourDatabase];
GO

-- Set data files to fixed 512 MB growth
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = N'YourDatabase', FILEGROWTH = 512MB);

-- Set log file to fixed 64 MB growth (aligns with SQL 2022 IFI optimization)
ALTER DATABASE [YourDatabase]
MODIFY FILE (NAME = N'YourDatabase_log', FILEGROWTH = 64MB);

-- Remediate high VLF count:
-- Step 1: Back up the log
-- Step 2: Shrink the log file (one-time only — shrink is not normal maintenance)
-- Step 3: Pre-grow the log to its target size in one operation using a large fixed increment
-- This re-creates VLFs at the correct size

-- VLF growth formula for SQL Server 2022:
-- Growth < 1/8 current log size   → 1 new VLF
-- Growth up to 64 MB              → 1 new VLF
-- Growth 64 MB to 1 GB            → 8 new VLFs
-- Growth over 1 GB                → 16 new VLFs
-- Pre-growing a 10 GB log in one 10 GB operation = 160 VLFs (16 per GB × 10)
-- Pre-growing the same log in 512 MB increments = much higher VLF count

13 Compatibility Level

The database compatibility level controls which query optimizer behaviors, T-SQL syntax rules, and Intelligent Query Processing features are active. SQL Server 2022 native compatibility level is 160. Databases running at older compatibility levels miss out on cardinality estimator improvements, parameter-sensitive plan optimization, DOP feedback, memory grant feedback, and the full set of intelligent query processing features introduced in SQL Server 2019 and 2022.

Upgrading the compatibility level is the most impactful database-level change you can make on a migrated database. It requires testing — in rare cases, a query that performed well under the older cardinality estimator degrades under the new one. Use Query Store to capture baseline plans before the change and identify any regressions afterward.

-- Check compatibility levels across all user databases
SELECT name, compatibility_level,
    CASE compatibility_level
        WHEN 160 THEN 'SQL Server 2022 (current)'
        WHEN 150 THEN 'SQL Server 2019'
        WHEN 140 THEN 'SQL Server 2017'
        WHEN 130 THEN 'SQL Server 2016'
        WHEN 120 THEN 'SQL Server 2014'
        ELSE 'Older — needs attention'
    END AS [Version Equivalent]
FROM sys.databases
WHERE database_id > 4
ORDER BY compatibility_level;

-- Set to SQL Server 2022 native
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;

-- If a query degrades after the change, you can force the old plan via Query Store
-- or use database-scoped config to use legacy CE without changing compat level:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;  -- last resort; test compat 160 first

14 Page Verify, Auto Shrink, Auto Close

PAGE_VERIFY CHECKSUM

SQL Server can validate every data page it writes to disk by calculating a checksum and storing it in the page header. When the page is read back, the checksum is recalculated and compared. A mismatch means the page was corrupted on disk or in transit — SQL Server raises error 824 and records the page in the suspect pages table.

Without CHECKSUM, a corrupted page can silently return wrong data to applications for weeks before anyone notices. TORN_PAGE detection catches a narrower class of corruption. NONE means no integrity validation whatsoever. All production databases should use CHECKSUM.

Auto Shrink — Never Enable

Auto Shrink periodically shrinks database files back toward their used space. It causes index fragmentation, generates VLFs when it shrinks log files, competes with user workload for I/O, and then forces SQL Server to regrow the files again when data is written — a cycle that wastes I/O continuously. There is no valid production use case for Auto Shrink. Shrinking a database file should be a deliberate, one-time DBA action, never an automated background process.

Auto Close — Never Enable

Auto Close shuts down the database and releases all resources when the last user disconnects. On the next connection, SQL Server must re-open the database, reload caches, recompile plans, and recheck integrity. This overhead appears every time the database transitions from idle to active. It was designed for single-user desktop databases. On any production server, Auto Close should be off.

-- Audit all databases for these settings
SELECT
    name,
    page_verify_option_desc,
    is_auto_shrink_on,   -- should be 0
    is_auto_close_on,    -- should be 0
    is_auto_create_stats_on,
    is_auto_update_stats_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

-- Fix all three on a database
ALTER DATABASE [YourDatabase] SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE [YourDatabase] SET AUTO_SHRINK OFF;
ALTER DATABASE [YourDatabase] SET AUTO_CLOSE OFF;

-- Generate fix script for all user databases where needed
SELECT
    'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM;' AS fix_page_verify
FROM sys.databases
WHERE database_id > 4
AND page_verify_option_desc <> 'CHECKSUM'
UNION ALL
SELECT 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF;'
FROM sys.databases WHERE database_id > 4 AND is_auto_shrink_on = 1
UNION ALL
SELECT 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF;'
FROM sys.databases WHERE database_id > 4 AND is_auto_close_on = 1;

15 Indirect Checkpoint and Target Recovery Time

SQL Server’s checkpoint mechanism flushes dirty pages from the buffer pool to disk so that crash recovery after an unexpected shutdown has less work to redo. Indirect checkpoint was introduced in SQL Server 2012 and became the default for new databases in SQL Server 2016 (inherited from the model database setting of 60 seconds).

With indirect checkpoint enabled and TARGET_RECOVERY_TIME set to 60 seconds, SQL Server maintains a steady trickle of checkpoint I/O rather than firing large spike checkpoints on a fixed interval. This makes I/O more predictable, reduces checkpoint-induced latency spikes, and shortens crash recovery time. The default of 0 seconds uses the older automatic checkpoint behavior that relies on a fixed log record count rather than dirty page tracking — less predictable and often less efficient.

-- Check target recovery time for all databases
SELECT name, target_recovery_time_in_seconds
FROM sys.databases
WHERE database_id > 4
ORDER BY name;
-- 0 = automatic checkpoint (legacy)
-- 60 = indirect checkpoint at 60 seconds (recommended default from model)

-- Set for an individual database
ALTER DATABASE [YourDatabase]
SET TARGET_RECOVERY_TIME = 60 SECONDS;

-- Set on the model database so all new databases inherit it
ALTER DATABASE model
SET TARGET_RECOVERY_TIME = 60 SECONDS;

-- Generate fix script for databases still at 0
SELECT 'ALTER DATABASE [' + name + '] SET TARGET_RECOVERY_TIME = 60 SECONDS;'
FROM sys.databases
WHERE database_id > 4
AND target_recovery_time_in_seconds = 0;

16 Query Store

Query Store captures query plans, runtime statistics, and execution history for every query that runs against a database. It is the foundation for plan regression detection, forced plan management, DOP Feedback, Memory Grant Feedback, and Parameter Sensitive Plan Optimization in SQL Server 2022. In SQL Server 2022, Query Store is enabled on all user databases by default — but it still needs to be sized and configured appropriately.

-- Audit Query Store status across all user databases
SELECT
    name,
    is_query_store_on,
    qso.actual_state_desc,
    qso.desired_state_desc,
    qso.current_storage_size_mb,
    qso.max_storage_size_mb,
    qso.query_capture_mode_desc,
    qso.wait_stats_capture_mode_desc
FROM sys.databases db
LEFT JOIN sys.database_query_store_options qso
    ON db.database_id = qso.database_id  -- must run in the context of each DB or use dynamic SQL
WHERE db.database_id > 4
ORDER BY name;

-- Configure Query Store on a database
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(
    OPERATION_MODE            = READ_WRITE,
    CLEANUP_POLICY            = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB       = 1000,
    QUERY_CAPTURE_MODE        = AUTO,    -- AUTO = captures significant queries only
    WAIT_STATS_CAPTURE_MODE   = ON
);

-- Emergency shutoff (SQL Server 2019 CU6+):
-- ALTER DATABASE [YourDatabase] SET QUERY_STORE = OFF(FORCED);

-- Check if Query Store has filled to capacity (current = max = problem)
SELECT DB_NAME(database_id), current_storage_size_mb, max_storage_size_mb
FROM sys.databases d
CROSS APPLY sys.dm_database_query_store_options o  -- use per-db context
WHERE current_storage_size_mb >= max_storage_size_mb * 0.95;

17 Read Committed Snapshot Isolation (RCSI)

By default, SQL Server uses a pessimistic locking model — readers and writers block each other. A SELECT holding a shared lock blocks an UPDATE on the same row, and vice versa. In high-concurrency OLTP environments, this causes blocking chains that degrade throughput significantly.

RCSI switches the default isolation level for the database to an optimistic model using row versioning. Readers no longer block writers, and writers no longer block readers. Instead of blocking, SQL Server serves readers a consistent snapshot of the data from immediately before the modification began — stored in the TempDB version store.

The TempDB version store consumption from RCSI is frequently overstated as a concern. In practice, it is modest for most OLTP workloads and the reduction in blocking and deadlocking far outweighs it. Test on a non-production system first, particularly if the application uses any explicit locking hints that assume pessimistic behavior.

-- Check current isolation state for all databases
SELECT
    name,
    snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

-- Enable RCSI on a database
-- NOTE: briefly disconnects all users — run during a maintenance window
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [YourDatabase] SET MULTI_USER;

-- Verify
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDatabase';

18 Authentication Mode and the sa Account

SQL Server supports two authentication modes: Windows Authentication only, and Mixed Mode (Windows + SQL logins). Windows Authentication is more secure because it leverages Active Directory, Kerberos, and the full Windows security stack — no passwords stored in SQL Server, no risk of SQL-specific credential stuffing attacks. Mixed Mode is required when Windows authentication is not available for an application, but every SQL login it enables is an additional attack surface.

The sa account is the most targeted login in SQL Server. It has unrestricted sysadmin access and cannot be renamed or dropped, only disabled. On any server running in Mixed Mode, the sa account should be disabled unless there is a specific documented reason it must be active. If it must remain enabled, rename it to something non-obvious and use a strong password with CHECK_POLICY = ON.

-- Check current authentication mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Windows Auth Only];
-- 1 = Windows Authentication only
-- 0 = Mixed Mode (SQL logins enabled)

-- Check the sa account status
SELECT name, is_disabled, LOGINPROPERTY(name,'IsExpired') AS [Password Expired]
FROM sys.server_principals
WHERE name = 'sa';

-- Disable sa if not required
ALTER LOGIN [sa] DISABLE;

-- If sa must remain enabled: rename and force password policy
ALTER LOGIN [sa] WITH NAME = [sql_sa_disabled];
ALTER LOGIN [sql_sa_disabled] WITH PASSWORD = 'Use-A-Long-Random-Passphrase-Here',
    CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

-- Audit all sysadmin members
SELECT sp.name AS [Login], sp.type_desc, sp.is_disabled,
       'sysadmin' AS [Role]
FROM sys.server_role_members rm
JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
JOIN sys.server_principals r  ON rm.role_principal_id  = r.principal_id
WHERE r.name = 'sysadmin'
ORDER BY sp.name;

-- Audit all SQL logins (should be minimal on Windows Auth preferred environments)
SELECT name, is_disabled, create_date, modify_date,
       LOGINPROPERTY(name,'IsExpired')  AS [Expired],
       LOGINPROPERTY(name,'IsMustChange') AS [MustChange]
FROM sys.server_principals
WHERE type = 'S'  -- SQL login
AND name NOT IN ('sa','##MS_PolicyTsqlExecutionLogin##','##MS_AgentSigningCertificate##')
ORDER BY name;

19 Encryption: TDE, TLS, and Always Encrypted

SQL Server 2022 provides three distinct encryption mechanisms that operate at different layers and protect different threat vectors. Understanding which applies to your compliance requirements is essential.

TDE — Transparent Data Encryption

TDE encrypts the physical database, backup, and TempDB files at the OS level. If a drive, backup tape, or storage array is removed and attached to another system, the data cannot be read without the TDE certificate. The performance overhead is typically 3–5% on modern hardware with AES-NI CPU instructions. TDE is transparent to applications — no code changes required. SQL Server 2022 supports hardware-accelerated TDE via Intel QAT.

-- Check TDE status across all databases
SELECT
    db.name,
    db.is_encrypted,
    dek.encryption_state_desc,
    dek.percent_complete,
    dek.key_algorithm,
    dek.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek
    ON db.database_id = dek.database_id
WHERE db.database_id > 4
ORDER BY db.name;

-- Enable TDE on a database (requires master key and certificate first)
USE master;
GO
-- Step 1: Create master key (if not already exists)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Use-A-Strong-Random-Password-Here';

-- Step 2: Create TDE certificate
CREATE CERTIFICATE TDE_Cert
WITH SUBJECT = 'TDE Certificate for Production';

-- Step 3: Back up the certificate IMMEDIATELY -- losing this = losing access to the database
BACKUP CERTIFICATE TDE_Cert
TO FILE = 'D:\CertBackups\TDE_Cert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\CertBackups\TDE_Cert_Key.pvk',
    ENCRYPTION BY PASSWORD = 'Use-A-Strong-Password-For-Cert-Backup'
);

-- Step 4: Enable TDE on the target database
USE [YourDatabase];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;
GO

Connection Encryption — TDS 8.0 and TLS 1.3

SQL Server 2022 introduced TDS 8.0, which requires all connections to use TLS 1.3 when configured for strict encryption. TLS 1.2 remains supported for compatibility. Verify all client connections are encrypted.

-- Verify all active connections are encrypted
SELECT
    session_id,
    encrypt_option,
    auth_scheme,
    net_transport,
    client_net_address
FROM sys.dm_exec_connections
WHERE session_id > 50  -- skip system sessions
ORDER BY encrypt_option;
-- Any row with encrypt_option = 'FALSE' is a compliance concern

20 Permissions, Roles, and Least Privilege

The principle of least privilege means every login, user, and application account has exactly the permissions it needs and nothing more. Sysadmin-level access for application service accounts is the single most common SQL Server security misconfiguration and the one with the highest blast radius when exploited.

-- Audit sysadmin logins
SELECT sp.name, sp.type_desc, sp.is_disabled
FROM sys.server_role_members rm
JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
JOIN sys.server_principals r  ON rm.role_principal_id  = r.principal_id
WHERE r.name = 'sysadmin'
ORDER BY sp.is_disabled, sp.name;

-- Find database users with db_owner membership
-- Run in each database context
SELECT dp.name AS [User], dp.type_desc
FROM sys.database_role_members rm
JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
JOIN sys.database_principals r  ON rm.role_principal_id  = r.principal_id
WHERE r.name = 'db_owner'
ORDER BY dp.name;

-- Application service accounts should use a custom role with only what they need:
-- Example: read-only reporting account
CREATE ROLE [rpt_readonly];
GRANT SELECT ON SCHEMA::dbo TO [rpt_readonly];
EXEC sp_addrolemember 'rpt_readonly', 'ReportingServiceAccount';

-- Check all explicit object permissions granted
SELECT
    dp.class_desc,
    OBJECT_NAME(dp.major_id) AS [Object],
    pr.name                  AS [Grantee],
    dp.permission_name,
    dp.state_desc
FROM sys.database_permissions dp
JOIN sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
WHERE dp.major_id > 0
AND pr.name NOT IN ('dbo','public','INFORMATION_SCHEMA','sys')
ORDER BY pr.name, OBJECT_NAME(dp.major_id);

21 SQL Server Audit and Alerting

SQL Server Agent alerts for high-severity errors provide immediate notification when SQL Server encounters problems that require DBA attention. Severities 19–25 indicate resource errors and internal SQL Server failures. Severities 16–18 are user-correctable errors but may indicate problems worth tracking. Error numbers 823, 824, and 825 indicate I/O-level database corruption and must be acted on immediately.

-- Create severity-based alerts (requires SQL Agent and an operator configured)
-- First, create an operator to receive notifications
USE msdb;
GO
EXEC sp_add_operator
    @name = N'DBA Team',
    @email_address = N'dba-team@yourcompany.com',
    @enabled = 1;

-- Alert for severity 19-25 (resource/fatal errors)
EXEC sp_add_alert
    @name          = N'Severity 19-25 Error',
    @severity      = 19,  -- repeat for 20-25
    @enabled       = 1,
    @delay_between_responses = 300,  -- 5 minutes
    @include_event_description_in = 1;

EXEC sp_add_notification
    @alert_name    = N'Severity 19-25 Error',
    @operator_name = N'DBA Team',
    @notification_method = 1;  -- email

-- Alert for error 823 (I/O error)
EXEC sp_add_alert
    @name        = N'Error 823 - IO Error',
    @message_id  = 823,
    @enabled     = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1;

-- Alert for error 824 (page corruption)
EXEC sp_add_alert
    @name        = N'Error 824 - Page Corruption',
    @message_id  = 824,
    @enabled     = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1;

-- Alert for error 825 (read retry)
EXEC sp_add_alert
    @name        = N'Error 825 - Read Retry',
    @message_id  = 825,
    @enabled     = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1;

-- Verify alerts are configured
SELECT name, severity, message_id, enabled,
       last_occurrence_date, last_occurrence_time,
       occurrence_count
FROM msdb.dbo.sysalerts
ORDER BY name;

22 Backup Strategy and Verification

A backup that has never been tested for restore is not a backup — it is a file that may or may not be restorable. The backup strategy below covers all recovery scenarios.

Recommended Backup Cadence

Backup Type	Frequency	Purpose
Full	Daily or weekly	Baseline for all restores
Differential	Every 4–12 hours	Reduces log chain needed for restore
Transaction Log	Every 15–60 minutes	Point-in-time recovery within RPO
CHECKDB	Weekly (user DBs), daily (system DBs)	Corruption detection
Restore Test	Monthly minimum, quarterly at minimum	Verify backup is actually usable

-- Backup with compression, checksum, and STATS for large databases
BACKUP DATABASE [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase_FULL.bak'
WITH COMPRESSION,
     CHECKSUM,
     STATS = 10,
     FORMAT,  -- overwrite existing media
     INIT;

-- Transaction log backup
BACKUP LOG [YourDatabase]
TO DISK = N'D:\Backups\YourDatabase_LOG.bak'
WITH COMPRESSION,
     CHECKSUM,
     STATS = 10;

-- Verify a backup without restoring it
RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\YourDatabase_FULL.bak'
WITH CHECKSUM;

-- Check last backup history for all databases (from SQLYARD Health Check Toolkit Query 8)
SELECT
    name,
    recovery_model_desc,
    DATABASEPROPERTYEX(name,'LastGoodCheckDbTime') AS [Last CheckDB]
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

-- Use Ola Hallengren's maintenance solution for production backup jobs:
-- https://ola.hallengren.com
-- Handles AG databases, backup verification, cleanup, and logging automatically

23 Index and Statistics Maintenance

Index fragmentation and out-of-date statistics are two of the most common causes of query plan regressions and performance degradation over time. Fragmentation above 30% warrants a rebuild; between 5–30%, a reorganize is sufficient. Statistics should be updated whenever a table’s data changes significantly — the auto-update statistics threshold in SQL Server 2016+ is dynamic (roughly the square root of row count), which helps for large tables but not all workloads.

-- Check index fragmentation for the current database
-- This query can be slow on very large databases — use LIMITED mode
SELECT
    SCHEMA_NAME(o.schema_id)                                    AS [Schema],
    OBJECT_NAME(ps.object_id)                                   AS [Table],
    i.name                                                       AS [Index],
    ps.index_type_desc,
    CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,1))        AS [Frag %],
    ps.page_count,
    i.fill_factor
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes  i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
JOIN sys.objects  o ON i.object_id  = o.object_id
WHERE ps.database_id = DB_ID()
AND ps.page_count > 500
ORDER BY ps.avg_fragmentation_in_percent DESC;
------
-- > 30%: REBUILD (or REORGANIZE if REBUILD is not possible online in Standard Edition)
-- 5-30%: REORGANIZE
-- < 5%:  No action needed

-- Check statistics age and modification counts
SELECT
    SCHEMA_NAME(o.schema_id) + '.' + o.name   AS [Object],
    s.name                                     AS [Statistic],
    STATS_DATE(s.object_id, s.stats_id)        AS [Last Updated],
    sp.modification_counter,
    sp.rows,
    sp.rows_sampled,
    CAST(sp.rows_sampled * 100.0 / NULLIF(sp.rows,0) AS DECIMAL(5,1)) AS [Sample %]
FROM sys.stats s
JOIN sys.objects o ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.type = 'U'
AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

-- For production maintenance: use Ola Hallengren's IndexOptimize
-- https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
-- It handles fragmentation thresholds, online/offline mode, AG replicas, and logging

24 Integrity Checks (CHECKDB)

DBCC CHECKDB is the only way to confirm that a database has no physical corruption. It verifies page checksums, structural consistency, index integrity, and cross-object relationships. A database that has never had CHECKDB run against it may have corruption that has been silently growing for months. By the time it manifests as an error, the backup chain may also be corrupted — meaning a clean restore is impossible.

The question is not whether to run CHECKDB, but how to schedule it within your maintenance window. On very large databases where a full CHECKDB takes too long, break it into pieces using PHYSICAL_ONLY for daily runs and a full CHECKDB weekly or monthly.

-- Check the last successful CHECKDB for all databases
SELECT
    name,
    DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS [Last Good CheckDB],
    DATEDIFF(day,
        CAST(DATABASEPROPERTYEX(name,'LastGoodCheckDbTime') AS DATETIME),
        GETDATE())                                   AS [Days Since Last CheckDB]
FROM sys.databases
WHERE database_id > 4
ORDER BY [Days Since Last CheckDB] DESC;

-- Run CHECKDB with NO_INFOMSGS to suppress informational output
-- Run during a low-activity window — can be resource-intensive
DBCC CHECKDB ([YourDatabase])
WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- For large databases: PHYSICAL_ONLY for faster daily run
-- (checks structural integrity and checksums but skips logical checks)
DBCC CHECKDB ([YourDatabase])
WITH PHYSICAL_ONLY, NO_INFOMSGS;

-- For Ola Hallengren: schedule DatabaseIntegrityCheck
-- @Databases = 'USER_DATABASES'
-- @CheckCommands = 'CHECKDB'
-- Recommended: weekly for all user databases, daily for system databases

25 Windows Power Plan, NTFS Allocation Units, and AV Exclusions

Windows Power Plan

Windows Server defaults to the Balanced power plan, which parks idle CPU cores and reduces processor clock speeds to save power. For a database server, this means queries that arrive while cores are parked may experience additional latency while the OS spins cores back up. Set the power plan to High Performance on all SQL Server hosts. This applies to physical servers and VMs — the power plan controls what happens inside Windows regardless of the underlying hypervisor.

-- Check current power plan from T-SQL via xp_cmdshell (if enabled)
-- Alternatively: Control Panel → Power Options → High Performance

-- From PowerShell (run as Administrator on the SQL Server host):
-- powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c
-- (8c5e7fda... is the GUID for High Performance)

-- Verify from PowerShell:
-- powercfg /getactivescheme

NTFS Allocation Unit Size — 64 KB

SQL Server reads and writes data in 8-page extents — 64 KB of data at a time. The default Windows NTFS allocation unit size is 4 KB. When SQL Server writes a 64 KB extent to a 4 KB allocation unit volume, Windows breaks that write into 16 separate 4 KB allocation operations internally. Formatting SQL Server volumes with 64 KB allocation units aligns the storage layer with SQL Server’s I/O pattern, reducing allocation overhead and improving throughput.

This must be set at format time — you cannot change the allocation unit size without reformatting the volume. Plan this during initial server setup before any files are created. All SQL Server volumes should use 64 KB: data files, log files, TempDB, and backups.

Antivirus Exclusions

Real-time antivirus scanning of SQL Server files causes I/O stalls. Every time SQL Server writes a page to disk, the AV engine intercepts the write to inspect the data — introducing latency into the critical path of every database write. This manifests as elevated write latency and unexplained I/O stalls that disappear when AV is disabled.

-- File extensions and paths to exclude from real-time AV scanning:
-- *.mdf   -- Primary data files
-- *.ndf   -- Secondary data files
-- *.ldf   -- Transaction log files
-- *.bak   -- Full and differential backup files
-- *.trn   -- Transaction log backup files
-- *.trc   -- SQL trace files
-- *.xel   -- Extended Events files
-- SQL Server binary directories:
--   C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\
--   C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\LOG\
--   C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\
--   TempDB volume path
-- Process exclusions (exclude the process, not just the folder):
--   sqlservr.exe
--   sqlagent.exe
--   sqlbrowser.exe

-- Reference: https://support.microsoft.com/help/309422

References

• Microsoft Docs — Server Memory Configuration Options (max server memory)
• Microsoft Docs — Configure Max Degree of Parallelism
• Microsoft Docs — tempdb Database
• Microsoft Docs — Database Instant File Initialization
• Microsoft Docs — SQL Server Security Best Practices
• Microsoft Docs — Transparent Data Encryption (TDE)
• Microsoft Docs — Query Store Overview
• Microsoft Docs — Database Properties (Options Page) — Page Verify, Auto Shrink, Auto Close
• Microsoft SQL Server Team — Indirect Checkpoint and TempDB
• Brent Ozar — Microsoft Now Recommends 75% Max Memory
• Erik Darling — Setting MAXDOP and Cost Threshold for Parallelism
• SQLYARD — SQL Server TempDB Files: How Many, Why, and When to Add More
• Ola Hallengren — SQL Server Maintenance Solution (backup, integrity, index jobs)
• Microsoft — Antivirus Exclusions for SQL Server
• SQLYARD — SQL Server 2022 DBA Health Check Toolkit