SQL Server 2000 and memory

5.2.1 Introduction

Another important resource on a Windows 2000 server is memory. Over the last few years the amount of memory found on servers and workstations has rapidly increased. Windows 2000 Datacenter Server, for example, supports up to 64 GB of physical memory.

Having large amounts of physical memory is not enough in itself. The software running on the server must be able to benefit from it, and it is therefore vital that the server operating system manages memory in an efficient and intelligent fashion. Windows 2000 employs a virtual memory manager to do just that, and it can provide excellent memory management on a wide range of memory configurations with multiple users.

SQL Server 2000 uses the virtual memory management features of Windows 2000 to enable it and other processes to share the physical memory on the server and to hold memory pages on disk in a page file.

Physical memory is a system resource. The more physical memory the better the system is likely to perform. If there is not enough physical memory on the server, then performance will be degraded as processes fight for memory. This section provides an overview of the

Windows 2000 virtual memory model and looks at how SQL Server 2000 uses memory. It then looks at how memory bottlenecks can be observed.

5.2.2 An overview of Windows 2000 virtual memory management

Similar to a number of modern server operating systems, Windows 2000 uses a flat, linear 32- bit memory model. Each process is able to address 4 GB of virtual memory. The upper 2 GB of virtual memory are reserved for system code and data, which are accessible to the process only when it is running in privileged mode. The lower 2 GB are available to the process when it is running in user mode. However, SQL Server 2000 Enterprise Edition provides support for using Windows 2000 Address Windowing Extensions (AWEs).

This enables SQL Server 2000 running on Windows 2000 Advanced Server to address 8 GB of memory and SQL Server 2000 running on Windows 2000 Datacenter Server to address 64 GB of memory.

Information held in physical memory can usually be categorized as either code or data. The pages given to a Windows 2000 process by the virtual memory manager are known as the working set of the process, and this working set holds pages containing any code and data recently used by the process. The working set of a process can grow or shrink as the virtual memory manager transfers pages of code and data between hard disk and physical memory. This is known as paging. All virtual memory operating systems page, and the secret is to make sure that the amount of physical memory and the memory requirements of processes are

such that paging does not become a burden on the system. In this situation, paging can cause disk bottlenecks and start to consume the processor.

If a page of code or data is required by a process, and it is not present in the working set of the process, a page fault results. The page is then brought into its working set. Whether the

working set of the process then grows is determined by the availability of free memory on the server. If there is an abundance of free memory, the working set of the process will grow as the new page is added. If there is a lack of free memory, pages in the working set that have not been used for a while will be removed. This is known as working set trimming. If pages are continually being taken out of the working set of a process to make room for new pages, it is likely that the removed pages will be needed again soon. The process will again page fault and the cycle will be repeated.

We can see that if memory is running low, code and data pages will be continually removed from, and added to, the working set of the process, resulting in many page faults. This can lead to a disk bottleneck and wasted CPU, since the system spends more time paging than doing useful work on behalf of the user.

There are two types of page fault. A hard page fault happens when the code or data page needs to be retrieved from disk. A soft page fault happens when it is discovered elsewhere in physical memory. Soft faults use CPU, but hard faults cause disk reads and writes to occur. When a page is removed from the working set, it may need to be written to disk if it has been changed. If it has not been changed, this need not happen. The area on disk that pages are read from and written to is known as the page file. The file name of the page file is pagefile.sys, and its default size is equal to 1.5 times the amount of physical memory. If memory is committed to a process (known as committed memory), space will be reserved for it in the page file.

5.2.3 How SQL Server 2000 uses memory

An instance of SQL Server 2000 is a single Windows 2000 process as is an instance of the SQL agent process that manages components such as the replication and alert subsystems. The amount of memory you can give to SQL Server 2000 really depends upon the amount of memory available on your Windows 2000 server, and this is a function of the amount of physical memory on the server and the memory requirements of other processes running on the server. Ideally, if it is possible, dedicate a single Windows 2000 Server to run a single instance of SQL Server 2000, and then SQL Server 2000 will not compete for memory resources with anything else. Of course, it can compete with Windows 2000 itself for

memory, but this will degrade performance and so the dynamic memory configuration in SQL Server 2000 leaves free memory for the operating system. If you decide to configure the memory requirements of SQL Server 2000 manually, you are advised to leave ample memory for the operating system.

Remember that multiple instances of SQL Server can run on one Windows 2000 server-a default instance with up to 16 named instances. Each of these instances will compete for memory.

So what is memory used for in an instance of SQL Server 2000? The short answer is lots of things. There is a pool of 8 KB buffers that are used for database pages-for example, data and

index pages and also query plans. Memory is required for user connections and locks. Most importantly, memory is required for the queries themselves.

Different queries can have very diverse memory requirements. A simple query such as a single row lookup will require little memory to execute. Such queries are typically found in online transaction processing systems (OLTPs). Other queries, such as the ad hoc queries found in data warehouse type systems, may need to perform large sorts. Some queries will need to perform hash joins on large amounts of data. The queries that need to sort and hash will benefit from lots of memory. If the sort can fit into memory, or the hash buckets can fit into memory, query performance will be improved.

When the query optimizer creates a plan for a query, it calculates the minimum memory a query will need and the maximum amount of memory it would benefit from. When a query needs to be executed, it is passed to a special scheduler. This scheduler checks to see if the query indeed does perform a sort or hash operation. If it does not, it is scheduled to run immediately. Queries that have a sort or hash operation will then be scheduled based on their memory requirements. Queries with small sorts or joins will be scheduled almost

immediately. Queries with large sorts or joins will be scheduled in such a way that only a few can run concurrently.

Configuring memory for SQL Server 2000

SQL Server 2000 will dynamically configure its memory requirements. It will expand to use up the free memory on the Windows 2000 server as long as it needs memory and that amount of memory is available on the server. It will not use all the free memory, since some will be needed by the operating system-typically about 4 MB to 10 MB. As other processes start up and need memory, the available free memory will drop and SQL Server will then release memory.

Two server configuration options, min server memory (MB) and max server memory (MB), can be used to specify upper and lower bounds for the memory a SQL Server 2000 instance will use. When the instance is started, it takes as much memory as it needs to initialize. This may well be below the min server memory (MB) value. However, once it has crossed this value, it should not drop below it. This ensures that even if the instance is not busy, some memory will be kept ready for starting queries. This ensures that their performance is not degraded by the instance trying to suddenly acquire memory it has given up. The max server memory (MB) value places an upper limit on the memory the instance will use.

These two server options can be set so that their values are equal. In this situation, once the instance has grown its memory to that value, it should not increase or decrease it.

These server configuration options can be set with the system stored procedure sp_configure or with the SQL Server Enterprise Manager. In the SQL Server Enterprise Manager the SQL Server 2000 instance name is right mouse-clicked and Properties chosen. The Memory tab is then selected. This is shown in Figure 5.10.

Figure 5.10: The SQL Server properties memory tab

The slider controls that set the min server memory (MB) and max server memory (MB) server configuration option values can be seen. These can be adjusted and are meaningful when the Dynamically configure SQL Server memory option is selected. If preferred, the Use a fixed memory size (MB) option can be selected, which effectively sets min server memory (MB) and max server memory (MB) values equal and stops dynamic configuration.

Note Address Windowing Extensions (AWEs) can be enabled in SQL Server 2000 Enterprise Edition to allow SQL Server to address large amounts of physical memory (8 GB on Windows 2000 Advanced Server and 64 GB on Windows 2000 Datacenter Server). In this case, dynamic memory management does not occur.

Once the server has been allocated memory, it uses it for a variety of objects-for example, user connections, locks, and the buffer pool (cache).

There are various methods to investigate the apportionment of memory. The System Monitor (described in Chapter 7) has a number of objects and counters to help us. Figure 5.11 shows the System Monitor in report format displaying some useful object counters.

Figure 5.11: System Monitor counters useful for checking memory use of objects In Figure 5.11 we can see three objects-Buffer Manager, Cache Manager, and Memory Manager. They belong to the instance of SQL Server 2000 named SQL2000_A. Some useful counters belonging to these objects are displayed.

The Buffer Manager: Total Pages counter represents the total number of 8-KB pages (buffers) in the buffer pool. This holds, for example, database pages and stored procedure query plans. There are currently 8,939 buffers in the pool.

The Cache Manager: Cache Pages counter, for the _Total instance, represents the total number of 8-KB pages (buffers) in the buffer pool used by cached objects, such as stored procedure plans, trigger plans, prepared SQL plans, and ad hoc SQL plans. If required, the number of pages used by each of these cached object types can be monitored individually. There are currently 4,867 pages used for cached objects.

The Memory Manager: Connection Memory (KB) counter represents the amount of memory in kilobytes used by connections. There are currently 384 KB used by connections. Generally, a new connection will take about 24 KB depending on the network packet size. The formula for connection memory is: ((3 * the network packet size) + 12 KB), with the default network packet size being 4 KB.

The Memory Manager: Lock Memory (KB) counter represents the amount of memory in kilobytes used by locks. There are currently 240 KB used by locks. Generally, a lock will take about 96 KB.

The Memory Manager: Optimizer Memory (KB) counter represents the amount of memory in kilobytes used for query optimization. There is no query optimization being performed at the time of the monitoring.

The Memory Manager: Total Server Memory (KB) counter represents the amount of dynamic memory that the instance is currently using. We can see that if we add up the Buffer Manager: Total Pages counter (remember, each page is 8 KB) and the Memory Manager counters, the value is not far from 72,592 KB. The figure arrived at is less, because we have not monitored all consumers of dynamic memory.

Another useful tool is DBCC MEMUSAGE. This has not been documented since SQL Server 6.5, and its output has changed dramatically since then. However, if we use it with that

thought in mind, we get the following output. dbcc memusage (names)

Buffer Cache Top 20

Database Name Object Name Index Name Buffers Dirty --- --- --- --- --- BIG accounts 5556 0 Master syscharsets 33 0 Master syscomments 24 0 Master sysmessages 14 0 BIG accounts UNKNOWN 11 0 :

:

This gives us an insight into the number of data and index pages used by the largest objects in cache.

To look at the sizes of cached objects, such as stored procedure plans, the syscacheobjects system table can be queried, as discussed in Chapter 4. Here is a fragment of output showing the pages used by different objects in cache.

SELECT cacheobjtype, objtype, pagesused, sql FROM master..syscacheobjects

ORDER BY pagesused DESC

Cacheobjtype objtype pagesused sql

--- --- --- --- Executable Plan Proc 2164 usp_test

Compiled Plan Proc 206 usp_test Compiled Plan Proc 52 sp_helpdb Executable Plan Proc 42 sp_helpdb

Compiled Plan Proc 31 sp_helpconstraint

5.2.4 Investigating memory bottlenecks

If memory starts to get tight on the server, performance will start to suffer. This is most likely to happen on a server that is running applications other than just SQL Server 2000, since they will contend for memory.

Before we investigate memory bottlenecks, we need to look at the tools we can use to do so. The first piece of information we will want to know is likely to be how much physical

memory the server has. We can easily check this by choosing About Windows from the Help menu in Windows Explorer, as shown in Figure 5.12.