February 21, 2007 at 1:21 pm
Hello,
I have a question regarding how to best monitor memory resources on a given server ? A few weeks ago, one of our Sql Server production servers was suffering from really poor response time. It turns out that after we added an extra 2GB of memory, performance improved dramatically.
I tried initially monitoring through the Windows based perfmon (performance monitor) utility. With the array of different counters available, I was a bit overwhelmed and it seemed that not all of the counters were helpful or useful in diagnosing the overall Memory usage of Sql Server.
I was hoping to benefit from your experience here. I’m looking for a counter which shows dynamically, how much memory we have available and how much we are currently using. I’m not having any luck in identifying these measures (if they actually exist?).
I have examined to perfmon counters from group SQLSERVER:Memory Manager.
I thought I could find what I was looking for by examining Target Server Memory and Total server memory counters but these were always equal. Is there a way to examine how much memory we have and how much we are using ?
Please share your thoughts, experiences, and any suggestions that you may have concerning the best way to monitor Sql Server Memory utilization.
Thanks in Advance.
Don
February 21, 2007 at 2:19 pm
May be trying tools like, http://www.teratrax.com/tpm/ is sometimes works simply the best.
-Laksh
February 22, 2007 at 12:01 am
Monitoring Total and Target server memory would let you know what is the allocation / deallocation rate of the server.
However a good way is to understand Memory Manager on SQL 2005/2000
Use DBCC MemoryStatus which compiles the list of available components inside the SQL Server Memory Manager. For ex: Data , Procedure caches, General Utilities (locks , Synchronization Objects).
http://support.microsoft.com/kb/907877/en-us
http://support.microsoft.com/kb/271624/en-us
Other Counters are as follows :
Memory: Available MBytes
Memory Manager: Total & Target Server Memory
Also Memory Grants Pending and Memory Grants Outstanding will let u know which Queries are waiting on memory for Compilation.
Process : Working Set Size, Private & Virtual Bytes
Also u can also find this out by Tracing SQL Performance and looking for the following events
Server Memory Change
Integer Data in "Degree of Parallelism" gives you the the amount of Memory in MB for the Query Compilation.
February 22, 2007 at 7:16 pm
Pay a vist to http://www.sql-server-performance.com/
It is a reat site just like this one !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply