Systems Engineering and RDBMS

Archive for July 5th, 2007

64-bit vs 32-bit Windows/SQL install

Posted by decipherinfosys on July 5, 2007

The 64-bit deployment offers a lot of advantages over the 32-bit deployments for Windows/SQL Server installs. The biggest one of them is the increase in the amount of memory that becomes available. In 32-bit systems, natively they can address only up-to 2-3GB of RAM (depending upon whether the /3gb switch is used or not). Using the /PAE (Physical Address Extension) switch in the boot.ini file and configuring SQL Server with AWE (Address Windowing Extensions), one can extend this amount of available memory to 32-bit systems and in the DataCenter OS, can take it up to 64GB of RAM using address virtualization which means that AWE creates kind of virtual windows for accessing this higher memory. Thus, each request for using this additional memory goes through this window which is an expensive operation as compared to accessing the memory natively. As a result of this, in highly concurrent load applications and applications where you need access to this additional memory, the cost of accessing the increased memory can far outweigh the benefits thus hindering performance.

In addition, the other key factor to keep in mind is that this additional memory that is made available via the AWE configuration can be used only for the buffer cache and not the procedure cache which means that for in-memory sort operations, hash joins or other data intensive operations, this additional memory cannot be used. Using 64-bit architecture helps alleviate these issues by making that memory available natively and this translates into less disk I/O, the queries that use in-memory sort operations or hash joins or cursors (though you should use SET based operations whenever possible) will benefit a lot since all of these can now be evaluated in memory rather than doing a disk operation.

You can check whether you have a 64-bit SQL Server installation or not in a couple of ways:

Registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\Edition

This should have the 64-bit mentioned in the brackets, example:

Enterprise Edition (64-bit)

Also, if you run “select @@version“, you can see that in the first line itself:

Microsoft SQL Server 2005 – 9.00.3042.00 (X64)

This information will also be present in the Add/Remove programs if you view the report for that information:

Database Engine
[Version: 9.00.3042.00 Edition: Enterprise Edition (64-bit)

Remember that if you install SQL Server 32-bit on a 64-bit OS, then it will run under the WOW (Windows on Windows) mode and that also defeats the whole purpose of using a 64-bit OS. Use 64-bit install of SQL Server instead. I have seen such installations at client sites hence I wanted to mention it – the client was wondering why they were not seeing any benefits from their 64-bit installation, infact they were running into performance issues.

And here is a whitepaper from Microsoft detailing the benefits of a 64-bit environment:

Another good blog post on the topic is available from the MSDN blog:

Posted in SQL Server, Windows | Leave a Comment »