Issues Logging SQL Server performance counters to

I would like to save performance information using perfmon.exe using the CounterLogs functionality. I am currently running a clustered SQL2000 SP4 machine running on Windows 2003 Advanced x64. When starting perfmon.exe I noticed that the SQL counters are not available, after some messing around I found another copy of perfmon.exe in C:WINDOWSSysWOW64perfmon.exe.

If I start this executable I can now see the SQL Server performance counters. And can interactively watch performance. The problem arises when I attempt to create a CounterLog. I can create the counter log and add the SQLServer counters but it does not log the SQLCounters.

Looking in the event log I get the following messages

The service was unable to add the counter '\BEDB3SQLServer:General StatisticsUser Connections' to the test log or alert. This log or alert will continue, but data for that counter will not be collected. The error returned is: The specified object is not found on the system.

Anyone know how to get this working?

Bert

The counters are there. I can view them when monitoring interactively. The problem occurs when I attempt to log them.

Yes when I run
select * from sysperfinfo

I see the counters, and the counters are available to select. This is more of a x64 bit problem. IS anyone else running x64 and can confirm the same behavior I am seeing?

Bert

what is this x64 problem? Anyone can help me to understand this?

-Johnson

I cant get perfmon.exe to LOG SQL counters to COunterLog. Are yourunning x64?

Bert

No, This is x64 not IA64. So I am running SQL2000 SP4. SO SQL is actually still 32bit running on 64 bit OS.

Bert

Left a post on x64 newsgroup...not to much activity.....

I am a little suprised that nobody here is tracking performance of SQL Server on x64 and confirm the same issues I am having.

Bert

I'm having exactly the same problem and still coudn't find a solution.
Anyone can help?

thanks in advanced

You can use the following workaround for this issue.

1) Stop the Performance Logs & Alerts service from services.msc
2) Open up the registry editor (regedit.exe), browse down to HKLMSystemCurrentControlSetServicesSysmonlog.
3) Change the ImagePath from the default value to %SystemRoot%syswow64smlogsvc.exe.( This would launch the 32-bit service on starting the Counter logs)
4) Open the 32-bit Perfmon from Start - Run - %SystemRoot%syswow64perfmon.exe, & add the SQL counter objects to a Counter log.
5) Set the various parameters of the Counter log, & start it to collect the data.

Excellent work man, you gave me a great solution.!

Thanks

+1, nicely done. I have set it up here and it works wonderfully. Another big, THANK YOU! <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />-Paul<br /><br />"as you think, so shall you be"

Just wanted to add:

You can log to database as well by using the WOW64 ODBC drivers.

Run C:WINDOWSSysWOW64odbcad32.exe to setup DSNs that can be used by the 32bit PerfMon to log to database as well.

-Paul

"as you think, so shall you be"