Why would you ever run the 32-bit version of SQL Server 2005 (or 2000) on an x64 Windows Server? After all, the 64-bit version of SQL Server 2005, both Enterprise and Standard Editions, takes full advantage of the 64-bit memory address space on an x64 system. The 64-bit versions of SQL Server 2005 also address all available RAM directly so that you no longer need to use AWE, making the combination of 64-bit SQL Server on an x64 system the most natural and well-travelled path. But there are a few cases where you might take the less-travelled path and run the 32-bit version of SQL Server, and in this article I´ll examine them.

First of all, let me make it clear that I am discussing database servers that use the x64 processors from Intel or AMD. These processors provide 64-bit computing with a hardware and firmware emulation for 32-bit Windows applications. I am not discussing servers using Intel´s Itanium 64-bit processors, which implement a software-only emulation for 32-bit applications.

The x64 version of Windows Server provides a mostly-native emulation for 32-bit applications under the WOW64 utility. WOW64 emulation on x64 Windows can perform quite well, often as well as on a comparable 32-bit server. The WOW64 utility runs in the background and starts automatically to establish the correct environment for a 32-bit executable. All you do is install and run the 32-bit software in the same way as you do on a 32-bit version of Windows.

Most DBAs would prefer to run a 64-bit version of SQL Server on an x64 system. It seems a natural fit, and if you took a survey, most likely you would find that combination to be by far the most common. (SQL Server 2000 has a 64-bit version only for Itanium servers, so you are restricted to running only the 32-bit version of SQL Server 2000 on an x64 Windows server.)

So why run a 32-bit version of SQL Server on an x64 Windows system? Several factors could lead you to make this choice, which I'll group as follows:

  • Performance
  • Number of SQL Server instances
  • Upgrade constraints

Let me review each of these.

Performance

It´s natural to expect initially that the 64-bit version of SQL Server will perform better on x64 Windows Server than the 32-bit version, because the 64-bit version does not require the WOW64.exe emulation. However, you never know for sure until you test. In some cases, I have seen test loads that, in fact, do perform better using the 32-bit version of SQL Server 2005, though the exact reasons are difficult to pin down. In other cases, the 64-bit version does better. In any case, if performance is a priority and load testing shows the 32-bit version better, your choice is clear.

Number of SQL Server Instances

The main disadvantage of using the 32-bit version of SQL Server on an x64 system is that you must then use AWE (Address Windowing Extensions) to address memory greater than 4GB of RAM (and in some cases, like SQL Server 2000 Standard Edition, even AWE is not available.) However, you may be able to overcome this limitation by using multiple SQL Server instances.

By default, the 32-bit versions of SQL Server 2005 and 2000 address only 2GB of RAM. However, using the WOW64, SQL Server 2005 Enterprise and Standard Editions will use 4GB of RAM. To use more memory, you must enable AWE, which adds some management overhead because you have to remember the additional configuration on your SQL Server instance. But you don´t have to run just one instance!

If you use multiple instances of SQL Server, you may not ever need to use AWE. For example, on a database server with 8GB of RAM, three instances of SQL Server using 2GB each leaves 2GB for Windows, which fits very nicely. On a server with 16GB of RAM, three SQL Server 2005 instances could use 4GB of RAM each (the maximum without resorting to AWE), and a fourth instance can use 2GB, still leaving 2GB for Windows. In these scenarios, you can run multiple instances without using AWE.

You might find using multiple instances appropriate if you are consolidating several disparate and smaller SQL Server database servers onto a single machine. You can keep the logins and permissions identical in the new instances, preserving other configurations such as languages, making the consolidation much simpler than if you had to combine them all into one instance.

But there is also a potential performance benefit to using multiple instances: each instance gets its own tempdb database. If you have multiple databases that make heavy use of tempdb, you might be able to split them among several instances and benefit from each instance having its own tempdb database. Effectively you are balancing the tempdb load across multiple instances. Of course you should always start by ensuring that every tempdb has at least the same number (or more) data files than the number of physical CPUs on the server. If tempdb overhead still remains unacceptably high, then multiple tempdbs from multiple SQL Server instances may help bring it under control.

Upgrade Constraints

You may not be able to upgrade some SQL Server 2000 applications to SQL Server 2005 for any number of reasons. A vendor may not support it; you can´t budget the development time; and so on. In that case, you will be forced to use the 32-bit version of SQL Server 2000 on your x64 Windows server. To make full use of the server´s RAM, if it has 8 or 16 or more GB, you can use AWE with the SQL Server 2000 Enterprise Edition, or multiple instances with SQL Server 2000 Standard Edition.

Taking the Alternate Path

There are indeed some conditions that can lead you to choose the 32-bit version of SQL Server on an x64 Windows server. The primary reason should be performance, because unless the 32-bit version of SQL Server 2005 performs significantly better, you should stick with the 64-bit version. 64-bit SQL Server with x64 Windows Server is the most-used and best-tested path. If you do use the 32-bit version of SQL Server on an x64 system, however, there are some monitoring issues you need to be aware of, in particular how to capture the 32-bit System Monitor (Perfmon) counters on an x64 Windows system. In the next installment, I´ll examine these monitoring issues and how to address them.