SQL Server Configuration

🧠

Max Server Memory

Limits SQL Server memory usage — the default value is dangerous

Critical

The default value is 2,147,483,647 MB (≈ unlimited) — SQL Server can consume all server memory, leaving too little for the OS. This causes server freezes, instability, and crashes.

Rule of thumb: Leave the OS at least max(4 GB, 10% of RAM). The rest can be allocated to SQL Server.

Total RAM	OS Reserve	Max Server Memory
8 GB	2 GB	6 144 MB
16 GB	4 GB	12 288 MB
32 GB	4 GB	28 672 MB
64 GB	6.4 GB	59 187 MB
128 GB	12.8 GB	116 531 MB
256 GB	25.6 GB	235 110 MB

-- !! Change value according to your server !!
-- Example: 32 GB RAM server -> max 28 672 MB for SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

SELECT name, value_in_use, description
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

⚠

If the server runs SSRS, SSAS, IIS etc., leave more memory for them. The Health Check tool will flag if Max Server Memory is unconfigured.

📌

Lock Pages in Memory (LPIM)

Prevents the OS from paging SQL Server memory to disk

Info

LPIM prevents Windows from paging (swapping) the SQL Server buffer pool to disk. Especially useful with large amounts of RAM and Enterprise Edition. Requires "Lock pages in memory" right for the service account.

-- Check if LPIM is active
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
-- Results: CONVENTIONAL | LOCK_PAGES | LARGE_PAGES

ℹ

To enable LPIM: secpol.msc → Local Policies → User Rights Assignment → Lock pages in memory → Add SQL service account. Then restart the SQL Server service.

⚡

MAXDOP (Max Degree of Parallelism)

Limits the number of processors used for a single query

Warning

Default value 0 = unlimited — a single query can use all CPU cores, blocking other queries. This causes CXPACKET waits and uneven performance.

CPU cores per NUMA node	Recommended MAXDOP
≤ 8 cores	= number of cores
8–16 cores	8
> 16 cores	cores / 2, max 16
OLTP workload	1–4 (short queries)
DWH/Analytical	8–16

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

-- Change value according to your CPU cores
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

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

💡

SQL Server 2019+ can set MAXDOP automatically during installation. Always check sys.dm_os_sys_info: cpu_count and socket_count to know the physical core count.

🎯

Cost Threshold for Parallelism (CTP)

The minimum cost at which SQL Server uses parallelism

Warning

The default value is 5 — meaning SQL Server starts using parallelism for very cheap queries. This causes excessive parallelism for short queries. Recommended range: 40–75.

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

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

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

🗄️

TempDB Configuration

File count, size, and location

Warning

TempDB is a shared resource for all users — improper configuration causes PAGELATCH waits and performance issues. Simplest approach: always use 8 files — works well for the vast majority of servers. All files must be equally sized. If there is no PAGELATCH_EX contention, no changes are needed.

Setting	Recommendation
Data file count	8
Equally sized	Yes
Autogrowth	MB-based (e.g. 512 MB)
Log files	Exactly 1
Location	Separate fast drive (e.g. T:\TempDB\)

-- Note: TempDB changes require SQL Server restart
SELECT name, physical_name, size/128 AS size_mb,
       max_size, is_percent_growth, growth
FROM sys.master_files
WHERE database_id = 2;

ALTER DATABASE tempdb MODIFY FILE (
    NAME = tempdev,
    FILENAME = 'T:\TempDB\tempdb.mdf',
    SIZE = 1024MB,
    FILEGROWTH = 512MB
);

ALTER DATABASE tempdb MODIFY FILE (
    NAME = templog,
    FILENAME = 'T:\TempDB\templog.ldf',
    SIZE = 512MB,
    FILEGROWTH = 256MB
);

-- Add additional data files (e.g. total 4 files)
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev2, FILENAME = 'T:\TempDB\tempdb2.ndf',
    SIZE = 1024MB, FILEGROWTH = 512MB
);
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev3, FILENAME = 'T:\TempDB\tempdb3.ndf',
    SIZE = 1024MB, FILEGROWTH = 512MB
);
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev4, FILENAME = 'T:\TempDB\tempdb4.ndf',
    SIZE = 1024MB, FILEGROWTH = 512MB
);

⚠

Trace Flags 1117 and 1118 are enabled by default in SQL Server 2016+. For older versions, add to startup parameters: -T1117 -T1118

How to check if 8 files are enough?
Run the script below during peak load. If PAGELATCH_EX waits are absent or very low — 8 files are sufficient. If contention is high, add 4 files at a time and re-check.

-- Check TempDB PAGELATCH contention
-- Run during peak load
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

SELECT
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall,
    size_on_disk_bytes / 1024 / 1024 AS size_mb
FROM sys.dm_io_virtual_file_stats(2, NULL)
ORDER BY io_stall DESC;

📈

Autogrowth Settings

%-based growth is dangerous — always use MB-based

Warning

By default, SQL Server uses percent-based autogrowth — for a large file, it can grow by hundreds of GBs at once, causing long blocks. Always use a fixed MB value.

-- Find files with %-based growth
SELECT
    DB_NAME(database_id)        AS db_name,
    name                        AS file_name,
    physical_name,
    size * 8 / 1024             AS size_mb,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS VARCHAR) + '% !'
        ELSE CAST(growth * 8 / 1024 AS VARCHAR) + ' MB'
    END                         AS autogrowth,
    is_percent_growth
FROM sys.master_files
WHERE is_percent_growth = 1
  AND database_id > 4
ORDER BY DB_NAME(database_id);

ALTER DATABASE [MyDatabase] MODIFY FILE (
    NAME = N'MyDB_data',
    FILEGROWTH = 512MB
);
ALTER DATABASE [MyDatabase] MODIFY FILE (
    NAME = N'MyDB_log',
    FILEGROWTH = 256MB
);

🚫

Auto-Shrink and Auto-Close

Disable immediately — both cause major performance issues

Critical

Auto-Shrink automatically shrinks files — then the file grows again via autogrowth. This cycle causes massive fragmentation and blocks other operations.
Auto-Close closes the database when there are no connections — reopening it takes time and degrades performance.

SELECT name,
       is_auto_shrink_on  AS auto_shrink,
       is_auto_close_on   AS auto_close
FROM sys.databases
WHERE is_auto_shrink_on = 1 OR is_auto_close_on = 1;

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF;' + CHAR(13)
             + 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF;'  + CHAR(13)
FROM sys.databases
WHERE is_auto_shrink_on = 1 OR is_auto_close_on = 1
  AND name NOT IN ('tempdb');

PRINT @sql;
-- EXEC sp_executesql @sql;

🚫

Never use DBCC SHRINKFILE chronically. A one-time use is OK (e.g. after a massive deletion), but regular use destroys index fragmentation.

🛡️

Page Verify = CHECKSUM

Detects disk errors before data loss

Critical

CHECKSUM calculates a checksum for each 8 KB page upon writing and verifies it upon reading. It detects disk errors before data loss occurs. All databases must use CHECKSUM.

SELECT name,
       CASE page_verify_option
           WHEN 0 THEN 'NONE !'
           WHEN 1 THEN 'TORN_PAGE_DETECTION'
           WHEN 2 THEN 'CHECKSUM ✓'
       END AS page_verify
FROM sys.databases
WHERE page_verify_option != 2 AND name != 'tempdb';

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM;' + CHAR(13)
FROM sys.databases
WHERE page_verify_option != 2 AND name NOT IN ('tempdb');
PRINT @sql;
-- EXEC sp_executesql @sql;

🔄

Compatibility Level

Old levels limit SQL Server features and the optimizer

Warning

After upgrading SQL Server, databases often remain at the old compatibility level. This restricts new features (e.g. batch mode, intelligent query processing). Upgrade gradually — test before applying in production.

SQL Server Version	Level
SQL Server 2025	170
SQL Server 2022	160
SQL Server 2019	150
SQL Server 2017	140
SQL Server 2016	130

SELECT name, compatibility_level,
       CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) * 10 AS current_server_compat
FROM sys.databases
WHERE compatibility_level < CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) * 10
  AND name NOT IN ('master','model','msdb','tempdb');

ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 160;

⚠

Before changing the level, enable Query Store and monitor query performance. Some queries might perform worse with a different optimizer.

📊

Query Store

Performance monitoring and plan regression protection (SQL 2016+)

Warning

Query Store saves query execution plans and statistics. It allows identifying plan regressions (when SQL Server chooses a worse plan) and forcing the previous plan.

ALTER DATABASE [MyDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO
);

SELECT name, is_query_store_on,
       actual_state_desc AS state,
       current_storage_size_mb,
       max_storage_size_mb
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY name;

💾

Backup Compression Default

Saves disk space and speeds up backups

Warning

By default, backup compression is disabled. Compression reduces backup size by 3-5x on average and speeds up the backup (less I/O). Enable it at the server level — affects all new backups.

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

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

SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'backup compression default';

ℹ

SQL Server 2022 also supports BACKUP ... WITH COMPRESSION ALGORITHM = QAT_DEFLATE Intel hardware compression — significantly faster for high CPU servers.

🗑️

MSDB History Cleanup

Backup history and Job history accumulate endlessly

Warning

MSDB database backup history and agent job history grow the MSDB size indefinitely. Clean it regularly with an SQL Agent job.

EXEC msdb.dbo.sp_delete_backuphistory
    @oldest_date = DATEADD(DAY, -30, GETDATE());

EXEC msdb.dbo.sp_purge_jobhistory
    @oldest_date = DATEADD(DAY, -30, GETDATE());

⚙️

Optimize for Ad Hoc Workloads

Saves Plan Cache memory for single-use queries

Info

If the server processes many single-use (ad hoc) queries, SQL Server caches each query execution plan — even those never used again. This setting only caches a stub, saving significant memory.

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

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

⚠

If Optimize for Ad Hoc is on, Query Store should be active in your databases.
The ad hoc setting leaves single-use plans out of the Plan Cache — meaning they won't be visible when inspecting the cache. Without Query Store, you won't be able to identify slow single-use queries or analyze their plans later.

Reference: Query Store configuration →

🔑

Remote DAC (Dedicated Admin Connection)

Emergency access to an overloaded server

Warning

DAC is a reserved connection for sysadmin users — it works even when the server is completely overloaded and rejecting normal connections. Always enable on production servers.

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

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

📋

Error Log Retention and Rotation

The default of 6 files is too few

Warning

By default, SQL Server keeps only 6 error log files. If the server restarts often or logs heavily, you will lose crucial diagnostic info. Increase the number and rotate daily via an Agent job.

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'NumErrorLogs', REG_DWORD, 30;

EXEC sp_cycle_errorlog;

DECLARE @NumErrorLogs INT;
EXEC xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'NumErrorLogs', @NumErrorLogs OUTPUT;
SELECT ISNULL(@NumErrorLogs, 6) AS num_error_logs;

🚀

Full Configuration Script

All recommended settings in one script — adjust before running!

Ready

🚫

Read the script before running! You must adjust: Max Server Memory (based on your RAM), MAXDOP (based on CPUs), TempDB file count and locations.

-- ============================================================
-- SQL Server Post-Install Configuration
-- dba.ee
-- ============================================================
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- TODO: Change MAX SERVER MEMORY
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

-- TODO: Change MAXDOP
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

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

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

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

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

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'NumErrorLogs', REG_DWORD, 30;

-- Page Verify CHECKSUM
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'ALTER DATABASE [' + name + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;' + CHAR(13)
FROM sys.databases
WHERE page_verify_option != 2 AND name != 'tempdb';
IF LEN(@sql) > 0 EXEC sp_executesql @sql;

-- Disable Auto-Shrink & Auto-Close
SET @sql = '';
SELECT @sql += 'ALTER DATABASE [' + name + '] SET AUTO_SHRINK OFF;'  + CHAR(13)
             + 'ALTER DATABASE [' + name + '] SET AUTO_CLOSE OFF;'   + CHAR(13)
FROM sys.databases
WHERE (is_auto_shrink_on = 1 OR is_auto_close_on = 1) AND name != 'tempdb';
IF LEN(@sql) > 0 EXEC sp_executesql @sql;

🔍

Run the Health Check tool →

Automatically checks all the above settings and shows what needs attention

$ sql-server-config