October 6, 2014 at 12:38 pm
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
October 6, 2014 at 1:18 pm
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/
October 6, 2014 at 1:20 pm
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.
October 6, 2014 at 1:24 pm
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
October 6, 2014 at 1:28 pm
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
October 6, 2014 at 1:29 pm
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
October 6, 2014 at 1:49 pm
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
October 6, 2014 at 2:06 pm
RObert, I'm in the process of synching my Server2 from Server 1. Once, its complete, will send the results.
Thanks
Jagan K
October 6, 2014 at 2:11 pm
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
October 6, 2014 at 2:13 pm
Yes. Same version and Save SP level
Thanks
Jagan K
October 6, 2014 at 2:57 pm
Here is the million dollar question!
Does the query optimizer consider the load on the server when it compiles an execution plan?
October 6, 2014 at 3:00 pm
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
October 6, 2014 at 3:06 pm
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
October 6, 2014 at 4:40 pm
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
October 6, 2014 at 5:32 pm
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