jwcolby
jwcolby at colbyconsulting.com
Thu Feb 14 12:40:17 CST 2008
I am in the process of running some "benchmarks" on my SQL Server machines. I have two machines: AMD Quad core, 4 GByte RAM - Running Windows 2003 x32 and SQL Server X32 AMD Dual Core, 8 gbytes RAM - Running Windows 2003 x64 and SQL Server X64 AMD Dual core, 4 GByte RAM - Running Windows 2003 x32 and SQL Server X32 AMD QUAD Core, 8 gbytes RAM - Running Windows 2003 x64 and SQL Server X64 I have a standard count database and resulting Excel workbook. The database 4 base queries to pull data out of three different databases. It then uses 17 "count" queries to get counts, usually with an ORDER BY clause to get the counts by state, income band, age code etc. This set of counts ultimately pulls records from my 50 million record address table joined to a matching 50 million record criteria table, joined to a zip code table so the resulting queries work the system pretty hard. Both the quad core and the dual core processor almost max the processor usage for all cores, you can still see the top of the waveform but it is running 95% plus on all cores. Page file usage is about 7.5 gig on the X64 system and 2.2 on the x32 system. I am running both machines off of the Areca 1220 RAID controller, however the RAID arrays are not identical because in the quad core x32 machine I have eight 500gb drives whereas in the dual core x64 machine I have only three 500gb drives. This can make a difference in some cases since the streaming read speed is (No of Drives * Read speed of drive) so as you add more drives to an array the streaming read speed off the array rises linearly. This means that the x64 system only has 3/8ths of the streaming read speed of the x32 system. I will be adding more drives to that controller some day but not anytime soon. To summarize, the x32 system just happens to have my only quad core chip. The x64 system just happens to have the dual core chip. Both have maxed out memory that I can provide based on the OS maximums and the motherboard maximums. I discovered after running all the tests that the X32 Quad was running on only 2 gigs of ram, so I put in 4 gigs and reran all the tests for that machine. And for the results (drum roll please.....). x4x32-2gb x4x32-4gb x2x32-4gb x2x64-8gb x4x64-8gb Test 1:56 1:30 2:03 1:23 0:52 Age00_17 2:01 1:27 2:04 1:30 1:02 ByIncome 0:55 0:44 0:59 0:45 0:33 ByState 2:06 1:25 2:02 1:20 0:52 Age75 (by code within that field) 4:17 3:04 4:18 3:50 3:07 Cnt Boating (single count, no groupby) So... The dual core running x64 appears to run about as fast as the quad core running x32 in all but one case. The quad core running x64 has a clear advantage over the dual code or quad core running x32 EXCEPT for that single case mentioned above, where it almost catches up. The additional 2 gigs of memory for the X32 makes a significant difference as would be expected. John W. Colby Colby Consulting www.ColbyConsulting.com