LLI Wiki — My Digital Knowledge Base

SQL Server instance settings to check

Checklist of SQL Server instance settings (parallelism, memory, backups, plan cache) to review on a new or existing server.

Settings to check on a SQL Server instance, whether it is new or already in use. The values are starting points to adapt to the server workload.

Instance settings

Setting Recommended value Remark
Cost threshold for parallelism 50 Cost above which the engine uses parallel plans
Lightweight pooling Disabled Disable it if it is enabled
Priority boost Disabled Disable it if it is enabled
Optimize for ad hoc workloads Enabled Reduces the plan cache space taken by single-use ad hoc plans
Max server memory (MB) Custom value Calculated with a memory reservation method that leaves memory for the operating system and other processes, instead of the default value
Backup compression default Enabled Compresses backups by default
Windows power plan High performance Apply it when possible
Plan cache flush Available when needed Provide a way to clear the plan cache on demand (for example with DBCC FREEPROCCACHE)
Mastodon LLIMastodon SocialFosstodon