SQL Server has been available in a 64-bit version since SQL Server 7.0, and most people are aware that 64-bit means a more powerful SQL Server system. Where does that power come from? If we’re talking about just the 64 bits, it means that SQL Server can directly address far more memory than with 32 bits.
With 32 bits, the maximum memory you can address is 4GB, and 2GB worth of addresses are reserved for the OS’s own use. A 32-bit OS, and any application running on that OS, including SQL Server, can only form addresses up to 2GB. If the machine has more than 2GB of RAM, the OS needs to have something called Physical Address Extensions (PAE) enabled, and SQL Server needs to have Address Windowing Extensions (AWE) enabled to access that additional memory. And even then, the "extra" memory beyond the directly addressable 2GB (i.e., the "visible" memory) can be used only for the buffer pool to hold pages read in from disk. This extra memory can’t be used for query plans, lock memory, user connection information, or any of the other ways SQL Server uses memory.
With 64 bits, the maximum memory address possible is far higher than current OSs or SQL Server versions support. The documentation for SQL Server indicates that with a 64-bit system, SQL Server maximum memory capacity is dependent on the OS limits, which you can read about at msdn.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2008. Of course, having more memory available gives you additional benefits beyond the increased memory support. With more memory available, more of your data can stay in cache and you could see your I/O rates drop.
There are other reasons you should consider running a 64-bit system, such as enhanced scalability. You might wonder how an increased register size can affect your system’s scalability, and the answer is that the 64-bit architecture itself provides more than just 64-bit address registers, including the following benefits:
• The 64-bit processor enhances parallelism, provides more linear scalability, and can support up to 64 processors.
• The 64-bit processor includes improved bus architecture that enhances performance by moving more data between cache and processors in shorter periods of time.
• The 64-bit processor provides a larger on-die cache, which allows for more efficient use of processor time, leading to faster completion of user requests.
So do you need 64 bits? As usual, the answer is "It depends." (This is, of course, the most common answer I give to students and readers when they ask me very broad questions.) If your SQL Server isn’t running on a machine with 64-bit CPUs using a 64-bit OS, then the answer is moot. If your largest database can fit into not much more than 2GB of memory, then there might be little benefit of using a 64-bit SQL Server system. SQL Server databases are fully portable between 32-bit and 64-bit because all the data structures and log structures are identical. Except for capacity limits, and the resulting performance benefits, there isn’t supposed to be any functional difference between the two architectures.
But of course, "isn’t supposed to" doesn’t mean there is any guarantee that there won’t be differences. One difference I just came across is the ability to add a traceflag to SQL Server’s startup parameters to allocate 4MB of space at a time during index building and rebuilding, instead of the default 64KB. You can see this flag documented in the SQL Server 2008 Books Online (BOL) at msdn.microsoft.com/en-us/library/ms190737.aspx. The flag is also available in SQL Server 2005, it just isn’t documented. In both SQL Server versions, the flag is valid for only 64-bit SQL Server instances.
So is 64-bit SQL Server worth testing? Having to buy new hardware is very problematic for many people, but with many (if not most) new chips being 64-bit enabled these days, it can be a lot more straightforward to test the 64-bit software. My second most common answer to very broad questions is "Try it and see!"