SQL Server 2005 Memory decomposition

Question

SQL Server 2005 Memory decomposition

Muhammad Haseeb Farhan

Right there with Babe

Points: 728
More actions
February 24, 2009 at 3:58 am

#384002

Hi experts,
My SQL 2005 Server eating about 3 Gig memory when i see it in task manager. Can you please tell me the way how can i decompose this 3 gig memory in further detailed components.
i know dbcc memorystatus command. but its confusion.
i also run sys.dm_os.memory_clerks dmv and sum up cntr_value but its less than what task manger showing.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You aren't going to get the exact amounts shown by TaskManager because memory allocations inside of SQL Server fluid, but the working set size shouldn't fluctuate. SQL will free memory internally but not free it to the OS. This should be within a few KB of your Task Manager, which is not the correct way to monitor these number, you should instead use your Perf counters for Working Set size in the Process object:

select sum(multi_pages_kb+virtual_memory_committed_kb) from sys.dm_os_memory_clerks

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Muhammad Haseeb Farhan (2/24/2009)
Can you please tell me the way how can i decompose this 3 gig memory

:w00t: Memory decomposition is a fairly new concept on the digital world I assume.

Certainly human memory or at least the device handling it a.k.a. brain - by being bio degradable can easily decompose.

On the digital world I've heard about fried and even toasted memory but never seen or smell rotten memory. Can you picture the site support guy coming to your desktop, smell it and saying something in the line of "man, your memory stinks like a skunk!" 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.