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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server 2008 R2 Memory Issue!

Author  Topic 

Naveed Amir
Starting Member

8 Posts

Posted - 2013-04-03 : 01:25:01
Dear Team,

Since we are using SQL Server 2008 R2 EE 64bit Sp2. We are facing memory issue, As we have allocated 21GB MAX memory to SQL Server out of total Physical memory 24GB.

Problem:

(As we know that SQL Server 2008 R2 caches all memory what we have allocated and does not release untill n unless we do not restart its services or restart the Server)

When there is no load on SQL server, SQL server shows the total memory which we have allocated to SQL in task manager, i.e. 21GB but when we see the process sqlservr.exe takes only 200MB.

How we can monitor the SQL server that we would know how actually SQL server memory required.


Regards,

Naveed Amir.


--------------------------------------------------------------------------------


Naveed Amir
IT Specialist

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-03 : 11:30:47
That setting is too high. I would knock it down to 19GB. You can use Perfmon, and look at SQL Server Memory Manager->Target Server Memory and Total Server Memory. Target is what SQL will try to use, and Total is what it is actually using.

You may be misunderstanding how it works, SQL doesn't immediately use 21GB at startup, it gradually grows it's memory usag up to the max setting as it needs the memory.

-Chad
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-03 : 14:47:42
Also, do an assessment of other programs which may require memory , such as Anti Virus and monitoring siftware or any other agents. That way you can ensure you allow those services to run without starving the SQL Server.
You can use DMVs , such as sys.dm_os_memory_clerks to see how SQL Server allocates the memory

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Naveed Amir
Starting Member

8 Posts

Posted - 2013-04-04 : 09:07:36
HI Chad,

As you said SQL server gradually grows its memory usage up to MAX memory setting as it needs the memory.
Thats what i am concering when SQL server holds all memory up to MAX how can we find that is SQL server actually utilizing this memory or just it caches the MAX memory.

Naveed Amir
IT Specialist
Go to Top of Page

Naveed Amir
Starting Member

8 Posts

Posted - 2013-04-04 : 09:12:06
If it actually do not Using the MAX memory then its should release the memory from MAX memory...
I just need to know that how can we find out SQL server memory consumption at time of when i run monthend...

Naveed Amir
IT Specialist
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-04 : 11:52:21
As said earlier, check target server memory vs total server memory
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-04 : 12:53:58
If it has it, it is using it. If you want to know what it is being used for, there are various DMVs you can query.

-Chad
Go to Top of Page

Naveed Amir
Starting Member

8 Posts

Posted - 2013-04-05 : 00:49:04
thanks chad,



Naveed Amir
IT Specialist
Go to Top of Page
   

- Advertisement -