Why Adding Indexes Slowed My Query and How an Undocumented Hint Restored Speed

During a performance‑tuning project for a client, a complex query that joins twelve tables showed a paradox: after creating the necessary indexes, I/O dropped sharply but the execution time rose from about 8 seconds to 20 seconds.

Root‑cause analysis

Examining the execution plans revealed that the original plan used a Hash Join with parallel execution, which handled the large tables efficiently. After the indexes were added, the optimizer estimated row counts incorrectly because the filter predicate fell within a very wide statistics step (actual rows ≈ 15 000, estimated average ≈ 800). Consequently, it switched to a Loop Join and disabled parallelism, causing the slowdown.

Undocumented hint solution

Because the statement runs inside a stored procedure, an undocumented query hint was applied to force the parallelism cost threshold to zero, effectively forcing parallel execution. With the hint, the runtime dropped to 5 seconds (using a Hash Join hint yields about 7 seconds).

Example without the hint

SELECT *
FROM [AdventureWorks].[Sales].[SalesOrderDetail] a
INNER JOIN [Sales].SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID

The default plan for this statement is serial, as shown in the first image.

Example with the undocumented hint

SELECT *
FROM [AdventureWorks].[Sales].[SalesOrderDetail] a
INNER JOIN [Sales].SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
OPTION (querytraceon 8649)

With the hint, the optimizer generates a parallel plan, as illustrated in the second image.

Takeaway

For complex DSS or OLAP queries where the optimizer chooses a sub‑optimal serial Loop Join due to inaccurate cardinality estimates, applying the undocumented querytraceon 8649 hint can coerce SQL Server to use parallel execution and significantly reduce query time.