General question on query performance
This is a generic question regarding query optimization.The query is working fine yesterday i.e. it was running for more than 5 mins. After looking at the actual plan todo I see the statistics are off once again. When I try to run the same query again, it takes more time. Basically, I wanted to know why is it so? I see estimated and actual no of rows are off. I want ed when does statistics go off ? What are the reasons for it.
Could anyone please shed some light on this. Anyone observed such kind of patterns and why it happens in that way? what things need to be watch out for?
Here specific scenario where I am facing issues dealing with statistics. Please find attached screenshot.
Basically, I found a table nonclustered index where Estimated number of rows and Actual number of rows are not matching. So, I dropped and re-created the index and re-ran the query still I see the same. I even tried to update the statistics of that particular table with FullScan, still it didn't help. WHY? How can I keep the statistics up to date so that Estimated and Actual number of rows match?
Any suggestions please.