tag:blogger.com,1999:blog-2173119910600284569.post7502330826400764507..comments2024-02-06T04:58:09.639-05:00Comments on Kendal Van Dyke: A Tale Of Two Execution (Updated)Unknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-2173119910600284569.post-47710335467139818092010-02-11T08:56:13.963-05:002010-02-11T08:56:13.963-05:00I didn't see anyone else comment on this, but ...I didn't see anyone else comment on this, but Plan A is terminating early within the optimization process because of a timeout. Plan B is completing it's optimization. That would explain the differences. Why precisely Plan is terminating is hard to know, but the differences in SQL Server version and any other load on the server could suggest that one of the servers isn't able to complete the optimization process, hence the different plan.<br /><br />You can see this by looking at the Properties for the INSERT operator on each plan.Grantedhttps://www.blogger.com/profile/05127416848859901204noreply@blogger.comtag:blogger.com,1999:blog-2173119910600284569.post-55359018695518749182010-02-11T06:34:10.693-05:002010-02-11T06:34:10.693-05:00I'm gonna have to go with Michael on this, and...I'm gonna have to go with Michael on this, and it's not just a name thing {=0).<br /><br />It sounds like it's related to parameter sniffing. The first time the proc compiles the optimizer uses the actual parameters passed in to the proc. This can cause a less than optimal plan to be cached depending on what parameters are used first.<br /><br />Another option for testing the theory is to start the proc by copying the parameters to local variables and using the variables in the rest of the proc. Since the variables aren't calculated until the actual execution the optimizer will make a guess as to what the value might be based on data distribution in the table. This may not give you the best plan, but it should be a consistent one. Useful for troubleshooting, if nothing else.Mike Wellsnoreply@blogger.comtag:blogger.com,1999:blog-2173119910600284569.post-73676445347297642112010-02-10T21:03:33.213-05:002010-02-10T21:03:33.213-05:00This week I experienced a similar issue and I saw ...This week I experienced a similar issue and I saw different query plans which came from the first parameters that were passed into a stored procedure when the query was compiled. The ultimate goal was to get a stable query plan, and that ultimately led to <a href="http://stackoverflow.com/questions/2225325/is-this-query-equivalent-to-sql-server-2008s-optimize-for-unknown" rel="nofollow">this</a> question on Stack Overflow.<br /><br />Maybe try to dig into the query plans on your server to try to determine what values (if any) the query was compiled with. In my case the differences boiled down to different "estimated row count" values even though the query was the same, stats were the same and the only thing different was the parameters that the query got compiled with. <br /><br />This ultimately led to looking at query hints like: OPTIMIZE FOR() and OPTIMIZE FOR UNKNOWNMichael J. Swarthttps://www.blogger.com/profile/05408240220683534698noreply@blogger.comtag:blogger.com,1999:blog-2173119910600284569.post-235765971824288532010-02-10T20:08:22.953-05:002010-02-10T20:08:22.953-05:00This comment has been removed by the author.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.com