SQL Server Performance Forum – Threads Archive

Execution plan differences on test and production

Hello all,
We are experience a very strange problem. We have a query that when run on our test server, runs in just about 1 second. The estimated execution plan matches the actual execution plan. In production, the exact same database (copying the MDF file to the production server), the query runs for 7 minutes. On that server, the estimated execution plan matches the test server’s, but the actual plan is wildly different. So far MS support hasn’t been able to tell us why the production server is choosing a different plan once the query is really run. The test server is a 4 CPU Pentium 2 450 with 2GB RAM and the Production Server is a Dual Pentium 4 2.8 Ghz with 3GB RAM. Disk I/O on both machines is minimal while the query is running. On production, the query consumes one entire processor. We have rebuilt indexes and run sp_updatestats a number of times without change. Anyone have any ideas out there? Thanks,
Patrick

---

you need to provide more info,
what is the query?, what indexes exist for each table
what is the test environ. plan
what is prod. env. plan what are difference between estimated and actual row counts in each step

---

Do you have the same service pack on both machines? if you are using option maxdop, is it still different? could you tell in what way is the execution plan different?

---

Actually, I was running under the assumption that both machines were SP3, they are not. The test machine is stock SQL Server 2000 and the production maching is SP3. Attaching the DB to another SP3 SQL Server machine here produces the same results as in production. So it has something to do with a change in one of the service packs. The big difference between the plans is in the good one, the planner uses an index once for a subquery, in the plan gone bad, it iterates over that index about 70 times. There are about 200K rows in the table/index. Thanks,
Patrick

---

Did you update statistics on bad plan?
Luis Martin
Moderator
SQL-Server-Performance.com

---

I have updated statistics on the whole database and I have even gone so far as to rebuild the indexes on the affected tables. The instances choosing the bad plan do so after estimating what would be the right plan. Thanks,
Patrick

---

If you have ORDER BY clause in your query then just separate query by two, leaving SELECT INTO and possible JOIN(s) in the first one and ORDER BY of the result in the second one.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by heron</i><br /><br />If you have ORDER BY clause in your query then just separate query by two, leaving SELECT INTO and possible JOIN(s) in the first one and ORDER BY of the result in the second one.<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Actually, MS support finally got back to me, and it has to do with something that changed between SP2 and SP3. It has been sent back to engineering and hopefully will have a patch. For some reason SP3 creates an additional internal work table that wasn’t created in SP2 and before which is causing the issue we are seeing.

---

Could you let me know what the resolution was? I have an extremely similar problem at a customer site that is urgent to resolve. Thanks, Lyman Hurd
Interwoven, Inc.

---

Lyman Can you confirm the service pack status on SQL Server & OS? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

---

---

---

The resolution from Microsoft was that SP3 actually fixed a bug in the optimizer that was allowing our query to run faster before. Their solution for me was to run the index tuning wizard twice in a row and apply the suggested indexes. Personally, I’ve never gotten the Index Tuning wizard to give me the indexes (they are going to walk me step-by-step how they did it next week). They sent me the indexes they got as a recommendation and our query got back to sub-second speeds. While I am not huge fan of this solution ( mostly because I can’t reproduce how they got the right indexes ), I think that maybe the "bug" in the optimizer should have stayed. Thanks,
Patrick

---

I apologize to all for the inadvertent "spam". I had composed a lengthy reply but had forgotten to sign into the forum and I was attempting to retrieve my text to submit having logged in which caused a comedy of errors with the back and refresh buttons. I had no idea that it was posting multiple copies of a null response. Apologies to all. We are running SQL 2000 Enterprise Edition. Internally with no service packs and at the customer site with SP3. One of the customer’s test machines has an additional hot fix but I am not sure which one. I’m going to take pbur’s advice and try tweaking the indices. If the suggestions it comes up with either don’t work or seem excessive, I’ll roll my sleaves up and try to rewrite the query. Unfortunately that option is the hardest since it is actually generated programattically via a search interface (our application exposes a logical view of the data that doesn’t exactly match the physical view in the database; e.g., the users can add custom fields without actually adding columns). Thanks, Lyman

---

No worries, about 3 replies.
I would also second Pbur’s tip to work on the indexes and make sure both server and client machines does have similar versions of service packs & hotfixes. Few times problems occurred due to mismatch of MDAC versions from server to client. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>