SQL Server Performance

Boost SQL Server Priority on Windows ...

Discussion in 'Performance Tuning for DBAs' started by stefanoale, Mar 19, 2004.

  1. stefanoale New Member

    ... setting for my SQL 2000 box running on Win2K, along with the " USE WINDOWS NT FIBERS " setting.
    Are they really beneficial to performance?
    I have always been advised against the fibers setting but I never heard anything about the SQL Priority setting. In either cases I never heard the reasoning behind it.

    Can anyone recommend either one? And if so why?
    If NOT why NOT?


    Thanks in advance.
  2. satya Moderator

    http://www.sql-server-performance.com/q&a1.asp if the hardware falls as per the Q&A specification.

    This can improve performance when a server is running only instances of SQL Server and no other applications. If you are running multiple instances of SQL Server on a computer, and turn on priority boost for only some of the instances, the performance of any instances running at normal priority can be adversely affected.

    KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q319942&SD=MSKB& to determine proper SQL h/w configuration.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. stefanoale New Member

    Thanks for the prompt response.<br />Based on my setup the links and your advice do not recommend either setting <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br />I guess if I want greater perf I'll need new hardware...
  4. bradmcgehee New Member

    New hardware, or optimizing your current queries and code, are both options available to you. This may or may not be your case, but I have found that rather than fix a performance program due to bad code, that hardware is thrown at the problem instead. This may help in the short run, but in the long run, you will eventually run into this same problem again, until the code is fixed.

    Brad M. McGehee, MVP
  5. satya Moderator

  6. stefanoale New Member

    Thanks again, the link you provided it is really helpful
  7. biged123456 New Member

    We recently did some research on priority boost and found some Microsoft articles that recommend it and some that recommend against it. Our onsite MS rep went to the MS SQL product team and now we no longer implement SQL boost. Boost has been linked to increases of Event ID 17824 - a SQL server to client connection network error. The problem with this is that these errors were suppressed as of SQL7, so you may not know if you are getting them unless you run with the msonly -T4029 trace flag. In a clustered environment, boost can also interfere with the IsAlive normal cluster communication and can lead to unexpected fail overs. To sum it up, there are hundreds to thousands of incidents of boost leading to calls to PSS.

    Here are some other articles that recommends against it -http://support.microsoft.com/default.aspx?scid=kb;en-us;319942#7
  8. satya Moderator

    Since beginning MS has conveyed that you need to test before you apply it on a production server.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Argyle New Member

    Some functions won't work if you switch over to fibers. I'll paste in an MS response from the microsoft.public.sqlserver.server news group:

    Its always recommended to use the Default UMS Thread switching Vs the light weight fiber pooling (value 1 in sp_configure ). However, switching to Fibre mode will be beneficial only in following scenarios,

    - Large multi-CPU servers.
    - All of the CPUs running near maximum capacity.
    - A lot of context switching

    The downside of Fibre mode, if you are using SQL2K is , XML stored procedures can not be executed. Examples of XML stored procedures are sp_xml_preparedocument and sp_xml_removedocument. See BooksOnline for more information about these stored procedures.

Share This Page