SQL Server Reference Guide

In a way, the computing industry is a lot like the automotive industry. I'll probably need to explain that.

The automotive industry is in a bit of a quandary these days. It seems strange that with fossil fuels becoming scarcer and more expensive, they still use it as the primary power source for their engines. In fact, the basic design for the internal combustion engine hasn't changed much since it was invented. To be sure, there have been amazing design changes that make the engines stronger, faster, lighter and cheaper, but the fact remains that they still work on the same principle as the original engines paced in the first cars.

One of the main reasons this is true is, well, us. We won't pay for a new car that runs on special fuels that are more expensive and hard to find. The engines would get cheaper, of course, if we bought more of them, but we won't buy more of them because they cost too much. Not only that, most of the "alternative" cars, those that run on batteries or what-not aren't as fancy as those we've had around for decades and decades. I don't agree with that logic, in fact, I ride a motorcycle. But those reasons are the ones you'll hear the most often.

Computer architectures are like that. Computers (at least the Microsoft on Intel varieties) started in the mainstream with 8-bit processors, with the first popular version (the Intel 8088) introduced in 1979. From there 16 bit processors (the Intel 80286) were in popular use three years later, and 32 bit architectures (beginning with the 80386) were in use no later than 3 years after that. So now here we are some 20 years later and we're still using... 32-bit processors and software.

Even though we've had the Intel Itanium (which is 64-bit) available for the Microsoft operating system since 2002, most servers in the server room are still running 32-bit processors. The reasons are similar to the ones used in the automobile industry — we just won't buy anything else, and it's hard to find software that runs on them. The other similarity with the auto industry is that the 64-bit architecture is more expensive — and oddly enough, in some cases the clock speeds are slow.

Some of that is changing. 64-bit computing (again, at least in the Intel/Microsoft pairing) is coming into its own, although not by people like you and I purchasing more Itaniums — there's a new chip in town.

So should you use 64-bit processors for your SQL Servers? And if you do, which operating systems, versions and editions can you use with them? In this overview I'll answer those questions.

Chip Architectures Defined

There are normally two major chip types that we've recently bought for our servers. The first are the 32-bit variety either from Intel or AMD. These go by various code names like Pentium and Sempron, and they reach very high clock speeds. This chip architecture runs all modern Microsoft operating systems, from Windows 95 to Vista along with all the server versions.

32-bit chip architectures have 32-bit instruction sets. That means developers can write instructions that are 32-bits "wide". 32-bit chips can address 4GB of "Virtual" RAM, and have extensions to address more. The issue is that they handle the memory in chunks that break out in 1GB, 2GB and 4GB limits. This all goes back to the original designs on those first 32-bit chips, just like the combustion engine on cars.

The second type of chip architecture comes in only one brand: the Intel Itanium. The Itanium is a true 64-bit chip, sharing no code with the 32-bit predecessors.

The great thing about 64-bit chips isn't just that they have twice the instruction set size. In fact, having a larger instruction set size doesn't always guarantee faster operation. Think about driving two vehicles over a fixed difference. One (the 32-bit vehicle) is a racecar. The other (the 64-bit vehicle) is a huge dump truck. If you have to carry a single package from point A to point B, the race car will carry that package much faster. So in that case, the 32-bit vehicle is faster than the 64-bit vehicle, even though it is more powerful. If, however, you have to carry a hundred packages, the truck (although slower) will only have to make one trip, while the racecar will have to make a hundred trips. This time, the dump truck wins.

OK, the analogy isn't perfect, but it gives you the idea that 64-bit processors aren't always "faster" than 32-bit versions. We'll come back to this idea in a moment.

The real power (for SQL Server, anyway) in a true 64-bit architecture is in how it parcels out memory. Since the 64-bit architecture isn't bound to the older memory models, it can pay out the memory in complete segments, called a "flat memory space".

The Itanium also requires a different operating system. Microsoft makes Windows 2000 Server and Windows 2003 Server for Itanium. It runs Microsoft software, but not everything Microsoft makes. Not only does it not run every software package Microsoft puts out, even some software that does run on Itanium doesn't run the same way. SQL Server (2000, at least) is one of those packages that has some quirks. I'll explain that in a bit.

But lately we've been given an option, and it's an option that is taking hold. A few years back AMD developed a chip (the Athlon) that had a 32-bit base, with 64-bit extensions. That means the chip will run the same 32-bit operating systems and applications that we currently own, and if someone develops an application that will take advantage of the 64-bit extensions, will run in 64-bit mode. At first Intel scoffed at the idea, but as the new AMD chipset became popular in the datacenter, they created their own chip with 64-bit extensions, called EMT64. Both of these are often referred to as just "x64", even though that's the AMD designation. Hey, even Microsoft has "podcasts".

The interesting thing about this chip is that Microsoft has developed a few operating systems that can take advantage of it. That is, the operating system is 64-bit enabled, but all of your old 32-bit applications will work too. And some applications, SQL Server 2005 included, have an edition that will run in 64-bit mode in this architecture as well.

The best part about this chipset is that even 32-bit applications, which are limited in the "flat memory" space to 4GB, can run better in an x64 operating system. That's because the OS runs in the higher memory, leaving all 4GB for the application.

With that overview in mind, let's explore which versions of SQL Server run on which chips, and which operating systems are required for both.

SQL Server Versions and Editions and Chip Architecture

SQL Server, at least the versions we're working with in this overview, has some pretty big differences in how they handle chip architectures. Let's start with the earlier version.

SQL Server 2000

SQL Server 2000 was originally built as a 32-bit application. That means that it has the limitations of that architecture, most notably, the memory limitations. It runs in all editions (from MSDE to Enterprise) on 32-bit operating systems, from Windows 95 to Vista and Windows Server 2003.

But to compete with Oracle, Microsoft desperately wanted a 64-bit offering. When Intel announced that it would produce a true 64-bit chip, Microsoft started to work on not only a 64-bit version of SQL Server 2000, but an operating system it would require to run. So they created both — two Itanium operating systems, Windows 2000 IA and eventually Windows Server 2003 IA, and SQL Server 2000 Enterprise edition for Itanium that runs under these operating systems.

There are some caveats, however. You can't run the other editions (even Enterprise edition for 32-bit) on the Itanium. And not everything in SQL Server 2000 is included in the Itanium version. For instance, all of the client tools don't run on SQL Server 2000 Itanium, and neither does Data Transformation Services (DTS). Any COM packages you may have written to be called from SQL Server would need to be redesigned for another method to run on Itanium.

But with the x64 architecture, SQL Server 2000 has found new life. You have two choices for all the editions. On an x64 system, you can install an x64 Operating system (such as XP, Vista or Windows Server 2003) and still run 32-bit applications. They don't run in x64 bit mode, but they do gain a little memory boost and the faster speeds some of these chips offer. This 32-bit mode running on an x64-bit operating system is called "WoW", for (32-bit) Windows on (64-bit) Windows. The other choice is to install a 32-bit Microsoft operating system on the x64 chip (yes, that works) and run the software as normal. You don't get any benefits from the new chip, but you can do it.

SQL Server 2005

SQL Server 2005 was designed from the outset with x64 and Itanium in mind. All of the editions (from Express to Enterprise) run on 32-bit processors with all current Microsoft operating systems, and they will also run in WoW mode on x64 architectures with an x64 Windows operating system.

And starting with the Standard edition, you can run SQL Server 2005 in an x64 flavor as well. This essentially gets you into 64-bit databases in a system that can also run 32-bit applications. Most of the time, that's a good thing.

It gets a little murkier with the Itanium version of 2005. Although more of the application runs on the platform than in SQL Server 2000, you'll run into some minor issues with 2005 as well. The most notable are database and SQL mail, and some of the OLEDB drivers. I'll provide a reference to more information about that at the end of this overview.

Handy Chart

Now, if all that isn't as clear as it could be, here's a quick chart that can help you understand what you need to run the various versions of SQL Server:

Real-World Architecture Decisions

As I mentioned earlier, there are times when 64-bit architectures are faster and other times when 32-bit is actually faster. I'll tell you about an exercise I performed on an application I once worked with.

At the company where I was a Data Architect, we had an application that had thousands of reads and inserts per second. We began to run into performance issues that we couldn't get out of easily. I had a restriction that we couldn't drastically alter the application, and I wasn't allowed to consider SQL Server 2005. We had to make our current environment run faster.

I got three systems sent to us on loan from a major hardware vendor. One was the fastest Pentium 32-bit system I could find, the second was an x64 AMD system that had a slightly slower clock speed, and the third was an Itanium system (although not one of the newer Itanium II systems) that was half the clock speed of the other two. I capped the memory on all three at 6GB, and configured each with Windows 2000 Enterprise, the highest version each could run. For the 32-bit I used 32, for the x64 I used 32, and for the Itanium I used the IA version. I followed all of the guides I could find on tuning each based on its capabilities.

The development team wrote a "test harness", which was a special application that ran through the same inputs and outputs as a normal day of application operation. I then rigged up the same input and output in shell scripts, and set each to increment itself automatically by a factor of ten, to run directly on the server and bog it down. In other words, I wanted to see which box was faster, and which could handle more work.

I set up various performance monitor counters on all the systems and began my test. They went as you might expect — at lower levels, the faster clock speeds of the Pentium outperformed the x64, and that stayed pretty much true throughout the test — this was expected since they both had a 32-bit operating system, one of my constraints. The IA 64 never outpaced the other two systems, but at the peak could handle 4 times the load — meaning that the smaller systems ran faster but became unresponsive at 1/4th of the IA64. I performed the tests over three days and then smoothed the results with statistical methods.

So what did this prove? Well, without changing anything else, the faster clock speed of the 32-bit system was the way to go for us. But a better method would be to install the latest operating systems and SQL Server versions, and re-architect the application to take advantage of its environment. Once again, business needs trumped technical ones.

I'm hoping one day that will change — that as DBAs we can get the business to see the long-term gains in getting the latest technology and giving us the time to create the proper solution for them. But then again, I'm hoping the car companies will wake up and start producing smaller, more efficient vehicles as well.

The key is that you should always test to ensure that 64-bit is the way to go. Like most technology decisions, just dropping in a new system won't necessarily fix your performance issues if you aren't willing to re-engineer your system to take advantage of it.

Informit Articles and Sample Chapters

You can find a lot more about the Itanium here.

Online Resources

More on 64-bit SQL Server from Microsoft here.

Here's a capabilities chart for the different editions.