April 4, 2008 at 5:29 am
hi,
how to get hit ratio of the sql server .because i want to see health of my sql server .so plz tell me if there is any query for HIT RATIO
April 4, 2008 at 6:09 am
Could you explain more what you want please?
What are you trying to see?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2008 at 6:17 am
use windows perfmon and monitor thes counters - if you want buffer cache hit ratio, go to SQL Server: Buffer Manager\Buffer Cache Hit Ratio, if you want to know the plan cache hit ratio, go to SQL Server: Plan Cache\Cache Hit Ratio
April 4, 2008 at 6:25 am
You can get some from queries. For example, the dynamic management view sys.dm_os_performance_counters contains the buffer cache hit ratio.
"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
April 4, 2008 at 7:04 am
Hello,
Thaxx for telling me ''go to SQL Server: Buffer Manager\Buffer Cache Hit Ratio, if you want to know the plan cache hit ratio, go to SQL Server: Plan Cache\Cache Hit Ratio''
still i cant found i just try .will u plz tell me where is buffer manager and plan cache
i have just tried this one and it shows me buffer cache hit ratio
select * from sys.dm_os_performance_counters
thaxx
April 4, 2008 at 7:09 am
Here's one way to use the data:
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'
AND object_name = 'SQLServer:Buffer Manager') a
JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
and object_name = 'SQLServer:Buffer Manager') b
It came from this site.
"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
April 4, 2008 at 7:17 am
hi ,
still there is error comes from this query as well
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'b'.
April 4, 2008 at 7:41 am
I took that straight from the link provided. My bad for not checking it. Here's a quick & dirty version that works correctly
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
"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
April 4, 2008 at 8:28 am
hi,
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
is it really right query bcause still its not working but when i put these brakets with hit ratio and ratio base its just show me buffer cache hit ratio .is it fine
April 4, 2008 at 8:31 am
Looks like the post has smileys in place of parenthesis, but yeah, the query is working fine once those are replaced.
"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
April 4, 2008 at 8:35 am
You can also add the join back. The main thing is you have to remove the object_name in the where clause as this is not always consistent.
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
INNER JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
) b on a.x = b.x
April 4, 2008 at 8:39 am
hi,
will u plz try this query bcuase i am not sure that my reasult is fine according to my question
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
or will u plz tell me what to replace smileys with ....
thaxx in advance
April 4, 2008 at 9:22 am
You query is correct...
You need to look through the dmv sys.dm_os_performance_counters, to see what you want to calculate. It is pretty much all there. You can use the query provided as a baseline for your calcuations.
This is how to look through the performance counter options
select *
from sys.dm_os_performance_counters
where counter_name = '%Cache%' --or counter_name like 'buffer%'
This is how you would calcuate plan cache a for particular database.
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [PlanCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Hit Ratio' AND
Instance_name = 'MyDatabase')
a
,
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Cache Hit Ratio Base' AND
Instance_name = 'MyDatabase')
b
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply