Thamizh (7/21/2013)
Will "DBCC FREEPROCCACHE" clear the old plan ?
I'm trying to find out all the possible outcomes if i drop and create the index?
If i get that, I can avoid the "dropping & recreating index" and go to direct steps(example:freeproccache,updatestats,etc..).
Dropping and recreating means that you flush all plans related to the table and you also update statistics with fullscan. If it's a parameter-sniffing issue, flushing the plan is enough. If the issue is due to outdated statistics, it's not.
One thing to check is if the query includes a search condition to select "the most recent rows" or somesuch. Such queries can quickly get problem with outdated statistics, because all search conditions fall outside the histogram.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]