SQL Server Performance Forum – Threads Archive
How can you tell if sql server is using memory?
HI ThereI have a server running Sql Server 2005 Standard Edition X64.
There are 3 instances on this server. The server has a total og 16Gig Ram, i have have configured each instance to use 4 GIg Ram (i have defined the memory setting to a max of 4Gig for each instance), leaving 4Gig for the OS. When i run a very IO intensive query on one of the instances i see serious disk queue lengths, the query is very IO intensive.
WHat i dont understand is if i run the query again straight afterwards i see the huge IO bottleneck again, my question is, WHY is the data not cached in memory form the previous execution, why is it hitting the all over disk again? This would normally be a tricky one, BUT this is the only database on this instance, it is only me doing this query there is hardly any other activity whatsoever. And the data scanned for the query could easily fit into 4Gig of ram or less. Also how can i monitor this? I have customers asking me why data is not getting cached, how can i get to the bottom of this? Thanx
In Performance Monitor, look at Process>sqlservr><img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rivate Bytes, for each instance. And also look at SQL Server:Buffer Manager>Buffer Cache Hit Ratio, if you’ve got enough RAM, this counter should be 99% consistently.
]]>