Blog

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.

↑ 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

    Thanks

    • 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: http://msdn.microsoft.com/en-us/library/ms187104.aspx

      Hope this helps!
      Kendra

  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
    and
    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

    Thanks
    Jim

  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.

    SET NOCOUNT ON;
    GO
    EXEC sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    SELECT
    [name]
    ,[description]
    ,[value_in_use]
    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’
    GO

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

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

    PRINT ‘ ‘
    PRINT ‘ ‘
    PRINT ‘ Analyzing Dangerous Settings in SQL Server ‘
    PRINT ‘ ‘
    PRINT ‘ —————————————————————————————————————————————————————————— ‘
    DECLARE
    @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
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @ValueInUse = 0
    BEGIN
    –PRINT @Valuedescript +’: is set to ‘+ @ValueInUse + ‘ –> Setting is good’
    PRINT @Valuedescript +’ – ‘+ @ValueName +’ = ‘+ @ValueInUse + ‘ –> Setting is good’
    END
    ELSE
    BEGIN
    PRINT ‘*** WARNING!!! DO NOT USE! ‘ + @Valuedescript +’: is set to ‘+@ValueInUse + ‘ –> Change this setting back to default! ***’

    IF @ValueName = ‘max worker threads’
    BEGIN
    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.’
    END

    IF @ValueName = ‘priority boost’
    BEGIN
    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.’
    END

    IF @ValueName = ‘lightweight pooling’
    BEGIN
    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.’

    END
    IF @ValueName like ‘affinity%’
    BEGIN
    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.’
    END

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

    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
    GO

  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,
      Kendra

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

        -Mike

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

    Thanks,

    Vishal

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

    Thanks,

    Vishal

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

      Kendra

  12. That makes sense. Thanks for the suggestions.

    You guys rock!

    Vishal

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php