I've just fixed a production performance issue by dropping an index and recreate it. I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
Arguments in favor of bad execution plan :
- Before dropping the index, I looked up the last update date for the statistics on the given table and they were up to date.
- My DBA has put in place Hallengren's index and statistic maintenance solution
- The slow query was a select statement executed from
sp_executesqlwith dates parameters. Executing the same select statement without
sp_executesqlwas fast, but also didn't use the same execution plan.
Arguments against bad execution plan :
- Before dropping the index, we went real wild and ran the forbidden
dbcc freeproccacheto clear any bad plan, but this didn't fix or change the performance issue.
The slow query happen to be using a table indexed by date. However, there is wide differences in the amount of records for each date. In other word, any given date range from few records to more than 100k and it is pretty random.
The database is running under compatibility level 140 (SQL Server 2017)
Was the source of the problem a bad plan or a stale index?
If it is a bad plan, then why
dbcc freeproccache didn't work in getting rid of it?
From what I know, this looked very much like a bad plan, but somehow
dbcc freeproccache didn't work. So, I'm left in the dark, confused about the whole situation.