As a SQL Server developer, it’s important to understand how the query optimizer works and why it chooses certain query plans. In this article, we will explore a real-life scenario where the optimizer picked an unexpected query plan and discuss the reasons behind it.

The scenario involves a query that retrieves sales order information from the AdventureWorks2017 database. The original query looked like this:

SET STATISTICS IO, TIME ON;
DECLARE @StartDate DATETIME = '1/1/2012';
DECLARE @EndDate DATETIME = '1/1/2013';
DECLARE @Status INT = 5;
DECLARE @TerritoryID INT = 6;

SELECT SalesOrderID, RevisionNumber, TerritoryID, ModifiedDate
FROM Sales.SalesOrderHeader
WHERE ModifiedDate >= @StartDate 
  AND ModifiedDate < @EndDate
  AND [Status] = @Status
  AND TerritoryID <> @TerritoryID;
SET STATISTICS IO, TIME OFF;

When executing this query, the expected query plan was generated with a query cost of 0.54456. However, the optimizer suggested an index that resulted in a different query plan with a lower query cost of 0.045771. This unexpected behavior raised several questions.

Upon further investigation, it was discovered that the optimizer took advantage of the ordering of the columns in the suggested index to achieve better performance. By rearranging the index column definition, the query plan reverted to the expected one with a query cost of 0.0200804.

Additionally, it was observed that including the TerritoryID column in the index’s INCLUDE clause had a minimal impact on the query plan and performance.

So why did the optimizer pick the wrong index and query plan in the first place? The answer lies in the complexity of the query and the optimizer’s goal of finding a “good enough” plan. In this relatively simple query with only one table, the optimizer may have “stopped short” and settled for a suboptimal plan.

Furthermore, the extra “garbage” associated with the query plan using the suggested index can be attributed to the additional logic coding introduced by the optimizer. This behavior is similar to what Pinal Dave found in his blog on unnecessary indexes.

Understanding the inner workings of the query optimizer can help us make informed decisions when optimizing our SQL Server queries. By analyzing query plans and experimenting with different index configurations, we can achieve better performance and efficiency.

Have you encountered similar situations with the SQL Server query optimizer? Share your experiences and insights in the comments below. Let’s collaborate and learn from each other.

Attached to this article, you will find the T-SQL script and the QueryPlan.XML files for each of the four scenarios discussed.