March 28, 2018 at 1:53 pm
I have found an issue where a query plan on the secondary replica is much different than the primary? Results are instant on Primary vs 9 seconds on secondary. Just to test I ran the same query on two other dev boxes and they both come back exactly like the primary. Any idea why the secondary can be so much different? I am on SQL Server 2016 SP1. Thanks you!
March 28, 2018 at 3:21 pm
That is possible that plans are different. What would I do in this case is create a plan guide on the primary replica and check how it works on the secondary.
March 29, 2018 at 7:13 am
butcherking13 - Wednesday, March 28, 2018 1:53 PMI have found an issue where a query plan on the secondary replica is much different than the primary? Results are instant on Primary vs 9 seconds on secondary. Just to test I ran the same query on two other dev boxes and they both come back exactly like the primary. Any idea why the secondary can be so much different? I am on SQL Server 2016 SP1. Thanks you!
Check the settings on the server. Are they same? Cost threshold for parallelism, max degree of parallelism, ANSI connection defaults, all these and more can cause plans to be different between machines even if the data, indexes, statistics and constraints are the same. Also, service packs and cumulative updates, traceflags. All that stuff. All of it needs to be the same and even then you could still end up with some differences in plans (especially regards to parallelism or not) based on the load on the server, available memory, available CPU. However, the primary driving factor are all the server settings.
"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
March 29, 2018 at 7:22 am
Thanks Grant and appreciate the response! Oddly enough, I tested on two different dev boxes as well and as primary they get same plan/time. The query doesn't go parallel and I've gone over all settings and will do a double take. The issue I brought up here is we are not updated on CU after SP1 and I have brought this up multiple times to no avail. I don't get why the SR DBA here and Director are so against patching latest CU. Thankfully we are a premiere account and I have opened a ticket.
Thanks again! By the way, I was in your precon in Richmond and it was great!
March 29, 2018 at 7:43 am
butcherking13 - Thursday, March 29, 2018 7:22 AMThanks Grant and appreciate the response! Oddly enough, I tested on two different dev boxes as well and as primary they get same plan/time. The query doesn't go parallel and I've gone over all settings and will do a double take. The issue I brought up here is we are not updated on CU after SP1 and I have brought this up multiple times to no avail. I don't get why the SR DBA here and Director are so against patching latest CU. Thankfully we are a premiere account and I have opened a ticket.
Thanks again! By the way, I was in your precon in Richmond and it was great!
THANK YOU!
Check the traceflags. There has to be a difference. It's there. You won't get differences in plans otherwise.
"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
March 29, 2018 at 7:45 am
butcherking13 - Thursday, March 29, 2018 7:22 AMThanks Grant and appreciate the response! Oddly enough, I tested on two different dev boxes as well and as primary they get same plan/time. The query doesn't go parallel and I've gone over all settings and will do a double take. The issue I brought up here is we are not updated on CU after SP1 and I have brought this up multiple times to no avail. I don't get why the SR DBA here and Director are so against patching latest CU. Thankfully we are a premiere account and I have opened a ticket.
Thanks again! By the way, I was in your precon in Richmond and it was great!
And yeah, I agree. Getting the CUs installed is a must. Waiting for service packs in this day and age is a mistake. Microsoft is unlikely to release many, if any, SPs any more.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply