| QUESTION & ANSWER |
Q&A: Making the jump to 64-bit SQL Server 2005 |
 |
By Adam Machanic, Contributor
24 Jan 2006 | SearchSQLServer.com |
|
|
Running SQL Server on a 64-bit platform has been an option to improve database performance and scalability for quite some time, but your configuration options were limited and not without problems. For one thing, SQL Server 2000 could only run on the expensive Itanium line of processors, and SQL Server's client tools were not compatible with 64-bit platforms. SQL Server 2005, on the other hand, offers new options to harness the power of 64-bit architectures -- and the problems that made 64-bit less desirable in the past are all but gone. If you're considering purchasing new hardware for SQL Server 2005 installations, take a serious look at the capabilities and price points of today's 64-bit systems.
SQL Server MVP Adam Machanic offers some points to consider in this Q&A. Why should SQL Server shops switch to 64-bit architecture?
Machanic: The most important answer to this question is that 64-bit platforms have greatly increased memory accessibility over 32-bit systems, which can only natively address up to 4 GB of RAM. Using
Address Windowing Extensions (AWE) and related technologies, 32-bit SQL Server systems can address up to 64 GB of RAM, but not without overhead for address virtualization: AWE creates virtual "windows" for accessing higher memory. Each request for upper memory must be made through this window, at a much higher cost than requests for native memory. As a result, in high-utilization scenarios, the ability to access increased memory can actually hinder rather than help performance. In addition, AWE memory is only used by SQL Server for buffer cache – not procedure cache – and therefore won't help optimize servers that make use of many ad-hoc queries. Nor can it be used to help with in-memory sorts, hash joins or other data-intensive operations.
Today's 64-bit systems can access up to 512 GB of RAM natively. This means your performance will not be impacted by address windowing, and the additional RAM can be used by any SQL Server caches, not just buffers. This increased memory capability directly translates into better performance in many cases; less disk I/O will be necessary as more data is held in cache. You'll also notice performance gains for queries that use intermediate sorts, hash joins or cursors. All of these can be more quickly evaluated in memory than if they need to be swapped to disk.
Why has 64-bit adoption been slow?
Machanic: Given these huge benefits, one may wonder why 64-bit SQL Server adoption has seemingly been slow to date. SQL Server 2000's 64-bit options were limited as the only supported 64-bit configuration for SQL Server 2000 was Itanium servers running Windows Server 2003. None of the SQL Server 2000 client tools were available to run on 64-bit servers either, including Enterprise Manager, Query Analyzer and SQL Profiler. Even DTS (Data Transformation Services) packages could not be run on 64-bit servers, meaning DTS could not take advantage of the increased power.
What are the advantages of a SQL Server 2005 64-bit architecture?
Machanic: SQL Server 2005 brings the benefits of 64-bit architectures to the enterprise at a lower price point and with greater functionality than was previously available. First and foremost, SQL Server 2005 supports installation on both Itanium and much lower-priced x64 servers. So, in addition to monetary savings, DBAs can now run either Intel or AMD processors – but discussion on that topic is beyond the scope of this tip.
The SQL Server 2005 client tools are fully compatible with 64-bit servers, and all SQL Server supporting services can run alongside SQL Server 2005 in 64-bit configurations. This includes Analysis Services, SQL Server Integration Services, Reporting Services and Notification Services. All of these services will be able to take advantage of the increased memory capabilities, helping to drive performance of both the core SQL Server installation and business integration needs.
Which installations should be upgraded to 64-bit?
Machanic: There are two primary markets for upgrades: 32-bit, single-server installations that need to be scaled up and 32-bit, multi-server installations that need to be consolidated. Each scenario has clear advantages.
The most obvious signs that a single-server installation might fall into the scale-up category are deeply queued disk activity, low buffer cache hit ratios and low page life expectancy. All of these issues, which can be measured using performance counters, are addressed by the increased memory capabilities of 64-bit systems.
Determining if your multi-server installation is a good candidate for consolidation, on the other hand, is a slightly more difficult question. Careful testing should be done to measure how much memory will be needed by all databases in total, whether processors will be able to handle concurrent queries from all databases and whether disk systems will be able to handle the increased pressure of simultaneous reads and writes. This is a much bigger decision to make than upgrading a single server, but in terms of overall ease of management, the payoffs can be immense. For more information on consolidating multiple 32-bit servers on a single 64-bit server, refer to the TechNet article, SQL Server Consolidation on the 64-bit Platform: Lessons Learned.
Moving to a 64-bit installation can make a huge difference in terms of SQL Server performance and scalability. The options made available by SQL Server 2005 make upgrading from 32-bit much more plausible. If you're investing in new hardware for the new DMBS, you would be wise to explore 64-bit options, especially those based on the lower-priced x64 processors.
For more information on using 64-bit servers for SQL Server 2005, see the Windows Server System white paper, Advantages of a 64-bit Environment.
|
|
|
|
