Understanding Optimized Parameter in Compare Showplan of SQL Server Management Studio

Question

I ran two queries with and without an optimizer hint and compared their execution plans. However, I'm struggling to determine which query performed better. Here are the details:

Query without Hint:

SELECT * 
FROM #orders o
JOIN #CUSTOMERS c ON o.customer_id = c.customer_id AND c.credit_limit = 500;

Execution plan for the query without the hint:

Query with Hint:

SELECT * 
FROM #orders o
JOIN #CUSTOMERS c ON o.customer_id = c.customer_id AND c.credit_limit = 500
OPTION(HASH JOIN);

Execution plan for the query with the hint:

Comparing Showplans:

I'm having trouble interpreting the meaning of "Optimized != false" in the right plan. Does this mean the right plan is not the optimized version, or that it doesn't need to be optimized? Also, the operator cost of the Hash Match is 66%, while the Nested Loop is 2%. So, the right plan should be the optimal plan (without hint query). What is the significance of "Optimized"?

Answer 1

The "Optmized" applies to the Nested Loops operator, and it means that this particular operator was not using a specific "optimized" shortcut. To be perfectly honest, what exactly this mean, I don't know on the top of my head.

The percentage you see applies to the estimates, not the actual cost. So the optimizer thinks the plan with Nested Loops is better, but it may be wrong.

To find out which query that is better you would do something like:

DECLARE @d datetime2(3) = sysdatetime()
-- Run query
SELECT datediff(ms, @d, sysdatetime())

Although, since this is a SELECT query, the time can be distorted by the time it takes for SSMS to render the data. And with this small data set, the times will be so small, that it is difficult to draw conclusions anyway.