Post reply

Maint Plans and processor and I/O Affinity Mask options in MSSQL 2008

  • April 30, 2009 at 8:57 am

    #137215

    Greetings,

    Please advise on the following scenario:

    I have a server running our first install of 2008 Standard Edition. When the "Automatically set processor/IO affinity mask for all processors" option under (right-click on instance name in OE)/Properties/Processors is checked ON, Maint Plans (such as Integrity Checks, etc.) work. If turned off, and 7 of 8 processors are checked ON for both Processor and IO Affinity, the Maint Plans do not work.

    In BOL it says:

    When specifying the affinity mask option, you must use it in connection with the affinity I/O mask configuration option. Do not enable the same CPU in both the affinity mask switch and the affinity I/O mask option. The bits corresponding to each CPU should be in one of these three states:

    0 in both the affinity mask option and the affinity I/O mask option.

    1 in the affinity mask option and 0 in the affinity I/O mask option.

    0 in the affinity mask option and 1 in the affinity I/O mask option.

    Now, if I'm reading this right, it says NOT to check ON for both Processor and I/O Affinity Mask on the same processor, which, in my case, 7 out of 8 processors are set this way (the incorrect way). A Microsoft consultant has advised the application people to set the processors this way, seemingly going against the aforementioned BOL article. The reason for this, they say, is to allow one CPU for OS and .net stuff and if a process runs away, there is still CPU to log in and fix the situation.

    My questions are:

    Why do the maint plans not work when the "auto" setting is unchecked?

    Is there any logical reasoning to have 1 CPU reserved for OS and .net "stuff" as the Microsoft consultant suggests?

    What settings for the affinity mask is most advisable?

    Info about the server and instance of SQL Server:

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

    Let me know if you require more info.

    Thanks for your help in advance.

  • May 1, 2009 at 8:43 am

    #987348

    If anyone could offer help on this, I would appreciate it. I know I probably posted this in the wrong area, so could someone move this to the 2008 forum?

    Thanks.

    Paul

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply