SQL Server Performance

Adding a second processor slows me down??

Discussion in 'Performance Tuning for DBAs' started by airjrdn, Mar 11, 2003.

  1. airjrdn New Member

    The server was running WinNT 4, SQL 2000, and had one processor. We upgraded to Win2k, and added a 2nd processor. A couple jobs that we run daily are now taking up to twice as long to run.

    In Enterprise Manager, SQL is showing it's setup to use all processors. I just set each of the stored procedures for one of the daily jobs to be recompiled on their next execution, and I'm running the job manually right now, but this is basically a guess.

    What would cause the performance hit?
  2. bradmcgehee New Member

    Under Windows NT 4, you must ensure that you are running the multiprocessor code for the OS. In other words, if you install Windows NT 4 with one CPU, only the single multiprocessor code is installed. And later if you install a second CPU, you must update Windows NT 4 with the correct code. I can't remember the exact steps on how to do this, it has been a long time since I did it, so you will need to search support.microsoft.com to find the proper instructions. But in your case, you said that you upgraded to Windows 2000, then added a second CPU. I don't have any personal experience doing this with Windows 2000, just Windows NT 4.0, so I don't know if the same applies to it. Again, you will want to check out Microsoft's website to be sure, or perhaps someone else in this forum knows for sure.

    When you look in Task Manager, does it show two CPUs in use, or only one?

    Brad M. McGehee
  3. satya Moderator

    The first option would be to run PERFMON and capture all H/W counters including SQL counters.

    Satya SKJ
  4. dtipton New Member

    Check the processor tab of the SQL Server Instance properties to ensure SQL is using both processors.
  5. airjrdn New Member

    Both CPUs show up on the processor tab in EM. The reason we upgraded to Win2k at the same time was to negate the need for the Up2MP utility that helps WinNT go from using one processor to using more than one (multi).

    I've been monitoring the server with Spotlight on SQL Server and Spotlight on Windows, and it looks like SQL is using both processors. The recompile of the stored procedures seems to have helped somewhat...I'll explain what I mean by this.

    We have a delete job that deletes about 750k records from a table with 70M records every day. Prior to deleting the 750k records, we update some summary tables (4 procs that run more quickly), then kick off the proc to delete the 750k records). After the recompiles, the 4 smaller procs ran more like they did before (possibly quicker, but it's hard to say for sure). The delete that deletes the 750k records seems to still be taking longer than it did before.
  6. bradmcgehee New Member

    Generally speaking, a DELETE won't benefit from multiple CPUs in a server. DELETEs tend to be more I/O intensive than CPU intensive. If you have not done so, consider rebuilding the indexes in the table in question, and also degfragment the physical files on your server. If you do this, SQL Server will need to be turned off in order to allow the SQL Server data files to be defragged.

    Brad M. McGehee
  7. airjrdn New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> Generally speaking, a DELETE won't benefit from multiple CPUs in a server. <hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Now you tell me. <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />j/k<br /><br />Recompiling the stored procedures seems to have done the trick. The times are back down where they were before, and seem to be a little faster in some cases.<br /><br />Thanks for all the help and suggestions!
  8. bytehd New Member

    NT HAL has a SINGLE/MULTI CPU kernel driver.
    Check Russinovich for the filename.
    When you upgrade, setup might not recognize the mainboard bios.
    DONT upgrade, install.

    my original install of w2k saw the Dell mainboard as multi-cpu
    with once cpu (hello AMIBIOS!).
    So my upgrade went smooth.

    NT: have to reload entire OS to change the kernel driver.
    MPS Spec i think.
  9. bytehd New Member

    Also, w2k IS slower than NT 4 on file ops
    so they say.........

Share This Page