Affinity Mask...

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • @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.

  • 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

  • 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.

  • 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".

  • ScottPletcher - Friday, June 8, 2018 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.

    But you always must license all cpu that OS have, no matter if you use them or disable in affinity mask.

  • e4d4 - Sunday, June 10, 2018 1:16 PM

    ScottPletcher - Friday, June 8, 2018 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.

    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".

  • 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.

  • 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