Strange 32/64 bit SQL performance counters issue

Question

Hi All,

 

I'm working on an issue where we're unable to see SQL server counters unless we launch perfmon in 32 bit mode.

 

Environment:

 

Windows Server 2003 x64 Enterprise Edition SP2

 

Windows SQL Server 2005 64 bit (confirmed by running select serverproperty ('edition')  and select @@VERSION) both which output the following:

 

Enterprise Edition (64-bit)
(1 row(s) affected)
--------------------------------

Microsoft SQL Server 2005 - 9.00.5259.00 (X64) 
       Jan 31 2011 15:31:49 
       Copyright (c) 1988-2005 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
 
(1 row(s) affected)

I've also checked the version of sqlctr90.dll under windows\system32 and have confirmed it to be 64 bit (it says NT AMD64). 

I've noticed a number of people having this issue are running a 32 bit SQL instance and can see the counters when they launch perfmon in 32 bit mode but the evidence above tells me that we're definitely running a 64bit instance of SQL but I'm baffled as to why we can only see these counters when we run perfmon in 32 bit mode?

 

We're using HPPA to monitor these servers and we're unable to gather data for these at the moment.

 

It should also be noted that we have a number of SQL servers that appear to be configured in the same way (although I didn't build them) that don't exhibit this problem, but we currently have a small number that do.

 

Hoping somebody can help.

 

Many thanks, Ben

 

Answer 1

Hi Ben,

Do you install SQL Server 2005 Integration Services from SQL Server 2005 Enterprise X64 Edition? Are you trying to add the SQL Server: SSIS Pipeline performance object and it doesn’t appear in the Performance object list? If so, one possible cause for this issue is that SQL Server Setup incorrectly sets the Library entry for the 64-bit SQL Server: SSIS Pipeline performance object to the location of the 32-bit performance object.
There are two workarounds for this issue. For more details, please refer to this article.

Also there is a similar issue with yours, it provides a workaround. For more details, please refer to this thread.

If these don’t work, please try to run the following query:

Select * from sys.dm_os_performance_counters

Check if it can return any results. For more details, please refer to this blog: What should we do when the sql server performance counters are missing. Please pay attention to the part Cause 3, at last it mentions: On Windows 2003 X64, you would have to open 32 bit Performance Monitor to look at the Performance Objects and counters for 32 bit SQL.

Best Regards,
Ray Chen

Answer 2

Hi All,

I'm working on an issue where we're unable to see SQL server counters unless we launch perfmon in 32 bit mode.

Environment:

Windows Server 2003 x64 Enterprise Edition SP2

Windows SQL Server 2005 64 bit (confirmed by running select serverproperty ('edition')  and select @@VERSION) both which output the following:

Enterprise Edition (64-bit)
(1 row(s) affected)
--------------------------------

Microsoft SQL Server 2005 - 9.00.5259.00 (X64) 
       Jan 31 2011 15:31:49 
       Copyright (c) 1988-2005 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
 
(1 row(s) affected)

I've also checked the version of sqlctr90.dll under windows\system32 and have confirmed it to be 64 bit (it says NT AMD64). 

I've noticed a number of people having this issue are running a 32 bit SQL instance and can see the counters when they launch perfmon in 32 bit mode but the evidence above tells me that we're definitely running a 64bit instance of SQL but I'm baffled as to why we can only see these counters when we run perfmon in 32 bit mode?

We're using HPPA to monitor these servers and we're unable to gather data for these at the moment.

It should also be noted that we have a number of SQL servers that appear to be configured in the same way (although I didn't build them) that don't exhibit this problem, but we currently have a small number that do.

Hoping somebody can help.

Many thanks, Ben

Hi Ben,

We had this issue a while ago and in order to resolve the issue we rebuilded performance counters. The process is documented in my blog here. Hope this will resolve the issue.

Thanks,

Basit

Answer 3

Hi, 

Thanks for coming back to me but unfortunately this didn't work. The counters have now gone from being shown as names in 32 bit mode to numbers (in 32 bit mode). They still remain unavailable in 64 bit mode. I was unable to force a WMI sync as winpriv.exe isn't running on our system. 

I have opened a call up with Microsoft in the meantime, but open to suggestions. 

Thanks, Ben