Tier one
Memory: give the operating system a floor
The default is 2147483647 MB, which means the engine takes everything and the OS starts paging under it.
| Option | Default | Set it to |
|---|---|---|
| max server memory (MB) | 2147483647 | Physical RAM minus the larger of 4 GB or 10 percent |
| min server memory (MB) | 0 | Leave at 0 unless the host runs nothing else |
sp_configure 'max server memory (MB)', 57344; reconfigure; for a 64 GB host. Add back anything else on the box, because this number is shared with SSIS, SSRS and backup buffers.
Two instances on one server each read the whole machine. Split the budget by hand, or they will split it by racing.
Tier one
Parallelism and the plan cache: four numbers
Three server defaults were chosen for a 2005 machine. The fourth matters more on 2025 than it ever did.
| Option | Default | Set it to |
|---|---|---|
| max degree of parallelism | 0 (all cores) | 8, and never above the cores in one NUMA node |
| cost threshold for parallelism | 5 | 50 as a start, then tune from your own plans |
| optimize for ad hoc workloads | 0 | 1 on any instance that sees generated SQL |
| blocked process threshold (s) | 0 (no reports) | 20 so blocking gets a report and an event |
Why MAXDOP still matters in 2025
DOP feedback is on by default now, and it lowers the degree of parallelism from whatever MAXDOP allows. Set MAXDOP to 0 and you have given it the whole machine as a starting point.
Eight and fifty are defensible defaults, not laws. A data warehouse and a booking system want different answers, and your plan cache is the honest referee.
Tier one
tempdb: eight files, one size, one growth step
A fresh install has one 8 MB data file that grows 64 MB at a time. Everything that spills does so into that single file.
- Eight data files, which is also one per four cores on a 32-core machine. Beyond eight, the returns stop.
- Identical initial size on every file, large enough to hold your peak. Unequal sizes get filled unevenly.
- Identical 64 MB growth on every file, so a growth event spreads instead of favouring the largest file.
- Pre-grown, not grown. Autogrowth during a busy afternoon is the pause you notice.
The old advice about trace flag 1118 is dead: uniform extent allocation became the behaviour in 2016, so the flag has nothing left to change.
Tier one
Every database: five options that ship wrong
These live below the instance, so a server-wide script that ignores them leaves every database on defaults.
| Option | Default | Recommended |
|---|---|---|
| read_committed_snapshot | Off | alter database YourDb set read_committed_snapshot on with rollback immediate; |
| accelerated_database_recovery | Off | alter database YourDb set accelerated_database_recovery = on; |
| query_store max_storage_size_mb | 1000 | 2048 or more, with capture mode set to custom |
| page_verify | Checksum | Verify it: restored and upgraded databases often say torn_page_detection or none |
| file autogrowth | Small fixed steps, or 10 percent on the log | One fixed 64 MB or larger step, identical across data files |
Two things not to change
Query Store has been on in read-write mode for new databases since 2022, so stop scripting it on and start checking it survived the upgrade. Leave delayed durability disabled unless you have measured that the log write is your bottleneck.
Also confirm auto close and auto shrink are off. They are off by default for most databases, and a restored database can carry them in.
Tier one
The attack surface: one change, nine verifications
Most security defaults are already correct. The work is proving it, not flipping it.
Change this one
remote admin connections defaults to 0, which forbids a dedicated admin connection over TCP. Set it to 1 before the day you need it, because that is the connection you reach for when the scheduler is wedged.
Verify these are still 0
xp_cmdshell, ole automation procedures, ad hoc distributed queries, clr enabled, cross db ownership chaining, external scripts enabled. On 2025 add the three new ones: external rest endpoint enabled, external AI runtimes enabled, allow server scoped db credentials.
Each lets code inside the database reach outside the server. Off is the correct default for all three, and turning one on belongs in a change record.
Keep clr strict security at its default of 1. Sign your assemblies rather than lowering it, and decide contained database authentication deliberately instead of by inheritance.
Tier one
Backups: three defaults worth nothing to fix
All three cost a RECONFIGURE and pay back immediately on storage and on your ability to prove a backup is readable.
| Option | Default | Set it to |
|---|---|---|
| backup compression default | 0 | 1, so nobody has to remember the keyword |
| backup compression algorithm | 0 | 3 on 2025, which selects ZSTD |
| backup checksum default | 0 | 1, so every backup checksums its pages and a bad one surfaces before a restore does |
sp_configure 'backup compression algorithm', 3; reconfigure; and the same shape for the other two. ZSTD exists only on 2025: 2022 rejects it in the statement as well as in the setting.
Tier three
Leave alone, and the advice that aged out
Every tuning script written before 2016 carries something here. Most of it is now either a default or a hazard.
| Setting | Why not |
|---|---|
| priority boost | Starves the OS and hides the real problem. Microsoft recommends against it. |
| lightweight pooling | A 1990s workaround for context switching that no longer applies. |
| max worker threads | 0 sizes the pool to the machine. Hand-setting it causes thread exhaustion. |
| fill factor | 0 already means 100 for reads-only and 80 is a per-index decision, not an instance one. |
| affinity mask | Constrains the scheduler for a problem virtualisation already solved. |
| recovery interval | Ignored once a database uses indirect checkpoints, which has been the default since 2016. |
| trace flag 1118 | Uniform extent allocation has been the behaviour since 2016. |
| trace flag 4199 | Use the query_optimizer_hotfixes setting, which is per database and reviewable. |
Two that are conditional, not defaults
Lock pages in memory is worth a test on bare metal and usually wrong on a hypervisor, where the host already manages memory. Measure before granting it.
Memory-optimized tempdb metadata is for measured temporary object metadata contention only. Microsoft recommends against enabling it speculatively, and it needs a restart.
One Enterprise action rather than a setting: page compression on large tables and indexes is off by default, and nothing turns it on for you.