As with other applications, SQL Server applications need memory to run. However, unlike most applications, SQL Server allows you to decide just how much memory it can use. That's good because SQL Server likes lots of memory.
Server memory options are set either within the GUI or with the sp_configure procedure by adjusting the "max server memory (MB)" setting.
Figure 1: Setting server memory properties.
Unlike most applications, you can configure SQL Server to use more than 2 GB of RAM by enabling the AWE (Address Windowing Extensions) memory setting, either on the same memory screen or via the sp_configure procedure by adjusting the "AWE enabled" setting. Both of these are advanced settings that cannot be seen without enabling the "show advanced options" setting.
Amount of RAM SQL Server needs
There is an old adage when it comes to Microsoft Windows: When in doubt, buy more RAM. And it's spot on when it comes to SQL Server, with the exception of SQL Servers that hold a very small amount of data. SQL Server requires a minimum of 512 MB of RAM in the server, and Microsoft recommends 1 GB of RAM. My personal recommendation is at least 1.5 GB of RAM in the server, with 1 GB of RAM for SQL Server and 512 MB of RAM for Windows. 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.
Although SQL Server 2005 doesn't have RAM limits, like SQL Server 2000 does, software selection is still important. When choosing your operating system (OS), be sure to select the correct version for your memory requirements. Windows 2003 Enterprise Edition supports up to 64 GB of RAM -- beyond that requires Windows 2003 Data Center Edition. So, purchasing a server with 128 GB of RAM and Windows 2003 Enterprise Edition would leave half the memory unused.
Minimum and maximum server memory settings
There are two numeric memory settings in SQL Server -- Minimum server memory and Maximum server memory. While there is some confusion as to how the Minimum server memory setting works, the Maximum server memory is clear cut: It is simply the highest amount of memory that SQL Server will use.
Many people believe that the minimum server memory setting tells SQL Server how much memory it should use when SQL first starts up, however this is not the case. The Minimum server memory setting is a low watermark setting. If Windows
needs to reclaim memory from SQL Server, it will request that SQL Server release memory from its control. SQL Server will return the memory back to the Operating System until the amount of memory in use reaches the minimum server setting.
Typically, I recommend that the max memory setting is 512 MB below the total amount of memory in the server. Once there are about 8 GB of RAM in the server, I alter this recommendation up to 1 GB of memory. I do this because systems that have that much memory in them typically have a lot of system processes running on them – such as backup software, lots of DTS/SSIS packages running, etc. – so the extra memory to the operating system can be useful.
Memory settings with multiple instances of SQL Server
Deciding on your memory settings when dealing with multiple instances can be quite tricky. When you have a single instance, you simply decide how much RAM the OS needs and set the rest for the database. With more than one instance, you have to make an informed decision as to how much memory each instance needs. SQL Server instances, which have smaller databases and lower transactions per second, obviously need less memory resources than instances with larger databases. When making these decisions, keep in mind which SQL Server version you're using and how it calculates the amount of procedure cache the SQL Server will use. On a 32-bit platform, the procedure cache must be located within the main application memory space (the first 2 GB of RAM) even with AWE enabled. You can read more about the procedure cache on the SQL Server with Mr. Denny blog.
| 32-bit platforms |
| SQL Server 2000 |
50% of the allocated memory or 1 GB, whichever is lower |
| SQL Server 2005&1t;SP2 |
50% of the allocated memory or 1 GB, whichever is lower |
| SQL Server 2005>= SP2 |
50% of the allocated memory or 1 GB, whichever is lower |
| 64-bit platforms |
| SQL Server 2000 |
50% of the allocated memory or 1 GB, whichever is lower |
| SQL Server 2005&1t;SP2 |
75% of the first 8 GB + 50% of the next 56 GB + 25% of the RAM over 64 GB |
| SQL Server 2005>= SP2 |
75% of the first 4 GB + 10% of the RAM over 4 GB |
AWE for memory
The Address Windowing Extensions API that Microsoft Windows exposes allows application developers to access more than 2 GB of memory on 32-bit systems. In Windows Server 2000, AWE is only available on the Advanced Server and Data Center editions of the operating system. In Windows Server 2003, AWE is available on all three server editions. To use AWE, you must enable the physical address extensions by adding the /PAE switch to your boot.ini file. Starting with Windows 2003 SP1, Windows automatically enables PAE on boot when more than 2 GB of RAM is installed.
Another switch that should be added to the boot.ini file is the /3GB switch. The /3GB switch enables SQL Server to access up to 3 GB of RAM.
Microsoft has written an excellent KB article entitled How to configure SQL Server to use more than 2 GB of physical memory. There are times when the /3GB switch should not be used, however. This is in systems running Windows 2003 Data Center edition, which have more than 16 GB of RAM.
When running SQL Server on a 32-bit system, you shouldn't enable AWE unless you are using more than 2 GB of RAM. Doing so can cause performance issues with SQL Server.
Changes with x64/64i Platforms
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.
| ABOUT THE AUTHOR: |
|
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Copyright 2008 TechTarget
|
|
 |
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
