By: Jeremy Kadlec | Comments (2) | Related: > SQL Server Configurations
Problem
I have a Windows 2003 Enterprise Edition server with SQL Server 2000 Enterprise Edition. The machine has 10 GB of memory, but I am not confident that the machine is using all of the memory that I have configured from an Enterprise Manager perspective. How can I validate how much memory SQL Server is using? I have a sneaking suspicion that only a small percentage of the memory is used, but I am not 100% sure. If the SQL Server is not using all of the memory, how can I have SQL Server access the 10 GB of memory or is that too much?
Solution
Purchasing memory and not having SQL Server use it should be a considered a crime against your user community in the database world. Since you have Windows 2003 Enterprise with SQL Server 2000 Enterprise Edition, one approach would be to assign 8 GB of the 10 GB of memory to SQL Server if it is a dedicated machine. If other applications or clustering or full text search catalogs are being used on the machine, then consider conducting some additional research to determine how much memory should be allocated to SQL Server. You will probably find that 8 GB of memory is too much to allocate to SQL Server.
Calculating Memory Usage
In SQL Server and/or Windows the following options are available to determine the SQL Server memory usage:
- Memory Configuration Tab - Using the memory configuration tab for Enterprise Manager is typically the interface used for determining the memory allocated to SQL Server. This interface can be access by navigating to SQL Server 2000 Enterprise Manager | Root | Server Name | right click | select Properties | Memory tab | review the dynamic and fixed memory configurations.
- sp_configure - Issue sp_configure and review the max server memory (MB) and min server memory (MB) counters.
- System Monitor Counters - Review the SQL Server: Memory Manager: Total Server Memory (KB) counter and divide the value by 1024 to convert to megabytes and again divide that number by 1024 to convert the number to gigabytes i.e. 8442864 KB = ~ 8.05 GB.
Changing Memory Usage
In order to change the memory usage in SQL Server, the following configuration changes need to be made from a SQL Server and Windows perspective:
- Boot.ini - In this circumstance, the following switches need to be added to the boot.ini file - /3GB /PAE.
- Max Server Memory (MB) - Use the sp_configure command to set the Max Server Memory to 8237 as follows:
| EXEC master.dbo.sp_configure GO EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC master.dbo.sp_configure 'max server memory', 8237 RECONFIGURE GO EXEC master.dbo.sp_configure GO |
- Min Server Memory (MB) - Use the sp_configure command to set the Min Server Memory to 1024 as follows:
| EXEC master.dbo.sp_configure GO EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC master.dbo.sp_configure 'min server memory', 1024 RECONFIGURE GO EXEC master.dbo.sp_configure GO |
- AWE Enabled - Use the sp_configure command to enable the AWE configuration as follows:
| EXEC master.dbo.sp_configure GO EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC master.dbo.sp_configure 'awe enabled', 1 RECONFIGURE GO EXEC master.dbo.sp_configure GO |
- Lock pages in memory - Configure the user account that the SQL Server service is running as with the Lock Page in Memory policy. This policy is in the User Rights Assignments in the Local Security Policy of the machine. Check out this KB to do so - SQL Server only uses 2 GB of memory even though the AWE option is enabled.
- SQL Server 2000 Service Pack 4 Hot Fix - Apply the following SQL Server hot fix - FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4.
- Windows Reboot - A few Windows reboots will be needed, so be sure to plan accordingly.
- Validation - In Windows System monitor check the SQL Server: Memory Manager: Total Server Memory (KB) counter to determine how much memory is in use. Keep in mind that you will have to divide the value in KB by 1024 two times to convert to gigabytes. In addition, in SQL Server issue sp_configure and review the three counters above that were changed to validate all three counters are accurate.
Next Steps
- Check out the memory allocation for all of your SQL Servers especially the instances that are supporting a large volume of memory with the Enterprise Edition and System Monitor to ensure they are using all of the memory that you have purchased.
- If your SQL Servers are not using all of their memory then work through the process on a test or development SQL Server if possible before making any changes to a production SQL Server instance.
- Be sure to properly analyze your machine for any applications that will require a significant amount of memory. Keep in mind applications like clustering or full text search may use more memory than you expect.
- As you make changes and work through the reboot(s) be sure to validate the memory configurations are accurate. So be sure to schedule downtime for these fixes or perform this process during a regularly scheduled maintenance window.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
