Tag Archives: SQL Memory setting

SQL 2005/2008 – Configure Memory (part 2)

How can we be sure that SQL is low on memory? Or what to set up on max Memory value?

Here is a short guide on how to analyze memory needs for an Instance of SQL Server:
Use Perfmon Counters to understand SQL Memory needs
Considering that you did not set up yet the maximum SQL Server Memory limit, start Perfmon and include the below 3 counters when the system is most highly used. A stress test would be good enough in order to catch the highest/lowest values.

  1. Memory: Available MBytes – reports the available physical memory. This gives a good indication of whether you are running low on physical memory. If the machine is running low on physical memory, paging will either be happening or will soon begin to happen. This is useful data for diagnosing OOM issues. If this counter is greater than 10% of the actual RAM in your machine then you probably have more than enough RAM and don’t need to worry.
  2. Process(sqlservr): Private Bytes – indicates the amount of memory being used that can’t be shared with other processes. You should monitor this if you want to see how much memory your process uses. This counter also gives a good indication of how your application impacts the entire system—using a lot of private bytes has a big impact on the machine since the memory cannot be shared with other processes
  3. SQLServer: Buffer ManagerPage Life expectancy – normal value: < = 300 sec. According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem.

a) SQL is short on memory
First indication that SQL/Machine is low on memory will be that SQL process will assign as much Memory as it finds free and available on the system – until the system will run out of memory. So check the First counter from the list. It should be over 10% of Total RAM. If it’s not, look at the third counter to see whether SQL is really hungry for memory or just very greedy. If Page Life Expectancy is above 300 sec, but the SQL still requires more memory – than it is the time to limit the SQL Memory so that at least 1 GB is left for other OS processes. If, at contrary Available MB is very low and Page Life Expectancy counter is below 300 sec, than you actually are forcing the system to cope with less memory than it needs. Add few GB to RAM and check again.
b) SQL has sufficient memory
The second case is the easiest meaning that SQL didn’t grab all of available memory and the system still has enough memory for its own processes. I usually take the value from Private Bytes and add few GB (1-3) to set maximum Memory limit on SQL engine setting.

In short, The Page Life Expectancy is a very good indication that SQL has less memory that it would be comfortable with and I suggest collecting this value periodically along with other counters mentioned.

Basically when PLE goes under 300 sec it means, the activity on the Disk will grow (more Paging will occur) in order to get the pages which should still be in buffer pool. At this point CPU will also grow in order to manage the High Disk Activity. So, don’t take fast conclusions if you see high disk activity or CPU increasing. Memory, Disk and CPU are very tightly related.

In addition you could add these counters to check CPU and Disk activity:

  • Processor(_Total)%: Processor Time – determines the percentage of time the processor is busy by measuring the percentage of time the thread of the Idle process is running and then subtracting that from 100 percent. If you begin to see values of 70 percent or more for the Processor% Processor Time counter, investigate your processor’s activity further, as follows:
    1. Examine SystemProcessor Queue Length.
    2. Identify the processes that are running when Processor% Processor Time and SystemProcessor Queue Length values are highest.
  • PhysicalDisk(_Total): Current Disk Queue Length – used to determine the number of I/O requests queued for service. Notice that this might overstate the true length of the queue, because the counter includes both queued and in-service requests. If the value of Current  Disk Queue Length exceeds twice the number of spindles, then you are likely developing a bottleneck. With a volume set, a queue that is never shorter than the number of active physical disks indicates that you are developing a bottleneck.  Normally, for a disk with two spindles the value should be max 2.

SQL 2005/2008 – Configure Memory (part 1)

I promised to come back with the checklist of things to be done after the installation of SQL 2005/2008 is completed. This post will mainly be focused on the memory setting. Even though the memory is not the single worry, it is the principal one in my mind.

For starters, check that you installed the correct version of SQL on the right platform, Service Pack and edition. The following commands will show you all the basic information linked to the Server properties. This information can be stored if you want in a dedicated DBA table from a dedicated DBA server. If you have a lot of servers in your administration the collection of this kind of information is mandatory.

select serverproperty ('productversion')
select serverproperty ('productlevel')
select serverproperty ('edition')
select @@version

1. Memory min and max values:

As with other applications, SQL Server applications need memory to run. However, unlike most applications, it allows you to decide how much memory it can use. That’s good because SQL Server likes lots of memory and it will take as much memory as it is available on the server. If this option is left to the default value, soon you will face the out-of-memory problem for other applications or even for the OS. The funny part is that, the memory acquired by SQL is not released back to the system. Say the system has physical memory = 16 GB. If at some point the SQL needs 15,5 GB of RAM to function and there was this amount of memory available – the SQL will grow the memory used to 15,5 GB and will not give it back to the system when the transaction is over. If SQL Server uses all of the memory in the server, and Windows doesn’t have enough memory to function, SQL Server will run as if it is short on memory. Query response time will go up, CPU usage will go up and disk I/O will go up as Windows begins paging more and more RAM to the hard drive. You don’t want to reach this situation because the complications can be disastrous for you databases.
You can set the minimum and maximum Memory values issuing sp_configure from SQL or accessing “Properties” windows of the SQL Server instance from GUI. Both min and max values are extremely important for SQL to function properly.

Set minimum memory for SQL Server:

Minimum memory needs are specified on msdn website or in installation files of each SQL edition as hardware requirements section.
Here are the Memory needs for SQL 2005/2008 editions:

SQL 2005:
Minimum: 512 MB
Recommended: 1 GB or more
SQL 2008:
Minimum: 1 GB
Recommended: 4 GB or more

Set the maximum memory for SQL Server:

-- to allow to see the advanced options of SQL:
sp_configure 'show advanced options', 1

-- To set the maximum server memory value:
sp_configure 'max server memory', 6144

After you set the maximum and minimum values there a few things to be checked or considered.
2. Memory considerations if you run SQL Server on 32-bit platform:
Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit processes are therefore limited to 4 GB. By default, on 32-bit Microsoft Windows operating systems, 2 GB are reserved for the operating system, and 2 GB are made available to the application – in our case to SQL Server.
2.1. /3GB switch.
If you have a total of 4 GB Physical memory and you need to address more than 2 GB to SQL, you have to specify the /3GB parameter in the Boot.ini file of Windows 2000/2003 Server and reboot the machine. With the /3GB parameter, the operating system reserves only 1 GB to its processes and the SQL Server can access up to 3 GB instead of the default setting of 2 GB.
2.2. PAE (Physical Address Extension) and AWE (Address Windowing Extensions)
Physical Address Extension (PAE) is a processor feature that enables x86 processors to access more than 4 GB of physical memory on capable versions of Windows. Certain 32-bit versions of Windows Server running on x86-based systems can use PAE to access up to 64 GB or 128 GB of physical memory, depending on the physical address size of the processor. However, PAE does not change the amount of virtual address space available to a process. Each process running in 32-bit Windows is still limited to a 4 GB virtual address space.  At this point we need to consider the AWE facility.

AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications, such as large database systems, to address more memory than can be supported in a 32-bit address space.

Steps to configure the operating system for AWE:

  1. To support more than 4 GB of physical memory on 32-bit operating systems, you must first add the /PAE parameter to the Boot.ini file and reboot the computer.
  2. Remove /3GB parameter from Boot.ini file IF there is more than 16 GB of physical memory available on a computer. The operating system requires 2 GB of virtual address space for system purposes and therefore can support only a 2 GB user mode virtual address space.
  3. Enable AWE. Go to Sql Server Management Console > Properties dialog > Memory > select  ‘Use AWE to allocate memory’ , or alternatively by issuing the following command in a query against the target server:
  4. sp_configure 'awe enabled', 1
  5. Ensure that you add the user that the SQL Server service is running under to the ‘Lock Pages in Memory’ Local Security Policy (see Microsoft KB 811891 for the exact details on how to do this).  If you don’t update the local security policy, SQL Server will not actually use AWE and continue to use only 2Gb of memory; furthermore, you’re likely to see the following in the SQL Server log:
  6. "Cannot use Address Windowing Extensions because lock memory privilege
    was not granted."
  7. Restart the server machine.

3. Memory considerations if you run SQL Server on 64-bit platform:
In today’s 64-bit platforms, some great improvements have been made in the memory arena. While 32-bit platforms require you to use AWE and PAE to access more than 2 GB of RAM, the 64-bit platforms don’t have this limitation. In 64-bit platforms, all memory is available to the applications, as long as they are compiled as 64-bit applications; 32-bit applications running in Windows on Windows (WOW) have the same memory limits that they have running on a 32-bit platform.

While SQL Server only provides a few simple memory settings, setting them correctly is extremely important. Correct memory settings will have SQL Server running smoothly for a long time to come. Memory settings should be reviewed regularly to ensure that the original settings are still appropriate. After all, the amount of memory installed last year may no longer be enough memory or allocated correctly anymore.

You can find out and monitor how much memory SQL needs and whether the Operating System is comfortable with the memory left. Check out my next post related to SQL Memory needs.