I have a SQL query that I am trying to optimize:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962' SELECT Id, MIN(SomeTimestamp), MAX(SomeInt) FROM dbo.MyTable WHERE Id = @Id AND SomeBit = 1 GROUP BY Id
MyTable has two indexes:
CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes ON dbo.MyTable (SomeTimestamp ASC) INCLUDE(Id, SomeInt) CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes ON dbo.MyTable (Id, SomeBit) INCLUDE (TotallyUnrelatedTimestamp)
When I execute the query exactly as written above, SQL Server scans the first index, resulting in 189,703 logical reads and a 2-3 second duration.
When I inline the
@Id variable and execute the query again, SQL Server seeks the second index, resulting in only 104 logical reads and a 0.001 second duration (basically instant).
I need the variable, but I want SQL to use the good plan. As a temporary solution I put an index hint on the query, and the query is basically instant. However, I try to stay away from index hints when possible. I usually assume that if the query optimizer is unable to do its job, then there is something I can do (or stop doing) to help it without explicitly telling it what to do.
So, why does SQL Server come up with a better plan when I inline the variable?