Shall I move to 64 bits when migrating to SQL 2005/2008? … Invest 10 minutes on this question before designing your new server

All of us are now using 64 bits SQL Server for almost every new configuration. While running SQL Server on 64 bits is surely better for many reasons, we should actually invest some time to decide actually which one will be best for new environment if you are migrating old 32 bits databases. There is no “General rule of migration” which can conclude that choosing a 64 bit system is always best idea over a 32 bit system.

When you move to a 64-bit environment, you pay some costs. Many data types (like register) that previously took 32 bits will now take 64 bits. Because the data takes more space, it offers greater range and precision.  The main advantage a 64-bit environment brings to SQL Server is a vastly greater address space, which means SQL Server can directly address far more RAM compared to a 32 bit system.

On the other hand, in a 32-bit system, you can address up to 4 GB of RAM. Of this 4 GB, Windows by default reserves 2 GB for the operating system. This means that when all code and data for SQL Server do not fit in 2 GB, SQL Server pay the overhead of paging data in and out of memory. This limitation can be severe with SQL Server 2008, where a single field of type VARCHAR(MAX) can be as large as 2 GB.

While Windows Server does let you alter the default memory allocation with the /3GB switch, which lets you give up to 3GB to the application processes, the fundamental 4GB limit remains. To work around this limit, 32-bit Windows Server 2008 provides

Address Windowing Extensions, which let an application use memory beyond the 4GB limit. However this technique does not really increase the address space, however; the extended memory has restrictions on its use, and you pay a performance penalty for using it. With AWE, the operating system is essentially mapping the extended memory in and out of the virtual address space. While this technique is much faster than paging to disk, it is slower than addressing the memory directly. Also, as mentioned above, the extra memory has restrictions on its use. For example, SQL Server can use it only to buffer data pages. The extra memory is not available for more connections, thread resources, queues, or other

Objects.

In contrast, 64-bit Windows Server 2008 supports up to 2 terabytes of physical RAM. The vastly larger address space has the potential to improve performance in almost every area. Obviously, any application that works directly with large amounts of data can benefit from this change. The increased address space also can improve other, less obvious functions, such as running with large numbers of connections and using SQL Server’s Analysis Services.

However despite the advantages of the 64-bit environment, under certain circumstances 32-bit environment can be more beneficial. For example, you may want to remain in 32 bits if your sites are running legacy applications or if your application is not compatible with 64 bits. It is also not always feasible to port a critical 32-bit application to 64-bit easily. For example, there is no 64-bit version of Data Transformation Services (DTS) or SQLMail. So if you have no way to remove 32 bits dependency, then staying with 32 bits can offer more advantage on 64 bits.

Also, moving to 64-bit does not always improve performance. If applications are not experiencing any memory pressure, then, obviously increasing the amount of available memory will not improve performance. Rather if you have an application that puts a lot of pressure on the L2 cache, it’s actually possible for performance to decline upon moving to 64-bit, because less data will fit in the L2 cache.

So to conclude, invest 10 minutes and decide which version you really want before you design a new SQL Server to migrate your old databases.