Post-install configuration for a new instance: max server memory and MAXDOP worked out from the server’s memory and CPUs, cost threshold for parallelism, optimize for ad hoc workloads, backup compression and checksum, the DAC, error log count, model growth and Query Store. Run it as it is first: it only shows what it would change. Then set @Apply = 1 and run it again. It also reports sa, instant file initialisation and lock pages in memory for you to check. Tested on SQL Server 2017 and 2025 and safe to run more than once.
-- Post-install configuration for a new SQL Server instance (SQL Server 2016 and later, Windows or Linux).
-- Author: Clive Roberts DBA
--
-- Replaces 03.Config_Options.sql, MAXDOP.sql and Server_settings_sp_configure.sql.
-- Run it once as it is: it only SHOWS what it would change (current, recommended, action).
-- Check the list, adjust the settings below if needed, then set @Apply = 1 and run it again.
-- Max server memory and MAXDOP are worked out from this server's memory and CPUs.
--
-- Not covered here (separate scripts): msdb sizing (01), error-log cycling job (02), tempdb files (04),
-- Database Mail (05), Agent alerting (06), operator and alerts (07), maintenance (08 onwards).
SET NOCOUNT ON;
DECLARE @Apply bit = 0; -- 0 = show only, 1 = make the changes
DECLARE @CostThreshold int = 50; -- cost threshold for parallelism
DECLARE @MaxDop int = NULL; -- NULL = work it out from the CPUs (Microsoft guidance)
DECLARE @MaxServerMemoryMB int = NULL; -- NULL = work it out from the memory (OS headroom kept)
DECLARE @NumErrorLogs int = 31; -- error logs kept (Windows only; on Linux use mssql-conf)
DECLARE @ModelDataGrowthMB int = 256; -- model data file growth, so new databases don't grow by %
DECLARE @ModelLogGrowthMB int = 128; -- model log file growth
DECLARE @ModelQueryStore bit = 1; -- turn Query Store on in model, so new databases get it
DECLARE @XpCmdShell bit = NULL; -- NULL = leave as it is (your backup jobs use it), 0 = off, 1 = on
DECLARE @FillFactor int = NULL; -- NULL = leave as it is (better set per index by IndexOptimize)
---------------------------------------------------------------------------------------------------
-- Work out memory and MAXDOP
---------------------------------------------------------------------------------------------------
DECLARE @TotalMB int = (SELECT physical_memory_kb / 1024 FROM sys.dm_os_sys_info);
DECLARE @Logical int = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @Numa int = (SELECT COUNT(*) FROM sys.dm_os_nodes WHERE node_state_desc NOT LIKE '%DAC%');
DECLARE @PerNode int = @Logical / NULLIF(@Numa, 0);
IF @MaxServerMemoryMB IS NULL
-- leave the OS 1 GB, plus 1 GB per 4 GB from 4-16 GB, plus 1 GB per 8 GB above 16 GB (minimum 2 GB headroom)
SET @MaxServerMemoryMB = @TotalMB - (SELECT MAX(v) FROM (VALUES (2048),
(1024 + CASE WHEN @TotalMB > 4096 THEN (CASE WHEN @TotalMB > 16384 THEN 16384 ELSE @TotalMB END - 4096) / 4 ELSE 0 END
+ CASE WHEN @TotalMB > 16384 THEN (@TotalMB - 16384) / 8 ELSE 0 END)) AS h(v));
IF @MaxDop IS NULL
SET @MaxDop = CASE
WHEN @Numa <= 1 AND @Logical <= 8 THEN @Logical
WHEN @Numa <= 1 THEN 8
WHEN @PerNode <= 16 THEN @PerNode
ELSE CASE WHEN @PerNode / 2 > 16 THEN 16 ELSE @PerNode / 2 END
END;
---------------------------------------------------------------------------------------------------
-- Compare with what the server has now
---------------------------------------------------------------------------------------------------
DECLARE @Plan TABLE (setting nvarchar(80), current_value nvarchar(100), recommended nvarchar(100), how nvarchar(20));
INSERT INTO @Plan (setting, current_value, recommended, how)
SELECT c.name, CONVERT(nvarchar(100), c.value_in_use), CONVERT(nvarchar(100), r.v), N'sp_configure'
FROM (VALUES (N'max server memory (MB)', @MaxServerMemoryMB),
(N'max degree of parallelism', @MaxDop),
(N'cost threshold for parallelism', @CostThreshold),
(N'optimize for ad hoc workloads', 1),
(N'backup compression default', 1),
(N'backup checksum default', 1),
(N'remote admin connections', 1),
(N'xp_cmdshell', @XpCmdShell),
(N'fill factor (%)', @FillFactor)) AS r(name, v)
JOIN sys.configurations AS c ON c.name = r.name
WHERE r.v IS NOT NULL;
DECLARE @CurrentLogs int;
IF SERVERPROPERTY('EngineEdition') <> 5 AND (SELECT host_platform FROM sys.dm_os_host_info) = N'Windows'
BEGIN
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs', @CurrentLogs OUTPUT;
INSERT INTO @Plan VALUES (N'number of error logs', CONVERT(nvarchar(100), ISNULL(@CurrentLogs, 6)), CONVERT(nvarchar(100), @NumErrorLogs), N'registry');
END;
INSERT INTO @Plan
SELECT N'model ' + CASE type WHEN 0 THEN N'data' ELSE N'log' END + N' growth',
CASE WHEN is_percent_growth = 1 THEN CONVERT(nvarchar(20), growth) + N'%' ELSE CONVERT(nvarchar(20), growth * 8 / 1024) + N' MB' END,
CONVERT(nvarchar(20), CASE type WHEN 0 THEN @ModelDataGrowthMB ELSE @ModelLogGrowthMB END) + N' MB',
N'model:' + name
FROM sys.master_files WHERE database_id = 3;
IF @ModelQueryStore = 1
INSERT INTO @Plan
SELECT N'model Query Store', CASE WHEN is_query_store_on = 1 THEN N'ON' ELSE N'OFF' END, N'ON', N'model:qs'
FROM sys.databases WHERE name = N'model';
---------------------------------------------------------------------------------------------------
-- Show the plan, then apply it if asked
---------------------------------------------------------------------------------------------------
SELECT setting, current_value, recommended,
CASE WHEN current_value = recommended THEN N'-' WHEN @Apply = 1 THEN N'CHANGED' ELSE N'would change' END AS action
FROM @Plan ORDER BY how, setting;
IF @Apply = 1
BEGIN
DECLARE @name nvarchar(80), @value nvarchar(100), @how nvarchar(20), @sql nvarchar(max), @advanced int;
SELECT @advanced = CONVERT(int, value_in_use) FROM sys.configurations WHERE name = N'show advanced options';
IF @advanced = 0 BEGIN EXEC sys.sp_configure N'show advanced options', 1; RECONFIGURE; END;
DECLARE changes CURSOR LOCAL FAST_FORWARD FOR
SELECT setting, recommended, how FROM @Plan WHERE current_value <> recommended;
OPEN changes;
FETCH NEXT FROM changes INTO @name, @value, @how;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @how = N'sp_configure'
BEGIN
DECLARE @v int = CONVERT(int, @value);
EXEC sys.sp_configure @name, @v;
RECONFIGURE;
END
ELSE IF @how = N'registry'
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'NumErrorLogs', REG_DWORD, @NumErrorLogs;
ELSE IF @how LIKE N'model:%' AND @how <> N'model:qs'
BEGIN
SET @sql = N'ALTER DATABASE [model] MODIFY FILE (NAME = ' + QUOTENAME(SUBSTRING(@how, 7, 128)) +
N', FILEGROWTH = ' + REPLACE(@value, N' MB', N'') + N'MB);';
EXEC sys.sp_executesql @sql;
END
ELSE IF @how = N'model:qs'
EXEC (N'ALTER DATABASE [model] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);');
FETCH NEXT FROM changes INTO @name, @value, @how;
END;
CLOSE changes; DEALLOCATE changes;
IF @advanced = 0 BEGIN EXEC sys.sp_configure N'show advanced options', 0; RECONFIGURE; END;
END;
---------------------------------------------------------------------------------------------------
-- Things to check by hand (reported only - never changed here)
---------------------------------------------------------------------------------------------------
SELECT N'sa login enabled' AS [check],
CASE WHEN is_disabled = 0 THEN N'yes - consider disabling or renaming it' ELSE N'no' END AS result
FROM sys.server_principals WHERE sid = 0x01
UNION ALL
SELECT N'instant file initialisation',
CASE WHEN MAX(CASE WHEN instant_file_initialization_enabled = N'Y' THEN 1 ELSE 0 END) = 1 THEN N'on'
ELSE N'off - grant the service account "Perform volume maintenance tasks"' END
FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server (%'
UNION ALL
SELECT N'lock pages in memory',
CASE sql_memory_model_desc WHEN N'LOCK_PAGES' THEN N'on' WHEN N'LARGE_PAGES' THEN N'on (large pages)' ELSE N'off (fine for most servers)' END
FROM sys.dm_os_sys_info
UNION ALL
SELECT N'server memory / CPUs / NUMA nodes', CONCAT(@TotalMB, N' MB / ', @Logical, N' logical CPUs / ', @Numa, N' node(s)');