THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

32-bit vs. x64

When it comes to the issue of 32-bit vs. x64, you can easily walk away from a presentation with an impression that going to x64 will most likely give your SQL Server application a performance boost. I'd like to show you--with some test data--that you should be careful with that impression.

The following three environments were tested on exactly the same hardware (an HP DL 585 with four dual-core sockets and16GB of physical memory):

  • SQL2005 x86x86. In this test environment, SQL Server 2005 32-bit Enterprise Edition (RTM) runs on Windows Server 2003 32-bit Enterprise Edition SP1.
  • SQL2005 x86x64. In this test environment, SQL Server 2005 32-bit Enterprise Edition (RTM) runs on Windows Server 2003 x64 Enterprise Edition SP1.
  • SQL2005 x64x64. In this test environment, SQL Server 2005 x64 Enterprise Edition (RTM) runs on Windows Server 2003 x64 Enterprise Edition SP1.

The following four different types of workloads--generated with a custom-built TPC-C-like test kit--were applied to each of the above three test environments:

  • Memory-bound read-only workloads. The test database was scaled for 100 warehouses with the database populated at about 8.5GB. 4GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads (simulating 200 users) running on a separate computer.

  • In-memory read-only workloads. The test database was scaled for 100 warehouses with the database populated at about 8.5GB. 12GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads running on a separate computer.

  • I/O-bound read-only workloads. The test database was scaled for 1000 warehouses with the database populated at about 79GB. 12GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads running on a separate computer.

  • I/O-bound read-write workloads. The test database was scaled for 1000 warehouses with the database populated at about 79GB. 12GB out of the 16GB physical memory was allocated to the SQL Server instance. Both read-only and read-write stored procedures (all five TPC-C transactions--NewOrder, OrderStatus, Payment, StockLevel, and Delivery--in the standard mix) were submitted from a workload driver on a separate computer.

The test results are shown in the following four tables. The transaction throughput was measured as the number of StockLevel transactions per second for the read-only workloads and the number of NewOrder transactions per second for the read-write workloads.


Table 1. Memory-bound Read-only Workloads

Environment Transaction per second (StockLevel)
SQL2005 x86x86 2800
SQL2005 x86x64 2700
SQL2005 x64x64 2600

Table 2. In-memory Read-only Workloads

Environment Transaction per second (StockLevel)
SQL2005 x86x86 3600
SQL2005 x86x64 3680
SQL2005 x64x64 2800

Table 3. I/O-bound Read-only Workloads

Environment Transaction per second (StockLevel)
SQL2005 x86x86 220
SQL2005 x86x64 215
SQL2005 x64x64 225

Table 4. I/O-bound Read-write Workloads

Environment Transaction per second (NewOrder)
SQL2005 x86x86 450
SQL2005 x86x64 440
SQL2005 x64x64 440

The absolute number of transactions per second is not important. Instead, you should focus on their relative values among the three test environments. The test results tell us the following:

  • For the in-memory read-only workload, x64 SQL2005 on x64 Win2K3 (i.e. SQL2005 x64x64) achieved about 2800 transactions per second, while both SQL2005 x86x64 and SQL2005 x86x86 reached about 3600, which is 29% better than SQL2005 x64x64.
  • For the other workloads, there was little or no significant difference in transaction throughput whether it's 32-bit or x64.

Now, these test results do not suggest in any way or shape that you should not go to x64. But rather, after you walk away from a seminar that touts x64 and before you commit your application to x64, do some tests yourself with your application workload! Of course, you may decide to move to x64, not for any immediate performance gain, but for future growth and scalability. That--coupled with a hardware upgrade--can be a good reason in itself indeed!

Published Tuesday, January 02, 2007 12:02 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John R said:

Thanks for an interesting article.  Tell me, in your testing did you ever run across any shops running SQL7 and 2000 under WOW, and ALSO running SQL05x64?  We're looking at consolodating several servers under x64, but we're curious about compatibility issues..

JR

April 2, 2007 7:17 AM
 

Linchi Shea said:

No, I have not heard anybody planning to run SQL7 under WOW64. To be honest, I don't even know whether SQL7 can run under WOW64. Regardless, nobody should be running SQL7 any more.

April 9, 2007 10:53 PM
 

Andrew Calvett said:

The 64 bit beast has been out there for a while now and new servers are generally 64 bit compatible.

July 9, 2007 6:25 PM
 

Wayne said:

Very interesting results.  I wonder what the numbers will look like for SS2008 on top of Server 2008, might you be re-running your tests towards the end of the year?

July 21, 2008 6:45 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement