Same Stored Proc, Different plans
So my last post (yesterday) extolled the virtues of going back to basics.
What triggered the thoughts was that I had the same SP running on my SQL 2008 server, with the exact same parmeters... and yet vastly different plans.
In this event, it is most likely a connection setting difference between the two systems running the stored proc. In my case SSMS and SSRS.
I suggest rather than recompiling every time, or fiddling around with the connection settings (both paths may not work btw), that you take the worst case execution plan, and tune it.
Look at what it is doing and get rid of the 'bad stuff'. Yeah, I said the same thing yesterday, but I wasted a day or two trying to be slick and force the 'good' plan to be used.
What triggered the thoughts was that I had the same SP running on my SQL 2008 server, with the exact same parmeters... and yet vastly different plans.
In this event, it is most likely a connection setting difference between the two systems running the stored proc. In my case SSMS and SSRS.
I suggest rather than recompiling every time, or fiddling around with the connection settings (both paths may not work btw), that you take the worst case execution plan, and tune it.
Look at what it is doing and get rid of the 'bad stuff'. Yeah, I said the same thing yesterday, but I wasted a day or two trying to be slick and force the 'good' plan to be used.
Labels: SET ARITHABORT, SQL Execution Plans, Stored Procedures
posted by TedS at 7:42 AM
0 Comments:
Post a Comment
<< Home