SQL Server Performance

Slow 64 Bit Performance

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by marshallbrandt, Jan 18, 2006.

  1. marshallbrandt New Member

    We had started to develop some new databases on SQL Server 2005 on a 32 bit server.

    Then a decision was made to buy a new server and HP/Compaq DL 385 with Opteron and put on Windows 2003 x64 and x64 Sql Server 2005

    We did not attach any existing databases on this new box. We created new tables on it and used SSIS to bring data into those tables.

    While working on an SSIS package we needed to delete out some data from x64 tables so that we could test enhancements.

    If we used the SQL Server Management Studio (either on a work station or on the server) and selected the data and deleted the records the computer would take 10 to 15 minutes to delete 40,000 rows that were less thatn 4000 characters wide. When we used the 32 bit server the same deletes took about 1 second.

    If we did a truncate table then we could get in seconds.

    We thought that maybe this was just a quirk in the Management Studio.

    Then we did some SQL backups of data that was less than 500 meg in size. This took about 1 minute 40 seconds on our old box. On the new box it took from 6 minutes 30 seconds to 7 minutes 15 seconds each time.

    Anything we tried on this box is slower than our old 32 server.

    We are not running any 32 bit drivers on the computer.

    The server has:
    8 gig ram
    SCSI mirrored 15,000 rpm drives that the OS and log files are on.
    Data files are on a RAID 5.

    We are considering uninstalling 64 bit SQL Server and going back to 32 bit and if that does not improve things go back to 32 bit Windows 2003 as well to see if things improve.


    Marshall Brandt
  2. Luis Martin Moderator

    Welcome to the Forum.

    How many processor in one server and in other?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. marshallbrandt New Member

    One processor in 64 Bit. The 32 bit had 2 processors and 2 gig ram. Unfortunatelys the moment we got the 64 bit running they repurposed the 32 bit SQL 2005 server for something else.

    We still have a 32 bit SQL 2000 server with 2 processors and 2 gig ram. We have tried some different benchmarks with the 2 trying to do as similar as possible and the 32 bit server just flat kicks the butt of the 64 bit server on anything that you can think to measure.

    What makes this seem strange.

    Both 32 bit machines had a single RAID 5 with everything OS, Logs and data all on the RAID 5.

    Now we have the OS and logs on mirrored 15,000 Scsi and data on RAID 5. You would think this thing would run circles around the 32 bit but sadly this is not the case.

    At this point we are going to uninstall 64 bit SQL Server 2005 and install 32 bit SQL 2005 Server and see if things improve.

    Right now this point the only people accessing this new server are 3 developers and when we compare it to the existing 32 bit SQL 2000 for testing it has 100 + users connected to it while we are testing and it still blows away the 64 bit.

    Marshall Brandt
  4. joechang New Member

    i have run tests on a broad range of sql ops and did not notice significant difference between 32-bit sql 2005 and 64-bit sql 2005. did notice that tcp ops were slightly slower on the 64-bit, which is slightly dissappointing

    i would have run perfmon for the disk counters in each case to see if there was a difference in disk usages characteristics
  5. TrussworksLeo New Member

    I am having the same problem.

    I have a Dual 64 box almost the same configuration. 2 2.8Ghz 64Bit with 8 Gigs of RAM on the two new boxes. We purchased 2 and both are slow.

    2 2.8Ghz 32Bit with 8Gigs of RAM on the old Box.

    The 32 Bit is almost 3 times faster. It makes no sense?

    The new box has SQL 2005 64bit.

    Anyone find anything on this?

    Leo
  6. joechang New Member

    i think you need to determine whether the difference is caused by disks or cpu,
    ie, pick a particular operation,
    run profiler and perfmon (seem my preferred counter list in www.qdpma.com, instructions)
    does this 64-bit version consume more cpu for this op, or is it in the disks
  7. agw New Member

    I just got a DL 585 with an MSA1500 plus and M30 for a total of 1.86TB in RAID 10 (28 spindles) and 16GB of RAM at 2700 QUAD dual core CPU.
    I was hoping to improve performances by at least 50% over my current DELL Poweredge 6600 with QUAD 2.8 and 4GB of Memory with 200GB (three spindles)
    but all you guys are reporting is bad news...
    Do you have any updates since last month?
  8. joechang New Member

    i am certain that if you do things right, with proper analysis, adjustments for the differences between 64-bit / 32-bit, SQL 2000 and 2005, and from 4 to 8 cores, you get improvement

    but if you blindly make the change without understanding your application, anything can happen.

    A 2.8GHz Xeon should be approx equal to 1.7GHz Opteron on a broad range of SQL ops, so your 2.7GHz should give you a 40% gain per core.
    The 8 core Opteron should give you a 50% throughput gain over 4 cores, but this is contigent on a number items, including proper analysis, good coding, no contention for resources, etc
  9. agw New Member

    Hi Joe and thank you for your comments as they're certainly comforting.
    I'll make sure to post results once I have it all up and running. (Around May I hope).
  10. chupaul New Member

    Hi all,

    We have 2 new AMD Opteron 8 core 2.2 Ghz with 12GB ram servers for heaving
    ETL and warehousing.

    We are running sql 2000 on all the other machines.

    Where can I get good advice on configuring these machines.

    Thanks, Paul
  11. chupaul New Member

    sorry, heavy ETL and warehousing.

    We want to understand how the sql 2005 64 bit perf can be maximixed or configured correctly.

    Thanks, Paul
  12. joechang New Member

    well i suppose you will be heaving data around,

    i think the biggest thing from here is to configure the disks correctly,
    assuming you got the powerful CPU complex for a reason, and not just for kicks,

    without knowing how much data you have and what your queries are,
    i would advise configuring the disks to deliver 2GB/sec

    for this, you will need 4-5 dual channel U320 SCSI/RAID controllers,
    4-5 external 3U dual channel enclosures, 4 disks per channel for a total of 32-40 disk drives, plus 2-4 internal disks

    i suppose you could also do this with 4-5 SAS controllers and 4-5 of the 1U SFF SAS disk enclosures, but i don't have configuration details for this yet
  13. cbarnhart New Member

    Marshall, Leo,

    I know this topic is stale by now, but did you guys ever determine what the problem is. I have s similar situation. I "attached" the database to the new 64bit server and ran identical stored procedures on each. The 32bit proc took 3 minutes; the proc on the 64bit server took 14 minutes.
    I thought it would be 4X faster, not 4X slower.

    CJ Barnhart
    UT Human Genetics Center
    713-500-9814
  14. agw New Member

    Hello Leo,

    I finally took the server to production and performance is just great! Many thanks to Joe Chang for all his help. (BTW I did buy the SQL Lite backup)
    I'm still on SQL 2000 (2040) 32bit because the developers of my application are not yet ready with 2005 but will do the move in January.
    Meanwhile I went ahead and enabled AWE today to see if with more memory allocated performance would be even better.
    I'll soon know but for now it seems to be just the same.
    I'll post more on this on a new topic.
    Andrea
  15. sversluis New Member

    quote:Originally posted by agw

    I just got a DL 585 with an MSA1500 plus and M30 for a total of 1.86TB in RAID 10 (28 spindles) and 16GB of RAM at 2700 QUAD dual core CPU.
    I was hoping to improve performances by at least 50% over my current DELL Poweredge 6600 with QUAD 2.8 and 4GB of Memory with 200GB (three spindles)
    but all you guys are reporting is bad news...
    Do you have any updates since last month?
  16. sversluis New Member

    quote:Originally posted by agw

    I just got a DL 585 with an MSA1500 plus and M30 for a total of 1.86TB in RAID 10 (28 spindles) and 16GB of RAM at 2700 QUAD dual core CPU.
    I was hoping to improve performances by at least 50% over my current DELL Poweredge 6600 with QUAD 2.8 and 4GB of Memory with 200GB (three spindles)
    but all you guys are reporting is bad news...
    Do you have any updates since last month?

    Pleas take a look at this:

    http://support.microsoft.com/kb/906892
  17. cb@bwhitman.com New Member

    I implemented a 64-bit HP Server with SQL Server 2005, no problem. Implemented another server SQL 2005, this time 32 bit, on Windows 2003 SBS and the performance is seriously degraded from win 2000 SQL 2000. Still looking for the cause and will post answer once I find. The CPUs on dual core Intel processors, HP server, show almost no CPU usuage even when a long query is executed, unlike the previous system which would do as you would expect and pop CPU usage up for a long query. Using HP's new 2.5 inch SAS drives in RAID 5 configuration, HP stated these would perform better than SCSI, maybe not?

    Restructuring tables to speed up but still you would thing that performance would not degrade moving fron SQL 2000 to SQL 2005 both 32 bit.


    The more I learn, the more I realize there is yet to learn.
  18. joechang New Member

    strange things can happen
    i always insist on a good profiler trace before and after the 2000 to 2005 change

Share This Page