Hi all i am new to this forum, though i am not a SQL DBA, i am responsible for monitoring the SQL instances in our company, we use windows perfmon tool we are collecting performance data using basic SQL counters to monitor the SQL servers (instances). One of the counter is Buffer cache hit ratio. We have set the thershold for this counter is <90. this counter trigger's whenever the value gets below 90. The problem is we are getting the alert through perfmon for one of our server (instance) for this counter value is 0. So we asked DBA to see the activity/performance of this instance. there are 5 to 6 users using this server. only one instance is there on that server. she is telling there is no performance problem on this server. then why this counter generate value as 0 continusly. Please give me the heads up on this........ ananth
It can't be. What OS (32,64bits), and SQL 2005 (sp?, 32, 64bits) do you have? Also, is one server with one instance?
Hi thanks for your reply its Win 2K3 with SP1 and SQL 2005 with SP2 (32 Bit) yes it is one server with one instance it have 4 Gb of Ram and the page file is proper so am wondering why the counter triggers contiously zero please help me reg ananth
See this blog post: SQL Server 2005 PERFMON counter spikes in Transactions/sec and Buffer Cache Hit ratio, what does it say? SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache? SQL Server performance monitoring requirement and specifications, heard about baseline and benchmarking? this is more important for you to keep a baseline and assess your database performance. How can data-type choice affect performance? another excellent explanation by kimberly here.
There could be 2 things. First, the service "Remote Registry" on the server must be running. Check it. If it's running, restart it. In my case restarting helped. Secondly, as long as your server is 2005 check if service "SQL Server Browser" on the box is running.
I;m unable to understand how remote registry and browser service would help in buffer cache hit ratio?