Q8.7: How much memory to configure?

System 10 and below.

Overview

At some point you'll wonder if your SQL Server has been configured with sufficient memory. We hope that it's not during some crisis but that's probably when it'll happen.

The most important thing in setting up memory for a SQL Server is that it has to be large enough to accomodate:

concurrent user connections
active procedures
and concurrent open databases.

By not setting the SQL Server up correctly it will affect the performance of it. A delicate balance needs to be struck where your SQL Server is large enough to accommodate the users but not too large where it adversely affects the CPU Server (such as causing swapping).

Assumptions made of the reader:

The reader has some experience administering SQL Servers.
All queries have been tuned and that there are no unnecessary table scans.

Preface

As the SQL Server starts up, it pre-allocates its structures to support the configuration. The memory that remains after the pre-allocation phase is the available cache.

The available cache is partitioned into two pieces:

buffer cache - data pages to be sent to a user connection or flushed to disk.
procedure cache - where query plans live.

The idea is to determine if the buffer cache and the procedure cache are of adequate size. As a DBA you can use dbcc memusage to ascertain this.

The information provided from a dbcc memusage, daunting at first, but taken in sections, is easy to understand and provides the DBA with the vital information that is necessary to determine if more memory is required and where it is required.

If the procedure cache is too small, user connections will get sporadic 701's:

There is insufficient system memory to run this query.

If the buffer cache is too small, response time may be poor or spiky.

The following text describes how to interpret the output of dbcc memusage and to correlate this back to the fundamental question:

Does my SQL Server have enough memory?

Definitions

Before delving into the world of dbcc memusage some definitions to get us through.

Buffer Cache (also referred to as the Data Cache): Area of memory where SQL Server stores the most recently used data pages and index pages in 2K page units. If SQL Server finds a data page or index page in the buffer cache, it doesn't need to perform a physical I/O (it is reported as a logical I/O). If a user connection selects data from a database, the SQL Server loads the 2K data page(s) here and then hands the information off to the user connection. If a user connection updates data, these pages are altered, and then they are flushed out to disk by the SQL Server.
This is a bit simplistic but it'll do. Read on for more info though.
The cache is maintained as a doubly linked list. The head of the list is where the most recently used pages are placed. Naturally towards the tail of the chain are the least recently used pages. If a page is requested and it is found on the chain, it is moved back to the front of the chain and the information is relayed, thus saving a physical I/O.
But wait! this recycling is not done forever. When a checkpoint occurs any dirty pages are flushed. Also, the parameter cbufwashsize determines how many times a page containing data can be recycled before it has to be flushed out to disk. For OAM and index pages the following parameters apply coamtrips and cindextrips respectively.
Procedure Cache: Area of memory where SQL Server stores the most recently used query plans of stored procedures and triggers. This procedure cache is also used by the Server when a procedure is being created and when a query is being compiled. Just like the buffer cache, if SQL Server finds a procedure or a compilation already in this cache, it doesn't need to read it from the disk.
The size of procedure cache is determined by the percentage of remaining memory configured for this Server parameter after SQL Server memory needs are met.

Available Cache

When the SQL Server starts up it pre-allocates its data structures to support the current configuration. For example, based on the number of user connections, additional netmem, open databases and so forth the dataserver pre-allocates how much memory it requires to support these configured items.

What remains after the pre-allocation is the available cache. The available cache is divided into buffer cache and procedure cache. The sp_configure "procedure cache" parameter determines the percentage breakdown. A value of 20 would read as follows:

20% of the available cache is dedicated to the procedure cache and 80% is dedicated to the buffer cache.

Your pal: dbcc memusage

dbcc memusage takes a snapshot of your SQL Server's current memory usage and reports this vital information back to you. The information returned provides information regarding the use of your procedure cache and how much of the buffer cache you are currently using.

An important piece of information is the size of the largest query plan. We'll talk about that more below.

It is best to run dbcc memusage after your SQL Server has reached a working set. For example, at the end of the day or during lunch time.

Running dbcc memusage will freeze the dataserver while it does its work. The more memory you have configured for the SQL Server the longer it'll take. Our experience is that for a SQL Server with 300MB it'll take about four minutes to execute. During this time, nothing else will execute: no user queries, no sp_who's...

In order to run dbcc memusage you must have sa privileges. Here's a sample execution for discussion purposes:

1> /* send the output to the screen instead of errorlog */
2> dbcc traceon(3604)
3> go
1> dbcc memusage
2> go
Memory Usage:

                            Meg.         2K Blks           Bytes

      Configured Memory:300.0000          153600        314572800

              Code size:  2.6375            1351         2765600
      Kernel Structures: 77.6262           39745        81396975
      Server Structures: 54.4032           27855        57045920
             Page Cache:129.5992           66355        135894640
           Proc Buffers:  1.1571             593         1213340
           Proc Headers: 25.0840           12843        26302464

Number of page buffers:    63856
Number of proc buffers:    15964

Buffer Cache, Top 20:

           DB Id         Object Id      Index Id        2K Buffers

               6         927446498             0            9424
               6         507969006             0            7799
               6         959446612             0            7563
               6         116351649             0            7428
               6        2135014687             5            2972
               6         607445358             0            2780
               6         507969006             2            2334
               6        2135014687             0            2047
               6         506589013             0            1766
               6        1022066847             0            1160
               6         116351649           255             987
               6         927446498             8             897
               6         927446498            10             733
               6         959446612             7             722
               6         506589013             1             687
               6         971918604             0             686
               6         116351649             6             387

Procedure Cache, Top 20:

Database Id: 6
Object Id: 1652357121
Object Name: lp_cm_case_list
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 16
Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages
----
Database Id: 6
Object Id: 1668357178
Object Name: lp_cm_subcase_list
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 10
Size of plans: 0.202827 Mb, 212680.000000 bytes, 110 pages
----
Database Id: 6
Object Id: 132351706
Object Name: csp_get_case
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 9
Size of plans: 0.149792 Mb, 157068.000000 bytes, 81 pages
----
Database Id: 6
Object Id: 1858261845
Object Name: lp_get_last_caller_new
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 2
Size of plans: 0.054710 Mb, 57368.000000 bytes, 30 pages
...

1> /* redirect output back to the errorlog */
2> dbcc traceoff(3604)
3> go

Dissecting memusage output

The output may appear overwhelming but it's actually pretty easy to parse. Let's look at each section.

Memory Usage

This section provides a breakdown of the memory configured for the SQL Server.

Memory Usage:

                            Meg.         2K Blks           Bytes

      Configured Memory:300.0000          153600        314572800

              Code size:  2.6375            1351         2765600
      Kernel Structures: 77.6262           39745        81396975
      Server Structures: 54.4032           27855        57045920
             Page Cache:129.5992           66355        135894640
           Proc Buffers:  1.1571             593         1213340
           Proc Headers: 25.0840           12843        26302464

Number of page buffers:    63856
Number of proc buffers:    15964

The Configured Memory does not equal the sum of the individual components. It does in the sybooks example but in practice it doesn't always. This is not critical and it is simply being noted here.

The Kernel Structures and Server structures are of mild interest. They can be used to cross-check that the pre-allocation is what you believe it to be. The salient line items are Number of page buffers and Number of proc buffers.

The Number of proc buffers translates directly to the number of 2K pages available for the procedure cache.

The Number of page buffers is the number of 2K pages available for the buffer cache.

As a side note and not trying to muddle things, these last two pieces of information can also be obtained from the errorlog:

... Number of buffers in buffer cache: 63856.
... Number of proc buffers allocated: 15964.

In our example, we have 15,964 2K pages (~32MB) for the procedure cache and 63,856 2K pages (~126MB) for the buffer cache.

Buffer Cache

The buffer cache contains the data pages that the SQL Server will be either flushing to disk or transmitting to a user connection.

If this area is too small, the SQL Server must flush 2K pages sooner than might be necessary to satisfy a user connection's request.

For example, in most database applications there are small edit tables that are used frequently by the application. These tables will populate the buffer cache and normally will remain resident during the entire life of the SQL Server. This is good because a user connection may request validation and the SQL Server will find the data page(s) resident in memory. If however there is insufficient memory configured, then these small tables will be flushed out of the buffer cache in order to satisfy another query. The next time a validation is requested, the tables will have to be re-read from disk in order to satisfy the request. Your performance will degrade.

Memory access is easily an order of magnitude faster than performing a physical I/O.

In this example we know from the previous section that we have 63,856 2K pages (or buffers) available in the buffer cache. The question to answer is, "do we have sufficient buffer cache configured?"

The following is the output of the dbcc memusage regarding the buffer cache:

Buffer Cache, Top 20:

           DB Id         Object Id      Index Id        2K Buffers

               6         927446498             0            9424
               6         507969006             0            7799
               6         959446612             0            7563
               6         116351649             0            7428
               6        2135014687             5            2972
               6         607445358             0            2780
               6         507969006             2            2334
               6        2135014687             0            2047
               6         506589013             0            1766
               6        1022066847             0            1160
               6         116351649           255             987
               6         927446498             8             897
               6         927446498            10             733
               6         959446612             7             722
               6         506589013             1             687
               6         971918604             0             686
               6         116351649             6             387

**Index Legend**
Value	Definition
0	Table data
1	Clustered index
2-250	Nonclustered indexes
255	Text pages

To translate the DB Id use select db_name(#) to map back to the database name.
To translate the Object Id, use the respective database and use the select object_name(#) command.

It's obvious that the first 10 items take up the largest portion of the buffer cache. Sum these values and compare the result to the amount of buffer cache configured.

Summing the 10 items nets a result of 45,263 2K data pages. Comparing that to the number of pages configured, 63,856, we see that this SQL Server has sufficient memory configured.

When do I need more Buffer Cache?

I follow the following rules of thumb to determine when I need more buffer cache:

If the sum of all the entries reported is equal to the number of pages configurd and all entries are relatively the same size. Crank it up.
Note the natural groupings that occur in the example. If the difference between any of the groups is greater than an order of magnitude I'd be suspicious. But only if the sum of the larger groups is very close to the number of pages configured.

Procedure Cache

If the procedure cache is not of sufficient size you may get sporadic 701 errors:

There is insufficient system memory to run this query.

In order to calculate the correct procedure cache one needs to apply the following formula (found in SQL Server Troubleshooting Guide - Chapter 2, Procedure Cache Sizing):

proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25
The flaw with the above formula is that if 10% of the users are executing the largest plan, then you'll overshoot. If you have distinct classes of connections whose largest plans are mutually exclusive then you need to account for that:
ttl proc cache = proc cache size * x% + proc cache size * y% ...

The max(# of concurrent users) is not the number of user connections configured but rather the actual number of connections during the peak period.

To compute the size of the largest [query] plan take the results from the dbcc memusage's, Procedure Cache section and apply the following formula:

query plan size = [size of plans in bytes] / [number of plans]

We can compute the size of the query plan for lp_cm_case_list by using the output of the dbcc memusage:

...
Database Id: 6
Object Id: 1652357121
Object Name: lp_cm_case_list
Version: 1
Uid: 1
Type: stored procedure
Number of trees: 0
Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages
Number of plans: 16
Size of plans: 0.323364 Mb, 339072.000000 bytes, 176 pages
----
...

Entering the respective numbers, the query plan size for lp_cm_case_list is 21K:

query plan size = 339072 / 16
query plan size = 21192 bytes or 21K

The formula would be applied to all objects found in the procedure cache and the largest value would be plugged into the procedure cache size formula:

**Query Plan Sizes**
Object	Query Plan Size
lp_cm_case_list	21K
lp_cm_subcase_list	21K
csp_get_case	19K
lp_get_last_caller_new	28K

The size of the largest [query] plan is 28K.

Entering these values into the formula:

proc cache size = max(# of concurrent users) * (size of the largest plan) * 1.25
proc cache size = 491 connections * 28K * 1.25
proc cache size = 17,185 2K pages required

Our example SQL Server has 15,964 2K pages configured but 17,185 2K pages are required. This SQL Server can benefit by having more procedure cache configured.

This can be done one of two ways:

If you have some headroom in your buffer cache, then sp_configure "procedure cache" to increase the ratio of procedure cache to buffer cache or

procedure cache =
[ proposed procedure cache ] /
( [ current procedure cache ] + [ current buffer cache ] )

The new procedure cache would be 22%:
procedure cache = 17,185 / ( 15,964 + 63,856 )
procedure cache = .2152 or 22%
If the buffer cache cannot be shrunken, then sp_configure "memory" to increase the total memory:

mem size =
([ proposed procedure cache ]) /
([ current procedure cache ] / [ current configured memory ])

The new memory size would be 165,399 2K pages, assuming that the procedure cache is unchanged:
mem size = 17,185 / ( 15,964 / 153,600 )
mem size = 165,399 2K pages