Server Settings Worth Checking on Any New Instance

A sensible SQL Server configuration gives the operating system room and the workload predictable resources. Check the installed settings before assuming setup made every decision you needed.

Record the Starting Point

I start with an inventory, not a script that changes everything. A new instance may share its host with another engine, backup software, or application services. Those neighbors affect choices that look obvious on an empty machine.

SELECT @@SERVERNAME AS server_name,
       SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('Edition') AS edition;
SELECT name, value AS configured_value, value_in_use,
       is_dynamic, is_advanced
FROM sys.configurations
WHERE name IN
 ('max server memory (MB)', 'min server memory (MB)',
  'max degree of parallelism', 'cost threshold for parallelism',
  'backup compression default');

A configured value can differ from the value currently in use. Record both, along with the reason for any planned change. Keep the previous setting so a test has a clear way back.

Leave Memory for the Rest of the Host

Max server memory is not a hard ceiling on every byte used by the SQL Server process. Some allocations sit outside its control. The operating system and other services also need capacity.

SELECT total_physical_memory_kb, available_physical_memory_kb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;
SELECT physical_memory_in_use_kb,
       process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory;

Choose an initial budget after accounting for the whole host. Then observe a representative workload and adjust from evidence. Giving each instance nearly all installed memory is not a sharing policy.

Min server memory does not immediately reserve that amount at startup. Understand its behavior before using it to divide a host. On a shared machine, also review how other applications grow during busy periods.

Treat Parallelism as Two Decisions

MAXDOP limits the degree of parallelism available to parallel work. Choose it with the visible processor and NUMA arrangement in mind. A setting copied from another server can be inappropriate even when both machines have similar names.

SELECT cpu_count, scheduler_count, socket_count,
       cores_per_socket, numa_node_count,
       softnuma_configuration_desc
FROM sys.dm_os_sys_info;
SELECT node_id, memory_node_id, online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_state_desc = 'ONLINE';

Cost threshold for parallelism influences when the optimizer considers parallel alternatives. Its units are estimated cost, not elapsed seconds. A query above the threshold is not guaranteed a parallel plan.

The default threshold of five is a starting value, not a universal recommendation. Change it gradually and observe a full workload cycle. Check database-scoped settings and query hints when behavior differs from the server setting.

Check File Initialization and Growth

Instant file initialization can avoid zeroing eligible data-file space during allocation. On Windows, its availability depends on the relevant service permission and other conditions. Read the actual service status rather than assuming the setup checkbox was selected.

SELECT servicename, startup_type_desc, status_desc,
       instant_file_initialization_enabled
FROM sys.dm_server_services;
SELECT DB_NAME(database_id) AS database_name, name, type_desc,
       size * 8.0 / 1024 AS size_mb,
       growth, is_percent_growth, max_size
FROM sys.master_files
ORDER BY database_id, file_id;

Pre-size files for expected work and retain growth as a safety mechanism. A fixed growth increment is easier to reason about than a changing percentage. The appropriate increment still depends on capacity and workload.

Do not apply the old claim that every log growth must always be zeroed. Starting with SQL Server 2022, log autogrowth up to 64 MB can benefit from instant initialization. Larger log autogrowth has different behavior.

Give Tempdb a Deliberate Layout

Review tempdb data-file count, size, and growth together. Equally sized data files with matching growth settings support a balanced allocation pattern. Creating one file for every processor without checking the documented guidance can add unnecessary complexity.

SELECT name, type_desc, physical_name,
       size * 8.0 / 1024 AS size_mb,
       growth, is_percent_growth
FROM tempdb.sys.database_files
ORDER BY file_id;

Separate allocation contention from slow storage and excessive temporary work. More files do not solve every tempdb problem. Measure contention before extending a sensible initial layout, and leave the log-file decision separate.

Review Backup Defaults Against Recovery Needs

Backup compression can reduce backup size while adding CPU work. Check edition support and test its effect during the real backup window. A compression setting cannot compensate for a missing restore test.

Finally, record the agreed baseline with the instance owner and review date. Include backup schedules, retention, and monitoring alongside these engine settings. Configuration is useful when someone can explain why it exists.

A server baseline is not a bag of magic numbers, it is a set of decisions you can explain.

This post was rewritten from scratch in September 2026. The original, published on 2011-11-12, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.