I was recently faced with a situation where the users were complaining that of the one of the development database servers where slowing down. And so I logged to the server remotely and opened task manager and noticed the CPU was performing as usual but the PF memory was summing up to the total server memory. My first thought was, is the server under memory pressure?
Note - This database instance in concern lives in a box where there are two sql server instances.
To investigate further I opened the performance monitor and configured some of the usual counters I monitor with.
Buffer cache hit ratio , page life expectancy ,Avg and current disk queue length , Processor time, sql server connections , user database and temp transactions , complications and recompilations.
When I went through the Buffer cache and page life expectancy counters I knew it was nothing to do with memory pressure. But what could it be that server was encountering this slowness and request time outs ?
It was then I ran into the Total server and Target server memory counters under Memory. What does total and target server memory counters reprecent.
Total server memory
Is the amount of memory required by sql server to perform its current activities. This is a collection of both buffer cache and IO cache.
Target server memory
Target server memory is the memory that is been allocated by the system to sql server at the time sql server starts.
In a normal circumstance total server memory should always be less that target server memory. What this implies is, sql server has enough memory for it to perform its tasks. If the total server memory counter exceeds target server memory counter then its definite indication of memory pressure.
And so target and total server memory counters were showing me there were some memory pressure on the server. I guessed this can be a onetime issue as buffer cache and page life expectancy counters were far from looking troubled.
And so I opened the sql agent counters for both the instances and just on supposition I stopped the two sql server agents as the distributors where having very high values. But I was surprised to find the total server memory didn’t reduce but hanged on to the same old figure. By this time the developers were well into their dev work with the servers back in action. But it would be not long before they identify the replication were not working as the Sql agents were stopped.
I first started the agent of the second instance and before I stated the agent on the server where the problem originally was reported and check for the total server memory. It was having a lesser value than the target server memory for that instance.
Conclusion
Target server memory and Total server memory are two important memory counters available in sql server. These two counters will help identify if the server is under memory pressure along with other counters like buffer cache hit ratio and page life expectancy.
Note - This database instance in concern lives in a box where there are two sql server instances.
To investigate further I opened the performance monitor and configured some of the usual counters I monitor with.
Buffer cache hit ratio , page life expectancy ,Avg and current disk queue length , Processor time, sql server connections , user database and temp transactions , complications and recompilations.
When I went through the Buffer cache and page life expectancy counters I knew it was nothing to do with memory pressure. But what could it be that server was encountering this slowness and request time outs ?
It was then I ran into the Total server and Target server memory counters under Memory. What does total and target server memory counters reprecent.
Total server memory
Is the amount of memory required by sql server to perform its current activities. This is a collection of both buffer cache and IO cache.
Target server memory
Target server memory is the memory that is been allocated by the system to sql server at the time sql server starts.
In a normal circumstance total server memory should always be less that target server memory. What this implies is, sql server has enough memory for it to perform its tasks. If the total server memory counter exceeds target server memory counter then its definite indication of memory pressure.
And so target and total server memory counters were showing me there were some memory pressure on the server. I guessed this can be a onetime issue as buffer cache and page life expectancy counters were far from looking troubled.
And so I opened the sql agent counters for both the instances and just on supposition I stopped the two sql server agents as the distributors where having very high values. But I was surprised to find the total server memory didn’t reduce but hanged on to the same old figure. By this time the developers were well into their dev work with the servers back in action. But it would be not long before they identify the replication were not working as the Sql agents were stopped.
I first started the agent of the second instance and before I stated the agent on the server where the problem originally was reported and check for the total server memory. It was having a lesser value than the target server memory for that instance.
Conclusion
Target server memory and Total server memory are two important memory counters available in sql server. These two counters will help identify if the server is under memory pressure along with other counters like buffer cache hit ratio and page life expectancy.
Comments
Post a Comment