Boost SQL Server Priority on Windows

  • Hi,

    I am trying to boost performance in SQL 2000 running on Windows 2003 server Standard Edition. There are several databases running on the server totalling 25GB. The server is an HP Proliant 350 G5 with 3.6GB Ram available. With 82 client PCs who logon on to the same server.

    What are the ramifications of ticking the Boost SQL Server Priority on Windows option?

    Regards,

    mof

  • The main impact will be if you run any other services, applications, etc on this machine. As now their thread priority will be dropped and you will see poorer performance for those applications as SQL will take priority.

    In my experience I have found that this has done very little to improve perofmance on a dedicated SQL Server. Others may have different experiences, so I would see how others respond as well. But I found my time was better spent on other items to get better performance out of the server. The first is always evaluation of the applicaiton code, this is usually the biggest performance target to gain extra umph out of the server. The second is indexing, configuration of data files, configuration of tempdb, etc to optimize throughput. The last is actually to improved hardware like more memory, faster processors, drives etc. You can always try switching the setting, take some baseline readings if you dont currently, and see how it compares... but I have never been satisfied.

  • mikeoflaherty (4/22/2009)


    What are the ramifications of ticking the Boost SQL Server Priority on Windows option?

    Don't. It's strongly advised (by Microsoft) that the setting not be changed.

    If you have performance problems, take a look at your queries. Make sure they're written optimally. Take a look at your indexes, make sure that they support the workload.

    From msdn (http://msdn.microsoft.com/en-us/library/aa196713(SQL.80).aspx)

    priority boost should be used only on a computer dedicated to SQL Server, and with a symmetric multiprocessor (SMP) configuration.

    Caution Boosting the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other Windows NT 4.0 or Windows 2000 tasks on the server.

    In some circumstances, setting priority boost to anything other than the default can cause the following communication error to be logged in the SQL Server error log:

    Error: 17824, Severity: 10, State: 0 Unable to write to ListenOn

    connection '', loginname '', hostname ''

    OS Error: 64, The specified network name is no longer available.

    p.s. Please post SQL 2000 questions in the SQL 2000 forums in the future.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Many thanks for your advice, much appreciated. I think I'll leave well alone and look for another solution.

    Regards,

    mof

  • If you look at this again, please be sure that you test, and test well, the impact. The advice from Microsoft is not to do it, unless they give you a reason, or your testing shows that it helps.

    Moving to 2000 forum.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply