SQL Server Performance

DB Restore 5 times Slower on New (4) Dual Core with 14GB Memory - Help?

Discussion in 'Performance Tuning for Hardware Configurations' started by DBADave, Sep 7, 2007.

  1. DBADave New Member

    We just built a new database server to replace an existing server. The configurations are below. When I restore a 2GB database on the old server it completes in about 1 minutes and 28 seconds. When I perform the same restore on our new server it completes in about 6 minutes and 28 seconds. If I perform a large file copy from one server to the new server and to the existing server the copy times are nearly identical. I've spent a day troubleshooting this and need some fresh suggestions on how to identify the problem. Any suggestions?
    Old Server
    Windows Server 2003 Standard Edition, SP1 - running SQL Server 2000 SP3a with Hot Fix - MS03-031
    (4) 3GHz CPUs with 4 GB Memory utilizing /3GB switch
    Data Drive is RAID 5 on 15k SCSI drives with a dedicated controller, having 576MB of write cache
    Log Drive is on a shared controller with the C drive having 192MB of write cache. Both Drives are RAID 1 on 15k SCSI..
    Backup Drive is on a shard controller with the tempdb drive, having 576MB of write cache. The backup drive is RAID 0 and tempdb is RAID 1, with both on 15k SCSI.
    New Server
    Windows Server 2003 Enterprise Edition, SP2 - running SQL Server 2000 SP3a with Hot Fix - MS03-031
    (4) Dual Core 3GHz CPUs with 14 GB Memory utilizing PAE and AWE (No /3GB switch)
    Data Drive is RAID 10 on 15k SAS drives with a dedicated controller, having 512MB of write cache
    Log Drive is on a shared controller with the C drive having 256MB of write cache. Both Drives are RAID 1 on 15k SAS.
    Backup Drive is on a shard controller with the tempdb drive, having 512MB of write cache. Both drives are RAID 1 on 15k SAS.
    I created a Restore Database script in Query Analyzer that performs a getdate() before and after the restore to display the total time taken to restore the database. If you look at the times below you will see the getdate() times indicate the restore took 6 minutes and 22 seconds. However at then end of the restore SQL Server indicates it took 14.654 seconds. Based upon a filemon trace I ran the information reported by SQL Server is incorrect. I can see reads from the backup drive and writes to the data drive occurring at 12:35:00 and ending at 12:41:22.
    2007-09-07 12:35:00.553
    Processed 3880 pages for database 'DBA_MOXY50', file 'Moxy50Sys' on file 1.
    Processed 208800 pages for database 'DBA_MOXY50', file 'Moxy50Data' on file 1.
    Processed 75624 pages for database 'DBA_MOXY50', file 'Moxy50Ix' on file 1.
    Processed 19 pages for database 'DBA_MOXY50', file 'Moxy50Log' on file 1.
    RESTORE DATABASE successfully processed 288323 pages in 14.654 seconds (161.180 MB/sec).
    2007-09-07 12:41:22.087
    Thanks, Dave
  2. satya Moderator

    Can you check are there any faulty NIC or any part of hardware on the new server, identically we have had same problem couple of weeks ago found that the NIC card wasn't set properly that has poor performance problem, so in this case better to review the health check from your H/W vendor.
  3. DBADave New Member

    I'll check. There is nothing listed in the Event Viewer logs so I'll have to look a bit deeper. Can a NIC issue impact a restore when no external communication is involved? I'm connected directly to the server and not communicating with any other server.
    Dave
  4. satya Moderator

  5. DBADave New Member

    Hi Satya,
    You may have been correct? One of our server admins said the NIC appeared to be having problems and noticed the drivers for the NIC and other devices were not up to date. He updated the drivers and changed some minor controller configurations and now restores take about 26 seconds vs. over 6 minutes prior to the update.
    I have noticed with AWE enabled, if I run 6 consecutive restores the very first execution takes anywhere between 31 and 37 seconds. All other restores take between 25 and 27 seconds. Do you think this is due to caching? I don't see as drastic a difference when AWE is not enabled.
    Thanks again, Dave
  6. satya Moderator

    [Y] You have put up an interesting question in asking whether AWE and Caching plays vital role in restore, I don't think but would like to check and will come back to you.

Share This Page