- Database & Server Configuration
✅ Ensure SQL Server is running on an optimized hardware setup.
✅ Configure max server memory to avoid excessive OS paging.
✅ Set max degree of parallelism (MAXDOP) based on CPU cores.
✅ Optimize cost threshold for parallelism (default 5 is often too low).
✅ Enable Instant File Initialization for faster data file growth.
✅ Keep TempDB on fast storage & configure multiple files (1 per core, max 8).
✅ Set AUTO_UPDATE_STATISTICS_ASYNC = ON for better query performance.
✅ Ensure Power Plan is set to High Performance on Windows.
- Index Optimization
✅ Identify missing indexes using sys.dm_db_missing_index_details.
✅ Remove unused and duplicate indexes to reduce overhead.
✅ Use Filtered Indexes for large tables with selective queries.
✅ Periodically rebuild/reorganize indexes to reduce fragmentation.
✅ Enable fill factor tuning (default 0 or 100 may not be optimal).
- Query Performance
✅ Analyze slow queries using Query Store or Extended Events.
✅ Avoid **SELECT *** (fetch only required columns).
✅ Use Indexed Views where applicable.
✅ Optimize joins using appropriate indexing strategies.
✅ Check execution plans and eliminate key lookups where possible.
✅ Replace cursors with set-based operations whenever possible.
✅ Use table variables vs. temp tables wisely.
- TempDB Optimization
✅ Ensure multiple TempDB files (1 per core, up to 8).
✅ Place TempDB on separate fast storage (SSD recommended).
✅ Pre-size TempDB to avoid frequent auto-growth events.
✅ Monitor TempDB contention using sys.dm_exec_requests.
- Statistics & Maintenance
✅ Ensure auto-update statistics is enabled.
✅ Regularly update column and index statistics (sp_updatestats).
✅ Schedule index maintenance using Ola Hallengren’s scripts or customized jobs.
✅ Monitor database file growth and set proper autogrowth settings.
✅ Use DBCC CHECKDB to check database integrity.
- Locking & Concurrency
✅ Identify blocking queries using sp_whoisactive.
✅ Minimize deadlocks with proper indexing and transaction handling.
✅ Use READ COMMITTED SNAPSHOT ISOLATION (RCSI) for OLTP workloads.
✅ Optimize transaction scope (keep them as short as possible).
✅ Avoid large batch updates by breaking them into smaller transactions.
- Memory & CPU Optimization
✅ Monitor Page Life Expectancy (PLE) (>300 sec is good).
✅ Track Buffer Pool usage to avoid excessive disk IO.
✅ Identify CPU-intensive queries using sys.dm_exec_query_stats.
✅ Optimize memory grants using Resource Governor if needed.
✅ Use Columnstore Indexes for large analytical workloads.
- Backup & Recovery Optimization
✅ Use compression for backups to reduce disk I/O.
✅ Store backups on separate storage from live data.
✅ Perform log backups frequently to prevent large log file growth.
✅ Regularly test database restores to ensure reliability.