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.
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.