Hello, Is it possible to capture buffer cache hit ratio using t-sql script: I am going to try this: declare @num float declare @dec float select @num = cntr_value from master..sysperfinfo where object_name = 'SQLServer:Buffer Manager' and counter_name = 'Buffer cache hit ratio' select @dec = cntr_value from master..sysperfinfo where object_name = 'SQLServer:Buffer Manager' and counter_name = 'Buffer cache hit ratio base' select cast( @num*100/@dec as numeric(5, 2)) Let me know if anybody has tried this way of capturing BCHR. Best Regards, Chetan "Calm seas can never make skillful sailors".
http://www.sql-server-performance.com/gv_monitoring_8_steps.asp http://www.dba-oracle.com/t_sql_server_ram_buffer_cache_scripts.htm MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hello , I have written a SP to capture the data of sysperfinfo from each SQL server (we have about 150 instances). However, for some instances there is no data at all in the sysperfinfo table. What could be the problem? The query is very simple: select * from openquery ( [linkedserver], ''select datepart(hh, getdate()) as db_hour, convert(varchar(10),getdate(), 121), cast ((SELECT cast(cntr_value*100 as float) FROM master.dbo.sysperfinfo WHERE counter_name = ''''Buffer cache hit ratio'''' ) / (SELECT cntr_value FROM master.dbo.sysperfinfo WHERE counter_name = ''''Buffer cache hit ratio base'''') as numeric(5, 2))'' ) ' Best Regards, Chetan "Calm seas can never make skillful sailors".
It happens when the some one using perfmon counters and restarted the services.. I believe it is fixed in SP4... What version and build you are on? Check the following.. http://support.microsoft.com/default.aspx?scid=KB;en-us;q196657 http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=687 MohammedU. Microsoft SQL Server MVP Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
If you are on pre-sp3 then better as soon as get on to SP4 as it is not supported anymore, missing data sounds like few PERFMON counters missing on those servers. IN this case you have to contact MS PSS for a bug fix solution: PSS ID Number: 812915 , BUG#: 363762 (SHILOH_bugs) Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com @http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
