Different plan on secondary vs Primary

Question

Different plan on secondary vs Primary

SqlServerDBA318

SSCrazy

Points: 2353
More actions
March 28, 2018 at 1:53 pm

#344521

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 1

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.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

butcherking13 - Wednesday, March 28, 2018 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!

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SqlServerDBA318 SSCrazy Points: 2353 More actions · Answer 3

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!

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

butcherking13 - Thursday, March 29, 2018 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!

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

butcherking13 - Thursday, March 29, 2018 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!

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software