SQL performance counter shows 0 as values

Question

SQL performance counter shows 0 as values

SQLshifu

SSC Eights!

Points: 953
More actions
June 25, 2008 at 8:12 am

#126103

Environment: sql server 2005 enterprise edition on windows server 2003.
Perfmon counters fro sql was working before and recently stopped working. The only change I am aware of is our sys admin installed more memory on the server.
I can add the counters but the values are ZERO. All other counters for the server are working properly.
Is there a registry setting or database property value that controls this?
thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

What counters are zero? All of them?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLshifu SSC Eights! Points: 953 More actions · Answer 2

Yeah, basically everything supplied by sql server. Other OS counters are fine such as Memory:Available MBytes, Processor:%Processor time.

For SQL, 0 is what I get fro the values:

Buffer cache hit ratio 0.000

Page life expctancy 0

MannySingh SSChampion Points: 12465 More actions · Answer 3

Do you see any performance issue on the Box with counters at 0.

did the admin did a HOT INSTALL for the memory(ONLINE), or did he Shutdown the box and install the memory.

Maninder
www.dbanation.com

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 4

Hi,

Try to use the following queries

select * from sysperfinfo --SQL Server 2000-2005

or

select * from sys.dm_os_performance_counters -- SQL Server 2005

Check the following link http://msdn.microsoft.com/en-us/library/ms187743.aspx

If the installation instance of your SQL Server fails to display the performance counters of the Windows Operating System, use the following Transact-SQL query to confirm that performance counters have been disabled.
Copy Code
SELECT COUNT (*) FROM sys.dm_os_performance_countersIf the return value is 0 rows, the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions" This denotes that performance counters were not enabled. The errors immediately prior to the 3409 listing should indicate the root cause for the failure of performance counter enabling. For more information about setup log files, see How to: View SQL Server 2005 Setup Log Files

Regards

SQLshifu SSC Eights! Points: 953 More actions · Answer 5

Mani Singh (6/25/2008)
Do you see any performance issue on the Box with counters at 0.
did the admin did a HOT INSTALL for the memory(ONLINE), or did he Shutdown the box and install the memory.

We restarted the computer for installing the memory.

SQLshifu SSC Eights! Points: 953 More actions · Answer 6

Ahmed Bouzamondo (6/25/2008)
Hi,
Try to use the following queries
select * from sysperfinfo --SQL Server 2000-2005
or
select * from sys.dm_os_performance_counters -- SQL Server 2005
Check the following link http://msdn.microsoft.com/en-us/library/ms187743.aspx
If the installation instance of your SQL Server fails to display the performance counters of the Windows Operating System, use the following Transact-SQL query to confirm that performance counters have been disabled.
Copy Code
SELECT COUNT (*) FROM sys.dm_os_performance_countersIf the return value is 0 rows, the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions" This denotes that performance counters were not enabled. The errors immediately prior to the 3409 listing should indicate the root cause for the failure of performance counter enabling. For more information about setup log files, see How to: View SQL Server 2005 Setup Log Files
Regards

Thanks for your reply.

My situation is sort of the opposite. I can't get values when using perfmon. SQL apparently has the values but it is just not sending it to WMI.

As a matter of fact, I have been using sys.dm_os_performance_counters to look at the counter values. I get the information I want, but it would be much nice if I can get it from perfmon.

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 7

Hi,

Check the following link

How to manually rebuild Performance Counter Library values

http://support.microsoft.com/kb/300956/en-us

Regards

MannySingh SSChampion Points: 12465 More actions · Answer 8

see if this helps

http://support.microsoft.com/?kbid=300956

Maninder
www.dbanation.com

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Does anyone know if any of the methods listed on this thread actually worked? We're suffering from precisely the same problem as what the OP originally posted. We can see and select the various SQLServer: counters but all of the values being returned are zero. They used to work just fine and just flat out stopped after a recent reboot.

The values for other categories of performance counters, such as Processor and System (File Read and File Write) are working as they did before.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)