Solved

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

Posted on 2011-02-25
9
902 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
ID: 34979634
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
ID: 34979853
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
ID: 34981038
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mmoya
ID: 34982483
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
 
LVL 17

Expert Comment

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

Expert Comment

by:dbaSQL
ID: 34982770
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
ID: 34982774
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
ID: 34982831
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
ID: 34983085
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

713 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