13

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

Outman
  • 103
  • 5
Someone
  • 139
  • 1
  • 3

6 Answers6

19

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.

Lenniey
  • 5,090
  • 2
  • 17
  • 28
  • 6
    OP mentions "2 GB at peak times" so it is entirely possible that SQL Server wants to use more than 2 GB but it cannot due to the 32-bit process limit. – MonkeyZeus Jan 16 '19 at 12:43
  • Could be the case, I really do not know if MS SQL Server 2008 uses multiple processes for different tasks / instances / databases / etc. – Lenniey Jan 16 '19 at 22:43
11

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.

David Schwartz
  • 31,215
  • 2
  • 53
  • 82
  • Do AVX and co instructions really have a noticeable impact on SQL Server performance? I would assume (but I have never benchmarked or tested it) that it would mostly be the IO subsystem on which it depends. – Voo Jan 16 '19 at 21:10
  • Some modern 32-bit code *does* assume SSE2, especially when running on an OS that only supports CPUs new enough to have SSE2 (along with some other required features). I assume Microsoft compiles their stuff with MSVC, which have a `/arch:SSE2` option for 32-bit code, equivalent to gcc/clang/ICC `-msse2`. I'd guess that SQL doesn't have a lot of SIMD vectorized loops, but copying small structs with 16-byte SIMD loads/stores is nice. – Peter Cordes Jan 17 '19 at 01:28
  • One of the more important changes in x86-64 is PC-relative addressing for efficient position-independent code. 32-bit PIC libraries typically have a ~10% or ~15% slowdown (IIRC) vs. 32-bit non-PIC. Having more integer registers helps a lot, too. One big advantage in 64-bit is a nicer calling convention, but on Windows (unlike Linux) 32-bit code does `__fastcall` to pass args in registers for many functions. The Linux 32-bit calling convention is purely on the stack, so it's pretty crap for small functions that don't inline. – Peter Cordes Jan 17 '19 at 01:33
  • If vectorization really does matter a lot for performance instead of assuming/requiring some specific support level, the code is probably checking for the newest SSE/AVX version the CPU supports and calling the appropriate implementation to get as much speed out of whatever system it's running on. – Dan Is Fiddling By Firelight Jan 17 '19 at 15:18
  • @DanNeely That assumes that whoever made the build put effort into improving the performance of the 32-bit version. My experience, at least, has been that they often assume that people who care about performance will be using the 64-bit build. – David Schwartz Jan 18 '19 at 00:12
6

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • If we're this nitpicky, why not be correct and mention that a 32-bit program can easily access hundreds of GB of RAM? ;) It's only the virtual address space that's limited. – Voo Jan 16 '19 at 11:30
  • 3
    @Voo: And SQL Server is one of the few programs that knows how. – joshudson Jan 16 '19 at 18:28
6

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.

Community
  • 1
miroxlav
  • 281
  • 3
  • 9
3

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.

yourcomputergenius
  • 78
  • 6
-2

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.

Arvind Bakshi
  • 1