How to Optimize Slow SQL Queries in SQL Server?

 

Slow queries top Google searches due to their impact on application performance, often caused by large datasets, missing indexes, or inefficient joins. In 2025, with SQL Server's Query Store enhancements, optimization is more accessible.

Scenario: An e-commerce app experiences delays in loading product lists during peak hours, where a query scanning millions of rows takes 30+ seconds.

Detailed Solution with Code Examples:

• Step 1: Analyze Execution Plans. Use SSMS to view the actual execution plan (Ctrl+M or Query > Include Actual Execution Plan). Look for table scans, key lookups, or high-cost operators (>30% cost).
• Step 2: Identify Missing Indexes. Query dynamic management views (DMVs) for recommendations:
  sql

  SELECT

  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

  'CREATE INDEX [missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'

  + ' ON ' + mid.statement

  + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') + ')'

  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

  migs.*, mid.database_id, mid.[object_id]

  FROM sys.dm_db_missing_index_groups mig

  INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

  INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

  WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

  ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
  Create suggested indexes, but test in dev first to avoid overhead.
• Step 3: Update Statistics. Outdated stats lead to poor plans:
  sql

  UPDATE STATISTICS dbo.YourTable WITH FULLSCAN; -- For accurate stats on large tables
• Step 4: Rewrite Queries. Avoid SELECT *; use specific columns. Replace correlated subqueries with JOINs or CTEs: Inefficient Example:
  sql

  SELECT OrderID, (SELECT CustomerName FROM Customers WHERE CustomerID = Orders.CustomerID) AS Customer

  FROM Orders;
  Optimized:
  sql

  WITH CTE_Orders AS (

  SELECT OrderID, CustomerID

  FROM Orders

  WHERE OrderDate > '2025-01-01'

  )

  SELECT o.OrderID, c.CustomerName

  FROM CTE_Orders o

  INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
• Step 5: Use Query Store (SQL Server 2016+). Enable it:
  sql

  ALTER DATABASE YourDB SET QUERY_STORE = ON;
  Query top slow queries:
  sql

  SELECT TOP 10 qt.query_text_id, q.query_id, qt.query_sql_text,

  AVG(qs.avg_duration) AS avg_duration

  FROM sys.query_store_query_text qt

  INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id

  INNER JOIN sys.query_store_plan qp ON q.query_id = qp.query_id

  INNER JOIN sys.query_store_runtime_stats qs ON qp.plan_id = qs.plan_id

  GROUP BY qt.query_text_id, q.query_id, qt.query_sql_text

  ORDER BY AVG(qs.avg_duration) DESC;

Best Practices:

• Regularly rebuild indexes if fragmentation >30%: ALTER INDEX ALL ON YourTable REBUILD;.
• Use covering indexes for frequent queries.
• Monitor with Extended Events for real-time insights.
• In OLTP systems, aim for queries under 100ms; test with realistic data volumes.
• Avoid over-indexing—balance read/write performance.

This approach can slash query times by 80% in scenarios like reporting dashboards.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone