SQL Server Performance

Execution plan differences on test and production

Discussion in 'T-SQL Performance Tuning for Developers' started by pbur, Feb 2, 2004.

  1. pbur New Member

    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
  2. joechang New Member

    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
  3. bambola New Member

    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?
  4. pbur New Member

    quote:Originally posted by bambola

    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
  5. Luis Martin Moderator

    Did you update statistics on bad plan?


    Luis Martin
    Moderator
    SQL-Server-Performance.com
  6. pbur New Member

    quote:Originally posted by LuisMartin

    Did you update statistics on bad plan?

    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
  7. heron New Member

    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=':)' />]
  8. pbur New Member

    <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.
  9. lhurd New Member

    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.
  10. satya Moderator

    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.
  11. lhurd New Member

    quote:Originally posted by satya

    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.
  12. lhurd New Member

    quote:Originally posted by satya

    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.
  13. lhurd New Member

    quote:Originally posted by satya

    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.
  14. pbur New Member

    quote:Originally posted by lhurd

    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.


    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
  15. lhurd New Member

    quote:Originally posted by satya

    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.

    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


  16. satya Moderator

    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.

Share This Page