SQL Server Perfmon counters missing for WoW installations   Leave a comment


Recently I encountered this issue where perfmon counters for SQL Server 2000 instances did not show up:


Checking though application logs in Event viewer I found this error:

Event Type:   Error
Event Source: Perflib
Event Category:      None
Event ID:     1022
:
Description:
Windows cannot open the 64-bit extensible counter DLL MSSQLSERVER in a 32-bit environment. Contact the file vendor to obtain a 32-bit version. Alternatively if you are running a 64-bit native environment, you can open the 64-bit extensible counter DLL by using the 64-bit version of Performance Monitor. To use this tool, open the Windows folder, open the System32 folder, and then start Perfmon.exe.

Troubleshooting further, I found this SQL Server 2000 (32-bit) was installed in WOW mode (64-bit OS). Although SQL server 2000 w/ SP4 can be installed on x64 OS (WOW mode) there is no 64-bit version of the counter DLL yet. Hence 32-bit version of perfmon would only be able to list the 32-bit SQL counters. The problem occurred because I just launched perfmon.exe which launches %windir%\System32\perfmon.exe  i.e. 64-bit perfmon. It determined an incompatibility hence disabled the SQL server performance counters as evident from the following error:

Event Type:   Error
Event Source: Perflib
Event Category:      None
Event ID:     1017
:
Description: Performance counter data collection from the "MSSQLSERVER" service has been disabled due to one or more errors generated by the performance counter library for that service. The error(s) that forced this action have been written to the application event log. The error(s) should be corrected before the performance counters for this service are enabled again.

To confirm whether performance counters are disabled, check if there exists a registry key with value “1”

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]

“Disable Performance Counters”=dword:00000001

If value of Disable Performance Counters is NOT “1” then you have perfmon counters for SQL already enabled. Try launching perfmon /32 and see if perfmon counters show up.

If value of Disable Performance Counters is “1” means it is disabled as in my case. To resolve this, follow these steps:

1.            Change the value of “Disable Performance Counters” to “0” or just delete this key. This would allow performance counters for SQL to be re-enabled.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance]

“Disable Performance Counters”=dword:00000000

 2.            Now, re-load the performance counters for SQL server instance. You need to navigate to the correct instance’s Binn folder from a command prompt to do this.

C:\Program Files (x86)\Microsoft SQL Server\MSSQL\Binn>lodctr sqlctr.ini

3.            Restart SQL Server services for above change to take effect.

4.            Now the most important step. To see permfon counters for SQL Server, launch 32-bit perfmon in either of the following ways:

i.              Go to “run” prompt and type

perfmon.exe /32

Or,

ii.             Launch perfmon from Syswow64 dir

%windir%\SysWOW64\perfmon.exe

 And there we go… SQL counters are back!!

The other important thing to note is that even though SQL counters would show up now, the counter data may not be recorded into perflogs as it needs to use the performance logs and alerts services, which does not recognize the 32-bit version SQL counters.

To record SQL counter values into Perflogs, you need to change the following registry key values:

1.            HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SysmonLog\ImagePath

Change ImagePath value from %SystemRoot%\system32\smlogsvc.exe to %SystemRoot%\syswow64\smlogsvc.exe

 2.                HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\SysmonLog\EventMessageFile

Change EventMessageFile value from %SystemRoot%\system32\smlogsvc.exe to %SystemRoot%\syswow64\smlogsvc.exe

3.            Start perfmon with /32 switch

Hope this helps!

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: