Let me set the stage:
- SQL Server > 2016
- Several databases with the same schemas
- Data is similar but not identical
- The same indexes exist etc., all systems but one work as expected
- Examining the execution plan recommends the creation of an index with ALL of the columns as INCLUDE. The same base column already exists without the includes.
- On ONE of the databases, the query engine does not utilize an existing index and uses a full table scan
- Manually executing the query (which uses a parameter) - full table scan!
- Manually executing the query without a parameter (used fixed date) - uses the index.
- Manually executing the query with a parameter and adding
WITH (INDEX(update_ts_INDEX))- uses the index. The query goes from 2 minutes to less than a second.
- I believe that the query cache might be corrupt - but I don't know if that is possible. By trade I am a java developer who has spent the last 3 years digging into SQL server performance tuning, etc.
- The fact that manually executing the query without a parameter - the query engine picks the right index - blows my mind. Ideas?
- On the other servers, which run the same code and queries, I can find NO evidence of this same query taking any time. They must be using the index.
- I just executed the command manually, in another region with a parameter, and it used the index so the issue is indeed in ONLY one region - one database.
Example of query:
DECLARE @P1 DATETIME = GETDATE() - .1; SELECT value_1, update_ts, value_2 FROM PRODUCTION_TABLE WHERE (PRODUCTION_TABLE.update_ts > @P1);
If I remove the variable and handcode a date, it uses the index.
Question: Can I surgically remove a bad cached query plan for this particular query?
--- UPDATE ---
OPTION (RECOMPILE) causes the query engine to pick the correct index.