Where can i find the: 1) cache hits and 2) cache lookup stats in SQL Server

Question

First of, the question is not: how to see SQL Server cache hit rate! For that one i already know of a view that contains that precises statistic.

My question is in fact: where are the raw statistics from where the hit ratio is calculated? Sql server, in msdn pages, states that the cache hit ratio is the total cache hits divided by the total cache lookups. So I am assuming that the RDBMS is storing these two values somewhere.

Anyone know where i can access them?

Answer 1

I believe that the exact metrics you seek are available via the Dynamic Management View (DMV):

• sys.dm_os_performance_counters

For example:

Select *
from sys.dm_os_performance_counters
WHERE OBJECT_NAME='SQLServer:Buffer Manager'

You can also use the Windows Performance Monitor to review the Buffer Cache statistics:

• The SQL Server Buffer Pool and Buffer Cache

For detailed information regarding the metrics concerning SQL Server Buffer Management consult the reference:

• SQL Server, Buffer Manager Object

To get detailed information regarding all of the SQL Server Memory space use the command:

DBCC memorystatus

Note: Regarding point 2) of the question, Page lookups == cache lookups and again this information is available in the afformentioned DMV.

Answer 2

Hi John

If you have access to the Sql Server then you can start Sql Server Profiler ,which will show you all the quries running on database, keep sql profiler running & start recorded web test (like in VSTS Web Test).

At least looking at running trace will give you good idea of how many times db is accessed.

Suppose you query is select * from cutomer ,see how many times it occurs in trace. Lets say 200 times

& number of test iterations , lets say 800.

then 800/(800-200) = cache hit ratio.