Vtgyjfy

Suppose that I have a 32 bit Windows Server box that operates several server applications along with an SQL Server, with a RAM usage of about 2 GB at peak times.

What would be the advantages of upgrading the Windows Server OS and SQL Server to the corresponding 64 bit versions, with the server applications remaining as 32 bit? The 64 bit versions allow access to more than 4 GB of RAM, but since 4 GB is not being fully utilized would that render the upgrade moot?

Versions: Windows Server 2008 R2, SQL Server 2008 R2 Datacenter Edition

Thanks

Strongly related: Good reasons to keep 32-bit Microsoft Windows desktop OSes

You are using a 64 bit OS. Server 2008 R2 was the first to only support 64 bit CPUs.

"Newer" versions of Windows aren't even designed for 32 bit. You maybe won't take advantage of anything, but there should neither be any disadvantages. That being said: Upgrade anyway, as Server 2008 R2 SP1 (which I hope you are using) will be EOL from 2020-01-14.

As for SQL Server 32 bit/64 bit: Your understanding is correct, if you won't ever need > ~3,75 GB of RAM (or >2 GB per process), you can use the 32 bit version without problems. But for newer versions there won't be any 32 bit version to install, as Microsoft switched to 64 bit only.

As already noted, you are using a 64-bit OS already. There are two advantages of switching to a 64-bit version of SQL Server and one disadvantage.

The sole disadvantage is that the 64-bit version of SQL Server will use 64-bit pointers. This means pointers will occupy twice as much memory, consume twice as much memory bandwidth, and so on. This is likely fairly negligible, but it is a disadvantage. It's partially compensated by the fact that switching to a 64-bit application will allow you to ditch the overhead of the compatibility layer 32-bit apps have to use to access a 64-bit OS's functions.

The major advantage is that numerous significant improvements were made in the CPU instruction set over time. Some of them were made along with the change to 64-bits and some of them were made previously.

But even for the ones made previously, the 32-bit build has to handle CPUs that don't have those features and to avoid the hassle of detection and switching between multiple instances, just doesn't use them even where they're present. For example, 64-bit CPUs must have SSE2, but 32-bit CPUs might not. So most 32-bit code just doesn't bother checking and assumes no SSE2. 64-bit code is assured SSE2 instructions are present and so will use it if it's the best option.

The biggest one is the increase in the number of named, general-purpose registers from 8 to 16. The number of 128-bit XMM registers was also doubled, from 8 to 16.

In addition, a 64-bit process can make use of large amounts of virtual memory. This is especially important with processes that access large amounts of structured data on disk. And, of course, they can use 64-bit integer operations which tend to improve the performance of encryption, compression, and even some filesystem operations on large filesystems.

Potential issue: DLL libraries of CLR user-defined functions (UDFs) will require their 64-bit versions.

If you are using a library of CLR User-Defined Functions, it will become bit-incompatible. 32-bit DLLs cannot be generally used in 64-bit software and vice versa. If you cannot get 64-bit version of some UDF library you use, you will lose that specific extension.

Basically it is the same issue as upgrading any 32-bit software with its add-ons to its 64-bit version. You also need to switch all add-ons to their 64-bit versions. Generally it is easy, but the problem are discontinued ones where replacements are not available.

Fundamentally: Yes. Assuming you never make updates that are then 4 bit only - not sure there even IS a 32 bit SQL Server more recent than 2008.

Issues with your question: "The 64 bit versions allow access to more than 4 GB of RAM," - make that 3gb ;) not 4. 1gb is always reserved.

Performance!

There are several technical answers here already, but without getting too technical, and depending on your application, you should see a performance upgrade.

The main pieces are:

Large memory addressing: The 64-bit architecture offers a larger
directly-addressable memory space. SQL Server 2005 (64-bit) is not
bound by the 4 GB memory limit of 32-bit systems. Therefore, more
memory is available for performing complex queries and supporting
essential database operations. This greater processing capacity
reduces the penalties of I/O latency by utilizing more memory than
traditional 32-bit systems.

Enhanced parallelism: The 64-bit architecture provides advanced
parallelism and threading. Improvements in parallel processing and bus
architectures enable 64-bit platforms to support larger numbers of
processors (up to 64) while providing close to linear scalability with
each additional processor. With a larger number of processors, SQL
Server can support more processes, applications, and users in a single
system.

https://teratrax.com/sql-server-64-bit/

The most dramatic results I saw on going from 32-bit to 64-bit SQL Server (this was SQL Server 2005) was about a 40% speed boost on a client's major application. All we did was install the 64-bit SQL Server, everything else was the same! That was a major performance boost in the real world.

You can have better multitasking performance especially with programs that have heavy multithreading built in. Moreover u can install more ram with 64-bit os. But do this only if the processor supports 64-bit instructions.