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
Requires Free Membership to View
can be more quickly evaluated in memory than if they need to be swapped to disk. Why has 64-bit adoption been slow? 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?
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?
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.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation