July 15, 2017 at 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
July 17, 2017 at 3:07 am
SQL_Surfer - Saturday, July 15, 2017 9:15 PMSame 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.
July 17, 2017 at 5:12 am
SQL_Surfer - Saturday, July 15, 2017 9:15 PMSame 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.
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
July 17, 2017 at 6:45 am
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
July 17, 2017 at 8:00 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2017 at 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.
July 17, 2017 at 8:54 am
SQL_Surfer - Monday, July 17, 2017 8:12 AMI 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2017 at 11:02 am
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
July 17, 2017 at 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?
July 17, 2017 at 12:24 pm
SQL_Surfer - Monday, July 17, 2017 12:07 PMGood 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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2017 at 12:42 pm
MAXDOP setting or the Cost Threshold for Parallelism are same. optimize for ad hoc workloads is 1 on server 1 vs 0 on server 2. Would that be it?
July 17, 2017 at 12:44 pm
OK, if CPU, memory, disks setup the same, have you checked the "cost threshold for parallelism" and "max degree of processing" on the 2 servers?
More importantly, looking deeper into the plans, it looks like Server2's plan has a Remote Query for table MasterClientSetUp but that table on Server1 is not Remote, so on Server1 the RealTimeWebApp_Stage database is on the same instance. Even though it looks like a low portion of the query cost, I suspect this could be a big factor in the time difference.
July 17, 2017 at 12:51 pm
That was it. Thank you so much Chris. You are a life savor!!!! Appreciate y'all for help.
July 17, 2017 at 12:56 pm
So, when we use linked server (remote query) in the join, is it safe to assume that we won't ever get parallel query plan?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply