July 22, 2011 at 11:55 am
Hey guys, so it's come that time where we buy a new beefier box to replace the production one, we've always gone Intel, and now I'm fearing I should have stuck with Intel, but we got an AMD solution, anyhow I will post full specs below and you guys can tell me what you think.
Thing is, right now, the legacy 3 year old intel in able to outperform query wise the new SQL by a LOT! Even while on full production load while the new box only had me in it querying it... same database full restore, on the legacy one a query that takes 20 seconds to execute, the new one takes 3 minutes... and the processors seem to be all chilling and not doing anything....
Legacy SQL box:
SiSoftware Sandra
Computer
Model : Dell PowerEdge R710
Workgroup : WORKGROUPAP
Host Name : SQL01
User : Administrator
Processor
Model : 2x Intel(R) Xeon(R) CPU X5560 @ 2.80GHz
Speed : 3GHz
Cores per Processor : 4 Unit(s)
Threads per Core : 2 Unit(s)
Type : Quad-Core
Integrated Data Cache : 4x 32kB, Synchronous, Write-Thru, 8-way, 64 byte line size, 2 threads sharing
L2 Cache : 4x 256kB, ECC, Synchronous, ATC, 8-way, 64 byte line size, 2 threads sharing
L3 Cache : 8MB, ECC, Synchronous, ATC, 16-way, Exclusive, 64 byte line size, 16 threads sharing
Computer
Mainboard : Dell 00W9X3
BIOS : Dell 1.1.4 05/08/2009
Bus(es) : ISA X-Bus PCI PCIe IMB USB i2c/SMBus
Multi-Processor (MP) Support : 2 Processor(s)
Multi-Processor Advanced PIC (APIC) : Yes
NUMA Support : 3 Node(s)
Total Memory : 64GB ECC DIMM Registered DDR3
Chipset
Model : Dell 5520 (Tylersburg-36D) I/O Hub
Front Side Bus Speed : 2x 3.2GHz (6.38GHz)
Chipset
Model : Intel Xeon (Nehalem) UnCore
Front Side Bus Speed : 2x 3.2GHz (6.38GHz)
Total Memory : 32GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 532MHz (1GHz)
Chipset
Model : Intel Xeon (Nehalem) UnCore
Front Side Bus Speed : 2x 3.2GHz (6.38GHz)
Total Memory : 32GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 532MHz (1GHz)
Memory Module(s)
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix HMT31GR7BFR4C-G7 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix HMT31GR7BFR4C-G7 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : Hynix M393B1K70BH1-CF8 8GB DIMM DDR3 PC3-8500U DDR3-1066
Video System
Video Adapter : RDPDD Chained DD ()
Graphics Processor
Storage Devices
DELL PERC 6/i 49.4GB (RAID) : 46GB (C:)
DELL PERC 6/i 438.5GB (RAID) : 408GB (E:)
PLDS DVD+/-RW DS-8A3S (SATA150, DVD+-RW, CD-RW, 2MB Cache) : N/A (D:)
Disk : N/A (F:)
Logical Storage Devices
Hard Disk (C:) : 46GB (NTFS) @ DELL PERC 6/i 49.4GB (RAID)
SQLData (E:) : 408GB (NTFS) @ DELL PERC 6/i 438.5GB (RAID)
Optical Drive (D:) : N/A @ PLDS DVD+/-RW DS-8A3S (SATA150, DVD+-RW, CD-RW, 2MB Cache)
Optical Drive (F:) : N/A @ Disk
Peripherals
LPC Hub Controller 1 : Dell (ICH9) LPC Interface Controller
LPC Legacy Controller 1 : SMSC EMC2700LPC
Serial Port(s) : 2
Disk Controller : Dell PowerEdge R710 SATA IDE Controller
Disk Controller : Dell PERC 6/i Integrated RAID Controller
USB Controller 1 : Dell PowerEdge R710 USB UHCI Controller
USB Controller 2 : Dell PowerEdge R710 USB UHCI Controller
USB Controller 3 : Dell PowerEdge R710 USB EHCI Controller
USB Controller 4 : Dell PowerEdge R710 USB UHCI Controller
USB Controller 5 : Dell PowerEdge R710 USB UHCI Controller
USB Controller 6 : Dell PowerEdge R710 USB EHCI Controller
SMBus/i2c Controller 1 : Intel ICH SMBus
SMBus/i2c Controller 2 : IPMI T1 Controller
Printers and Faxes
Printer : Microsoft XPS Document Writer (600x600, Colour)
Printer : Microsoft Shared Fax Driver (200x200)
Printer : Microsoft XPS Document Writer (600x600, Colour)
Network Services
Network Adapter : Broadcom BCM5709C NetXtreme II GigE (NDIS VBD Client) (Ethernet, 1Gbps)
Network Adapter : Broadcom BCM5709C NetXtreme II GigE (NDIS VBD Client) #2 (Ethernet)
Network Adapter : Broadcom BCM5709C NetXtreme II GigE (NDIS VBD Client) #3 (Ethernet)
Network Adapter : Broadcom BCM5709C NetXtreme II GigE (NDIS VBD Client) #4 (Ethernet)
Power Management
Mains (AC) Line Status : On-Line
Operating System
Windows System : Microsoft Windows 2008 Advanced/Enterprise Server 6.00.6002 (Service Pack 2)
Platform Compliance : x64
New SQL box:
SiSoftware Sandra
Computer
Model : HP ProLiant DL585 G7
Workgroup : WORKGROUPAP
Host Name : SQL01
User : Alexander Chacon
Processor
Model : 4x AMD Opteron(tm) Processor 6172
Speed : 2GHz
Cores per Processor : 12 Unit(s)
Type : Twelve-Core
Integrated Data Cache : 12x 64kB, Synchronous, Write-Back, 2-way, Exclusive, 64 byte line size
L2 Cache : 12x 512kB, ECC, Synchronous, Write-Back, 16-way, Exclusive, 64 byte line size
L3 Cache : 10MB, ECC, Synchronous, Write-Back, 96-way, 64 byte line size, 12 threads sharing
Computer
BIOS : HP A16 06/24/2010
Bus(es) : X-Bus PCI PCIe IMB USB i2c/SMBus
Multi-Processor (MP) Support : 4 Processor(s)
Multi-Processor Advanced PIC (APIC) : Yes
NUMA Support : 8 Node(s)
Total Memory : 128GB ECC DIMM Registered DDR3
Chipset
Model : ATI (AMD) RD890 GFX dual slot (2x16) PCI-e Hydra
Front Side Bus Speed : 2x 2.6GHz (5.2GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 40GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 400MHz (800MHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 8GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 667MHz (1.33GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 32GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 533MHz (1GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 32GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 533MHz (1GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 32GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 533MHz (1GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 16GB ECC DIMM Registered DDR3
Channels : 1
Memory Bus Speed : 2x 533MHz (1GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 8GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 667MHz (1.33GHz)
Chipset
Model : AMD F10 Athlon 64/Opteron/Sempron HT Hub
Total Memory : 24GB ECC DIMM Registered DDR3
Channels : 2
Memory Bus Speed : 2x 400MHz (800MHz)
Chipset
Model : ATI (AMD) RD890 GFX dual slot (2x16) PCI-e Hydra
Front Side Bus Speed : 2x 2.6GHz (5.2GHz)
Memory Module(s)
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 4GB DIMM DDR3 PC3-10700U DDR3-1332
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Memory Module : 16GB DIMM DDR3 PC3-8500U DDR3-1066
Video System
Video Adapter : RDPDD Chained DD
Graphics Processor
Storage Devices
HP LOGICAL VOLUME 293.6GB (RAID, 15000rpm) : 273GB (C:)
HP LOGICAL VOLUME 600GB (RAID, 15000rpm) : 559GB (D:)
3PARdataVV 536.9GB (Fibre Channel, 10025rpm) : 500GB (E:)
3PARdataVV 536.9GB (Fibre Channel, 10025rpm) : 500GB
3PARdataVV 536.9GB (Fibre Channel, 10025rpm) : 500GB
3PARdataVV 536.9GB (Fibre Channel, 10025rpm) : 500GB
Logical Storage Devices
OS (C:) : 273GB (NTFS) @ HP LOGICAL VOLUME 293.6GB (RAID, 15000rpm)
DATA0 (D:) : 559GB (NTFS) @ HP LOGICAL VOLUME 600GB (RAID, 15000rpm)
DATA1 SAN (E:) : 500GB (NTFS) @ 3PARdataVV 536.9GB (Fibre Channel, 10025rpm)
Network Services
Network Adapter : HP NC375i Integrated Quad Port Multifunction Gigabit Server Adapter (Ethernet)
Network Adapter : HP Network Team #1 (Ethernet, 200Mbps)
Operating System
Windows System : Microsoft Windows 2008 R2 Advanced/Enterprise Server 6.01.7600
Platform Compliance : x64
July 22, 2011 at 12:22 pm
Ever since the Core-2 architecture came out, Intel has been seriously outperforming AMD. Used to be the other way around, for a LONG time, but not any more.
Also, even though the AMD chip has more cores effectively, each one runs at 2/3rds the speed of the Intel cores. So, if you get massive parallel processing, it might perform better overall, but each thread will be completed slower than it would on the Intel CPU.
So you'll probably get better mileage out of the AMD CPU once it's in use for a lot of concurrent threads, but it will probably be slower for just you. Don't use that as a metric. Fire it up with some load testing, multiple connections doing multiple things at the same time, and see how it does on that. That'll be a better test.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2011 at 6:47 am
Sure GSquared, understood, so they perform quite better per thread than your regular Intel, however I find hard to believe that an AMD Opteron 6172 @ 2 GHz processor that was released on March 2010 is not at least close as fast as an Intel X5560 @ 2.80GHz released in Q1 09, I mean, queries go from 7 seconds on the Intel to sometimes 2 minutes on the AMD, I hope someone is definitely wrong with this box otherwise I'm toast! Help!
July 24, 2011 at 6:59 am
Also on Passmark CPU test AMD 6172 gets 7748 vs Intel X5560 5423 ?
July 24, 2011 at 7:38 am
The are a couple of things I had to do to resolve a similar issue an AMD server I introduced a while ago.
1. I disabled cool n quiet in the BIOS. This caused thread timing issues as cores were running at different speeds
2. There was also a windows 7/2008 hotfix which applied to running large applications such as sql server
I'm sure there was something else which I cant think of right now. 😀
July 25, 2011 at 2:20 am
If your CPUs are, indeed, "chilling and not doing anything", why are you assuming it's them that's at fault? Sounds more likely to be an I/O issue if you're not getting any CPU load when running these queries.
July 25, 2011 at 7:25 am
Thanks guys for you valuable input. MysteryJimbo I would like to hear more about your story and if you remember that missing 3rd step would love to hear about it. Paul we are indeed looking into the RAIDED 10 drives and SAN to see if that improves performance. Will post back my results but if anyone else wants to make a contribution it is highly appreciated.
Thanks!
Alex C
July 25, 2011 at 10:38 am
Mystery Jimbo,
I am also seeing issues that may or may not be related to this type of issue, we are still flushing out other potential issues, but can you post the link to the patch from MS? I want to see if that may assist us.
Also, we found that trying some trial and error with the parrallelism settings did help us out a bit.
July 25, 2011 at 12:53 pm
This is the one
http://support.microsoft.com/kb/979149
You can implement a work around by setting a min and max memory setting and "lock pages in memory" security policy. I had a call with microsoft to resolve my issue and it appeared to relate primarily to the x64 binaries rather than ia64.
July 25, 2011 at 1:12 pm
Thanks MysteryJimbo, not sure the KB article would work for me since server doesn't really become unresponsive, it's just taking too long to complete queries that I would hope were faster due to the increased disk speed, processing power and RAM of the new server, we are troubleshooting all these issues right now to try and find the bottleneck, and will report back, any new input always appreciated.
Thanks
Alex C
July 25, 2011 at 1:38 pm
I will echo the thanks, I think this is not our issue, as the box is totally responsive the whole time.
July 25, 2011 at 3:12 pm
Hi guys,
I probably should have said that my system was also responsive all of the time but on what appeared to be random occasions a random query would take an innappropriate time to complete. The system itself was not unresponsive.
July 25, 2011 at 3:21 pm
Oh ok I love the way microsoft describes their hotfixes then hehehe, seems to be fairly common. I also seem to want to engage microsoft themselves with this one, what is the best way to quickly connect with them and escalate this issue to one of the SQL Server engineers? Just have never had to call microsoft tech support for SQL server ever, thanks!!!
July 26, 2011 at 12:19 am
I contacted our account manager who raised the call with the sql server support team.
July 26, 2011 at 6:41 am
Silly question, but what happens when you run the query on the new machine twice in a row?
The reason I ask is that it sounds very much like an I/O issue and that the new machine, without any load, has to retrieve all the data from disk while the old system with a load on it may already have much of the data in memory.
I would also see if the query plans are the same on both machines.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply