Boost SQL Server Priority on Windows ...

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

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

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
Webmaster
SQL-Server-Performance.Com

Thanks again, the link you provided it is really helpful

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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_5uwe.asp

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