Solved

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

Posted on 2011-02-25
9
897 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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