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!