January 28, 2009 at 9:47 am
Here is the configuration:
Server 2003 EE 64-bit SP2, 4GB RAM
SQL Server 2005 EE 64-bit SP3
I'm having trouble allocating memory to the sqlservr process. I've read up on granting Lock Pages in Memory - User Rights Assignment. I granted this privilege to System, Local Service and Network Service, We've also set the /3GB and /PAE flags in the boot.ini with no luck.
When I run a query to a temp table, the sqlservr process stays at 100MB and the page file fills up instead.
Here is the output for DBCC Memorystatus:
Memory Manager
VM Reserved4257568
VM Committed46104
AWE Allocated1630208
Reserved Memory1024
Reserved Memory In Use0
Memory Node Id=0
VM Reserved4251808
VM Committed40432
AWE Allocated1630208
MultiPage Allocator18232
SinglePage Allocator14784
After searching a lot, I've come up empty. Any help would be greatly appreciated.
Thanks!!
January 28, 2009 at 3:42 pm
hi there , few things ...there is misleading thing on memory setting so to be safe please read the following
please read this article from Slava...
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
4 gig is a very small amount for SQL, how big is your DB?
you need to look at a few counter.
sql target memory
sql total memory
avail memory .... if target and total are = then check your buffer cache if it getting below 99% you are having a problem
if this the case follow this ---http://support.microsoft.com/default.aspx/kb/271624
Sql will grab all the memory it can you will not need to help it, my guess sql has grabbed all the memory it can and now it has to go the page file.......more than likely you will need to add more RAM..
are you running anything else on this server beside SQL? if so this could be big problem? this could be cause externa, memory pressure....
January 28, 2009 at 4:26 pm
Just so you know, /PAE and /3GB have no affect on 64bit systems. You do not have to turn on AWE in SQL Server either. SQL Server on 64bit hardware will use all of the memory that the OS can handle.
I see this quite often and really don't understand it. You have spent all that money to purchase the 64bit hardware, 64bit Enterprise OS, Enterprise licensing for SQL Server - and you skimped on memory? How much extra cost is it to bump the system up to 32GB of memory? Especially when compared to the expenses already incurred for the other components?
Sorry - it just make me wonder what people are thinking.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2009 at 8:12 am
It is just a test server. We are testing EE before upgrading from SE. SQL Server is the only thing on the box, so I don't think there is a problem with other processes grabbing memory. I understand that TaskManager can be misleading, but is it wrong about the page file as well? When we run a large query to a temp table on our SE box, the process memory goes to a bout 3.2 GB, then it starts paging. When we do the same query on the EE box, the process memory stays at 100MB and the page file grows immediately to 3.5GB.
I will run perfmon to see what happens.
Thanks.
January 29, 2009 at 9:40 am
You really cannot depend upon Task Manager. You can use perfmon to determine the exact usage of memory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply