October 3, 2014 at 7:55 am
I have a SP that runs on the primary in 18 min and 45 min on the secondary( poorly written cursor,trying to fix it).
Both machines are Exactly the same.
I ran them in the middle of the night when no one was on the Sec. Node as we use it for reporting.
PLE: 7,000+
AVG Disk sec/write below .01
AVG Disk sec/read below .01
CPU below 5%
both machines set a max dop 4
I am out of ideas?
This SP ran fine for months until monday. It went from 18 min to 45min on the secondary only.
THANKS
October 3, 2014 at 11:36 am
When was the last time statistics were updated? Sounds like a bad plan cached on the secondary.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2014 at 12:51 pm
According to microsoft "For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records"
I ran both SP on the Primary and Secondary with Statistics IO on....I got back that same information on disk activity.
CPU time was higher on the Slow node. I increased Mad degree Parallelism from 4 to 12 and SP came back just as fast as the Primary.
Had the server team check hardware and no errors.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply