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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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