Clever Geek Handbook

Where can I find: 1) cache hits and 2) cache lookup statistics in SQL Server

First, the question is not how to see the SQL Server cache hit rate! For this, I already know the opinion, which contains what sets the statistics.

My question is actually: where are the original statistics, where is the hit rate calculated from? The Sql Server, in the msdn pages, indicates that the cache hit rate is the total number of cache attempts divided by the total cache lookup. So I am assuming the RDBMS is storing these two values ​​somewhere.

Does anyone know where I can access them?

+2


source to share


2 answers


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

For example:

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

You can also use Windows Performance Monitor to view buffer cache statistics:



For details on metrics related to SQL Server buffer management, see the link:

To get detailed information about all SQL Server memory spaces, use the command:

DBCC memorystatus

Note. Regarding point 2) of the question, page search == cache search and again this information is available in the remote DMV.

+2


source


If you have access to SQL Server, you can run SQL Server Profiler, which will show you all the queries running on the database. Store the SQL Server Profiler and run the recorded web test (for example, in a VSTS web test).

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

Suppose you requested

select * from customer


see how many times this happens in the trace.

Let's say 200 times

& number of test iterations, say 800.

Then 800 / (800-200) = cache hit rate.

0


source






More articles:


All Articles