Creating a custom Data Collector Set with SQL x86 counters on an x64 OS

In one of our recent CSS cases, we noticed a strange difference between the Windows Small Business Server (SBS) 2008 x64 and Windows Server 2008 x64. One could create a Data Collector Set (DCS) with SQL Server x86 counters, collect and view the samples just fine on the SBS 2008 x64, out of the box. However, on Windows Server 2008 x64 this proved to be not possible.

This workaround will be provided “as-is” , with no support whatsoever or liability from Microsoft. For use in a productive environment, we recommend you to install the x64 version of SQL Server especially when running on a x64 OS.

First let’s take a look at the two scenarios:

Windows SBS 2008 x64 with SQL Server 2005 SP2 x86 installed by default

- You can see that by default, there are two SQL Server processes running, one 64bit the other 32bit.

- The x64 Process is running from this directory:

- The x86 process is running from here:

- You can see here the two Services associated with the two SQL Processes:

The x86 service is MSSQL$SBSMONITORING and the x64 service is MSSQL$MICROSOFT##SSEE

- The SQL Performance counters are stored in a DLL called sqlctr90.dll.
Here you see that on a SBS 2008 server, there are two DLLs installed by default: one 32bit in c:\windows\syswow64 and 64bit in c:\windows\system32

Windows Server 2008 SP2 x64 with SQL Server 2005 SP3 x86

- You see that there is only one 32bit SQL Server process running (no 64bit process running like on SBS 2008)

- We see here the file location of the running process:

- We see only one SQL Service running, named after the test instance I created on the virtual machine (TEST32)

- If we search now for the sqlctr90.dll, we only see one file: the 32bit version in c:\windows\syswow64

The workaround is to simply 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 Server (SP3 since SP2 is actually not supported any more!)

Once you copy the DLL, you can proceed with creating a custom DCS with SQL Server 32bit counters:

1. Choose a name for the DCS and select “create manually (advanced)”

2. Choose “Performance counter” and click “Next”

3. Choose any SQL related counters (here you see General Statistics for example):

4. Here you select the location, wrap up the DCS creation and start the collection.
After a chosen amount of time, you can stop the collection.

5. To view the counters, in Perfmon you select and open the created BLG file of the custom DCS

6. You now select which objects you would like to see displayed:

7. As you can see, sample collection works fine.

As always, i hope you find this post useful and feel free to rate it or drop some comments, we love to hear back from you!

Bogdan Palos
- Technical Lead / Enterprise Platforms Support (Core)

Comments

• Anonymous
  August 04, 2010
  Hi , we have a similar problem. 64 bit server, 32 bit sql2005. Using 32bit perfmon we can see the interactive counters but we cannot save them to a file. Would this solution work for us? Regards Peter