Actual SQL Server Memory Usgage

Question

Actual SQL Server Memory Usgage

chinn

SSCrazy

Points: 2840
More actions
June 3, 2011 at 1:21 pm

#238532

I have a sql server 2008 64bit and windows 2008r2 64 bit and i have 8gb memory
and i set the max to 6gb and i see sql server always using 6gb as expected but how
do i know how much it is actually using...
Any help on this is greatly appreciated.
thanks in advance

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

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

JeremyE SSCoach Points: 15829 More actions · Answer 1

The perfmon counter SQL Server:Memory Manager:Total Server Memory(KB) will tell you exactly how much memory SQL is using.

chinn SSCrazy Points: 2840 More actions · Answer 2

But in this case i am seeing both Total and Target same.

What ever the max memory is set to...

I want to know..if it's having pressure...

Thanks,

JeremyE SSCoach Points: 15829 More actions · Answer 3

To assess memory pressure you need to look at SQL Server:Buffer Manager and the counters

Buffer Cache Hit Ratio (should be > 90) This is the % of pages in memory without reading from disk.

Page Life Expectancy (should be > 300) This is the number of seconds pages stay in memory before being flushed to disk.

These are just a couple to get you going.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

chinn (6/3/2011)
I have a sql server 2008 64bit and windows 2008r2 64 bit and i have 8gb memory
and i set the max to 6gb and i see sql server always using 6gb as expected but how
do i know how much it is actually using...
Any help on this is greatly appreciated.
thanks in advance

Look the past discussion

http://qa.sqlservercentral.com/Forums/Topic702953-360-1.aspx

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

chinn SSCrazy Points: 2840 More actions · Answer 5

Thanks Everybody ..on this.

But my question is .

If somebody comes to me and asks how much memory SQL Server is actually using out of the allocated how would i answer that...

Thanks....

nesma_mounir Grasshopper Points: 19 More actions · Answer 6

Hi,

To see the real memory used by sql server it is enough to execute (on master db):

select * from sys.sysperfinfo

where counter_name like '%Total Server Memory (KB)%'

and if this server is dedicated to SQL server so you can assign 7 GB to SQL leaving 1 GB to the Operating system.

Regards,

Nesma Mounir

http://nesmamounir.wordpress.com/

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

chinn (6/4/2011)
But my question is .
If somebody comes to me and asks how much memory SQL Server is actually using out of the allocated how would i answer that...

JeremyE (6/3/2011)
The perfmon counter SQL Server:Memory Manager:Total Server Memory(KB) will tell you exactly how much memory SQL is using.

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

chinn SSCrazy Points: 2840 More actions · Answer 8

Total Server Mem and Target Server Mem are almost the same thing..Does it mean it's using 6gb and it needs more...

thanks....

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Means it wants 6GB and it has 6GB.

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

sujitkmishra Mr or Mrs. 500 Points: 504 More actions · Answer 10

I 'wd worry about a possible memory pressure if Target Server memory is more than Total Server memory, Page life expectancy is less than say 200 and buffer cache hit ratio < 90%.

I 'wd trend on Memory Grants Pending and pages/sec to see the trend on memory pressure.

By design, SQL will use up as much memory as it can within the allocation. If you are going to allocate 7GB then you 'll see it is using the extra 1GB after some time. So don't worry about it unless you see any symptoms of memory pressure. To check current memory status you can use

DBCC MEMORYSTATUS.