Buffer Cache Hit Ratio - PLE

Question

Buffer Cache Hit Ratio - PLE

Arsh

SSCertifiable

Points: 6345
More actions
September 19, 2018 at 2:26 am

#377436

Dear Experts
I noticed a strange thing on some of our databases. When I wanted to get the values for Buffer Cache Hit Ratio and Page Life Expectancy , it returns nothing for these databases . Has it got to do with the size of these databases . They are just few GB's in size.
Best Regards
Arshad

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

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

How are you measuring the Buffer Cache Hit Ratio on a per database basis? It's a server level metric. It's also pretty useless as a metric. Same thing goes for the Page Life Expectancy. These are not database only values. They're server level values, so how are you measuring them per database?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Arsh SSCertifiable Points: 6345 More actions · Answer 2

Grant Fritchey - Wednesday, September 19, 2018 4:37 AM
How are you measuring the Buffer Cache Hit Ratio on a per database basis? It's a server level metric. It's also pretty useless as a metric. Same thing goes for the Page Life Expectancy. These are not database only values. They're server level values, so how are you measuring them per database?

Thanks Grant for pointing out that they are server level values. My concern still why they are blank ?

Regards..Arshad

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

Again, how are you recording them?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Arsh SSCertifiable Points: 6345 More actions · Answer 4

Grant Fritchey - Wednesday, September 19, 2018 5:51 AM
Again, how are you recording them?

I am querying them first time. I used the queries below :

--- Buffer Cache Hit Ratio

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

--- Page Life Expectancy

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'

Rdgsâ€¦.Arshad

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

Those should return data. If they're not, it's likely that something is up with sys.dm_os_performance_counters, not your databases or servers. Check all the possibilities Pinal Dave lists.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Arsh SSCertifiable Points: 6345 More actions · Answer 6

Grant Fritchey - Thursday, September 20, 2018 5:07 AM
Those should return data. If they're not, it's likely that something is up with sys.dm_os_performance_counters, not your databases or servers. Check all the possibilities Pinal Dave lists.

Yep it looks like access issue . User sa is able to get the info . The user I m trying has read-only access that has been granted the 'VIEW SERVER STATE' . Thank you.

Regards...Arshad

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 7

Arsh - Thursday, September 20, 2018 7:20 AM
Grant Fritchey - Thursday, September 20, 2018 5:07 AM
Those should return data. If they're not, it's likely that something is up with sys.dm_os_performance_counters, not your databases or servers. Check all the possibilities Pinal Dave lists.
Yep it looks like access issue . User sa is able to get the info . The user I m trying has read-only access that has been granted the 'VIEW SERVER STATE' . Thank you.
Regards...Arshad

Build a stored procedure with EXECUTE AS OWNER and give the user privs to execute only the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)