Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Differences in Query execution plan between 2 servers Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 3:34 PM
Points: 24, Visits: 177
Hi all,

I have a server running on Amazon which was duplicated on Data Center. Databases are identical, statistics is uptodate, indexes are not fragmented. Query takes 4 min on 1 server and never come out on another. I tried re-writing query, but nothing helps. Execution plan is showing 90-98% of sorting or hash match. I tried to put XML of the query from the "good" machine and force the 2nd machine to use it - this did work. But the moment I remove it, the performance is bad again. The question is - what kind of settings can cause SQL Server 2008 R2 to choose wrong execution plan and what needs to be changed?

Thanks.
Irina

P.S. Both plans are attached. Amazon_machine is the good one and data_center is the bad one


  Post Attachments 
amazon_machine.sqlplan (12 views, 31.59 KB)
data_center.sqlplan (7 views, 22.34 KB)
Post #1337495
Posted Monday, July 30, 2012 1:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:23 AM
Points: 2,691, Visits: 3,376
Your best bet is to save both execution plans and post them here. Otherwise, it will be hard to tell.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1337505
Posted Tuesday, July 31, 2012 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
Looking at the plans, the first thing that jumps out is that the estimates are different between the two plans. This suggests that you either have completely different data sets or completely different sets of statistics for the same data set. It has to be one or the other. The optimizer works from the statistics it has and yours are absolutely different.

That said, one plan is parallel and the other is not. Do you have different cost threshold for parallelism between the two servers? Do the two servers have different numbers of CPUs? Does one of them have affinity or something else along those lines different than the other?

You also have a warning on the temp table on the poorly performing plan. It's missing statistics. Are auto-update stats turned off on that server?

Those are the initial things I can see that you can check into to see why you're getting such different plans between the two servers.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1337847
Posted Tuesday, July 31, 2012 7:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 3:34 PM
Points: 24, Visits: 177
Thank you very much for your suggestions.
Please see attached the new execution plan after parallelism was turned on.
The dataset is absolutely the same, the number of processors - 4 on a "good" machine and 8 on a "bad"
I am not sure if autostatistic is on, but I run stats collection and it said it was not needed and no changes to execution plan.


  Post Attachments 
data_center_paral.sqlplan (8 views, 28.41 KB)
Post #1337906
Posted Tuesday, July 31, 2012 7:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
One is estimating 230000 rows and the other is estimating millions. That is either a difference in data or in stats on the data. Must be. No other options.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1337910
Posted Tuesday, July 31, 2012 8:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:23 AM
Points: 2,691, Visits: 3,376
Grant Fritchey (7/31/2012)
One is estimating 230000 rows and the other is estimating millions. That is either a difference in data or in stats on the data. Must be. No other options.
Or a slight difference in the query resulting in an accidental crossjoin(s)? To the OP, I'm not suggesting you are lying about the queries being exactly the same, its just that we all make mistakes (from time to time lol) and there are few other explanations.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1337912
Posted Tuesday, July 31, 2012 8:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 3:34 PM
Points: 24, Visits: 177
We run it so many times, 2 people. I wish I would make a mistake Just run out of options.
I tried yesterday to run a query with an execution plan from the "good" one and it did work, but for some unknown reasons SQL Server is choosing the wrong plan when it runs query as it is, even i tried to put tables in a different order in the "from" clause. We checked settings for the sql server and it is the same. I checked auto-update statistic option and it is set to "true".
Post #1337923
Posted Tuesday, July 31, 2012 8:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
SQLKnowItAll (7/31/2012)
Grant Fritchey (7/31/2012)
One is estimating 230000 rows and the other is estimating millions. That is either a difference in data or in stats on the data. Must be. No other options.
Or a slight difference in the query resulting in an accidental crossjoin(s)? To the OP, I'm not suggesting you are lying about the queries being exactly the same, its just that we all make mistakes (from time to time lol) and there are few other explanations.


Could be, but I compared the two queries. They look the same. I'm still on stats. Gotta be.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1337948
Posted Tuesday, July 31, 2012 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 3:34 PM
Points: 24, Visits: 177
As I mentioned we did run statistics and it didn't help. The other difference is that data center machine is clustered (3 physical machines with 3 sql server instances). May some settings be involved?
Post #1337952
Posted Tuesday, July 31, 2012 8:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 1,232, Visits: 9,678
It does sound like stats one way or another, but as your non-ANSI joins are not simple equality checks, you could easily end up with a more stable plan by converting these to ANSI joins. I've given a quick example below - please check the results before using:

SELECT  pixel ,
syndicationOutletId ,
outlet ,
geo.[metro-code] ,
dma.metroName ,
dma.regions ,
SUM(imps) impressions ,
COUNT(imps) uniques
FROM #temp4 t
INNER JOIN VINDICOSUITE_NETACUITY.dbo.GeoDatabase geo WITH ( NOLOCK) ON t.ipNumber BETWEEN geo.ipFrom AND geo.ipTo
INNER JOIN VINDICOSUITE_NETACUITY.dbo.Metro dma ON geo.[metro-code] = dma.metroKey
WHERE geo.[metro-code] NOT LIKE 'metro-code0'
GROUP BY syndicationOutletId ,
geo.[metro-code] ,
dma.metroName ,
outlet ,
pixel ,
dma.regions


I'd also suggest not using the NOLOCK clause if you can help it. It's pretty dangerous unless you don't mind the results being inaccurate.
Post #1337953
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse