One of the most common issues with a SQL Server environment is the occasions where the queries run on it are running slower than usual. When this happens; there will be bottlenecks to production jobs and decision making data generation.

Here are 7 potential fixes for SQL Server slow query issues:

1. Create and Optimize Indexes
   • Add missing indexes (use Database Engine Tuning Advisor or sys.dm_db_missing_index_details).
   • Reduce overhead costs by dropping duplicate or unneeded indexes.
2. Update Statistics
   • Run UPDATE STATISTICS or enable AUTO_UPDATE_STATISTICS to ensure the query optimizer has accurate data distribution.
3. Rewrite Queries
   • Avoid SELECT *.
   • Break complex joins up into smaller queries.
   • Reduce row scans by filtering properly (WHERE).
4. Use Execution plans for Diagnosis
   • Analyze actual execution plans using SSMS in order to detect scans, key looksups, and operators that cause performance hits.
5. Reduce Parameter Sniffing Issues
   • Use OPTION (RECOMPILE) for problematic queries.
   • Use OPTIMIZE FOR UNKNOWN tips when parameter values vary widely.
6. Optimize TempDB Usage
   • Add multiple TempDB data files (same size).
   • Use fast storage with TF1117/1118 (pre SQL 2016 SP1, now defaults), to reduce allocation contention.
7. Review Server Configuration
   • Set MAXDOP to the appropriate value (generally MAXDOP is / #CPUs/ NUMA nodes ).
   • Set Threshold Cost for Parallelism Higher than default (default is 5).