On a client server they are running 32-bit SQL Server 2005 (I know, not supported - that's a different rant/post) on a 64-bit Windows 2008 installation. When I logged onto the server for the first time to try to pull some basic data, I was greeted with one of the most-hated items of dealing with 32-bit SQL Server on a 64-bit operating system:
NO PERFMON COUNTERS!!!!!!
Public Service Announcement - Always *always* install your applications with the same architecture as your O/S - meaning always install only 64-bit applications on your 64-bit Windows Server!
Having seen this before, I went to the first item in my 32-bit toolbox - the WOW64 version of Perfmon located at C:\Windows\WOW64\perfmon.msc. Many people don't seem to know this exists, but it allows you to access the 32-bit version of Perfmon required to access the counters for 32-bit SQL Server (or any other 32-bit application with performance counters) on a 64-bit Windows machine.
No Good.
I placed a shortcut for the 32-bit Perfmon on the desktop for future use (regardless of how I eventually resolved the issue, I knew I would definitely need to use the 32-bit Perfmon) and went to Management Studio to see if the counters were in sys.dm_os_performance_counters, which they were. This showed my that the counters did exist - if they were completely missing or broken, they wouldn't be visible in the DMV either.
In the past when I had run into this issue, it had been on SQL Express and the counters didn't exist at all - not even in the DMV. In those situations the answer was to load the counters via lodctr after copying them from another machine of the same version. Since the counters existed I didn't believe that would be the answer (although eventually it was close - keep reading) so I turned to my Google-Fu to see where I should go next...
...and didn't find much. A few forum postings and blog articles with different symptoms and different resolutions, but nothing AHA. In my frustration I went through the steps to repair or reload the existing counters in case some of them were corrupted somehow.
First, I tried to just rebuild the existing counters via lodctr /R but that did not resolve the issue.
Next I went through the full steps to unload and reload the counters as described here by Alex Pinsker (blog/@alex_pinsker). The catch is that near the end of the process Alex calls for a server reboot, and this was a production server. I tried several things that I could do without impacting production, including:
- Re-syncing the counters with WMI (WINMGMT.EXE /RESYNCPERF)
- Restarting the "Performance Logs and Alerts" service
- Restarting "Windows Management Instrumentation" (WMI) service
- Restarting the "Remote Registry" service (which shouldn't have mattered since I was trying to access the counters locally, but it isn't impactful to try)
A new day, and BINGO - I could see the SQL Server counters in the 32-bit Perfmon via the interactive ("real-time") Performance Monitor after the reboot.
With counters firmly in hand, I went to the next step - trying to set up a data collector. I went through the basic steps to set up the collector and started it, and then a short time later I stopped and re-started the collector to view the contents of the perf file.
ARGH- no SQL Server counters. The Windows hives of counters (Processor, Logical Disk, etc.) were there, but no SQL Server counters.
I had initially opened the perf file by double-clicking it, so, just to make sure, I opened 32-bit Perfmon manually via my shortcut and changed its data source to my perf file just in case that was the issue, but no good.
More than a little Google-Fu later and I finally found something that described my situation pretty well, from a Microsoft CSS Engineer in Romania: http://blogs.technet.com/b/roplatforms/archive/2010/05/03/creating-a-custom-data-collector-set-with-sql-x86-counters-on-an-x64-os.aspx
The engineer, Bogdan Palos, was writing about a situation on Small Business Server (SBS) compared to Windows 2008 X64, but the basic scenario was similar - trying to create a data collector of 32-bit counters on a Windows 2008 X64 installation.
The suggested fix seemed more than a little bizarre:
...copy the 64bit version of “sqlctr90.dll” from another machine running a x64 SQL instance, to the “c:\windows\system32” directory of the Server 2008 x64 OS machine running SQL Server 2005 x86. We recommend using the DLL from similar SP levels of SQL ServerApparently the files from the x86 SQL Server installation are not enough - you actually need a file from an X64 install?
But guess what - it worked! After simply copying the sqlctr90.dll file from another SQL Server 2005 X64 of the same version number into the c:\Windows\system32 directory on this server and re-starting my Perfmon collector, it began collecting the SQL Server hives of counters.
Just one more little thing to file under "Seriously, Microsoft?"
What did I learn?
- 32-bit is evil (already knew this, but it was definitely reinforced).
- Microsoft will never cease to amaze me in how some things need to be hacked to work.
- There is always something else to learn!
Hope this helps!
[Disclaimer: I do work for Microsoft, but as a DBA over in Developer Division.]
ReplyDeleteI want to applaud the heroics you went through to get the Perfmon counters working. I know sometimes our customers task us with pretty untenable situations that we need to cope with.
My only other comment that I have is you state up front that you know it's an un-supported scenario, but then take the SQL Server team to task for having to hack it to make it work. By definition, unsupported means it won't be guaranteed to work in that scenario.
I've had to do similar things multiple times as well, but blaming Microsoft is like blaming the dishwasher manufacturer when your salmon doesn't come out cooked just right.
I do agree unreservedly with your 1st and 2nd points, though. :) Thanks for a great article.
Dale - my point about being unsupported was that the client was running a currently unsupported version of SQL (2005) - my issue with MS in this scenario is that to make this work I had to hack by copying in a different version's DLL (64-bit vs 32-bit) - and this would have been true even if I had run into this a couple years ago when 2005 was still supported, so being off support now doesn't impact that issue.
DeleteThanks for reading and for your comments!
Actually it is a known issue. When we were doing performance testing for MSSQL 2008 which runs on win2008 servers, we faced similar issue as our driver box OS was win2003.
ReplyDeleteBelow is the feedback from MS for the issue.
- Do not use Performance Monitoring tools installed on a Win 2003 server to monitor performance counters installed on a Win 2008 R2 server.
- If you need to use such a monitoring environment, make sure the first connection is made from another Windows 2008 R2 server and then after that all subsequent connections from Win 2003 client machines should go through fine.
This is the workaround we used to get over it.
- mstsc to another windows 2008 server
- Start collecting perfmon for SQL server2008 using win2008 server. Actually we can just add the counters and then cancel it. It is just the loading matters.
- If you are not able to collect perfmon on win 2003 even after above step, start "Remote Registry" services on widnows 2008 where MSSQL server is running and try again.
- Now you should be able to collect permon from windows 2003 servers.
Hope this would also helps.