Why the default settings are a problem
SQL Server has to install just as well on a developer's laptop as on a 64-core server. Its defaults therefore favor compatibility, and some of them have barely changed since the late 1990s. Microsoft can't change them without risking disruption to millions of existing installations: it is up to the team running the server to adjust them.
Yet many instances were installed by an integrator, a software vendor or a busy system administrator who clicked through the setup wizard without revisiting these settings. They are the first things we check during a SQL Server audit, because they take minutes to fix and benefit every application on the instance.
1. Max server memory is not capped
By default, max server memory is set to 2,147,483,647 MB: in other words, no limit at all. SQL Server then takes all the memory it can, and the operating system, the antivirus or the backup agent end up starved. The result: paging, unexplained slowdowns, and sometimes a server that stops responding under peak load.
What to do: leave the operating system at least 4 GB or 10% of memory, whichever is higher, and more if other services run on the machine. Since SQL Server 2019, the setup wizard suggests a recommended value, but an upgraded instance keeps its old setting.
2. The cost threshold for parallelism is still 5
The cost threshold for parallelism setting defines the estimated cost above which a query can run in parallel across multiple cores. Its default value, 5, was calibrated for 1998 hardware. Today, nearly every query clears that bar: small queries get split across several threads, spend more time coordinating than working, and tie up cores that other queries need.
What to do: start at 50, then adjust based on the cost of your real queries in Query Store or the plan cache.
3. The degree of parallelism is not limited
With max degree of parallelism at 0, the default in versions before 2019, a single query can use every core on the server. On a 32- or 64-core machine, one moderately heavy report is enough to make every other session wait.
What to do: follow Microsoft's recommendation, which is no more than 8, and no more than the number of cores per NUMA node. Then validate the exact value against the real workload.
4. tempdb is undersized
tempdb handles every sort, hash join, temporary table and row version. With a single data file, sessions compete for allocation pages and PAGELATCH waits start to appear. Since SQL Server 2016, setup creates multiple files, but instances upgraded from an older version, or adjusted by hand, often have a single file, files of different sizes, or percentage-based growth.
What to do: one data file per logical core, up to 8, all the same size and with the same fixed growth, ideally on dedicated fast storage.
5. Single-use execution plans clutter memory
Without the optimize for ad hoc workloads option, SQL Server keeps the full plan for every query, even the ones that will never run again. With an ORM or a lot of dynamic SQL, the cache fills up with thousands of useless plans, at the expense of data.
What to do: turn it on. The full plan is then cached only the second time a query runs. It's safe on virtually every instance.
6. Auto-shrink and auto-close are enabled
The AUTO_SHRINK option automatically shrinks a database's files whenever it has free space. Every shrink fragments the indexes, the next maintenance run rebuilds them and grows the file back, and then the shrink starts all over again: I/O spent for nothing. It is off by default, but we regularly find it turned on. AUTO_CLOSE, on by default for databases created with Express edition, closes the database as soon as the last user disconnects: the next connection pays for a full startup, with an empty cache.
What to do: turn both off on every production database.
7. The recovery model doesn't match the backups
A new database inherits the recovery model of the model system database, usually FULL on Standard and Enterprise editions. In FULL, without regular log backups, the log file grows until it fills the disk. The usual reflex is then to switch the database to SIMPLE, which fixes the space problem but rules out any point-in-time restore: after an incident at 3 p.m., you are back to last night's backup.
What to do: decide, database by database, how much data loss is acceptable (RPO), then align the recovery model and log backup frequency with that target.
8. No maintenance is scheduled
Out of the box, SQL Server backs up nothing and never checks database integrity. Without scheduled jobs, corruption can go unnoticed for months, until it has spread to every backup you keep.
What to do: schedule full, differential and log backups, a regular DBCC CHECKDB, statistics updates, and test a full restore at least once a quarter. A backup that has never been restored is only a hypothesis.
The script to check everything
These queries are read-only and safe to run in production:
/* 1. Instance settings */
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'cost threshold for parallelism',
'max degree of parallelism', 'optimize for ad hoc workloads');
/* 2. tempdb data files (count, size, growth) */
SELECT name, size * 8 / 1024 AS size_mb, growth, is_percent_growth
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';
/* 3. User database options */
SELECT name, recovery_model_desc, is_auto_shrink_on, is_auto_close_on
FROM sys.databases
WHERE database_id > 4;
/* 4. Most recent full and log backups */
SELECT d.name, d.recovery_model_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;And, once you have validated the values for your server, the instance-level fixes:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 57344; /* example: 64 GB server */
EXEC sp_configure 'cost threshold for parallelism', 50; /* starting point */
EXEC sp_configure 'max degree of parallelism', 8; /* depends on cores and NUMA nodes */
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;These changes don't require a restart, but some of them clear the plan cache: apply them outside peak hours, one at a time, recording the state before and after. The values above are starting points, not universal truths: a shared server, a data warehouse instance or a tightly constrained virtual machine calls for different settings.
Beyond the settings
These eight points are only part of what we review. A full audit also covers real-world performance, security, high availability and the lifecycle of your versions, with a prioritized report and the remediation scripts: see our SQL Server audit service. And if your users are already complaining about slowness, start with our method to find the real cause of a slow SQL Server.
