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


Same query different Execution plan on two different server


Same query different Execution plan on two different server

Author
Message
SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1674 Visits: 1098
Same database, same tables, same query but running on two different servers bringing two different execution plans. plan1_server1 gets executed in under 2 secs but plan2_server2 gets executed in 8 seconds. Both returns the same number of rows. Can anybody help me out here looking at these two execution plans? What do i need to do on server 2, to get the result at the similar speed?

--SQL_Surfer
Attachments
plan2_server2.sqlplan (15 views, 8.00 KB)
plan1_server1.sqlplan (10 views, 13.00 KB)
tim.ffitch
tim.ffitch
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 47
SQL_Surfer - Saturday, July 15, 2017 9:15 PM
Same database, same tables, same query but running on two different servers bringing two different execution plans. plan1_server1 gets executed in under 2 secs but plan2_server2 gets executed in 8 seconds. Both returns the same number of rows. Can anybody help me out here looking at these two execution plans? What do i need to do on server 2, to get the result at the similar speed?

--SQL_Surfer

Update statistics on both databases and try again.

ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41937 Visits: 20008
SQL_Surfer - Saturday, July 15, 2017 9:15 PM
Same database, same tables, same query but running on two different servers bringing two different execution plans. plan1_server1 gets executed in under 2 secs but plan2_server2 gets executed in 8 seconds. Both returns the same number of rows. Can anybody help me out here looking at these two execution plans? What do i need to do on server 2, to get the result at the similar speed?

--SQL_Surfer

Neither SSMS nor SQL Sentry Plan Explorer will open these files.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72840 Visits: 40957
files are actually zip files with a sqlplan extension.

On Plan1_server1 you can see the estimated and actual number of rows are way off(estimated = 91.2K, actual = 1.8K, so I wholeheartedly agree that you should update statistics first.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99237 Visits: 33014
The files inside the zips are .queryanalysis files along with some JSON file. I have to ask. What are these and where do they come from?

----------------------------------------------------
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
SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1674 Visits: 1098

I ran against SQLSentry and generated the file. It saves as .queryanalysis file. But I couldn't upload them. So, I had to rename them as .sqlplan.


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99237 Visits: 33014
SQL_Surfer - Monday, July 17, 2017 8:12 AM

I ran against SQLSentry and generated the file. It saves as .queryanalysis file. But I couldn't upload them. So, I had to rename them as .sqlplan.



Oh, that explains why I can't see anything. I don't have SQLSentry tools. Sorry, can't help here.

----------------------------------------------------
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
Chris Harshman
Chris Harshman
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10996 Visits: 4676
I notice on Server1 the execution plan went parallel in multiple places, does Server1 have more CPU cores in it than Server2? That could be a contributing factor in it coming up with a different plan. Here's a good article with some things to consider that affect the plan on different servers:
https://sqlperformance.com/2014/12/sql-plan/different-plans-identical-servers
SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1674 Visits: 1098
Good catch. CPU and memory and I/O same. But tempdb config is slightly different. I've tempdb split inot 8 files on server 1 vs 1 file. Would that cause parallel plan on server 1?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99237 Visits: 33014
SQL_Surfer - Monday, July 17, 2017 12:07 PM
Good catch. CPU and memory and I/O same. But tempdb config is slightly different. I've tempdb split inot 8 files on server 1 vs 1 file. Would that cause parallel plan on server 1?

That wouldn't, but differences in the MAXDOP setting or the Cost Threshold for Parallelism could. Are these the same.
Also, has anyone checking the plans looked at the ANSI settings, etc., to see if there are possible reasons the plan compiling two different ways apart from statistics (which seems to be the likely culprit). I'd do this but since the plans aren't in standard format, I can't help.


----------------------------------------------------
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
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