SQL Server Reference Guide

More than almost any other component in your SQL Server computer, sizing, configuring and tuning memory use has the highest impact on capabilities and performance. Most database applications are not computationally intense, meaning that the database doesn't have to do a lot of mathematical processing. They are also very often not network intense, since (in proper code design) the only data returned to a client is the data that is needed — keeping the transit payload quite low.

But databases do need a fast I/O (storage) subsystem, since that's where the data is stored. And they also need a lot of memory, since that's where the data is not only processed, but cached and delivered to the user.

In this overview, I'll explain how SQL Server uses memory, and how you can configure it for your system. We'll get a little more detailed in this overview than some of the other systems I've covered, since the details of memory matter so much. This overview forms the basis for some of the monitoring topics I've covered elsewhere.

Memory Architectures

The first thing I'll need to explain is that all memory is not created equal. That is, different computer architectures handle memory in different ways. I won't cover all of the information about processor architectures, since I've written an entire overview on that topic, but we do need to bring some of that information to this overview to continue. If the following explanation isn't detailed enough for you, check out that other architecture overview and then return to this one.

All the memory setups I'll describe are sectioned off into pages, or sections, that the server can use. When values in a memory page are in use, the server keeps them as much as possible in physical RAM chips, which are very fast. When the server runs out of physical memory or the information in the memory pages isn't asked for very often, the operating system can store it on a file on the hard drive, called paging. This activity is orders of magnitude slower than physical RAM access, and should be avoided as much as possible. All of this memory together is called Virtual memory. You might also see it referred to as the Virtual Address Space, or VAS.

You might also see terms such as Paged Pool and Non-Paged Pool. These refer to how the specific contents of memory are handled. If the contents are in the Non-Paged Pool, then they won't ever go to the page file. These are things the operating system must have available at all times, such as the instruction set to handle the paging file! Of course this means that if the contents are in the Paged Pool area, they can be written out to the drive. This will become important to know when you monitor memory.

There are three basic chipsets in use for SQL Server at this writing: 32-bit (from both Intel and AMD), x64 (from AMD) and EMT64 (from Intel), and IA64 (the Itanium, from Intel). Now that we've covered some general information about memory, let's take a quick look at how each chip parses it out.

32-bit

For many of us, 32-bit processors still inhabit most of our servers. But they don't handle memory in the most efficient manner. To maintain compatibility all the way back to 8-bit software, these chips are restricted to directly addressing more than 4GB of memory. I say "directly addressing", since they can, though a few tricks, address more memory, but there is a penalty for higher memory amounts. We'll come back to that later.

32-bit processors (again, specifically from the Windows operating system standpoint) halve the 4GB of addressable memory into two pieces: 2GB for the Kernel mode (or what the OS wants to control) and 2GB for the User mode (or what the applications can directly control). What that means is that SQL Server, or any other application, can only directly address 2GB of memory — with a few exceptions. I'll explain those in the configuration section.

The point is that without special help, SQL Server (or any other application for that matter) is constrained into 2GB of RAM, which isn't a lot as we'll see shortly.

x64 and EMT64

This new type of chip is kind of a hybrid between the older 32-bit processors and the new 64-bit processors. They both contain "extensions" which have a great deal of impact on the memory space that the chip can address.

In the case of an older operating system or application, the x64 and EMT64 chips can act just like an older 32-bit chip. In fact, you can even use an x64-enabled operating system (such as XP64 or Vista or Windows 2003 Server) and run both 32-bit and x64-bit applications at the same time.

The x64 and EMT64 architectures can address far more than 32-bit chips — up to 16 terabytes in fact. And they do it in a very interesting way: they aren't constrained by the 2GB/2GB limit. This is called a "flat" memory space. It starts at 0 and works all the way through the RAM on the system, with no breaks in between. We'll see why this is important in a moment.

The Intel Itanium or IA64

This chip is a completely new architecture entirely. It does not share any binary code with its 32-bit predecessors. You have to install an IA64 operating system such as Windows 2000 Advanced Server for Itanium or Windows 2003 Server Enterprise for Itanium and applications written for that operating system such as Microsoft Exchange or SQL Server for Itanium.

The memory here is also flat, and can go up to 16 terabytes.

With the chip definitions out of the way, let's take a look at the way SQL Server parses the memory out.

SQL Server Memory Allocation

On startup, SQL Server sets aside an area called MemToLeave. This is about 384MB of RAM that SQL Server uses to hand out the smallest bits of work (called the max worker threads) and a few other housekeeping tools.

After that, SQL Server evaluates how much memory the operating system can spare, and grants some (based on a setting I'll describe later) to the Buffer Pool. This memory is very important to SQL Server – it's the 8K pages that match the 8K data page size that the database uses. This buffer pool handles the in and out of the data.

Just after that allocation a pointer location is reserved to the LazyWriter process. This process runs every second and decides whether the system needs to flush the buffer (it's done with the memory), release it to another page, or ask for more memory from the operating system. If SQL Server has been configured to use a small amount of memory at the start and allowed to grow, LazyWriter handles that growth. If the system was assigned a fixed amount of RAM, then you'll notice the system grow to the minimum amount you specified and stay there. Then LazyWriter makes sure that it doesn't go above the max, all the while paging things in and out of the memory space.

Oh, and LazyWriter also updates the PerfMon counters for memory. I wonder why they named it Lazy?

Now, because of those 32-bit restrictions I explained earlier, all of the data pages, queries and connection information (such as locks) for SQL Server have to fit within the 2GB of space (with one caveat, which I'll explain in a moment). The rest of the memory can store data blocks, which are sort of "read-aheads" for the data, so it isn't wasted, but it's this lower memory space that is so important.

In x64/EMT64 and IA64, it's much different. Everything fits in as much memory as you have. That's a huge advantage for a database, as long as you're requesting lots of large data sets and not lots of small ones.

Configuring Memory for SQL Server

You can set the memory for SQL Server 2000 using Enterprise Manager, which I've described in this tutorial. You can use SQL Server Management Studio for SQL Server 2005, as I've described this other tutorial. In either case, you're going to look for the server in question, right-click it, and then select Properties from the menu that appears. From there look for the Memory tab, and set the memory either to use a fixed amount (my choice) or select a floor and a ceiling amount. This last choice is often used for servers that are used for SQL Server and other applications – always a bad thing, in my book. In any case, this process holds true for either 32-bit or 64-bit SQL Server.

For x64/EMT64 (SQL Server 2005 x64 only) and IA64 (SQL Server 2000 and 2005 for Itanium), that's all there is to it. SQL Server will use whatever memory you specify here.

In the case of 32-bit SQL Server 2000 or 2005, you might wonder why you would ever buy a server with more than 4GB of RAM. If SQL Server is limited to the most important memory in the 2GB space, why bother? Well, there are some tricks you can do to improve the odds a little for your system.

First, you can add a switch to the BOOT.INI file on your server. To the end of the boot string add the following:

/3GB

This allows the operating system to dole out more than 2GB to SQL Server. Understand that this isn't free, and can have serious implications for your server. See the links at the bottom of this overview for more on that.

In addition, there is another switch you can add to the BOOT.INI file:

/PAE

If you add this switch, reboot your server, and then return to the configuration tools I mentioned earlier, you'll see a setting to enable AWE memory. AWE stands for Advanced Windowing Extensions, and it allows a pointer at the end of the 4GB memory space in 32-bit processors to use more than 4GB of RAM. Once again there is a penalty here — SQL Server will no longer manage the memory in this space dynamically, and it can only hold data blocks.

In future tutorials and overviews, I'll explain how you can monitor and size your memory based on this information.

Informit Articles and Sample Chapters

If you're just aching to get started on the monitoring of your server's memory, check out this book excerpt.

Online Resources

There are several important links that I need to point out for this overview. With the information I've covered here, make sure you read each one:

http://msdn2.microsoft.com/en-us/library/aa175282(sql.80).aspx

http://www.teratrax.com/articles/sql_server_memory_cache.html

http://support.microsoft.com/kb/321363

http://www.sql-server-performance.com/awe_memory.asp