October 8, 2019 at 7:19 pm
We have a clustered sql server, 2016 SP2/CU1, enterprise, with 2 nodes. There is a c# job running that spins off 200+ parallel processes which calls a large stored procedure.
When node 1 is active, the calls to the stored procedure fail with timeout expired on a specific query (a select that we know this is a trouble spot, however only recently started timing out). Our monitoring via solarwinds during this time, also reports CPU at 100%, ~300 active sessions, and extreme amount of wait time (1,200,000 seconds) .
When node 2 is active, the jobs all finish successfully (no timeouts), there is not a lot of wait time (5000 seconds max), CPU is about 30% with ~20 active sessions.
Both nodes seem to be identical as far as configuration goes, what could explain the difference in wait time between the nodes with the same process running? Any suggestion on where to start looking would be appreciated?
October 8, 2019 at 7:24 pm
Hmm, interesting. With those symptoms, I'd probably first look for a physical reason. Bad RAM, bad NIC, bad local drive, some physical failing on one node vs the other.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 9, 2019 at 7:42 am
Page / Table Fragmentation, Statistics last updated?
October 10, 2019 at 1:03 am
Can you check Minimum & maximum memory settings on both the Instances. I had similar issues recently, memory setup was the issue.
October 10, 2019 at 3:24 pm
Hi all, thanks for the replies!
Fragmentation and statistics are good.
The min memory setting is 0 on both, the max is 112G on 1 and 118G on 2.
They are looking into possible hardware issues but say they haven't detected any so far.
October 10, 2019 at 4:31 pm
You have to configure perfmon counter and collect some week data of both nodes when on their active and compare it. You can get some idea.
This may help:
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply