solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

SQL Sentry, Inc. - SQL Sentry Performance Advisor

SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.

Learn more!

Memory Allocation for SQL Server 2000 Enterprise Edition

By: Jeremy Kadlec | Read Comments (2) | Related Tips: More > SQL Server Configurations

Free SQL Server Learning - Making the most out of SQL Server Agent

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.
- How to edit the Boot.ini file in Windows Server 2003
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 Update: 6/19/2007

About the author

Become a paid author

Comments and Feedback:

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

Read The Tip

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 adamdale345@gmail.com.

Regards,

Adam

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

Read The Tip

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

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial

Wish your SQL Servers could run wide open? Learn how the Edgewood SQL Server Consultants can make it happen.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

The SQL Server Security THREAT - It’s Closer Than You Think