sqlservr.exe NOT increasing in memory

Question

Post reply

sqlservr.exe NOT increasing in memory

adrianchamp

SSC-Addicted

Points: 404
More actions
September 29, 2010 at 5:20 am

#238922

This is a bit of a strange one. We are all used to SQL server consuming all the available memory (as by design). On my production server this is limited to 6GB and normally sqlservr.exe is running at 6GB as exepected.
However since yesterday it stubbornly won't grow beyond approx 384MB. I failed over to our passive node to see if the same problem is occuring there and the same thing is happening. I have tried to force sql to load the memory by querying some large tables. When I do this I can see the commit charge increasing in Task Manager as expected until the memory is used but the sqlservr.exe doesn't grow. There are no other processes listed in Task Manager using the memory either.
This doesn't seem to effecitng our system performance (at the moment) but I am get nervous when standard expected behaviours change. I am at a loss to what is going on and any suggestions anyone has would be greatly appreciated.
Thanks
Adrian

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are you seeing that memory usage?

Using AWE or is locked pages enabled?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

grahamc SSCertifiable Points: 6762 More actions · Answer 2

Where are you looking at the memory consumption? If I remember correctly (maybe wrong, its been a while :)), with the AWE switches, Task Manager does not report the correct memory usage.

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

If SQL Server configuration is fine execute following commnad to find memory related details.

select * from sys.dm_os_performance_counters where object_name like '%Memory Manager%'

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

adrianchamp SSC-Addicted Points: 404 More actions · Answer 4

It is the memory usage in Task Manager that I am viewing.

We are running SQL Server 2005 Standard Edition 64 bit.

The AWE flag is not set.

We are using the lock pages in memory trace flag (-T 845). This was added several months ago on advice by Microsoft to solve a seperate issue. I am pretty sure the sqlservr.exe has been showing at 6GB since then but now I am beginning to doubt my own memory.

If this low memory for sqlservr.exe (in task manager) is a consequence of using the trace flag then at least that is an explanation.

Adrian

grahamc SSCertifiable Points: 6762 More actions · Answer 5

Silly question, but has someone put in an upper limit on the amount of memory that SQL can use?

adrianchamp SSC-Addicted Points: 404 More actions · Answer 6

Not a silly question as it was one of things I double checked as well. Max Server memory is 6144 MB.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

adrianchamp (9/29/2010)
It is the memory usage in Task Manager that I am viewing.
....
If this low memory for sqlservr.exe (in task manager) is a consequence of using the trace flag then at least that is an explanation.

iirc, Task manager does not show memory allocated via the API that's used when locked Pages is set. That's one reason why it's generally recommended to use performance monitor (perfmon) to monitor SQL memory, not task manager

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adrianchamp SSC-Addicted Points: 404 More actions · Answer 8

Gail,

Thanks for the information. That explains why everything is working as it should be and i am just worrying over nothing.

Thanks everyone.

Adrian

adrianchamp SSC-Addicted Points: 404 More actions · Answer 9

Now managed to find a blog from Microsoft on the subject. http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx