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.
Prev. page
[1]
2
next page 
