Every software product has its gotchas. SQL Server has some settings which sound like a great idea but can cause major problems for performance and availability when used improperly. In this 30 minute video, Kendra Little takes you on a tour of SQL Server’s most dangerous settings, from priority boost to lightweight pooling. She’ll explain why you need to be cautious and how you can check if your SQL Servers are configured safely.

Looking for the links discussed in the video? Scroll on down the page…

Hungry to Learn Even More?

Dig into these links from the video.

Priority Boost

Read the fine print on Priority Boost in Books Online here.

CPU Affinity Mask and I/O Affinity Mask

Check out Bob Dorr’s explanation of the problems with using CPU and I/O Affinity Masking here.

LightWeight Pooling, aka Fiber Mode

Read “The Perils of Fiber Mode” by Ken Henderson here.

For more videos like this:

Kendra Little
My goal is for you to understand your SQL Server’s behavior– and learn how to change it. When I’m not figuring out the solutions to your database problems, you’ll find me at user group meetings in Portland, Oregon. I also love to draw.
Kendra Little on sabtwitterKendra Little on sablinkedin
↑ Back to top
  1. Your perils of fiber mode link has a h missing in http.

  2. And now is missing the “h” for the “http” link 🙂

  3. Hi Kendra,
    I currently have “Automatically set processor affinity mask for all processors” and “Automatically set I/o affinity mask for all processors” checked in SSMS and I have 4 cpus showing with checks under for “Processor Affinity” and “I/O Affinity”. I also show these values in sp_configure:
    name config_value run_value
    ———————————– ———— ———–
    affinity I/O mask 0 0
    affinity mask 0 0
    affinity64 I/O mask 0 0
    affinity64 mask 0 0

    I do not see why these are set to 0 since SSMS shows them as being checked.
    Can you advise on what I should be seeing in SSMS and sp_confugure with regards to
    what should and should not be checked and what my values should be. I am totaly confused


    • Hi Jim,

      0 is the default value for these settings– when you see that in sys.configurations (or sp_configure), it means that these are being automatically set, just like you see in the GUI.

      If you have a non-zero value for these settings it is then interpreted as a bitmask and you have imposed a user-defined affinity mask.

      If you’d like to read more on this, check out this MSDN article:

      Hope this helps!

  4. Thanks Kendra

    but I am still not sure what I should be setting these to:

    I currently have “Automatically set processor affinity mask for all processors” and “Automatically set I/o affinity mask for all processors” checked in SSMS
    I have 4 cpus showing with checks under “Processor Affinity” and “I/O Affinity

    Should the above two be checked or not, in your opinion, and what about the 4 cpu boxes that are currently checked


  5. Hello everyone,

    I really liked the video, so much so that I wrote a script. Have a look and let me know what you think. 🙂

    — SQL Server Dangerous Settings

    — If any of the below setting are used (other than default) you will experience issues.
    — Only use these settings if recommended by Microsoft Suppport.

    EXEC sp_configure ‘show advanced options’, 1;
    INTO #SQL_Server_Settings
    FROM master.sys.configurations
    where [name] = ‘affinity64 mask’
    or [name] = ‘affinity I/O mask’
    or [name] = ‘affinity64 I/O mask’
    or [name] = ‘lightweight pooling’
    or [name] = ‘priority boost’
    or [name] = ‘max worker threads’

    EXEC sp_configure ‘show advanced options’, 0;
    — Testing area – unremark this section of the script to test outputs

    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘affinity64 mask’
    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘affinity64 I/O mask’
    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘affinity I/O mask’
    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘lightweight pooling’
    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘priority boost’
    –update #SQL_Server_Settings set [value_in_use] = 1 where [name] = ‘max worker threads’
    — remark the above section to run against actual values

    PRINT ‘ ‘
    PRINT ‘ ‘
    PRINT ‘ Analyzing Dangerous Settings in SQL Server ‘
    PRINT ‘ ‘
    PRINT ‘ —————————————————————————————————————————————————————————— ‘
    @Valuedescript VARCHAR(100)
    ,@ValueName VARCHAR (100)
    ,@ValueInUse VARCHAR (100)

    DECLARE DangerousSettings
    CURSOR FOR SELECT [description] ,[name] ,CONVERT(VARCHAR (100),[value_in_use]) FROM #SQL_Server_Settings

    OPEN DangerousSettings
    FETCH NEXT FROM DangerousSettings INTO @Valuedescript, @ValueName,@ValueInUse
    IF @ValueInUse = 0
    –PRINT @Valuedescript +’: is set to ‘+ @ValueInUse + ‘ –> Setting is good’
    PRINT @Valuedescript +’ – ‘+ @ValueName +’ = ‘+ @ValueInUse + ‘ –> Setting is good’
    PRINT ‘*** WARNING!!! DO NOT USE! ‘ + @Valuedescript +’: is set to ‘+@ValueInUse + ‘ –> Change this setting back to default! ***’

    IF @ValueName = ‘max worker threads’
    PRINT ‘ ‘
    PRINT ‘Max Work Threads setting my cause blocking and thread pool issues/errors.’
    PRINT ‘When all worker threads are active with long running queries, SQL Server may appear unresponsive until’
    PRINT ‘a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable.’
    PRINT ‘If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server’
    PRINT ‘using the dedicated administrator connection (DAC), and kill the process.’
    PRINT ‘** Only use if requested by Microsoft Support **’
    PRINT ‘The default value for this option in sp_configure is 0.’

    IF @ValueName = ‘priority boost’
    PRINT ‘ ‘
    PRINT ‘”Boost SQL Server priority” setting will drain OS and network functions and causes issues/errors.’
    PRINT ‘Raising the priority too high may drain resources from essential operating system and network functions, ‘
    PRINT ‘resulting in problems shutting down SQL Server or using other operating system tasks on the server. ‘
    PRINT ‘** Only use if requested by Microsoft Support ** ‘
    PRINT ‘The default value for this option in sp_configure is 0.’

    IF @ValueName = ‘lightweight pooling’
    PRINT ‘ ‘
    PRINT ‘”Use Windows fibers (lightweight pooling)”. By setting lightweight pooling to 1 causes SQL Server to switch to fiber mode scheduling. ‘
    PRINT ‘Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: “clr enabled” or “lightweight pooling”. ‘
    PRINT ‘Features that rely upon CLR and that do not work properly in fiber mode include the hierarchy data type, replication, and Policy-Based Management.’
    PRINT ‘CLR, replication and extended stored procedures will fail and/or not work.’
    PRINT ‘** Only use if requested by Microsoft Support **’
    PRINT ‘The default value for this option in sp_configure is 0.’

    IF @ValueName like ‘affinity%’
    PRINT ‘ ‘
    PRINT ‘I/O and processor affinity changes will cause strange issues/errors and is not necessary on and 64 bit server.’
    PRINT ‘Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server.’
    PRINT ‘These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have’
    PRINT ‘unpredictable results. SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.’
    PRINT ‘Using the GUI, under server properties select the “Automatically set processor affinity mask for all processors” and’
    PRINT ‘select the “Automatically set I/O affinity mask for all processors”. This will correct the issues.’
    PRINT ‘** Only use if requested by Microsoft Support **’
    PRINT ‘The default value for this option in sp_configure is 0.’

    PRINT ‘ ‘
    FETCH NEXT FROM DangerousSettings INTO @Valuedescript, @ValueName,@ValueInUse

    CLOSE DangerousSettings
    DEALLOCATE DangerousSettings

    PRINT ‘ —————————————————————————————————————————————————————————— ‘
    PRINT ‘If any of the settings are used (other than default) you will experience issues. Only use these settings if recommended by Microsoft Suppport.’
    PRINT ‘You can change the settings with SP_CONFIGURE or the GUI (right click on server, select properties and select Processors’

    DROP table #SQL_Server_Settings

  6. Great video, Kendra, thanks for the knowledge! I know my situation is probably not unique, but we are developing a product that will have a SQL backend to a single website. We don’t have many licensing resources right now, so combing IIS and SQL on the same box to serve the pages that will connect to the SQL backend seems like an attractive solution.

    If it grows to be problematic, I can always move the website to a different server, but in the meantime, would manually setting CPU or I/O affinity actually make sense in this scenario, or should I simply limit the Max Memory of SQL to allow the 4GB/10% for IIS and everything else that was mentioned in the video?

    I know I’m 6+ months late to the party, but any insight would be appreciated!

    • Hey Mike,

      Your question is a really tough one to answer. It’s basically “which is the less bad option?” Unfortunately, I can’t recommend either in general.

      As a general rule I would not recommend touching affinity masking as a pro-active measure to help ward off problems, though. It’s more likely to cause ’em than ward them off, and it certainly makes your troubleshooting very sticky. It also doesn’t save you anything on licensing the SQL Server– that’s a myth. Those tend to be the most expensive licenses for a website, so you’d really be better off having IIS share with almost anything else rather than the SQL Server. If you have the option to virtualize your application servers then that’s absolutely where I’d be looking first.

      Hope this helps,

      • Thanks Kendra, I know its not the most optimal setup. The server actually will be a VM (Server 2012 Std w/ SQL 2012), but its the last OSE license we have (before a new purchase). I wasn’t looking to use affinity as a cost-savings measure, just more of a “guardrail” to ensure that SQL and IIS each have resources allocated to them on the same box.

        If the default, automatic settings are graceful in allowing both services (IIS and SQL) to grab what they need, I’m fine with that….I had just read somewhere that setting the affinity would help “guarantee” resources would be available for IIS more gracefully than the automatic setting.

        Are you advising that I avoid IIS and SQL co-habitation if I can? The product has no user base right now, and I feel I could move the site to a shared hosting server if problems arise, but that cohabitation would be a convenient and economic win right now.

        Thanks for your suggestions!


        • I would avoid putting them together, yes. SQL Server really isn’t designed to share, and any method of making it share is much more messy than graceful. WIth the affinity masking route, the possibility of it frustrating you more than it helps is really quite high.

          It does sound like you have very low activity now and that performance is not currently an issue. If that’s the case, then perhaps your initial configuration is fine focussing on what’s convenient than the best possible world– that is often the best answer for a business, I totally agree.

          • Awesome, thanks again for your advice! I will aim to keep them segregated as best I can 🙂

  7. Pingback: Managing Memory & Processors within SQL Server | Designed-Solutions

  8. Great Video! Just the information I was looking for.

  9. Thanks for the great video. I enjoy the weekly discussions.

  10. This question is for CPU Affinity Mask and I/O Affinity Mask option. I have a SQL Server that has 4 instances of SQL on it and I would like to allocate certain number of CPUs to each instance (more CPUs to the bigger and more used instances). Would you recommend against setting Processor affinity for each SQL instance?

    Server – SQL 2012 64 Bit with 20 processors.



    • Vishal – I wouldn’t recommend running 4 instances of SQL Server on a single piece of hardware. That sounds like a great candidate for virtualization, with each instance getting its own OS.

  11. We are in the planning stages of a two-node multisite SQL 2012 cluster and we want to have 4 SQL instances on the same server like Server\Inst1, Server\Inst2, Server\Inst3, Server\Inst4. But as per my information, that is not allowed in a clustered environment, we will have to setup 4 independent instances of SQL like Server1\Inst1, Server2\Inst2, Server3\Inst3, Server4\Inst4 on the same piece of hardware. The SQL servers on each site are VMs that are tied to one set of storage at each site.
    Each server has 256GB of memory and 20 cores on 2 NUMAs.

    Trying to decide the final configuration before we proceed with the install.



    • Hi Vishal,

      Brent is suggesting that instead of one VM with four instances in it, you would have four VMs each with one instance in it. You would control resources at the virtualization level. I agree completely that would be better in your situation if you care about performance.


  12. That makes sense. Thanks for the suggestions.

    You guys rock!


  13. Hi Kendra, Brent

    we’re running a 2 node cluster with 12 instances on it in an active/passive mode (2 NUMA, 12 cores). I was thinking about changing the CPU affinity so the heavier instances don’t interfere with the less loaded instances.
    Also, I would disable CPU0 just to leave it entirely to the OS (for example because I read about CPU0 being used for networking by default)
    What are your thoughts on that?


    • Hi Thierry,

      If performance is important, you don’t want to have multiple instances running on a single node. If performance is NOT important, then you wouldn’t go changing CPU affinity.

      Essentially, CPU affinity is not going to give you good performance with 12 instances on a 2 node cluster. If that’s a problem, you need to change your architecture.


      • Hi Kendra,

        thx for the quick reply. I understand what you’re saying but going for 12 separate servers will drive the cost up tremendously. And it’s all about the money 🙁

        Just another quick question if I may. As I understand it SQL schedules threads on the least busy CPU (scheduler), right? Does the load factor for a scheduler reflect the overall system activity for a CPU or is it strictly an internal view for the SQL instance?
        In other words, does a SQL instance know some other process (SQL or something else) is using a specific CPU and will avoid using it because it’s busy?

        Thx again!

        • Because of the cost issue, there’s two approaches to this number of instances: consolidation (when possible), and virtualization (when it’s not possible).

          Yes, you can get contention between one SQL instance and other SQL instances or Windows. But the fact that you’re asking the question makes me think you don’t know how to measure CPU contention and you’re not sure if it’s a problem– you’re guessing. That’s REALLY not the situation where you want to start implementing affinity masking.

          • Hello again
            I use the following to determine CPU pressure (from Maybe there are better ways…
            select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / si.os_quantum * scheduler_count), 2) as Additional_Sockets_Necessary
            from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = ‘SOS_SCHEDULER_YIELD’

  14. Hi Kendra – thanks for all your videos – very informative.
    The issue of resource isolation is big for us. We want to run multiple instances on non-VM platforms (new licensing model means it’s cheaper!). It’s not so much a matter of judging existing CPU loads, but of planning for consistent performance and preventing the rogue processing of one errant Instance from hammering all the others (as i’m sure you’ve seen happen many times).
    With Windows Resource Manager being removed in favour of Hyper-V (how that helps when not using VM is anyone’s guess) – i’m starting to run out of options.
    CPU affinity masks seems like the perfect solution. We may be constraining optimal performance in some circumstances, but would be getting more consistent, reliable performance all of the time (i.e. if we bind an instance to 2 cpus then we know it’s always going to have those 2 cpus). It also enables me to manage a large scale estate more easily as I know where I have free resources without having to spend ages checking performance stats and logs (i.e. when working out where to add a new instance).

    I’ve heard some downsides re: “optimal use of numa” but again – my view is that sacrificing optimal for consistent is good in a large environment when micro-managing all instances is impossible.

    What do you think?
    thanks again

  15. Nice video

    Was watching as I was investigating affinity settings.

    The MaxThreads is interesting as using mirroring I have come across the situation where on high MultiCore servers it creates hundreds of mirror threads which exceed the default Thread pool. The only way around it is to up the thread count or move mirror databases. I realise its probably an exception to the rule, even I waited for MS to recommend we change the setting.

  16. Hi Kendra,
    Scenario: server has 2CPUx6cores, two numa nodes, with average CPU%Usage about 10%. As it looks as CPU is pretty much underused, does it make sense to “turn off” one CPU for test purpose, using affinity mask, and see what is influence on CPU Usage?
    The idea is to save money on licensing. Is there any other method which would tell me how many CPU is enough as we have many boxes with too many CPUs which are pretty much underused.
    Thanks a lot

    • Alexsandar – bad news. SQL Server licensing has to be applied to all of the CPU cores on the box. Anything Windows can see, SQL Server has to be licensed for. For details, check out the SQL Server Licensing Guide.

      • Hi Brent,
        Thanks for quick answer and sorry for misleading you with my low level English.
        I was aware of that.
        But i need info if one CPU will be able to cope with all requests. So if one CPU is enough, i can buy new server with one CPU and replaced existing one and save on licensing.
        So, can i just for figuring out if one CPU is enough turn one of my two CPUs off, with affinity mask and rely on new CPU usage info?
        I hope i was a bit more clear now?

Leave a Reply

Your email address will not be published. Required fields are marked *