SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differences in Query execution plan between 2 servers


Differences in Query execution plan between 2 servers

Author
Message
iashurova
iashurova
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
Attachments
amazon_machine.sqlplan (16 views, 31.00 KB)
data_center.sqlplan (12 views, 22.00 KB)
Jared Karney
Jared Karney
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5008 Visits: 3694
Your best bet is to save both execution plans and post them here. Otherwise, it will be hard to tell.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40983 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
iashurova
iashurova
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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.
Attachments
data_center_paral.sqlplan (12 views, 28.00 KB)
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40983 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jared Karney
Jared Karney
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5008 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
iashurova
iashurova
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 177
We run it so many times, 2 people. I wish I would make a mistake Sad 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".
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40983 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
iashurova
iashurova
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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?
HowardW
HowardW
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2275 Visits: 9892
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search