Understanding the Buffer Cache

Fundamentally, the SQL Server buffer cache is a (large) area of RAM that's used as a scratchpada place to store frequently accessed data pages, procedures, query plans, and other detritus needed by the server. In reality, SQL Server uses several buffer caches to hold recently used data and manages them independently. As SQL Server is asked to fetch data from the database, it first looks in the buffer cache(s) to see if the data has been recently been fetched. Since database changes are also written to the cache before being committed to the hard disk, the data pages that are used most often tend to stay in the cache, where they can be retrieved more quickly. The side-effect of this behavior is that SQL Server can actually get faster as more users are added, as long as they're doing about the same thing with the same data, because SQL Server does not have to fetch the data from diskit's already in memory.

IMHO

More RAM makes SQL Server faster.

Managing Cached Query Plans

When SQL Server builds a query plan, it also writes it to the cache, where it remains until forced out by other, (hopefully) more important data or by request. When a query is executed that (closely) resembles a cached plan, SQL Server does not bother to build a new query planit simply uses the plan already in the cache. Generally, that's what happens when SQL Server is asked to execute a stored procedure, but it can also reuse the query plan of an ad hoc query. First, SQL Server checks to see if the stored procedure query plan is already in memoryif it is, it's simply reused. Frankly, it's very likely that SQL Server will reuse a stored procedure query plan (a virtual certainty)^[23]. However, the chances aren't as good that an ad hoc query plan can be reused.

^[23] One of the problems I'll talk about in Chapter 11, "Executing SQLCommand Objects," is why it's not great that these cached plans are always used. Sometimes, they aren't a good match for what needs to be done and need to be rebuilt.

IMHO

Stored procedure query plans are almost always executed from the cacheeven if they don't really match the query being executed.

Eventually, the cache fills with "pertinent" data rows and query plans. I say "pertinent" because these data rows and plans are the ones that have been accessed the most frequentlythey are the rows and plans that pertain to current client operations. When the cache is full, SQL Server asks for more memory from the OS. If no additional memory is available, SQL Server has to decide where to put newly arriving data and plans. To deal with this issue, SQL Server uses a least-frequently-used (LFU) system to discard items in the cache. To help decide what goes and what gets kept, each time a row or plan is accessed, its "use count" is incremented. All SQL Server has to do is find the blocks of memory with the lowest number to overlay. Sure, when new rows are fetched or when you fetch a Binary large object (BLOB, a picture or large text file stored in the database), the cache can be stressed and some pertinent data and plans might be overlaid to make room for the new data values.

The size of the cache is determined automatically by SQL Server. Its space is allocated out of the memory reserved for the server. You can set the maximum and minimum server memory using the Server Properties dialog exposed in SQL Server Management Studio (as shown in Figure 2.73).

Figure 2.73. Configuring the memory allocation in SQL Server Management Studio.

Generally, one of the easiest ways to improve SQL Server's performance is to increase the amount of memory allocated to SQL Server, so installing more RAM in your server can really pay off in big performance gainsassuming you aren't squandering the new RAM by running a print or report server or playing DVD movies on the same system. However, when working with SQL Server Express edition, your user might not appreciate SQL Server consuming more than its share of memory.

Of course, memory allocation varies quite a bit when SQL Server is used on a dedicated systemthat is, when it's the only software running on the box. In this case, the only competition SQL Server has to contend with is the operating system. This means you can run SQL Server on a smaller systemone that does not need as much power, as it's not distracted by user interaction and other programs. In a small office, I often recommend using that older system that's sitting around collecting dust to host SQL Server. It has plenty of power to do the job.

When SQL Server is installed alongside Microsoft Office and the plethora of other spam filters, virus protectors, VOIP applications, IM, and other stuff many folks run on their work systems^[24], SQL Server has plenty of competition for RAM. Depending on SQL Server's role, you might consider setting the maximum server memory to a much lower number. Consider that SQL Server is a "good citizen" when it comes to the memory it uses on the system. It asks for more memory only when it's needed and releases it when requested to do so by the operating system. Other applications are not so benevolent. For example, Microsoft PowerPoint grabs all of the memory on the system (and every system within 40 feetor so it seems) and holds it until the application ends. If you must run an application like PowerPoint on the same system as SQL Server, it's a good idea to set the minimum server memory (as shown in Figure 2.73).

^[24] Of course, home systems are even more loaded with background tasks and stuff that can hog CPU and RAM resources.