Hi friends i have fired the following query for find out buffer hit ratio one of the production server, but it is display nothing select [object_name], [counter_name], [cntr_value] from master.dbo.sysperfinfo where counter_name = 'Buffer cache hit ratio' or counter_name = 'Buffer cache hit ratio base' (0) rows return please can you tell me any one, waht is worng this server?
Welcome to the forums. What is the service pack level on SQL SErver? See these http://support.microsoft.com/default.aspx?scid=KB;en-us;q196657 & http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=687 links that talks about.
Thanks satya for your reply.. os: windows server 2003 enterprise edtion database : SQL server 2000 enterprise edtion SP: SP3 for sql server. product version 8.00.760(sp3) Every three days we are facing problem in this server, apache server also installed on this server and database connected to WEB application So user after 3 days called me application running very slow, that we are restarting SQL SERVER services after that it will be clear, than user will get same problem after 3 days. I had checked locking every day, so many SPID has been locked and we are getting most of select, update and delete command through trace porfiler tools. please tell me , how to encountered this problem? thanks
How about the resources such as memory & CPU? FIrst things first, you have to patch the server to the latest service pack on SQL 2000 which is SP4, as there are many fixes to such problems in terms of query optimizaiton & memory. So I would recommend to test and apply the SP4, also see this blog http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx that talks about how to monitor and start the performance tuning.
CPU usage 38% PF usage 2.09 GB sqlservr.exe momory using - 1.680.50 Total RAM - 3.25 GB, 4 CPU 1. SQL Agent Alert - using more cpu, Can i delete all Demo: agent alert on production server?, if i deletean agent alert what impact on database? suggestion please alreday i have configured for database maintenance as follows, . 1.Rebuild index every week sunday on 8:00 PM. 2. Update_statistices daily on 2:00 AM (precentage of database sampling 10 %) 3.Check Integrity every week sunday, Tuesday, friday on 12:45 AM. 4. Full Backup daily on 9:00 PM . 5. TLog backup daily on every 1 hours. ------------------ we able to getting all counter another server, on this server SP is RTM. could you tell me, why sp 3 missing all counter in sql server.? Thanks.
I believe you are missing the main point of testing and apply the latest service pack (SP4) for SQL 2000, the problem you are getting missing for counters is a part of the issue too of missing or unreported buffer cache hit ratio.
