SQL Server Performance

Turn off processor 0?

Discussion in 'Performance Tuning for Hardware Configurations' started by Fuze, Jun 2, 2005.

  1. Fuze New Member

    I'm certain that I'd come across some documentation in the past with respect to processor configuration and SQL performance. In a multi-processor environment, say 8, running SQL on a Windows 2k platform, I belive the processors would pan out as such by default:

    0. Most OS functionality / SQL
    1.
    2.
    3.
    4.
    5.
    6.
    7. Most network activity

    From what I recall, you could improve your SQL performance by not allowing it to use processor 0. I think it may have also mentioned restricting access to processor N as well but when we did testing that didn't seem to make any impact.

    Anyway, I'm trying to dig up this documentation but can't seem to find it. Can anyone point me in the right direction?

    Fuze
  2. joechang New Member

    unless you have certain extreme cases, there is no meaningful value to excluding SQL from #0.
    normally, the network interrupt is handled by one of the higher proc #, but not necessarily #7, maybe #6,
    but unless your app was designed by some one who seriously did not know what they were doing (more common than you might think), you will probably not need to worry about this.
    check your SQL Batches/sec in perfmon, if its not in the 5K/sec range, this should not be a issue.
    also check taskman, is one of the upper procs continously nearly peaked?
    if so, you could exclude SQL from that proc, but i think there more to be gained with network interrupt affinity tuning (pester MS on this, or better yet, if you are buying big iron systems, bug your HW vendor).
    you could also distribute load across multiple NICs, assuming that each NIC interrupt gets handled by a different proc.
    on this, it does not matter how low the network utilization is, it the CPU load from handling the interrupt that is the problem
  3. Fuze New Member

    The network processor, as we tried the last processor, didn't seem to make a difference. But through our testing, we did see a pretty significant performance gain when we disallowed SQL the use of processor 0.

    The idea to disable processor 0 wasn't my own, I recall reading about it, testing it, etc...

    I'm just having a hard time finding that same documentation now. Was just wondering if anyone else has a reference to what I'm looking for.

    Fuze
  4. satya Moderator

    If your tests proves by configuring number of processors for SQL will have a gain with similar production load, then go for it and in general it is not recommended as Joe referred.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. joechang New Member

    i just cannot come up with a reasonable explain why disallowing 0 helps overall throughput. is it improving overall performance, or just the SQL connections that ended up on 0?
    there was a version BMC Patrol agent that would peg 0 for 30 sec every 5min, if something like that is the case, then yes, moving spids off 0 helps, but this is a problem with some one elses process, not the OS or SQL Server

Share This Page