Memory Used by SQL SERVER 2005

Question

Post reply

Memory Used by SQL SERVER 2005

c.ajitkumar

SSC Eights!

Points: 973
More actions
November 10, 2010 at 6:48 am

#143340

Hi All,
I am using sql server 2005 standard edition can anybody guide me for
how to check memory (RAM) usage by sql server .
Thanks in Advance.
Reagrds,
Ajit

Viewing 14 posts - 1 through 13 (of 13 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

DBCC memorystatus

Scroll down and look out for buffer counts Buffers

value under Committed is the amount of RAM SQL Server is using.

Pradeep Singh

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 2

Pradeep

Thanks for reply

but in the buffer count buffer comitted is looking static its not changes as the memory usage reduced in task manager

can u please describe these things

Ajit

Geoff A SSChampion Points: 11511 More actions · Answer 3

your SQL server is using the memory that you have allocated to it.

if you allowed the SQL Server to determine how much is allocated, the answer is all of it.

if you want the memory back (or you just want to see task manager show a different number) you would run;

DBCC FREESYSTEMCACHE(All)

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FLUSHPROCINDB( db_id )

DBCC DROPCLEANBUFFERS

however, running that in a prod enviroment will really make your users mad.....

also, task manager is not the place to view memory used by SQL Server.

you need to look at the Buffer Cache Hit ratio to make sure it is being used properly.

your Buffer Cache Hit ratio should be in the high 99%.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Hi Ajit,

Use the following Perfmon counters

SQLServer:Memory Manager -- Target Server Memory (KB)

SQLServer:Memory Manager -- Total Server Memory (KB)

Also for detailed consumption details, use DBCC MEMORYSTATUS

http://support.microsoft.com/kb/907877

Thank You,

Best Regards,

SQLBuddy

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

c.ajitkumar (11/10/2010)
Hi All,
I am using sql server 2005 standard edition can anybody guide me for
how to check memory (RAM) usage by sql server .
Thanks in Advance.
Reagrds,
Ajit

Give me more details ,why you want check?

So that we can help you.

SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

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

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 6

hi

thanks for replying

in my scenario i have 64 GB of RAM and i set the min memory =1GB and max Memory 45 GB

but when server is busy the task manager showing 55 GB usage by sqlserver.exe

so thats why i want to check how much memory sql server actually consuming that time

i am using SQL SERVER 2005 x64 Standard Edition.

Ajit

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

c.ajitkumar (11/11/2010)
hi
thanks for replying
in my scenario i have 64 GB of RAM and i set the min memory =1GB and max Memory 45 GB
but when server is busy the task manager showing 55 GB usage by sqlserver.exe
so thats why i want to check how much memory sql server actually consuming that time
i am using SQL SERVER 2005 x64 Standard Edition.
Ajit

Now you got sql server actually consuming memory.

Note : Task manager is not give a clear picture.

Did you feel the server has memory pressure ?

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

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 8

yeah we have memory pressure but i am not able to understand some thing

when i executed ur query i got the same value for total memory and target memory in peak hours and non peak hours.

ps. One Orange Chip Points: 29422 More actions · Answer 9

Total memory refers to the amount of memory that SQL Server has reserved for itself. Target memory refers to the amount of memory SQL Server needs.

If you need to know details of what sub processes are consuming memory, query sys.dm_os_memory_clerks dmv.

If you have set max memory for SQL Server as 45 GB, it wont cross that limit. See page life expectancy and buffer cache hit ratio if too much paging is happening. usage of 55 GB means something else is running on the server. does the server has a 2nd instance running?

Pradeep Singh

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 10

c.ajitkumar (11/11/2010)
yeah we have memory pressure but i am not able to understand some thing
when i executed ur query i got the same value for total memory and target memory in peak hours and non peak hours.

As per PS told check those counters

Also Check the following Counters in perfmon.msc and replay.
1.Memory: Pages/sec
Average between 0 and 20
2.Memory: Available Bytes >
Less than 20 to 25 percent of installed RAM is an indication of insufficient memory
3.SQL Server: Buffer Manager: Buffer cache hit ratio >90
4.SQL Server: Buffer Manager: Page Life Expectancy >300
Muthukkumaran SQLDBA
MCTS

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 11

Did you add sql server account to Lock Pages in Memory ?

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

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 12

server is using 10 GB memory for some other process .......

let me check the things as u guided

Thanks

Ajit

c.ajitkumar SSC Eights! Points: 973 More actions · Answer 13

I am a bit confuse abt it so i didn't add any user in

lock pages in memory option