SQL Server not using memory - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Server not using memory

Author

Topic

andykn
Starting Member

12 Posts

Posted - 2013-05-11 : 10:36:56

I have a (Altiris) database running on a SQL Server. Recently I noticed it was only using about 200MB RAM instead of the 14GB allocated as Min and Max that it's usually used. Both our SQL Admins and Server admins say they've made no changes and I'm not aware of any setting in the Altiris application that could cause this.

I only noticed the this after the Altiris web console slowed significantly following the DB filling the max disk space set. But the memory use didn't increase after the disk space was increased.

What in SQL might stop SQLServer.exe using all allocated memory? It's SQL Server 2008 R2 running on Windows Server 2008 R2 with 16GB RAM

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-11 : 16:21:19

First, setting the min and max memory to the same number is not recommended (http://msdn.microsoft.com/en-us/library/ms177455(v=sql.105).aspx ) I usually leave the min memory as the 0 default. But that may not be your problem.

I know this might sound silly to say, but the max and min memory are specified in MegaBytes, not in GigaBytes. I don't think that is the issue since you said everything was working fine until recently.

There are a number of data management views (sys.dm_xxxx) that deal with memory - for example sys.dm_os_sys_memory. You might want to look through those to see if they give you any additional clues.

I don't have any other thoughts, except to suggest a restart of the server if possible.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-12 : 02:06:41

I normally have the minimum memory set at the minimum memory required - which is usually less than the maximum

You can use the sys.dm_os_buffer_descriptors DMV to identify the memory usage of the database
http://www.sqlserver-dba.com/2012/11/sql-server-calculate-database-memory-usage-with-sysdm_os_buffer_descriptors.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-12 : 02:07:12

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-13 : 13:34:32

There are some setting (Such as Auto Close) that cause your caches (Including buffer cache) to be flushed periodically. Do you happen to have auto close enabled?

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-13 : 13:40:24

I could be wrong about the buffer pool being cleared by auto close in retrospect. I think it actually just flushes the proc cache.

Is the server under memory pressure?

-Chad

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-05-13 : 14:28:00

If you are looking at Task Manager to determine how much memory SQL Server is using - that may be where you have your issue. Task Manager does not accurately report how much memory SQL Server is using.

You can use perfmon and look at SQL Total and Target memory counters to see how much memory is actually being used.

Note: with only 16GB of memory available, I probably would not set max memory to 14GB. I would keep it around 12GB and no more than 13GB to make sure the OS has enough memory available.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 14:49:58

I second Jeff's suggestion, more so because the max memory you set in SQL Server is really the max that the buffer pool is allowed to use. Memory for everything else that SQL Server needs does not get counted in determining that max. So 2Gig is really on the low side.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-14 : 06:55:24

As part of setting the SQL Server max memory - complete a memory usgae exercise for other apps - such as monitoring agents, anti virus, OS etc . That way you'll have a better idea.
It gets more complicated if you are on a VM with multiple geuest to host ratio - particuarly if the host has over allocated memory .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

andykn
Starting Member

12 Posts

Posted - 2013-05-18 : 12:34:36

Thanks for all the replies.

I have rebooted the servers involved. This weekend I managed to move a copy of the db for an old Altiris server from an old SQL server to one of the problematic ones. On the old SQL system it was using the 14GB RAM in Task Manager it was allocated out of 16GB. On the new SQL Server it would only use around 100kb. Pointing the app back to the old SQL Server immediately caused the memory usage to rise, to 4GB within 30 mins.

Whilst I appreciate the comments on the best memory configuration, and will certainly look into them if I can get this working properly, I'm now sure it's an environment setting, almost certainly within SQL as both old and new Altiris and SQL systems are in the same OUs and use the same service accounts.

Whilst I understand Task Manager may not paint the best picture I still expect it to show high memory usage for SQL Server, it always does with Altiris. I'm sure it's not using the memory it needs because the Altiris console peformance is so poor.

What I need to know is what would stop it using the memory it needs, like in the old 32 bit days if the SQL Service Account didn't have "Lock Pages in memory" right it couldn't use AWE.

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-18 : 19:49:12

What are the values for the following perfmon counters?

Memory->Available MBytes
SQL Memory Manager->Target Server Memory
SQL Memory Manager->Total Server Memory

-Chad

andykn
Starting Member

12 Posts

Posted - 2013-05-19 : 13:39:44

quote:
Originally posted by chadmat

What are the values for the following perfmon counters?

Memory->Available MBytes
SQL Memory Manager->Target Server Memory
SQL Memory Manager->Total Server Memory

-Chad

For the SQL Server that works as expected (SQL Server 2005 on 64 bit Windows Server 2003, 16GB RAM fitted):

Avail 257 MB
Target 14,268,824 KB
Total 14,268,824 KB

For the SQl Server that won't use it's full allocation of memory (SQL Server 2008 R2 on Windows Server 2008 R2, 8GB RAM fitted)

Avail 7,999.600 MB
Target 6,144,000 KB
Total 6,144,000 KB

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-20 : 08:41:33

Is the server experiencing the problem 32 bit or 64 bit?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-20 : 14:00:55

SQL Server is using 6GB. What are you expecting it to use?

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-20 : 14:03:49

The old server has 2x the memory, perhaps that is why it performs better?

-Chad

andykn
Starting Member

12 Posts

Posted - 2013-05-21 : 05:32:32

quote:
Originally posted by jackv

Is the server experiencing the problem 32 bit or 64 bit?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

2008 R2 with SQL 2008 R2, 64 bit.

andykn
Starting Member

12 Posts

Posted - 2013-05-21 : 05:41:12

quote:
Originally posted by chadmat

SQL Server is using 6GB. What are you expecting it to use?

-Chad

I expect to see sqlservr.exe using that memory in Task Manager because, on systems that are working as expected, with SQL Server 2008 R2 on Windows Server 2008 R2, this is what I see. So I'm trying to discover what the difference is between a working Altiris/SQL system and a very slow one and so far this is the only difference I can see.

The Altiris console is behaving like SQL is using 100k RAM not 6GB RAM, I look after other systems where SQL has 6GB RAM available and these work fine.

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-21 : 12:16:48

You are not using the right tool (Task Manager). Perfmon is not lying, Task Manager is know no lie. Are there other DBs on the server that could be consuming the lion's share of the buffer pool? You could query sys.dm_os_buffer_descriptors and see how much of the 6GB is used by the Altris DB.

-Chad

andykn
Starting Member

12 Posts

Posted - 2013-05-21 : 18:36:18

quote:
Originally posted by chadmat

You are not using the right tool (Task Manager). Perfmon is not lying, Task Manager is know no lie. Are there other DBs on the server that could be consuming the lion's share of the buffer pool? You could query sys.dm_os_buffer_descriptors and see how much of the 6GB is used by the Altris DB.

-Chad

There are no other DBs on the SQL Server. My issue is that Task Manager always used to show sqlservr.exe using all allocated memory - on other 2008R2 SQL servers running the Altiris db with no other dbs Task Manager shows sqlservr.exe using all allocated memory. It's only on this one that Task Manager not only shows low memory usage the console reflects very poor SQL performance too.

Perhaps the question is what would cause one SQL Server to only show ~100k in Task Manager where another doing the same job shows ~6GB?

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-22 : 03:22:04

Do you have LPIM or AWE enabled?

-Chad

andykn
Starting Member

12 Posts

Posted - 2013-05-22 : 10:42:20

quote:
Originally posted by chadmat

Do you have LPIM or AWE enabled?

-Chad

No. The system always used to work fine without them.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources