Statistics Issues

Ashley Theobald 2015-07-17 09:22:52

Hi,

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

sdyckes 2015-07-17 22:04:57
If you compare the Compiled Value between the 2 plans, you will see a couple of differences. This can be found in the Parameters tab of the Plan Diagram portion.
alt text

The plan parameters before recompile:

alt text

The plan parameters after recompile:

alt text

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.

Ashley Theobald 2015-07-20 08:58:44
That's fantastic, thanks for the response.

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

SQLSaurus 2015-07-20 10:55:31