Post reply

Execution plans

  • January 11, 2006 at 8:14 am

    #170544

     

    I have two servers dev and production with the same software and hardware configuration and patching (including SQL server). I have copied one database from production to development and ensured that the indexes etc have been updated on both. Then I ran an execution plan on a SQL statement. The results I get are significantly different from one server to the other – for example on server is running a table scan on a table the other an index seek.

    Is this “acceptable” SQL server behaviour? I, possibly naively, expected the plans to be very similar.
     
    Regards
     
    Stuart

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • January 11, 2006 at 11:52 am

    #614149

    >>ensured that the indexes etc have been updated on both.

    What does "updated" mean in this context ?

    - DBCC DbReIndex() or IndexDefrag() ?

    - Statistics updated ?

  • January 12, 2006 at 2:15 am

    #614263

    Second the question of what exacly you did with the indexes.

    How big is the table in question?

    It is indeed acceptable for the execution plans to differ. On my production server if I run the same query at different times of the day I get very different execution plans. It depends on the memory that is available and how busy the CPU(s) are.

    If there is little free memory but the CPU(s) are idle, then SQL might pick a plan that is a little more cpu intensive but uses less memory than it would in the opposite condition.

    Under light CPU load it might run a query in parallel, whereas if the CPU load was heavier it might not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 12, 2006 at 9:30 am

    #614408

    Update stats on both

  • January 12, 2006 at 11:03 pm

    #614536

    How did you copy the database from production?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 16, 2006 at 1:00 am

    #614844

    All,

    Thanks for your comments. I think the biggest issue is what was going on in the back ground on both the servers. There quite large differences in the back ground services / applications.

     

    Thanks for your input

     

    Stuart

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • January 16, 2006 at 9:22 pm

    #615046

    Not sure why that would matter at all but OK, whatever you say.  I was thinking that maybe the servers had some different settings (like collation and memory allocation), a different amount of disk space, amount of memory (hashing/loop lookups/bookmarks), # of CPU's (parallelism), TempDB size (hashing/loop lookups/bookmarks), Service Pack, location split of MDF/LDF files, or even a different flavor of SQL Server altogether (ie. Standard Edition vs Enterprise Edition) as Development servers typically do when compared to their much more powerful Production Servers.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 18, 2006 at 9:10 am

    #615515

    In answer to all your points / questions - in no particular order

    Data was moved by scripting the table including indexes running on Dev server then DTS the data between the two servers.

    Indexdefrag and update Statistics run on both servers.

    Table is around 8 million rows - not that big

    The servers were configured at the same time, collation order memory usage set the same on both. The main differences however, is that the Dev server also has IIS running on it along with Analysis Services, therefore at least two more background services running than on production. Also there are data cubes on the Dev server so there the free disk space is less on the Dev server. Temp db was approx the same size on both servers at the time of the query running.

    It was these differences which led me to believe that this was the difference in the execution plan. Unless anyone can think of any thing else . . . .

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • January 18, 2006 at 8:30 pm

    #615682

    I'm thinking that those extra services take up some of the memory that would normally have been available to SQL Server on otherwise identical machines so I'm thinking you're right.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 18, 2006 at 11:11 pm

    #615689

    Also index defrag only reorders the leaf-nodes of an index (as opposed to index rebuild which completely recreates it.)

    Hence, on your dev server the higher-level nodes could have a different fragmentation to that of the prod server. (try DBCC SHOWCONTIG on both)

    In addition, I would assume that the load on the prod machine was higher than the dev machine (users connectng to SQL)?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 19, 2006 at 1:39 am

    #615697

    What is DBCC DbReIndex() or IndexDefrag() ?

  • January 19, 2006 at 1:49 am

    #615699

    DBCC DBREINDEX

    Rebuilds one or more indexes for a table in the specified database. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. This means an index can be rebuilt without knowing the table's structure or constraints, which could occur after a bulk copy of data into the table.

    DBCC INDEXDEFRAG

    Defragments clustered and secondary indexes of the specified table or view. DBCC INDEXDEFRAG can defragment clustered and nonclustered indexes on tables and views. DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance.

    They are both ways to reduce the fragmentation of an index. Look in Books online if you want all the details.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply