Betting on 64-Bit

Although SQL Server 2000 Enterprise Edition (64-bit) has been available for 2 years, many organizations are reluctant to invest in it. In discussions I've had with clients, many DBAs and developers have dismissed 64-bit because they think it's suited only for very large database (VLDB) applications or massive Analysis Services OLAP deployments and would be too costly for their organizations. However, three compelling real-world scenarios might make you reconsider putting your money on 64-bit now. Let's take a look at the benefits 64-bit SQL Server can provide in database consolidation, consolidated business intelligence (BI), and SQL Server multi-instance clustering.

As you consider these benefits, keep in mind that when most people refer to running SQL Server 2000 Enterprise Edition (64-bit) on a server, they're talking about an Intel Itanium2 platform, also known as a 64-bit Intel Architecture (IA64) or Itanium Processor Family (IPF) platform. (For a description of Itanium2 innovations, see the sidebar "Itanium Inside—Redux," page 27.) Itanium2 is the only 64-bit platform SQL Server 2000 64-bit will run on. However, SQL Server 2005 will support IA64 and x64 on both AMD and Intel x64 processors. If you want insight about incorporating AMD Opteron into your enterprise, check out Brian Moran's November 2004 SQL Server Perspectives article "Is 64-Bit Too Extravagant?" which is listed in Related Reading.

SQL Server Database Server Consolidation

Database servers are prime candidates for server consolidation. A recent AMR Research study of 251 companies found that database-consolidation projects "offer the hope of lower costs, greater ease of database administration, and the chance to improve the quality of their database technology by reducing the number of database instances and emphasizing some vendors over others." (To read the complete study, see the Microsoft article "Database Consolidation: Reducing Cost and Complexity" in Related Reading.)

Database consolidation is a good idea, but in a 32-bit environment, it has significant drawbacks. If you design your database consolidation on a traditional 32-bit server platform, your SQL Server can natively address only 4GB of memory, and applications can initially access only 2GB of that memory because the OS kernel reserves 2GB for itself. You can override this limitation with the addition of /PAE and /3GB switches to the boot.ini file, which changes the server to physical address extension (PAE) mode and lets your non-kernel applications use up to 3GB of memory. Keep in mind that all of a server's applications—including those we forget about, such as antivirus or monitoring software—must share those 3GB. Address Windowing Extensions (AWE) lets you access memory beyond the 4GB limit, but many memory-intensive processes can't access AWE memory. And once you enable PAE, you disable SQL Server's ability to dynamically manage memory. Thus, once SQL Server acquires memory, it won't relinquish it until you restart SQL Server.

If you depend on AWE, you might mistakenly assume that you can consolidate hundreds of databases onto a beefy 32-bit server loaded with 8GB, 16GB, or more memory, then effectively manage the consolidated load with AWE for memory access beyond the native 4GB limit. But if you examine SQL Server memory-pool activity when one server cohosts many databases, you'll see that each database requires increased memory to host its metadata objects (aka system data structures)—all the entity and attribute names, data types, the lock table, and so forth. Then, each database has a group of users (or other applications) that connect to it. Each database connection has an assigned connection context space in the memory pool that stores connection information such as parameter values for queries and stored procedures, cursor-positioning information, currently referenced tables, and intermediate query results for solving complex, multistep query plans. Some of these items can be quite bulky. Neither system data structures nor connection-context memory-pool constructs can use AWE; they must operate inside the native 4GB of the 32-bit platform. In addition, the procedure cache can't use AWE, and AWE doesn't permit storage of intermediate query results when solving complex, multistep query plans because those results are also stored in the connection-context space in the memory pool. The lack of AWE support for connection context can bring OLTP or data warehouse applications to their knees. This problem is amplified in a consolidated environment because multiple databases are vying for memory.

Now that I've painted a grim picture of database consolidation on 32-bit, let's examine 64-bit as the alternative. The 64-bit architecture can natively address 18 exabytes of memory (that's 18 billion billion bytes—4.3 billion times larger than 32-bit), but current 64-bit installations are limited by the hardware and software they run on. According to Microsoft's "Windows Server 2003 Feature Highlights" (see Related Reading), if you have an Itanium2 server that has sufficient capacity, Windows 2003 lets you configure it to use up to 512GB of RAM. With that kind of available memory, you can instantly eliminate all the roadblocks you encounter with 32-bit SQL Server by providing a huge, flat, natively addressable memory allocation to your consolidated SQL Server instances.

After you relieve memory bottlenecks, you'll understand the processing capabilities of the Itanium2 processor. Not only does the 64-bit chip access more RAM, but its architecture can access that memory faster (through more and wider paths). Also, the Itanium2 boasts a large Level 3 (L3) on-die memory cache, which simply means that the chip has more local memory—between 3MB and 9MB, depending on your chip selection—to eliminate roundtrips to the RAM bank for frequently used information. The 64-bit architecture, and the Itanium2 processor specifically, excels at parallelism and performance prediction, letting one processor handle multiple instruction sets simultaneously and discern what code might be requested next and proactively execute it. These combined capabilities reduce the number of processors you need for a database consolidation, and therefore will significantly reduce your SQL Server licensing costs. For a more detailed description of 64-bit server consolidation, check out Mike Ruthruff's Microsoft TechNet white paper "SQL Server Consolidation on the 64-Bit Platform: Lessons Learned" in Related Reading.

Consolidated BI

In walking through database consolidation, we looked at some of the pains of 32-bit memory limitations in the SQL Server relational engine. A 32-bit architecture has similar—and sometimes worse—limitations when you introduce Analysis Services for BI. A key problem is that Analysis Services doesn't support AWE. Analysis Services is an amazing product; it queries large relational data stores and creates OLAP data stores for detailed data and summary values or aggregates, all in a compressed format. Analysis Services then uses the compressed data stores to offer rapid (often sub-second) responses to reporting queries. But this functionality comes with a cost. When Analysis Services starts, it caches dimensions (which are multidimensional or OLAP entities, as Ken Miller explains in his April 1999 article "OLAP Terminology" in Related Reading) in memory—which is excellent until you think about caching a Customer or Product dimension that contains 5 million or 10 million or 15 million members. And when you process dimensions, an old copy (or shadow copy) of the dimension remains in place until the new copy finishes processing and the new replaces the old. Perhaps you also have custom security in place so that only certain users can see certain customer or product information. In such a case, each role assignment for a dimension requires that a separate, custom copy of the dimension is cached in memory. So you can understand how Analysis Services dimensions alone can rapidly consume memory—without even considering the cubes that the dimensions belong to, which have all the detailed fact records. Analysis Services' 32-bit memory limits, along with the memory demands of populating the cubes with details and aggregates, cause serious BI folks to immediately consider 64-bit for a dedicated Analysis Services server.

One way to improve Analysis Services performance is to put your relational data mart in a SQL Server instance that's on the same server as Analysis Services. In a typical Analysis Services setup, you load cubes on a dedicated Analysis server that queries a dedicated SQL Server over TCP/IP or named pipes. The network connection is often a bottleneck. But if Analysis Services and SQL Server are on the same server, Analysis Services can connect to SQL Server through shared memory, which lets you load cubes that are sourced from SQL Server at blazing speeds. Of course, the degree of performance increase is dependent on disk I/O and other factors.

That's a great performance trick, except that a 32-bit platform doesn't provide enough addressable memory to let SQL Server and Analysis Services play nicely together, even in a small to midsized environment. But with 64-bit, the memory constraints go away and processing horsepower increases, making 64-bit the optimal choice for a consolidated BI architecture. If you have a 4-way 32-bit SQL Server and a 4-way 32-bit Analysis Services server, you can consolidate them on a 4-way 64-bit BI server. You get ample memory and processing power to support SQL Server (hosting the relational data warehouse) and Analysis Services (hosting the multidimensional data marts). Because data-warehouse data population is usually batch oriented, you can get strong performance for both relational and OLAP tiers, and you have a lower overall costs because you're using your hardware and software licenses more efficiently and you've eliminated a server.

SQL Server Multi-Instance Clustering

As Microsoft Clustering Services (MSCS) in Windows 2003 and Windows 2000 has matured, SQL Server has gained a lot of market space because it lets you maintain high data availability when you run SQL Server on a Windows failover cluster (not to be confused with the Network Load Balancing—NLB—clusters that Web farms use). As Figure 1 shows, a typical active-passive failover cluster configuration consists of two nodes: a primary node and a failover node. (For information about clustering, see the clustering articles by Brian Knight and Richard Waymire in Related Reading.) This architecture is dependable, but it can also be very costly; Microsoft doesn't require you to license SQL Server on the failover (or passive) node in a cluster, but you can't avoid the hardware cost, the cost of the OS (which must be Enterprise Edition to support clustering), and the administrative overhead for the passive server. If you have several mission-critical SQL Server instances in your enterprise, each requiring a clustered environment, maintaining two servers for each instance adds up quickly. A cost-saving alternative is multi-instance clustering (aka an active-active cluster), in which you have two nodes, each acting as a primary node for a SQL Server instance and as a failover node for the other instance, as Figure 2 shows. This architecture lets you fully utilize all your investments in hardware and the OS, and it has little added administrative overhead—you add only the cost of licensing SQL Server on the second node of the cluster. This architecture works well in environments that don't have large databases, high concurrency, or overly complex queries. But if any memory-hungry activity is happening on the clustered instances during a failover, as in the database-consolidation senario, the two SQL Server instances will compete for all the available RAM and processing facilities on the same server. Instantly, all activity in the databases in both SQL Server instances will slow to a crawl. After about the third call from an irate database user, what seemed like a great plan (saving money by using multi-instance clustering) turns out to be a really bad plan.

Conceptually and logically, nothing is wrong with the multi-instance clustering architecture, but physically it just doesn't work with the 4GB addressable memory space and limited processing horsepower of a 32-bit server. However, if you host the multi-instance cluster on 64-bit hardware, your cluster has a greater capacity to weather a failover with less effect on end users. Of course, you need to appropriately configure your servers with adequate excess memory so that you can realize these benefits, but these costs are nominal compared to the costs of maintaining an expensive active-passive model. In an active-active model, you immediately make up for the cost of the 64-bit hardware and increased memory because all hardware and software is in use rather than sitting in a passive mode.

Some shops that use Windows 2003 Enterprise Edition or Windows 2000 Datacenter Edition might employ an N+1 clustered architecture, in which, for example, the cluster might contain four nodes—three active nodes and one standby node that the others use for failing over. When you crunch the numbers, this architecture is less expensive than a two-node single-instance cluster (active-passive), even in a 32-bit architecture, because you're using more of the hardware you've invested in. But even this model can be further improved if you use 64-bit. If you have an N+1 cluster on 32-bit and more than one node fails over, you need to manually intervene (or do a fair amount of cluster.exe scripting) and carefully supervise the fail-back or restore process. Otherwise, you can quickly end up with all of your SQL Server instances running on only one node in the cluster—and you might not have enough resources to run all your instances on the same 32-bit node. But hosting the cluster on 64-bit hardware gives you the memory and processing power you need to weather this type of convergent failover with less effect on end users, so you'll have the time to ride out a recovery—even from a catastrophic hardware failure.

Time to Ante Up

With SQL Server 2005 just ahead, we have a lot to look forward to in 64-bit—notably, comprehensive product builds for Itanium2, Opteron, and Xeon-extended—and we'll have all the SQL Server 2005 subproducts, including Integration Services, Reporting Services, Notification Services, and Client Tools. If you're considering new server purchases, and your organization is hedging its bets by thinking about sticking with a 32-bit system, take another look at how 64-bit might serve both your current and future needs. You might discover that it's worth the gamble.

Quote: "You can override this limitation with the addition of /PAE and /3GB switches to the boot.ini file, which changes the server to physical address extension (PAE) mode and lets your non-kernel applications use up to 3GB of memory. Keep in mind that all of a server's applications—including those we forget about, such as antivirus or monitoring software—must share those 3GB."
Isn't it so, that by using the /3Gb switch, you increase the user memory part for each process (which is not shared between applications) to 3Gb, and decrease the kernel memory part (which is shared between all applications) to 1 Gb?
Gé Brander

You bring up some excellent points. One thing I would like to point out, however, is that if an organization already has multiple 32-bit servers in place in either an active-passive or active-active setup, there is a way to go to N+1 without the "gotchas" and without upgrading everything to 64-bit. Simply make the "+1" server, i.e. the failover server, a 64-bit server which would be able to handle several 32-bit active clusters failing at the same time. True, they would be relagating their most powerful hardware to a role where it is usually not (hopefully never) used, but that is cheaper than going to an all 64-bit setup or multiple active-passive clusters and avoids the gotchas of the active-active scenario.
Andrew Steitz
acsteitz_(a)_yahoo.com

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More