Solved

Sql Server 2005 AWE on Windows 2008 32-bit 16gb of RAM

Posted on 2011-02-25
9
892 Views
Last Modified: 2012-05-11
I need SQL experts to send over some advice. I need to solve as to why our master SQl server is consistently having issues. We have a SQL Server 2005, Windows 2008 32-bit with 16GB of RAM. AWE I believe is currently set to 3GB, with 3 SQL INstances Please help!

0
Comment
Question by:mmoya
  • 7
  • 2
9 Comments
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
The first thing I would say is that AWE is expected to be removed in the next version of SQL Server, so it may not be the ideal configuration option.  See here:  http://msdn.microsoft.com/en-us/library/ms190673.aspx

Regardless, your post is a little vague -- what type of 'issues' is your server consistently having?
0
 

Author Comment

by:mmoya
Comment Utility
Apologize. Didn't realize I didn't include this part...

Error from SQL server log file
Date                     2/23/2011 10:00:33 PM
Log                       SQL Server (Archive #1 - 2/23/2011 10:04:00 PM)

Source                  ---multiple spID

Message
There is insufficient system memory to run this query.
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
First, be aware that there was a bug on this in v2005, and they issued a hot fix:
http://support.microsoft.com/kb/912439

Before anything else, I would be certain that you are at the latest build and patches available.

If you are, then I suppose I would check your memory configuration.  Best case scenario, your SQL Server would be a dedicated machine, and you would leave min and max memory settings to their defaults, and allow SQL to configure the memory dynamically.  However, if you have other applications on the server, it is often necessary to config memory settings explicitly.

Run this first, to ensure your advanced options are enabled:

USE master;
GO
EXEC sp_configure 'show advanced option', '1';

Then you shouild check the settings for both min server memory (MB) and max server memory (MB).  If your max server memory (MB) is unchanged, or it is a value close to that of your min server memory (MB), you could increase your max value.  See this reference for suggested max memory settings:
http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

Also, you should check the size of the virtual memory paging file. If possible, increase the size of the file.



0
 

Author Comment

by:mmoya
Comment Utility
Will defnitely test the bug fix. Thank you!

Patches and latest build are up-to-date.

Memory settings are as follows:
Use AWE to allocate memory = unchecked
minimum server memory (mb) = 0
maximum server memory (mb)= 2147483647

Other memory options:
Index creation memory (in KB, O = dynamic memory) = 0
Minimum memory per query (in KB)= 99999999

Total paging file size for all drives: 3625 MB

How do you allow SQL to configure the memory dynamically?

And the settings that is discussed in article = http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx is meant for 64 bit systems and we are using 32-bit.

Let me know. Thank you again.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
You allow SQL to configure the memory dynamically by simply leaving the settings at default.
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
For 32bit systems with >4gb memory, and multiple instances, you do need the AWE enabled, and you need to config min and max ram.

sql server will start with whatever it needs at start time, and allocate more (up to the max), as needed.  Releasing to the OS when it can, but never less than the min that you have specified.
0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
let me see if I can find some suggestions on the min/max for 32x systems.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
Comment Utility
Still looking for 32x min/max recommendations, but I forgot, you will also need to set the 'Lock pages in memory' setting for the SQL Server service account, when using AWE.  See here:
http://msdn.microsoft.com/en-us/library/ms190730.aspx


Also, this is a very good read on sql server memory:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx


0
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
Check this out, it gives very detailed explanations in the memory configuration, and allocation, for both 32x and 64x systems:
http://www.eraofdata.com/blog/sql-server-memory-configuration/
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now