Results of NCC Members Question: Benefits of running 64bit Windows vs 32bit?
Results of NCC Member's Question: Issued 4 November 2011
NCC Members can ask relevant questions of the wider NCC membership, moderated by NCC. We collate the responses, where appropriate anonymise them, then publish the results on the website for the benefit of the wider Membership. Can you help with this question from a charity?
“We are currently experiencing performance issues with running SQL Server 2005 Standard edition on Windows 2003 R2 standard edition, both applications are 32bit and running as virtual machines in VMware v4.0 environment. We are interested to know if there is a tangible benefit to be gained by changing the operating system and application from 32bit to 64bit, whilst refreshing the both versions of the respective products. e.g. running Windows 2008 R2 and SQL Server 2008 64bit?
Number of responses: 5
Please note that these are responses from NCC Member organisations. NCC is not responsible for views expressed and does not does not recommend any particular supplier. NCC can provide respondent contact details should you wish to discuss further.
From a central government department:
“...absolutely there is. 64bit will allow SQLServer to address memory above about 3Gb which should make a noticeable difference to performance, They will, of course, need to add additional memory for that to make a difference and use a 64bit version of VMWare.”
From a central government department
“64 bit windows removes the limitation of addressable memory in 32 bit. That being said, I'd strongly suggest analysis of the performance issues by an experienced dba - it might not be a memory issue.
A great tool to help with this is Quest's Spotlight on SQL Server - the single server version was free.”
From a legal practice
“Running 64bit becomes necessary when you want to utilise more than 4gb RAM. If the performance is slow because of memory issues then they should. If they are not planning on upgrading RAM then there is no real benefit.”
From a large manufacturing company
“You don’t specifically state the nature of the performance issues you are facing but maybe I could share some experience we have gained from our own VMWare deployments and also advice we have sought and heeded from some recognised IS experts. I assume the VMware systems are configured optimally. There is no doubt that moving to a Windows Server 2008 64bit OS with SQL 2008 64bit can dramatically improve performance. This is down to one main factor and this is the ability to address far more memory space. The simple act of doing this means that more transactions are dealt with in memory rather than from disk. The obvious result is a much more responsive system. The more memory you can allocate to the system the better it gets.
However life is not that simple in a Virtualized environment. The golden rule here is always, always capacity plan any servers you plan to conduct a P2V migration on beforehand. If you use tools like VMWare’s own capacity planner or Capacity IQ it will let you know beforehand the impact of moving the server before you actually commit.
We have, on numerous occasions, been advised to avoid virtualizing higher end SQL servers as the disk IO cannot be accommodated in a VMWare environment unless you allocate the same or similar disk as existed in the physical environment; in which case one has to ask whether the end result is cost effective. We have adhered to this advice and so far not met any problems. There may also be knock performance issues of other servers hosted on the same VM system. Please consider if the VM environment you have is capable of running SQL under heavy workloads (including backups). You may be faced with investing in upgrading your VMWare infrastructure or moving SQL back to Physical.”
From a charity:
“You said you had your SQL Server virtualized; is it just the application part of the SQL server or you have virtualized your SQL storage as well? If so this would be my first guess. As for the 32 to 64bit upgrade; SQL Server does actually benefit from it quite a lot due to the increased physical memory allocation, 64bit architecture is also faster when it comes to passing data to the cache and the processor. In your email you have also mentioned ‘an application’. Is this a 3rd party application? If yes make sure the application support 64bit architecture. Before doing any of the above, though, I would look for bottlenecks and once they are found you can decide how to deal with the issue.”