how to know which part of sql server is consuming how much memory.

Question

I know about DBCC memory status.....When I see total server memory counter it show 10 GB which is  500 MB short of target server memory .How can I find which part is taking how much memory.........

---

Cheers!!! SqlFrenzy

Answer 1

    
   You can find it here : http://msdn.microsoft.com/en-us/library/aa905152(SQL.80).aspx

---

Lakshman

Answer 2

Hi Frenzy

Can you please refer the following link where in it describes in detail about monitoring memory usage .
http://support.microsoft.com/?id=907877

--------------------------------------------------------------------------------------------------------------------------------------------
Thanks , Suhas V

Answer 3

Most probably it should be your BUFFER POOL. And if that is the case , have a look at the msdn blog http://blogs.msdn.com/sqlperf/archive/2007/05/18/BufferPoolDataPagesBreakdown.aspx article to see what's there in your sql buffer pool.

---

Thanks, Leks

Answer 4

Hi Frenzy

Can you please refer the following link where in it describes in detail about monitoring memory usage .
http://support.microsoft.com/?id=907877

--------------------------------------------------------------------------------------------------------------------------------------------
Thanks , Suhas V

thanks Suhas...I mentioned that I have knowledge about MemoryStatus command...I was looking for something else as the memorystatus result required time to understand and diagnose.

---

Cheers!!! SqlFrenzy

Answer 5

Most probably it should be your BUFFER POOL. And if that is the case , have a look at the msdn blog http://blogs.msdn.com/sqlperf/archive/2007/05/18/BufferPoolDataPagesBreakdown.aspx article to see what's there in your sql buffer pool.

---

Thanks, Leks

Thanks Lekss; I ran the query given in the link....Total cached_pages_count is 571 which means buffer pool is consuming around 4568KB of memory which is very less...

I also checked the memory used by query plans using the query

select
   objtype, 
   count(*)as number_of_plans, 
   sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs, 
   avg(usecounts)as avg_use_count 
from sys.dm_exec_cached_plans 
group by objtype

which came out to be 739MB. I am not sure whether this is part of the RAM or Hard disk.

To conclude, If the above is part of RAM then total memory consumed by buffer pool is 739MB, which I suppose is not a problem. 

---

Cheers!!! SqlFrenzy