Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Memory Allocation for SQL Server 2000 Enterprise Edition


By:   |   Last Updated: 2007-06-19   |   Comments (2)   |   Related Tips: More > 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.


Last Updated: 2007-06-19


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, January 16, 2012 - 9:14:54 AM - Jeremy Kadlec Back To Top

Adam,

Thank you for the question.

What are the results of this:

"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."

Is this 32 GB or 60 GB?

Before applying the hot fix in production, I would test it on a test server.

On another note, I would really consider trying to upgrade to SQL Server 2008 R2 to eliminate some of these memory issues.

Thank you,
Jeremy Kadlec


Thursday, January 12, 2012 - 9:08:05 PM - Adam Dale Back To Top

Jeremy,

Thanks a lot for this post.

I am trying to find how much memory can sql server 2000 sp4 enterprise (it's on a A/P cluster and this is the only instance) use that's on windows 2003 sp2 enterprise (physical memory is 64 GB)? AWE, /PAE are enabled and the max server memory is set to 60 GB on this server. Some say sql 2000 can't make use of more than 32 GB. Is that true?

When I see the Target server memory counter, it's shows 32 GB. Can you please clarify if this is due to the bug you mentioned in this post or sql can't use more than 32 GB? If this is a bug, we will apply the hotfix.

I can be reached at [email protected] 

Regards,

Adam


Learn more about SQL Server tools