If you take the path less travelled of running 32-bit SQL Server on a Windows x64 system, as described in the last installment of SQL Server Observer, then you have a couple of surprises in store when you try to use the Windows System Monitor, also known as Perfmon.

Just to review, the Windows x64 operating system comes with a built-in emulator called the WOW64 which allows it to run 32-bit applications almost natively. Windows x64 can draw on the fact that the x64 CPU chipsets from Intel and AMD can natively emulate the Intel x86 instruction set, and thereby execute 32-bit applications seamlessly. The emulation is also quite efficient: when you're upgrading to a new (and usually faster) x64 server, testing may show your 32-bit applications running faster than before, even though they are not taking advantage of the full 64-bit processor.

Why Run a 32-bit Application on x64 Windows?

Fair question! As I pointed out in the previous installment of SQL Server Observer (“The x64 Path Less Travelled”), there are a couple of reasons:

Not all applications can run under x64 Windows: SQL Server 2000, for example. So if you are migrating a SQL Server 2000 system to x64 Windows, you'll have to run it under the WOW64.

Your testing may show that the 32-bit version of SQL Server 2005 (and soon SQL Server 2008) runs faster than the 64-bit version on an x64 server.

Whatever the reason, if you choose to run the 32-bit version of SQL Server, you'll find a significant problem: missing performance counters!

Where's Perfmon?

First let's have a digression about terminology. In Windows Server 2003, Windows XP, and later, there is no more Perfmon. The name now is System Monitor. For most applications, this would be a simple name change, but in Windows it's become much more confusing.

The Performance Utility Previously Known as Perfmon has an official name, sometimes, of System Monitor. But most people (with much experience in this business) also then add Perfmon in parentheses afterwards to be clear.

The problem is that the utility's name is not consistently used. If you start it up interactively, from Control Panel's Administrative tools, it's just called “Performance”. Within it, the interactive graphical part is called System Monitor, and then there's another node called Performance Logs and Alerts.

However, if you run it from the command line, or the Start/Run command, you can just enter “Perfmon” or “Perfmon.exe”. Sure enough, the executable perfmon.exe is located on disk!

Yet if you are persistent and go to the Microsoft Windows site, pages will refer to System Monitor. On the other hand, when you listen to Microsoft speakers at conferences, they almost universally call the utility “Perfmon”, either because that's how they know it or they assume the audience expects that term.

So, what's the real name? For this article, I'll take the easy way: I'll call it System Monitor (Perfmon) so that there's no possible confusion, at least for now!

Problem 1: Missing SQL Server Performance Counters!

After you've installed any 32-bit version of SQL Server on a fresh Windows Server x64 system, start up System Monitor (Perfmon) and try to add some counters for your 32-bit SQL Server instance. You won't find them: System Monitor's Add Counters dialog box will contain counters for any 64-bit SQL Server instance; it's just the 32-bit instance counters that will be missing.

You'll find the 32-bit counters missing whether you run System Monitor (Perfmon) interactively (from the System Monitor node), or if you try to build a counter log file so that you can collect performance counters in the background.

The explanation problem here is that on a Windows x64 system, the System Monitor (Perfmon) utility is also, by default, a 64-bit application. Microsoft did not include the ability to gather 32-bit counter information from the 64-bit System Monitor (Perfmon) utility.

So is there a 32-bit version? Yes, and it comes in two parts, one slightly hidden and the other one much more deeply hidden!

Solution Part 1: Using 32-bit Counters Interactively

On a Windows x64 system, if you want to collect performance counters from a 32-bit application, you must run the 32-bit version of System Monitor (Perfmon). By default, if you just run Perfmon from the Start/Run dialog box, you'll get the 64-bit version.

To find the 32-bit version of System Monitor on an x64 Windows Server or XP system, just find Perfmon.exe in the following folder:

C:\WINDOWS\SysWOW64\

(This assumes you let Windows install itself into a Windows folder. If not, just go find the SysWOW64 folder.)

You can create a shortcut to this executable on the desktop for all users, and rename it as “32-bit Perfmon”. Now when you run the 32-bit version of System Monitor, notice that you still have access to all the counters for 64-bit applications!

Problem 2: Counter Logs Won't Work!

Unfortunately, even running the 32-bit version of System Monitor (Perfmon) will not allow you to collect 32-bit performance counter data into performance logs. You can add the counters, but when you inspect the logs later, they won't have any data for the 32-bit applications!

The problem here is that System Monitor uses a different program to collect performance counter data into a log. It uses a service called smlogsvc.exe, and-yes, you guessed it-there's a 64-bit version and a 32-bit version!

Solution Part 2: Using 32-bit Counter Logs

In order to collect data into performance logs from 32-bit applications, you must replace the 64-bit service with the 32-bit service. There are some complex ways to do this using registry hacking, but the most effective and safe way is to use the Windows sc.exe utility, which sets a service. I found this solution on Todd Carter's blog: http://blogs.msdn.com/toddca/archive/2007/05/08/logging-32bit-asp-net-performance-counters-on-a-windows-2003-64bit-os.aspx.

I adapted the following example from Todd's blog:

sc config sysmonlog binPath=%systemroot%\syswow64\smlogsvc.exe

You run this from the Command window, all in one line. Basically I'm telling the sc.exe command to configure the sysmonlog service to use smlogsvc.exe from the 32-bit \syswow64 path, and not the original path.

The above example assumes you are running it on the server and not from a remote server; that's why the command does not reference a server name. Also, note that there is a single blank right after the = sign. The command will fail without it!

What's nice now is that like its interactive 32-bit Perfmon.exe cousin, the 32-bit smlogsvc.exe utility will also capture both 32-bit and 64-bit counters.

So Far, So Good

I've used these solutions successfully for several months now on some production SQL Servers, running the 32-bit instance of SQL Server 2000 and 2005, on Windows Server 2003 x64, and so far I have not encountered problems. That's not to say none will occur, but so far, so good!