Priority boost details – and why it’s not recommended


Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why?

First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening is that after restart the SQL engine will call Win32 API SetPriorityClass() and passes in HIGH_PRIORITY_CLASS (if you are debugger savvy, you can set breakpoints on these APIs and check what is happening – that’s what I did, no source code is required to verify this). From MSDN:

HIGH_PRIORITY_CLASS

0x00000080

Process that performs time-critical tasks that must be executed immediately. The threads of the process preempt the threads of normal or idle priority class processes. An example is the Task List, which must respond quickly when called by the user, regardless of the load on the operating system. Use extreme care when using the high-priority class, because a high-priority class application can use nearly all available CPU time.

It then proceeds to call SetThreadPriority() with priority as THREAD_PRIORITY_HIGHEST. For this combination of Process Priority Class and Thread Priority Level, the base priority level of these worker threads is 15. The only ones higher than this in the hierarchy of the OS are any threads which have process priority class set to REALTIME_PRIORITY_CLASS (which should be a very rare case for any application.) this means that many SQL worker threads are running at a priority level which is close to the highest on the system. Hence, they will tend to be selected frequently by kernel dispatcher to execute on the CPU.

So what is the effect?

There is clear precedent in the support teams of priority boost causing unresponsive servers. Sluggish UI / mouse / keyboard movements are other common symptoms if this setting is interfering with the capability of the OS to give (non-SQL) threads their desired quantum on the CPU. On a cluster, having priority boosted SQL threads can cause other critical threads such as the resource monitor’s IsAlive poll thread to timeout, thereby causing unwanted failover. Therefore we do not recommend to set priority boost to 1, especially in clustered instances.

Reference links:

SetPriorityClass: http://msdn.microsoft.com/en-us/library/ms686219(VS.85).aspx

SetThreadPriority: http://msdn.microsoft.com/en-us/library/ms686277(VS.85).aspx

Effective Base Priority: http://msdn.microsoft.com/en-us/library/ms685100(VS.85).aspx

Windows Priority levels: http://www.microsoft.com/mspress/books/sampchap/4354c.aspx and http://www.microsoft.com/mspress/books/sampchap/4354d.aspx

Comments (8)

  1. ppindia says:

    If it is unstable, then when it is used?

  2. arvindsh says:

    The necessity to use this parameter was mostly in the pre-SQL 7.0 era, over the last few releases the relevance of this parameter has greatly decreased. In the earlier releases our scheduler was not exactly the best design for scalability.

    So in summary, when is it used? – very very rarely, and only after extensive testing is performed and results show that priority boost helps.

  3. SQLKID says:

    Then why even expose this parameter? Kind of giving tools to end users to burn their hands with <g>

  4. arvindsh says:

    The plan is to retire it eventually. See the notes at msdn.microsoft.com/…/ms180943.aspx which mention that this setting might be removed in later releases. Currently the legacy bandwagon forces us to retain this setting.

  5. Stefano Gioia says:

    The bizarre rates of tpsE on TPC-E are achieved with the help of priority boost and other forgotten parameters, like lightweigth pooling. I guess this is the only reason why those parameters stiil exists on SQL Server.

  6. Erwin Samayoa says:

    I have used it in SQL 2008 in a virtualized server using ECX, when loading clients was timing out, with the priority boost the time out practically disapeared.

    regards

  7. arvindsh says:

    @Erwin, your observation is interesting but I believe you are masking the core issue by using priority boost to workaround. The true reason for what you observed cannot be formally stated without an investigation by us, but if I were to hazard a guess it would be the overcommit of resources.

    I see (based on my practical experience) many ESX VMs are actually overcommitted on both RAM and CPU. In such cases due to balloon driver behavior and the CPU overcommit, the guest OS does not get due time on the physical CPU. By setting thread priority boost, you might just get the incremental advantage, but that is very much cosmetic.

  8. Rob St George says:

    You should never use this setting in a production environment as per the article. And especially don't use it on a SQL Cluster