February 8, 2017 at 4:42 am
TheSQLGuru - Tuesday, January 31, 2017 2:52 PM[...]
Please do drop us a line about how things go after you are able to reconfigure DB-11. I will offer you a free hour of consulting if that doesn't fix the issue. 😎
Hi again Kevin,
I did as you said and turned off HT, then I changed the Processor Affinity mask to use only 16 CPUs (the last 8 on each socket) and failed over the mirror. However, this brought the CPU load way up, i.e. from 5-6% to 30+%, and since we don't like to run that high for non-peak operations I had to think again. The worker count though was well within the limits of SQL Server and that confirmed that I was on the right track.
I compared the errorlog on the two servers and noticed that the one that still had HT enabled stated "SQL Server detected 2 sockets with 12 cores per socket and 24 logical processors per socket, 48 total logical processors; using 32 logical processors based on SQL Server licensing." Seeing this I decided not to disable HT on this server but still enable Processor Affinity mask, choosing the last 16 CPUs on each Socket for a total of 32 logical processors.
After failing back to the old master and having let it run like that for 5 days now everything looks good. The number of workers is still high (I think) but mostly below the 832 that SQL Server should keep it at. The CPU has come to rest a little bit higher than before but not enough to be alarming.
Thank you very much for your help in finding a solution to this problem.
Yomet
February 8, 2017 at 8:20 pm
Good news! Thanks for letting us know!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply