SQL Server – Performance Tuning Tips

• Choose the right index type: Use clustered indexes for primary key columns and non‑clustered indexes for frequent search columns.
• Covering indexes: Include frequently selected columns to avoid key lookups.
• Filtered indexes: Apply filters to index only a subset of rows when appropriate.
• Maintenance: Rebuild or reorganize fragmented indexes regularly; use sys.dm_db_index_physical_stats to monitor.

• Prefer set‑based operations over row‑by‑row loops.
• Use appropriate join types; avoid cross joins unless intentional.
• Leverage the Query Store to capture regressed queries.
• Analyze execution plans (Ctrl+M in SSMS) for missing indexes or costly operators.
• Parameterize queries to benefit from plan reuse.

Accurate statistics guide the optimizer.

• Enable AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS.
• For large tables, consider FULLSCAN or SAMPLE updates.
• Use UPDATE STATISTICS with WITH FULLSCAN after massive data loads.

• Configure max server memory to reserve OS memory.
• Monitor CPU usage via sys.dm_exec_requests and sys.dm_os_wait_stats.
• Use Resource Governor to limit runaway queries.

• Separate data, log, and tempdb files onto different drives.
• Enable Instant File Initialization for faster file growth.
• Use sys.dm_io_virtual_file_stats to identify hot files.

• Use Extended Events for lightweight tracing.
• Leverage Query Store and DMVs for performance baselines.
• Set up alerts on critical wait types (e.g., LCK_M_X).