[dba-SQLServer] SQL Server "benchmarks"

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 




More information about the dba-SQLServer mailing list