Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-25
9
Medium Priority
?
935 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 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