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) |