Need some help advise on one of the issues that we have bee working on. For one of our SQL databases (SQL2014) we use database mirroring ( i know its old but we are working on migration) and there is a slight difference between 2 servers as below

DC1 (Primary) and DC2 (Secondary) all specs between 2 severs are same except CPU where DC1 has 80 logical processor 20 sockets 4 cores, while DC2 has 64 processors with 16 sockets 4 core. CPU family is same with speed on DC1 being 2.2GHZ and DC2 being 2.4 GHZ

Now we did failover to DC2 and a query/proc was known to have some slowness due to index not covered by the query on DC1 itself running for 12 mins or so. However after failover to DC2 query just been running 20 mins or so.

I did captured the actual exec plan and plans are exactly same for DC1/DC2 with exact same estimates. I also verified the MAXDOP and CTOP to make sure they are same and even that is matching along with other DB properties.

How do i troubleshoot further to find that 8 min extra slowness because both queries have same execution plan and getting same number of threads in parallel just to make sure CPU is not an issue?

  • 1
    "How do i troubleshoot further..." - You upload the actual execution plans for both instances to Paste The Plan and link them in your Post. FWIW, 12 minutes to 20 minutes is about a 66% increase runtime, and going from 80 processors to 64 processors is not that far off for from that, for it to be part of the problem. But we have no way of actually telling without seeing the query and it's execution plan.
    – J.D.
    Oct 2 at 20:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.