September 26, 2017 at 8:06 am
Hey Folks,
Appreciate if somebody can share knowledge on factors one has to take into account before setting processor affinity mask for SQL instances. Thank you.
September 26, 2017 at 10:40 am
The main factor is why you're fiddling with it at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2018 at 2:04 pm
@gilamonster, loved your reply,, I am currently working with a client who has their affinity mask set to 1072694271 and when we asked why they didn't know. Trying to get them to put it back to 0 but of course its a tough fight.
June 7, 2018 at 5:35 pm
I looked at the mask: 111111111100000000001111111111, which I'm guessing is:
00000000001111111111
00000000001111111111
...and they have 10-core processors with hyperthreading turned on, and this mask is to stick to the physical cores.
Is that the reason?
Eddie Wuerch
MCM: SQL
June 8, 2018 at 9:51 am
They say they don't know why, "the old DBA set it", but they actually have multiple 15 core processors so its not optimal anyway.
I'm a big believer in when MS says "leave it alone" they are usually correct.
June 8, 2018 at 2:23 pm
Are you licensed for all the CPUs? At one time, the affinity mask was used to run on machine that had more processors than you were licensed for, by cancelling out the unlicensed processors.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2018 at 1:16 pm
ScottPletcher - Friday, June 8, 2018 2:23 PMAre you licensed for all the CPUs? At one time, the affinity mask was used to run on machine that had more processors than you were licensed for, by cancelling out the unlicensed processors.
But you always must license all cpu that OS have, no matter if you use them or disable in affinity mask.
June 11, 2018 at 8:28 am
e4d4 - Sunday, June 10, 2018 1:16 PMScottPletcher - Friday, June 8, 2018 2:23 PMAre you licensed for all the CPUs? At one time, the affinity mask was used to run on machine that had more processors than you were licensed for, by cancelling out the unlicensed processors.But you always must license all cpu that OS have, no matter if you use them or disable in affinity mask.
Yes, now you do. But they said "the old DBA set it", and that's why I said "at one time" SQL allowed you to use the mask and limit licensing to masked processors. And that was the only valid reason I could think of for using the affinity mask in the first place.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 13, 2018 at 8:01 am
As an explanation, I work for a company that provides SaaS so I work with literally 1000's of different clients, each with a different set up. This particular client is extremely large, they have Core licensing and have for years, truly I don't think there is an adequate reason for why the affinity mask was changed.
But thank you all for your comments and happy to have added to the debate.
June 13, 2018 at 8:55 am
I think you're on the right track. Likely no good reason. I'd turn this back to use all processors.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply