SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Server 2008 R2 Memory Issue!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Naveed Amir
Starting Member

Pakistan
8 Posts

Posted - 04/03/2013 :  01:25:01  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/03/2013 :  11:30:47  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 04/03/2013 :  14:47:42  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Pakistan
8 Posts

Posted - 04/04/2013 :  09:07:36  Show Profile  Reply with Quote
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

Pakistan
8 Posts

Posted - 04/04/2013 :  09:12:06  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/04/2013 :  11:52:21  Show Profile  Visit russell's Homepage  Reply with Quote
As said earlier, check target server memory vs total server memory
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/04/2013 :  12:53:58  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

Pakistan
8 Posts

Posted - 04/05/2013 :  00:49:04  Show Profile  Reply with Quote
thanks chad,



Naveed Amir
IT Specialist
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000