Why does my SQL query slow down dramatically with additional joins and grouped calculations despite indexes on key columns everywhere?

Question

I maintain a SQL Server reporting query that combines order, customer, payment, shipment, and product tables for monthly operational metrics. After adding two business-required joins and several CASE-based aggregate calculations, runtime increased from seconds to over ten minutes during peak load. I already have indexes on join keys, filter columns, and date fields, yet the execution plan still includes large scans and expensive sort operations. I must preserve existing output and business logic exactly. What should I prioritize first to tune this query safely in production, including index redesign, query rewrites, statistics updates, and plan regression checks, while minimizing deployment risk and performance variance?

Answer 1

Prioritize low‑risk, high‑impact steps that keep the business logic and result set identical, and validate each change with plans and runtime comparisons.

1. Baseline the current behavior and identify the real bottleneck
2. Capture the current execution plan (actual if possible) and save it as the baseline.
3. Check whether the query is:
   • CPU‑bound (worker time ≈ elapsed time) and doing many logical reads, or
   • Waiting on memory, I/O, or THREADPOOL.
   For CPU‑bound queries, focus on reducing logical reads, large scans, and sorts. See the guidance on diagnosing CPU‑bound and slow‑running queries and using execution plans and wait stats to identify the root cause.
4. Fix SARGability and avoid non‑seekable predicates

Even with indexes “everywhere,” SARGability issues can force scans and large sorts:

• Look for expressions on indexed columns in WHERE, JOIN, GROUP BY, and ORDER BY (for example, CONVERT, CAST, ISNULL, COALESCE). These prevent index seeks and drive scans and high CPU.
• First choice: rewrite predicates so both sides of comparisons are the same data type and avoid wrapping the indexed column.
• If the query cannot be rewritten easily, consider a computed column that matches the expression and index that computed column, so the optimizer can seek on it.

Example from the context:

-- Non‑SARGable
ON CONVERT(int, T1.ProdID) = T2.ProductID

-- Preferred: align data types and avoid CONVERT on the column
ON T1.ProdID = T2.ProductID

This is a safe first step because it does not change business logic, only how the optimizer can use indexes.

3. Reduce unnecessary sorts and hash operations

The added joins and CASE‑based aggregates often introduce large sorts and hash joins that require big memory grants and CPU:

• Verify whether ORDER BY is truly required for the final result. If the application or reporting layer (for example, Power BI, Reporting Services) can sort, remove ORDER BY from the SQL query to eliminate a large final sort.
• Review GROUP BY and aggregate expressions. Large GROUP BY on unsorted inputs often leads to expensive sorts or hash aggregates.
• Create or adjust indexes to support the most expensive JOIN + GROUP BY patterns so the engine can avoid large hash/sort operations:
  • For example, composite indexes on (join_key, grouping_key) or (filter_columns, join_key) depending on the plan.
• For heavy aggregation queries over large rowsets, evaluate whether a COLUMNSTORE index on the fact table (orders/payments/shipments) is appropriate. Columnstore can significantly reduce I/O and memory for GROUP BY analytics queries.

These changes preserve logic but can dramatically reduce memory grants and CPU.

4. Revisit join strategy and row goals

Additional joins can cause the optimizer to choose suboptimal join types or hit optimizer timeouts:

• Many joins increase the search space of possible plans, making optimizer timeouts more likely and leading to “good‑enough” but slow plans.
• Check for row‑goal operators (TOP, FAST, EXISTS, IN, SET ROWCOUNT, OPTION (FAST N)) that can force Nested Loop joins on large tables and cause long‑running queries.
• If the plan shows large hash joins or merge joins with big memory grants, first try to fix them with better indexes and statistics rather than hints.
• As a last resort, consider join hints such as LOOP JOIN only after careful testing, because forcing a join type can make some parameter combinations much slower or even fail with error 8622 if a valid plan cannot be produced.

5. Ensure statistics are current and consistent

Out‑of‑date or poor‑quality statistics can cause bad cardinality estimates, leading to:

• Wrong join order or join type
• Over‑ or under‑estimated memory grants
• Optimizer timeouts on complex queries

Actions:

• Update statistics on the main fact and dimension tables involved in the query, using an appropriate sample rate.
• Ensure statistics are updated consistently across environments when comparing performance between servers.

This is low‑risk and often improves plan quality without changing code.

6. Targeted index redesign instead of “indexes everywhere”

Having many indexes is not the same as having the right ones. Prioritize:

• Missing or misaligned indexes on the largest tables for the specific query pattern:
  • Join keys
  • Filter columns
  • Grouping and ordering columns
• Use missing index suggestions and tuning tools as input, not as automatic truth. Combine them with the actual execution plan to design a small number of high‑value indexes.
• For CASE‑based aggregates, focus on supporting the underlying grouping and filtering columns; the CASE expressions themselves usually do not need separate indexes.

Keep index changes incremental in production (one or two indexes at a time) and validate impact.

7. Control and monitor plan changes (plan regression safety)

To minimize deployment risk and performance variance:

1. Capture and store the current “good” plan (from before the slowdown, if available) as a baseline.
2. After each change (statistics, index, or query rewrite), compare:
   • Actual execution plan shape
   • Logical reads, CPU time, elapsed time
3. If a previous plan is known to be better, use safe plan‑forcing mechanisms:
   • Query Store to force a known good plan for the specific query.
   • OPTION (USE PLAN N'<XML_Plan>') or plan guides as alternatives when Query Store is not available.

This lets changes be rolled back quickly if a new plan regresses.

8. Stepwise, low‑risk tuning order for production

Given the need to preserve logic and minimize risk, a practical order is:

1. Baseline and diagnose
   • Save current plan and runtime metrics.
   • Identify top cost operators (scans, sorts, hash joins) and SARGability issues.
2. Statistics refresh
   • Update statistics on key tables.
   • Re‑run and compare plan and runtime.
3. SARGability fixes
   • Remove unnecessary CONVERT/CAST/ISNULL/COALESCE on indexed columns.
   • If needed, add computed columns + indexes instead of changing query logic.
4. Index redesign (targeted)
   • Add or adjust a small number of composite indexes to support the observed join and grouping patterns.
   • Consider COLUMNSTORE for large analytic fact tables.
5. Reduce sorts and hash operations
   • Remove unnecessary ORDER BY.
   • Ensure indexes support GROUP BY and joins to avoid large sorts.
6. Plan stability
   • Use Query Store or plan forcing to lock in a good plan once found.
   • Monitor for regressions during peak load.
7. Hints and advanced options (last resort)
   • Only after the above, and only with thorough testing, consider join hints or FORCE ORDER to reduce optimizer search space or avoid bad join choices.

Following this order keeps each change small, observable, and reversible, while directly targeting the causes of scans, sorts, and unstable plans that appear after adding joins and aggregates.

---

References:

• Troubleshoot slow performance or low memory issues caused by memory grants in SQL Server
• Troubleshoot queries that seem to run endlessly in SQL Server
• Troubleshoot high-CPU-usage issues in SQL Server
• Troubleshoot slow-running queries in SQL Server
• Troubleshoot slow queries affected by query optimizer timeout
• Troubleshoot a query that shows a significant performance difference between two servers
• Decreased query performance after upgrade from SQL Server 2012 or earlier to 2014 or later
• optmization tecniques. - Microsoft Q&A
• Select most recent data per record from multiple tables - Microsoft Q&A
• Tools for SQL Query Tuning - Microsoft Q&A
• Performance degrade after migrating to SQL server 2022 - Microsoft Q&A
• Queries have been running really slowly since upgrading the database to SQL Server 2025. - Microsoft Q&A