A Tale Of Two Execution (Updated)

<div>Update: Scroll to the bottom for more information added after originally posting. Earlier this week a coworker asked me for help figuring out why an execution plan was different for a query run against two identically configured servers. I've since spent the better part of the week trying to figure out why, and after several suggestions from folks on Twitter and two MVPs I still don't have an answer. First, A Little Background My company has 9 SQL Servers that are used by web servers to render pages for a web application. Each SQL server is more or less identical to one another – Dell 2950, 4 GB RAM, Windows 2003 R2 x86, and SQL 2005 Standard Edition. The /3GB switch is set to let SQL use 3GB of RAM, and these are dedicated SQL servers with no other processes running on them. Every server is a subscriber to transactional replication so they've got identical data and schema. There's no SSIS, SSAS, or SS-anything else-S at play on these boxes. The only difference is that a few servers have dual-dual CPUs and a few have dual-quad CPUs, but as you'll read in a minute that doesn't seem to matter. The Problem Now here's the problem…At the beginning of the week, for this simple query 1 of the 9 servers resulted in execution plan "A" and the other 8 used plan "B". Things have shifted; now 7 of the 9 are using plan "A", but 2 servers are still using plan "B". The first thing that came to mind was out of date or updated statistics. So I isolated one server using plan "A" and one using plan "B" and ran an UPDATE STATISTICS with FULLSCAN on all the indexes for the two tables included in the query. I verified the statistics from both servers matched by reviewing the results of DBCC SHOWSTATISTICS. No change in execution plans. (For what it's worth, AUTO_UPDATE_STATISTICS is enabled and we have an agent job that runs sp_updatestats throughout the day to keep statistics up to date) I looked at SET options for connections. Identical. I looked at collations. Identical. I looked at the data itself. Identical. I looked at server configuration options (sp_configure). Identical. I looked at database compatibility levels. Identical. I looked at memory available to SQL Server. Identical as far as I can tell. I looked at SQL server versions. A-ha! <ul> <li>Servers with plan "A": <ul> <li>9.00.4230.00 </li> <li>9.00.4226.00 (6 of these) </li> </ul> </li> <li>Servers with plan "B": <ul> <li>9.00.4262.00 </li> <li>9.00.3054.00 </li> </ul> </li> </ul> So maybe there's something there with the version difference. Except that the server that started the week on plan "A" is the one running 9.00.4230.00. The 6 servers running 9.00.4226.00? They were using plan "B" and have since switched to plan "A". And what about the server running 9.00.4262.00? That's a later version than the one running 9.00.4230.00, yet it's using plan "B". That makes me doubt that it's something related to the version number. Just for fun and to add to the confusion, I changed the table variable in the query to a temporary table…and the plans matched! Think You Can Figure It Out? I'm stumped, and I'd love to hear what you think the reason for the difference in plans might be. I've linked to a the schema for the tables, the output of DBCC SHOWSTATISTICS for the index that I think is the root cause of the problem, the query itself, and execution plans "A" and "B" below. Object names and servers have been changed to protect the innocent. If there's something I'm missing that you want to know more about, contact me on Twitter (@SQLDBA) or leave a comment. I'd love to figure out what's going on! <a href="http://kendal-van-dyke.googlegroups.com/web/DifferentExecutionPlansProblem.zip?gda=nFfbT1QAAAD7qszPbVd2vy0smg0Y98KMmo0Rd3xvfzK-2FO55Z41lhja_XgPBSXJRwbBWdNOh3No3Dc4Co90pjHt45H66Ci2VervUohE3YNENn3wMh1Pnc3OAWZC50hVl-fZ6-QcRqg&gsc=rMvUIgsAAADXgsKtWdaf1AfDuKVGYXrw">Download the schema and queries here</a>   UPDATE: OK, as some people were quick to point out, the XML execution plans I included showed two different builds of SQL. So I went poking around and found that one of the 9.00.4266.00 builds has gone back to using plan "B" again. That means I have two different servers on the same build with different execution plans. To be sure it wasn't obviously statistics related I ran another UPDATE STATISTICS with FULLSCAN (and verified that statistics matched across servers), cleared the buffer cache, and the plans were still different. I also realized that in the course of changing object names I ended up with a duplicate column. I have fixed the schema script and updated the download with the plan "A" and plan "B" from the 9.00.4266.00 builds. </div>

4 comments

Brad Schulz said...: This comment has been removed by the author.; February 10, 2010 at 8:08 PM
Michael J. Swart said...: 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 this question on Stack Overflow.

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.

This ultimately led to looking at query hints like: OPTIMIZE FOR() and OPTIMIZE FOR UNKNOWN; February 10, 2010 at 9:03 PM
Mike Wells said...: I'm gonna have to go with Michael on this, and it's not just a name thing {=0).

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.

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.; February 11, 2010 at 6:34 AM
Granted said...: 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.

You can see this by looking at the Properties for the INSERT operator on each plan.; February 11, 2010 at 8:56 AM

Kendal Van Dyke

4 comments

Popular Posts

Labels

What I'm Saying On Twitter

Kendal Van Dyke

Share This

Related Posts

4 comments

Popular Posts

Labels

What I'm Saying On Twitter