April 16, 2020 at 1:30 am
Hi Team ,
We have Prod publisher database and subscription database using the transaction replication .Last week one of prod application were completely slow due to bad query .So we recreate the query and tested in publisher , it was taking 14 sec meanwhile our developer has executed same query on Subscription database , it is execute within 2 sec . Please find the below configuration server
Publisher : SQL server 2017 CU15, 16 Core and 128 GB, Windows server 2016
Subscription : SQL server 2017 CU15, 4 Core ,32 GB and windows server 2016.
We have updated the statistics ,rebuild index and analyses query execution plan , i don't find any findings.
It would be great if anyone faced same situation and found the solution .
April 16, 2020 at 8:23 am
Are you updating statistics with the same frequency on both servers? Please post the actual execution plan for the query on each server.
John
April 16, 2020 at 10:12 am
Hi John,
Thanks for reply my query , We are updating the statistics in same frequency which is publisher is scheduled on Sunday morning 4 am and Subscription is schedule on Saturday 10 PM. I've checked the execution plan to both server . The publisher database or server is using the index scan and subscription is using the index seek .
Please find the screen shot for execution plan.
April 16, 2020 at 10:17 am
If you've got different execution plans, chances are something is different. It could be that the statistics are more up to date on one server than the other, or maybe the servers are at different versions or SP/CU levels, or you have different trace flags enabled, or the databases are at different compatibility levels.
Also, once a week really isn't often enough to update statistics on an OLTP server. I would recommend doing it every day.
Please post the execution plans themselves (not just a picture) if you need any further help.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply