January 31, 2017 at 2:52 pm
yomet10 - Tuesday, January 31, 2017 2:27 PMThanks for the quick reply Kevin.All our SQL Server are dedicated machines, the only other things that are running are what's needed to send logs and server statistics to our monitoring servers.
Since you suggest turning off HT, SQL 2016 Std. will maximize the usage of the new servers which are also running on Dual Xeon 12 Core CPUs.
I am looking into Glenn Barry's site now but will need to continue later since I am also reading "A Month of Activity Monitoring" on Adam Machanic's site 🙂 New HW will not come in soon so the Activity Monitoring is definitely more important.
I'll get back when I have fixed DB-11.
Yomet10
I concur with your thoughts on the dual-12-core SQL 2016 setup. No need to monkey with affinities since you will have (just!!) 24 physical cores of goodness. :hehe:
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. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
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 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply