Same Query, Different Server, Different Plan

  • Hello,

    We have Identical hardware on two servers. Also, Index fragmentation and statistics are same. But, Execution plan is different for the same query.

    Server 1 was generating bad plan. Server 2 was generating a good plan. Attached are the bad plan, good plan and the SQL.

    Server 1 Configuration:

    Memory: 256GB Memory

    Processor: 4 sockets, 8 Cores, 16 Logical Processor.

    Cost Threshold Of Parallelism: 5

    Max Degree of Parallelism : 4

    Server 2 Configuration:

    Memory: 128GB

    Processor: 4 sockets, 8 Cores, 16 Logical Processor.

    Cost Threshold Of Parallelism: 5

    Max Degree of Parallelism : 4

    We observed CXPackets waits high in Server1 and we forced the query with MAXDOP = 1, good plan is going in sequential and completing in 2min24 Seconds.

    When allowed to go in parallel, bad plan is completing in 10 minutes. Can the same SQL take different execution plans, given that Statistics, Index Fragmentation and Server configurations are same?

    We do have lot of load on Server 1 through out the day. Would SQL Server take the load on the server into consideration for designing an execution Plan?

    Thanks
    Jagan K

  • jvkondapalli (10/6/2014)


    Hello,

    We have Identical hardware on two servers. Also, Index fragmentation and statistics are same. But, Execution plan is different for the same query.

    Server 1 was generating bad plan. Server 2 was generating a good plan. Attached are the bad plan, good plan and the SQL.

    Server 1 Configuration:

    Memory: 256GB Memory

    Processor: 4 sockets, 8 Cores, 16 Logical Processor.

    Cost Threshold Of Parallelism: 5

    Max Degree of Parallelism : 4

    Server 2 Configuration:

    Memory: 128GB

    Processor: 4 sockets, 8 Cores, 16 Logical Processor.

    Cost Threshold Of Parallelism: 5

    Max Degree of Parallelism : 4

    We observed CXPackets waits high in Server1 and we forced the query with MAXDOP = 1, good plan is going in sequential and completing in 2min24 Seconds.

    When allowed to go in parallel, bad plan is completing in 10 minutes. Can the same SQL take different execution plans, given that Statistics, Index Fragmentation and Server configurations are same?

    We do have lot of load on Server 1 through out the day. Would SQL Server take the load on the server into consideration for designing an execution Plan?

    This looks just like the question you posted on stackoverflow not long ago but at least here we have some details. 😀

    Those two plans look really similar to me. I would start with removing that NOLOCK hint in there. That is just going to add some unnecessary complications to deciphering what is going on. I would recommend removing that hint entirely from your arsenal.

    Not quite sure how you can say that stats and index fragmentation are the same on both databases. The number of rows in the tables are not consistent across these two databases so comparing one to the other is a bit troublesome.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • First, Hardware is not identical, server 1 has twice as much memory as server two.

    Second, the plans you provided use different values for the parameters.

    good plan: [@dtImportOrder]= '2014-10-01 14:56:58.380'

    [@fkRevPeriod] = 2014001.16

    bad plan: [@dtImportOrder]= '2014-10-06 11:17:45.657'

    [@fkRevPeriod] = (2014001.12)

    the good plan returns 3130 rows(96 million estimated) and the bad plan returns 0 rows(82 million estimated)

    The difference in parameters might account for the different plan but at least if the comparison was using the same parameters that possibility would be taken out of the equation.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • The database was restored from Server 1 to Server 2. So, the Stats and Index fragmentation should be same.

    According to what I see in the Execution Plans, There is difference between good and bad plan.

    Thanks
    Jagan K

  • I'm not sure that it would help, but try to add condition "tgt_roi.fkRevPeriod = @fkRevPeriod" into ON clause, like this:

    ON tgt_roi.fkOrderItem = src_oi.fkOrderItem AND tgt_roi.fkRevPeriod = src_oi.fkRevPeriod AND tgt_roi.fkOrder = src_oi.fkOrder

    AND tgt_roi.fkRevPeriod = @fkRevPeriod


    Alex Suprun

  • Robert klimes (10/6/2014)


    First, Hardware is not identical, server 1 has twice as much memory as server two.

    Second, the plans you provided use different values for the parameters.

    good plan: [@dtImportOrder]= '2014-10-01 14:56:58.380'

    [@fkRevPeriod] = 2014001.16

    bad plan: [@dtImportOrder]= '2014-10-06 11:17:45.657'

    [@fkRevPeriod] = (2014001.12)

    the good plan returns 3130 rows(96 million estimated) and the bad plan returns 0 rows(82 million estimated)

    The difference in parameters might account for the different plan but at least if the comparison was using the same parameters that possibility would be taken out of the equation.

    Robert, Eventhough, the @dtImportOrder and fkRevPeriod are different between the plans, the plan is not changing. Those were the plans that were available handy. So, I added them.

    Thanks
    Jagan K

  • I think where you are getting a difference is when you are loading the CTE for your source.

    run the following on both servers and check if you get the same results and check the plans. after you run it on both servers change the where clause value to 2014001.16 then run both again and compare plans.

    ;WITH cte_source_order_items ( fkOrderItem , fkRevPeriod , fkOrder , fkProductComponent , dtCreated , bCancelled, fkUserOrder, ExtendedPrice, fkParentOrderItem, fkProductConfiguration, fkState, fkCounty, fkShipMethod

    , fkRelationshipType, nExtendedPrice, fkProcessingStatus, fkProcessingOrder, fkProcessingOrderOrderItem, sProductName, bFullFilled, fkOwnUserOrder, fkUserOrderAnyOrder, fkUserOrderParentOrderItem, fkRootOrderItem, fkStateParentOrderItem )

    AS ( SELECT

    fkOrderItem = OI.pkOrderItem

    , fkRevPeriod = @fkRevPeriod

    , fkOrder = OI.fkOrder

    , fkProductComponent = OI.fkProductComponent

    , dtCreated = OI.dtCreated

    , bCancelled = OI.bCancelled

    , fkUserOrder = OI.fkUserOrder

    , ExtendedPrice = OI.nExtendedPrice

    , fkParentOrderItem = COALESCE(OI.fkParentOrderItem, OI.fkSourceOrderItem)

    , fkProductConfiguration = OI.fkProductConfiguration

    , fkState = OI.fkState

    , fkCounty = OI.fkCounty

    , fkShipMethod = OI.fkShipMethod

    , fkRelationshipType = OI.fkRelationshipType

    , nExtendedPrice = OI.nExtendedPrice

    -- v10.0 Changes, Upgrade/Downgrade and Remove Cancel date

    , fkProcessingStatus = OI.fkProcessingStatus

    , fkProcessingOrder = OI.fkUserOrder

    , fkProcessingOrderOrderItem = CASE WHEN OI.fkUserOrder IS NOT NULL THEN OI.pkOrderItem ELSE NULL END

    , sProductName = OI.sProductName

    , bFullFilled = 0

    , fkOwnUserOrder = OI.fkUserOrder

    , fkUserOrderAnyOrder = OI.fkUserOrder

    , fkUserOrderParentOrderItem = CASE WHEN OI.fkUserOrder IS NOT NULL THEN OI.pkOrderItem ELSE NULL END

    --v9.0 Changes UNDO

    , fkRootOrderItem = CASE WHEN COALESCE(OI.fkParentOrderItem, OI.fkSourceOrderItem) IS NULL THEN OI.pkOrderItem ELSE NULL END

    , fkStateParentOrderItem = CASE WHEN OI.fkState IS NOT NULL THEN OI.pkOrderItem ELSE NULL END

    FROM

    dbo.RevOrder AS RO

    INNER JOIN dbo.vw_OrderItem AS OI (NOLOCK) ON RO.fkOrder = OI.fkOrder

    WHERE

    RO.fkRevPeriod = 2014001.12 )

    SELECT *

    FROM cte_source_order_items

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • RObert, I'm in the process of synching my Server2 from Server 1. Once, its complete, will send the results.

    Thanks
    Jagan K

  • Do they have the exact same SQL Server version and service pack level?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Yes. Same version and Save SP level

    Thanks
    Jagan K

  • Here is the million dollar question!

    Does the query optimizer consider the load on the server when it compiles an execution plan?

  • sharadov (10/6/2014)


    Here is the million dollar question!

    Does the query optimizer consider the load on the server when it compiles an execution plan?

    No

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I'm on my phone, so I can't look at the plans and this will be short.

    Because we have two different servers with different memory, I would focus there. Validate all server settings that can be the same are. Check the ANSI connection settings too.

    From the sounds of things you have different plans for identical parameters, business. You posted plans with changed parameters. That makes it harder to compare plans because it could just be bad parameter sniffing.

    Still, sounds like server differences.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The original question is totally wrong. Who cares why the query plan is different on these 2 servers or if one server has more memory or if there is a few records difference in the result? Why do we need to waste time on finding the differences?

    The question here is how to make the query run fast on BOTH servers even if the plans are slightly different. For that we need to make sure that good enough plan is generated on both servers. I don't see why one plan is called good and another one bad, because they both are terrible.

    I would strongly suggest to put a filter condition on a target before the merge statement.

    So, try to replace

    MERGE dbo.RevOrderItem AS tgt_roiwith

    ,cte_target AS

    (

    SELECT * FROM dbo.RevOrderItem WHERE fkRevPeriod = @fkRevPeriod

    )

    MERGE cte_target AS tgt_roi


    Alex Suprun

  • Grent/Robert/Sean, Attached are the good and bad plans. These are obtained after refreshing Server2. Still the server configuration is same as before(not identical memory wise). We ran the query without MAXDOP hint.

    Robert, I ran the query you gave and its returning same set of rows 80164.

    Alexander, I do understand that there are ways to fix the query and get over with this problem. The Idea is to understand why we are seeing different plans in different servers. Correct me if I'm wrong, SQL Server has no insight on the memory and the current load on the server while designing an execution plan. So, given the table stats are same and indexes are equally fragmented on both sides, why are the query plans different? - this my basic question that I want to get away from this issue.

    Thanks
    Jagan K

Viewing 15 posts - 1 through 15 (of 16 total)

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