Catalog Manager\Cache Hit Ratio

Question

Post reply

Catalog Manager\Cache Hit Ratio

rajankjohn

SSCertifiable

Points: 5061
More actions
April 4, 2008 at 5:42 am

#186114

One of my colleagues has a client whose DBA isnsists on 90% value for the performance counter - SQL Server: Catalog Manager\Cache hit ratio. I tried to get some inductry values for this counter, but didnt get much help. I am not very sure about relevence of this counter in a OLTP application. Can anyone in the community please shed some light over this counter?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rajankjohn SSCertifiable Points: 5061 More actions · Answer 1

none seem to have much idea on this. can someone throw some light even though not a complete answer?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

This counter should be as close to 100% as possible.

On our systems the following query returns 0.999:

SELECT
ROUND(CAST (A.cntr_value1 AS NUMERIC) / CAST (B.cntr_value2 AS NUMERIC), 3) AS Buffer_Cache_Hit_Ratio
FROM
(
SELECT cntr_value AS cntr_value1
FROM sys.dm_os_performance_counters
WHERE object_name = 'MSSQL$instanceName:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio'
) AS A
,
(
SELECT cntr_value AS cntr_value2
FROM sys.dm_os_performance_counters
WHERE object_name = 'MSSQL$instanceName:Buffer Manager'
AND counter_name = 'Buffer cache hit ratio base'
) AS B

See:

http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

rajankjohn SSCertifiable Points: 5061 More actions · Answer 3

Marios - The link you are mentioning says about buffer cache hit ratio. I know buffer cache hit ratio and plan cache hit ratio. I am looking for references on the Catalog Metadata\Cache hit ratio.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 4

Any help will be appreciated. If there are no answers from sqlservercentral, God knows where I will get an answer from 🙁

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

It's a tough one - I did a google search, as I'm sure you did, and could not get anything...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

jagpalsingh4 Ten Centuries Points: 1311 More actions · Answer 6

Hi,

just try this i think its useful for u

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio')

a

,

(SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base')

b

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

I think this is roughly the same as what posted earlier. The OP is looking for Catalog Metadata\Cache hit ratio

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 8

So just to clarify this post deal w/ the number of sql calls relative to the number of cache hits. Meaning what's the percent of times a cached execution plan is used compared to the total?

Is this correct? I'm at 79%. Not good for me. And we've got some dynamic sql on our site that i'm doing my best to converted into stored procs. One of our prod boxes hits 98% cpu utilization for a couple hours every monday. So i've been toying w the performance counter views. interesting stuff.

I isolated a spid that’s running a search proc thats 2,200 lines long. They're loading a #table w/ values then based on the procs parameters their doing subsequent deletes then returning the final set. Granted it runs in only three second however it spans 25 spid to parallel executions in the process. I think this is the cpu hog culprit. When I change maxdop to 1 it take 11 seconds to run. when I set maxdop to 5 it takes 8.