64-bit verses 32-bit . . . What’s right for you?

Recently we been hit with a rash of inquiries around whether folks should install the 32-bit version of the PowerPivot client, or go with the larger capacity 64-bit version. Office 2010 is the first version of Office to offer a native 64-bit option. In previous releases, you had to install the 32-bit version to run under WOW (Windows-On-Windows) mode. WOW emulates a 32-bit environment under the 64-bit OS. That is cool and all, but you were still limited by the 32-bit address space (2GB of memory). Now with Office 2010 64-bit the memory use is virtually unlimited for a client application.

So where to begin . . . Being a geek, let’s start off with a technical topic. When you are running in a 64-bit process, then you can only use dlls and other executables that are build as native 64-bit applications. Therefore whatever our choice, 32-bit or 64-bit, the ‘bit-ness’ of our approach has to hold for the entire process. For example, if we pick 64-bit, then we need the 64-bit version of Excel 2010, we need the 64-bit versions of any add-ins, such as PowerPivot for Excel, and we need 64-bit versions of any OLE DB providers or ODBC drivers (using the 64-bit version of the OLE DB Provider for ODBC here). So let’s keep that in mind.

Ok, here we go:

Pro for 32-bit:

• It does not require an upgrade to a new OS. While yes, Windows 64-bit is still Windows, there are subtle differences between them.
• Likewise, it does not require new PCs. Depending on what your PCs are, they might not be capable of running 64-bit or may not have the capacity to make use of the 64-bit address space, e.g. they might only have 2GB of memory.
• There is better availability of add-ins and other components on 32-bit. Your existing add-ins will continue to work. For example, if we want to run the Microsoft Communicator add-in to Outlook 2010, then there is only a 32-bit version available. The installation of 32-bit verses 64-bit is at the Office “level” – you cannot have a 32-bit version of Outlook and a 64-bit version of Excel.

An excellent example of this limitation is that the Data Mining add-in for Excel is only available as a 32-bit version. Therefore if you need to run the data mining add-in then you must install Excel 32-bit. The larger dataset and performance improvements of 64-bit are not available to you.

• Likewise there is better availability of data providers on 32-bit. This can be important, particularly if you are using the PowerPivot mashup capability, then being able to use older legacy ODBC drivers is important. Some of those ODBC drivers are available in 64-bit versions (for example, there is a 64-bit version of the SQL Server OLE DB provider), but not all of them. Remember that you cannot mix ‘bit-ness’. If you install the 64-bit version of Excel, then you must use the 64-bit versions of any data providers. But this can be a double edged sword. Data refresh on the server is a 64-bit only environment. Therefore you could easily back yourself back into a corner by selecting a data provider that is available in 32-bit on the PowerPivot for Excel client – but is not available for the 64-bit server and thus you cannot use the data refresh facility on the server. The workaround for a situation like this can be problematic – it is to install a 32-bit version of SSRS and use the data feeds feature to get access to the data. The 32-bit server running Reporting Services is the ‘link’ via to the 32-bit provider. Or you could use a linked server in a similar 64-bit server connecting through a 32-bit server. Kind of a hack if you ask me.
• Lastly, an advantage of 32-bit is that you can run Office 2007 and Office 2010 side-by-side on the same 32-bit desktop. This is not possible with 64-bit because there is no 64-bit version of Office 2007 (we need a common registry hive to have the side-by-side work).

Pros for 64-bit:

• Let’s start with the biggest advantage first – you can handler larger datasets with 64-bit. You can save an Excel workbook with embedded PowerPivot data in it up to 2GB of embedded data. This represents 4GB of in-memory data. In the 32-bit world, you will find that the largest Excel workbook with embedded PowerPivot data is about 300-400MB. This is because in the 32-bit world, the 2GB virtual address space has to handle Excel, plus any add-ins including the PowerPivot for Excel add-in, plus the PowerPivot vertipaq in-memory engine, plus its data structures, dlls, etc . . . thus you will find the largest in-memory data is about 600-800MB – and that translates to a workbook of 300-400MB. Now, it may be that this is large enough for all of your user data, and you may even have a set a lower limit for the SharePoint maximum file size upload. Out-of-the-box PowerPivot for SharePoint in a “New Server” configuration sets the maximum file upload to 200MB. But if you want to go larger, you may quickly find that 32-bit just isn’t large enough for the datasets that end-users need.
• There are some performance gains at the OS level as applications grow beyond 2GB. While this might not be an issue with most desktop applications such as Office, it is quite apparent in larger applications that push the computing power of the desktop – and those benefit from not having to page virtual address space to disk as its memory usage increases. No knowing if your desktop users might be using one of these ‘power’ applications on their desktop it is hard to establish if they will see a performance gain in going to 64-bit, but there are some apps that do.

Thus you have some interesting choices ahead of yourself as we all walk the path of the Brave New World of 64-bit on the desktop. Let’s just hope that we can have some fun while we struggle with it.

Enjoy.