Statistics Issues
I am having problems with one of our biggest Customers whereby every day a specific Stored Procedure is causing locks and failing to run. We are having to recompile the procedure to fix this.
Attached are the plans before and after the recompile, I am struggling to work out why the Stats are so out prior to the recompile and appear ok afterwards? If it was a Statistics issue surely I'd see the same results after a recompile?
Any help would be greatly appreciated.
Many Thanks,
Ashley Theobald
The plan parameters before recompile:
The plan parameters after recompile:
The date range between the [@StartDate] and [@EndDate] before recompile and after the recompile are considerably different. This difference in the date range on the Compiled Values will result in the difference in the estimated rows. 698,249 before vs 297 after.
The [@AncillariesOnly] will also contribute to the difference.
Here's a post that explains in detail:
http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
If I understand correctly, SQL is creating and using an Execution Plan which was created based on a large date range which is highly inefficient when running for smaller date ranges?
Many Thanks,
Ashley