 |
|
| SQL Server Tips: |
|
|
|
DATABASE MANAGEMENT AND ADMINISTRATION
32-bit vs. the 64-bit SQL Server performance surge
By Serdar Yegulalp, Contributor
12.05.2006
Rating: -3.94- (out of 5)
|
The 64-bit platform in SQL Server is setting a new computing standard. Will you make the leap from 32-bit to 64-bit? You're certainly familiar with 32-bit, but new advantages await with the increased memory of the 64-bit SQL Server edition. Hardware upgrades are now equipped with 64-bit, so the upgrade is at your fingertips. I'll compare the two editions showing the 64-bit improvements, including sustaining a larger number of concurrent users with its increased physical memory, Analysis Services optimization and overall performance boosts.
Even the most casual observer of the Microsoft SQL Server world knows that SQL Server now exists in two implementations: a 32-bit edition and a 64-bit edition. Microsoft is shipping many other server products this way as well, with the company trending toward offering only 64-bit editions. The release version of Exchange Server 2007, for instance, is 64-bit only; there is a 32-bit version, but it's officially unsupported and, according to Microsoft, should only be used for testing and development, not production.
With the release of SQL Server 2005, Microsoft offered SQL Server in parallel 32- and 64-bit editions and pushed hard for the adoption of the 64-bit edition among those who would benefit most from it. Those performing data mining, data warehousing and analysis (such as cubes) would get the most from the 64-bit edition. That said, people might still be confused about who or what SQL Server's 64-bit edition is for, how it's made available to customers and what's needed to get the most from it.
The single biggest difference between 32- and 64-bit editions of SQL Server, of course, is that Microsoft compiled and optimized the 64-bit edition specifically to run on a 64-bit Windows Server. That, in turn, requires a 64-bit processor. SQL Server 2005 (and Windows Server 2003) both run on the 64-bit x 64 (Intel/AMD) and the Itanium (Intel-only) platforms. Therefore, the hardware that's needed to run 64-bit SQL Server ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
has been in the marketplace for some time and may in fact already be in your organization.
The first main advantage 64-bit provides for SQL Server is the ability to directly deal with larger amounts of memory. The 32-bit system can directly address 4 GB of memory at the most; the only way to get to more memory than that in a 32-bit system is by using techniques like Address Windowing Extensions. Though it will allow you to use anywhere from 32 GB to 64 GB of RAM, it will cost you in performance. It's not possible to use much of this memory in a truly contiguous fashion -- it's often only useful for the sake of caching data pages, not performing actual work.
A 64-bit system, on the other hand, can directly address 1,024 gigabytes of physical memory, so the amount of data SQL Server can cache, hold directly in memory and perform live operations on, is larger by whole orders of magnitude. Operations that might take a long time or would require a lot of work in temporary tables in a 32-bit system can be done in-memory and much faster on a 64-bit system, since there's a lot less paging or blocking going on.
Sixty-four-bit environments also have the ability to support up to 64 processors. SQL Server was originally written to parallelize work as heavily as possible across physical and virtual processors, and in existing 32-bit setups it does so very effectively. If you move an existing SQL Server workload from a 32-bit, four-way system to, say, a 64-bit, 16-way system, the work will not only be spread out across more individual CPUs but also will be handled all the more efficiently.
Here are some scenarios in which moving to a 64-bit system will pay off:
Any SQL Server application that works with large amounts of directly addressable memory. One example, cited by Microsoft in its documentation about the advantages of 64-bit environments for SQL Server is SAP's Advance Planning and Optimization.
Running Analysis Services. This can be one of the most memory-hungry applications around, especially if you're working with databases several gigabytes in size or processing data cubes with many dimensions.
Consolidating several existing 32-bit database applications onto one 64-bit server. Because the memory barriers that existed before are gone, these apps can run side by side on the same machine and still show off a boost in performance that they couldn't have before.
Applications with large numbers of concurrent users. Each existing user requires a certain amount of physical memory, so the more physical memory that can be addressed, the greater the number of concurrent user connections can be sustained without breaking a sweat.
There are also a few reasons why it might not be a good idea to move to a 64-bit SQL Server (at least not immediately):
Don't migrate "just because." Moving to the 64-bit SQL Server will still require a good deal of planning and work. Unless you're going to get immediate and tangible results from doing so, there's no point in inconveniencing people right now. For instance, if you're working with databases using the SQL Server Desktop Engine and everything you do fits comfortably into 1 GB or even 2 GB of RAM, 64-bitness won't give you anything you don't already have. On the other hand, if you're fast outgrowing those constraints, then budgeting for a 64-bit upgrade is probably a good idea.
Don't move unless you're prepared to embrace SQL Server 2005 Integration Services as part of what you're doing. The old Data Transformation Services (DTS) system from SQL Server 2000 and before has not been ported to 64-bit environments. DTS was supposed to be replaced by SSIS — which is significantly more powerful and flexible — but many people might have DTS packages in use. To that end, if a move is in sight and you have DTS packages that you rely on, plan ahead. Become familiar with SSIS so these packages can be converted or rewritten before the 64-bit leap.
The 64-bit platform has shaped up into the future standard platform for server computing. For some time, the missing ingredient has been the applications and the operating system support available together and working in concert. The good news is that, at this point, any future hardware upgrades will come with 64-bitness as part of the package, and the users can make the leap to a 64-bit OS (and 64-bit SQL) at their leisure — if they haven't done so already.
ABOUT THE AUTHOR:
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2006 TechTarget
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
