Troubleshooting: SQL Server (2005, 2008) Performance Counter Collection Problems

I was asked by our support team to help outline performance counter troubleshooting when the counters don't appear, are missing or don’t seem to be working as expected. I agreed to look into it and as you can see from the information below it turned into a much larger effort than I originally planned.  

(I originally had this and the How It Works post (https://blogs.msdn.com/psssql/archive/2009/03/03/how-it-works-almost-everything-you-wanted-to-know-about-the-sql-server-2005-2008-performance-counter-collection-components.aspx) in an e-mail. I have converted it to the blog so I hope formatting and content still applies.)

Looking at some of the issues Microsoft SQL Server Support was handling I found a trend that unlodctr/lodctr was used the vast majority of the time and it resolved the problem. Anytime I see such a trend I want to know the root cause so a fix can be made and no one has to take additional steps. What I found is that the solution does work for the vast majority of scenarios I tested and there is good reason for that. 

After putting this together here is what I would tell you to do if you want to just get them working. If you want to know the root case of the problem you can follow the detailed instructions instead. 

Using an elevated administrator command prompt perform the following steps.

1. Change the path to the BINN directory of the SQL Server instance you desire to correct. (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn)

2. Execute unlodctr <<REGISTERED SERVER NAME>> For example: unlodctr MSSQL$SQL2008 or SQLAgent$SQL2008, …

3. Execute lodctr /T:<<perf-sql* matching the counters you desire to load>> For example: perf-MSSQL$SQL2008sqlctr.ini or perf-SQLAgent$SQL2008sqlagtctr.ini for SQLAgent, … The /T is important to load the SQL Server performance counter provider as a trusted provider.

4. Cycle the remote registry service net stop "Remote Registry" and net start "Remote Registry"

5. Force a WMI synchronization using winmgmt /resyncperfctr "<<PID>>" where PID is the process id of the WinPriv.exe (you can get this from Task Manager)

==============================================
Detailed Troubleshooting
==============================================

I have outlined the specific registry keys and other files required for the SQL Server performance counter collection. Many of the corrections can be accomplished with the Lodctr utility. For example, instead of adding the Disable Performance Counters value you can toggle the value using lodctr /D or /E instead of performing direct registry manipulations.

Warning: Many of the techniques involved in performance counter troubleshooting require changes to the registry. As always, incorrect changes to the registry can render the system unusable so make all changes with appropriate caution.

Preparing to Troubleshoot

1. Start by getting your bearings and opening an elevated command prompt as outlined in the summary section. 

2. Issue lodctr /Q:MSSQL$2008 to provide you with the base details about the performance counter service you are troubleshooting. This is the basic data located in the Services registry key but helpful in determining many of the key aspects of the performace counter library registration. 

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn>lodctr /Q:MSSQL$SQL2008

Performance Counter ID Queries [PERFLIB]:
Base Index: 0x00000737 (1847)
Last Counter Text ID: 0x0000471C (18204)
Last Help Text ID: 0x0000471D (18205)

[MSSQL$SQL2008] Performance Counters (Enabled)
    DLL Name: perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll
    Open Procedure: OpenSQLPerformanceData
    Collect Procedure: CollectSQLPerformanceData
    Close Procedure: CloseSQLPerformanceData
    First Counter ID: 0x00004320 (17184)
    Last Counter ID: 0x00004658 (18008)
    First Help ID: 0x00004321 (17185)
    Last Help ID: 0x00004659 (18009)  

3. Backup the performance registry settings so you can restore them if needed using the command lodctr /S:MyRegistrySettings.txt. 

This is not only helpful for a restore need but helpful to compare changes I have made. I often issue lodctr /S:MyPDATEDRegistrySettings.txt and compare it to the original capture to see specific changes and help determine root cause.

Step 01

A. Check the event log for WmiMgmt, PerfLib, PerfCtrs and LoadPerf warning and errors.

It is possible that another performance library is causing an error and stopping the enumeration of other counters. For example a counter library for service AAA.dll could cause a problem preventing the SQL Server counters from being loaded or collected. If you have other counter errors attempt to Disable Performance Counters the problem counter library(s) as see of the problem persists. 

Enable the following registry keys and re-test to see if additional information is placed in the Application Event Log. 

· HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\ExtCounterTestLevel = 0x4 (REG_DWORD)

· HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Configuration Flags = 0x10 (REG_DWORD)

· HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\EventLogLevel = 0x3 (REG_DWORD)

Note:   Restart the remote registry service to remote collection and enable the keys on the client and target systems to review both sides of the data flow. (Reference: https://www.microsoft.com/technet/prodtechnol/windows2000serv/reskit/regentry/94221.mspx

The following is an error you might encounter. 

The configuration information of the performance library "C:\Windows\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll" for the "MSSQL$SQL2008" service does not match the trusted performance library information stored in the registry. The functions in this library will not be treated as trusted.

This indicates the <<Service name>>\Performance\Library Validation Code value is invalid. This code is generated by lodctr as a checksum of the performance counter DLL. If the DLL has been updated outside lodctr the value won’t match and the error is logged. 

B.Validate the First and Last Counter Values 

Using the First/Last Counter and First/Last Help values from the SQL Server Service\Performance Key do the values align properly with the TITLES database. If not unlodctr / lodctr will be required to correct the TITLES database and Performance key values. 

Note:   Validate you have a matching SQL Server INI and .H file for performance counters. Mismatched files will populate the registry with invalid values.  

For testing purposes I changed the First Counter value to 32000 under my MSSQL$SQL2008\Performance key. Performance monitor no longer shows my MSSQL$SQL2008 counters. 

The Open Procedure for service "MSSQL$SQL2008" in DLL "C:\Windows\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code. 

<param1>MSSQL$SQL2008</param1>

<param2>C:\Windows\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll</param2>

<binaryDataSize>8</binaryDataSize>

<binaryData>F303000000000000</binaryData>   

                Byte swapping the data value you get 0x000003F3 (1011) - The configuration registry key could not be opened, indicating the index could not be located in the TITLES database.  

Step 02

Check the SQL Server error log for errors. SQL Server performs several checks to make sure the shared objects and memory are properly configured. If a configuration issue is present the counters are disabled. 

Active Processes

Many of the errors that can be logged are caused by invalid shared memory and named objects states. Use Process Explorer (https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) to find all owners of shared objects and stop these processes/services. 

Using the Find menu start by finding those processes that own a SQL_PERF mutex. 

clip_image002

Next search for MEMOBJ shared memory regions. 

clip_image004 

Next check for 'sqlctr' DLL owners. (You can use the command line utility tasklist.exe /M to determine which processes have loaded the DLL as well) 

Note: TaskList.exe will not work if the "Windows Management Instrumentation" service is disabled. You will instead receive a message that the service cannot be started. 

clip_image006 

You can double click on the process and it will take you to the process details in the main window. In the figure above WmiPrvSE.exe is listed. Double clicking on that shows that WmiPrvSE.exe is part of the services.exe process tree and as such is a service on the machine accessing the SQL Server performance counters. 

clip_image008 

sys.dm_os_performance_counters

SQL Server exposes the shared memory, performance data with the DMV sys.dm_os_performance_counters. If rows are returned from the DMV but not to a performance collection utility then SQL Server is producing the counters but an error is occurring in the collection frame work. 

Column

Description

cntr_value

The raw counter value. The Perflib is designed to handle delta change calculated based on the collection interval. An application simply increments or decrements the counters accordingly.

cntr_type

The counter type such as raw, average, etc..  

For absolute values the raw value is the current value. For time based values you compare two snapshots of the data and use the duration between the snapshots to determine the change deltas. Counter Types Reference: https://msdn.microsoft.com/en-us/library/ms803989.aspx

-x

If no rows are returned check to see of the -x startup parameter has been used to start SQL Server. The -x parameter disables SQL Server performance counters. This is unusual as it also disables performance reporting for many DMVs that contain performance data points such as CPU, Reads, Writes and other performance related counters. In SQL Server 2000 and earlier builds this sometimes provided a performance increase on high end servers but SQL Server 2005 redesigned the performance counter object layout to avoid memory hot spots, making this option obsolete. 

Step 03

Use Process Monitor I.E. ProcMon (https://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) to see if the counters are getting loaded and the registry keys are getting used. 

A. Start on the local system and launch ProcMon

B. Establish the following filters on the computer the SQL Server is installed on.  

clip_image010 

C. Start | Run: "MMC.exe perfmon.msc"  

You should see mmc.exe use the PerfLib (advapi32.dll) to retrieve the registry keys and then load the SQL Server counter library. 

The Perflib accesses the core PerfLib key to obtain basic TITLES database information. 

clip_image012 

Then Perflib checks to see if the TITLES database is in the middle of an update or the performance counters have been disabled for the entire system. 

clip_image014 

After basic sanity checks are complete the Services key is used to load the various performance libraries. In the output the SQL Server, SQLAgent and other SQL related counter loading should be present. The figure below shows my MSSQL$SQL2008 instance is queried and loaded using the perf-MSSQL$SQL2008-sqlctr.10.0.1600.22.dll.

clip_image016 

You will see the same pattern for a successful load in the svchost.exe process when a remote performance client begins collection against the target. The process column shows the process name. 

                If you don't see a similar success pattern you will need to make sure the registry setup properly. 

1. Are the performance counters globally disabled?

2. Does the following registry key exists and contain valid data and point to the proper files and paths?

[HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<<service name>>\Performance]

Library
Open
Collect
Close
PerfIniFile

3. Has the specific counter set been disabled via the [Services\<<service name>>\Performance] registry key: Disable Performance Counters  

4. Does the following registry key exists and contain a valid Export key value that matches the instance name?  

  [HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<<service name>>\Linkage]

Export 

5. Use unlodctr and lodctr /T to make sure the TITLES database is populated correctly for the desired counters.

When using unlodctr you use the MSSQLServer (default) or MSSQL$INSTANCE to identify the proper Services\<<Service name>> registry path. 

Note:   For remote access issues you must cycle the remote registry service after making any registry changes. 

D. Can you see the counters locally and not remotely?  

It is possible the remote registry service is not properly configured or needs to be started. Attempt to restart the registry service. 

net stop "Remote Registry"
net start "Remote Registry"

Step 04

WMI service runsWmiPrvse.exe, WinMgmt.exe and related dynamic-link libraries (DLLs) from the %Windir%\System32\Wbem directory. The CIM Repository is a database for static WMI data and object definitions. The CIM Repository is stored in the CIM.rep file in the %Windir%\System32\Wbem\Repository directory or in files in the Repository\FS directory. 

Note: If you can't see the counters with a standard mmc.exe perfmon.exe you won't be able to get them to properly work from a WMI channel. Make sure they are visible from the Pdh/PerfLib channel before working on WMI visibility.

WOW Support: Collection of 32 bit SQL Server counters is not supported from WMI on 64 bit operating systems.

Namespace: ROOT\CIMV2

Troubleshooting WMI Basics

https://support.microsoft.com/kb/266416
https://www.microsoft.com/technet/scriptcenter/topics/help/wmi.mspx
https://technet.microsoft.com/en-us/library/cc180468.aspx
https://social.technet.microsoft.com/Search/en-US/?query=WMIDiag&ac=8 

A. Run cscript WMIDiag.vbs to validate the WMI installation. 

Download: https://www.microsoft.com/downloads/details.aspx?familyid=d7ba3cd6-18d1-4d05-b11e-4c64192ae97d&displaylang=en   (The install contains a DOC containing usage examples and numerous troubleshooting techniques.) 

B. Do you have access to the performance counters using Perfmon.msc but when you specify the /WMI parameter they are no longer visible.  

C.Check the Application event log for message from event source WmiMgmt.  

D. Check the %Windir%\System32\Wbem\Logs (*.log) for issues.  

Enable additional logging for WMI, especially using resyncperf and clearadap phases of your troubleshooting.

LoggingWMI Activity: https://msdn.microsoft.com/en-us/library/aa392285(VS.85).aspx  "Software\\Microsoft\\WBEM\\CIMOM"

E.Is the "Windows Management Instrumentation" service running?  

If the "Remote Registry" is running but the "Windows Remote Management" service is stopped a remote client will be able to collect counters using (mmc.exe perfmon.msc) but not (mmc.exe perfmon.msc /WMI). 

Shown here is an attempt in PowerShell to list the WMI providers when the WMI service has been disabled. 

get-wmiobject -list

Get-WmiObject : The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. (Exception from HRESULT: 0x80070422) 

Note:   Some performance counters can be developed as a high performance (Hi-Perf) provider but SQL Server is not such a provider. Hi-perf provider is also known as 'pull provider'. In an instance provider (like SQL Server), WMI calls the Performance Counter Provider which in turn calls the performance library to collect this data and finally it returns all instances to the client. In the design of the Hi-perf provider this layer is removed.  

F. Can you enumerate and query the performance objects from WMI by checking basic registration and permission path?  

I used a PowerShell to enumerate the counters for my MSSQL$SQL2008 instance. Notice that the $ is removed from the name when searching. 

                   Local Enumeration: foreach($ctr in $( get-wmiobject -list | where {$_.Name -like '*MSSQLSQL2008*'})) { $ctr.Name; }

Remote Enumeration: foreach($ctr in $( get-wmiobject -Computer MyComputerName -list | where {$_.Name -like '*MSSQLSQL2008*'})) { $ctr.Name; } 

Output Sample

Win32_PerfFormattedData_MSSQLSQL2008_MSSQLSQL2008AccessMethods
Win32_PerfRawData_MSSQLSQL2008_MSSQLSQL2008AccessMethods
Win32_PerfFormattedData_MSSQLSQL2008_MSSQLSQL2008BackupDevice
.... 

To control WMI settings and security use the WMI Control Properties in Computer Management | Service and Applications.

Reference: https://technet.microsoft.com/en-us/library/cc180468.aspx 

Query Raw Values

Using one of the SQL Server exposed objects can you query the data with WMI using PowerShell? 

get-wmiobject -namespace "root\cimv2" -computer "." -query "select * from Win32_PerfRawData_MSSQLSQL2008CI_MSSQLSQL2008CIAccessMethods";  

G.Synchronize the WMI counter table with the performance registry keys. (winmgmt /resyncperf and winmgmt /clearadap

WinMgmt Switches: https://technet.microsoft.com/en-us/library/cc180468.aspx 

WmiPrvSE.exe loads the performance counters when the /WMI switch is used and the remote registry (svchost.exe) when using remote registry access. WMI uses AutoDiscovery/AutoPurge (ADAP) to build an internal performance counter table. If this becomes out of sync with the TITLES database it can prevent WMI performance counter collections. 

To rebuild the table follow: https://support.microsoft.com/kb/266416 

I used the PowerShell script to enumerate the MSSQL$SQL2008 instance counters. I think performed an unlodctr MSSQL$SQL2008 and enumerated the list again with PowerShell. The get-wmiobject returns the SQL Server performance counters but they are no longer registered in the common PerfLib locations. 

I issued winmgmt /resyncperf and I can still enumerate the counters. I had to issue the winmgmt /resyncperf "5422" - where 5422 is the Process Id (PID) of the WinPriv.exe process to get the sync to take place properly. 

I have been able to do the opposite of this as well. I lodctr to register the SQL Server counters but get-wmiobject will not list the values. Waiting a few seconds on my Vista installation and the resync automatically took place. If the automatic resync does not occur the resyncperf command can be used to force the synchronization. 

When the WMI information is out of sync the remote client may not get back a complete performance object listing and it tends to take a long time to attempt the enumeration. 

H.Another Way to View What Is Registered With WMI

1. Start Wbemtest.exe on the machine that produces the performance counters

2. Press the Connect button and use root\cimv2

image image

3. Press the Enum Classes Button, select recursive and press the OK button.

image 

4. Scroll down to a Win32_PerfRawData_<<SQL SERVER COUNTER>> section of the query output. If the SQL Server counters are not present the WMI database registration is out of sync or you have a WOW instance without a 64 bit performance counter library available.

image

5. Double click the counter group to display object details. Here you can see how the counter is registered, data sizes, properties and other facets.

image

6. Click the Show MOF button to see specific registration details. I have abbreviated the output below for clarity.  

Notice that you can see the service registry key, display names and other information related to the WMI registration information. 

Narrowed to Targetpages and the perfindex maps to the TITLES database Perflib counter entries.  Shown below is an example of an out of sync WMI database with the TITLES database from my x64, Windows XP SQL 2005 WOW instance.   Notice that the perfindex WMI is using does not match the TITLES registration and will prevent WMI collection of the counter.

    [dynamic: ToInstance, provider("Nt5_GenericPerfProvider_V1"), registrykey("MSSQL$SQL2005WOW"): ToInstance, locale(1033): ToInstance, perfindex(21248): ToInstance, helpindex(21249): ToInstance, perfdetail(0): ToInstance, genericperfctr: ToInstance, hiperf: ToInstance, singleton: DisableOverride ToInstance ToSubClass]
class Win32_PerfRawData_MSSQLSQL2005WOW_MSSQLSQL2005WOWBufferManager : Win32_PerfRawData
{

...
[DisplayName("Target pages"): ToInstance, countertype(65792): ToInstance, perfindex(21284) : ToInstance, helpindex(21285): ToInstance, defaultscale(-2): ToInstance, perfdetail(1): ToInstance] uint64 Targetpages;
};

    TITLES Shows (26690) MSSQL$SQL2005WOW:Buffer Manager and the my HKLM\....\<<Instance Name>>\Perforamnce First Counter: 26690

    I ran winmgmt /resyncperfctr "<<PID>>" on my machine and now I get a matching perfindex from WMI.

Step 05

Debugging (Examples use Vista x64 with SQL 2008 Instance) 

If there is still a problem obtaining the performance counters it is time to look inside the system. Previous steps already outlined how to determine the process(s) loading the computer library. 

A.Install the Windows Debugging Tools on the performance counter target computer. (https://www.microsoft.com/whdc/devtools/debugging/default.mspx

B.Start MMC.exe under the debugger on the counter target computer. windbg.exe mmc.exe perfmon.msc 

C.Issue the following debugger commands. These commands will setup and load the public symbols.  

.sympath srv*https://msdl.microsoft.com/download/symbols

.reload 

Use the g (go command) to start running the executable. As each DLL is loaded the ModLoad message is produced. 

ModLoad: 000007fe`fe910000 000007fe`fe93d000 C:\Windows\system32\IMM32.DLL 

                After the initial modules are loaded attempt to add a new counter to the Windows Performance Monitor. We expect to see performance library loaded. 

ModLoad: 00000000`6f500000 00000000`6f6c4000 C:\Windows\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll 

If you don't see the module loaded we have an issue with the DLL or the registry as previously discussed. 

SQL2008 Counters Marked Disabled

I noticed this time an unknown exception was produced while mmc.exe was loading the performance library modules. 

ModLoad: 00000642`ff460000 00000642`ff47a000 C:\Windows\Microsoft.NET\Framework64\v2.0.50727\CorperfmonExt.dll

ModLoad: 000007fe`f7cb0000 000007fe`f7cba000 C:\Windows\system32\bitsperf.dll

(398c.3078): Unknown exception - code 000006d9 (first chance)

I used the sxe 000006d9 and restarted my scenario again. When the exception was encountered I dumped the stack using the k command. 

00000000`05e2d820 000007fe`ffa1f826 kernel32!RaiseException+0x39

00000000`05e2d8f0 000007fe`ffadd040 RPCRT4!RpcpRaiseException+0x26

00000000`05e2d920 000007fe`ffadfa0b RPCRT4!Ndr64pClientExceptionHandling+0x658

00000000`05e2d980 000007fe`ffadfe01 RPCRT4!NdrpClientCall3+0x6b

00000000`05e2dbd0 000007fe`fe3ae3d2 RPCRT4!NdrClientCall3+0xdd

00000000`05e2df50 000007fe`fe3ae247 ADVAPI32!OpenRemoteExtObjectLibrary+0x1d5

     ^------^ This function is called when loading the provider DLL failed. It attempts

              to load the DLL in the PerfHost process by calling its RPC interface.
00000000`05e2e210 000007fe`fe38a1b3 ADVAPI32!OpenExtObjectLibrary+0xc73

00000000`05e2e9f0 000007fe`fe38cbc2 ADVAPI32!QueryExtensibleData+0x876

00000000`05e2ecd0 000007fe`fe38c6e1 ADVAPI32!PerfRegQueryValue+0x682

00000000`05e2f180 000007fe`fe3a94b3 ADVAPI32!LocalBaseRegQueryValue+0x295

00000000`05e2f2d0 000007fe`f8ec9a4c ADVAPI32!RegQueryValueExW+0xef

00000000`05e2f380 000007fe`f8ecc4b9 pdh!GetSystemPerfData+0xbc

00000000`05e2f420 000007fe`f8ef5c07 pdh!GetMachine+0x2f9

00000000`05e2f6b0 000007fe`f8ef6363 pdh!PdhiEnumObjects+0x6f

00000000`05e2f820 000007fe`f7da3b6e pdh!PdhEnumObjectsHW+0x1a3

00000000`05e2f8a0 000007fe`f7da8ad4 pdhui!PdhiLoadMachineObjects+0x142  

The stack is using the PerfLib (advapi32) and attempting to load a performance object library and it failed. Now I wanted to determine which library failed to load so I just went back to Process Monitor and looked. 

From the debugger the last DLL loaded was bitsperf.dll and then the error so I can focus on the bitsperf.dll load timeframe in the Process Monitor capture. I found that the next counter library is CcmFramework and it has been disabled on my machine.

clip_image018 

I allowed the debugger to continue execution and sandwiched right between the msscntrs.dll and perfdisk.dll was an exception. Looking at the Process Monitor data you can see the SQL Server performance counter key is interrogated and it returns a non-zero value indicating they are disabled. 

ModLoad: 000007fe`f7c90000 000007fe`f7ca3000 C:\Windows\system32\msscntrs.dll

(2230.1390): Unknown exception - code 000006d9 (first chance)

First chance exceptions are reported before any exception handling.

This exception may be expected and handled.

kernel32!RaiseException+0x39:

00000000`7754649d 4881c4c8000000 add rsp,0C8h

ModLoad: 000007fe`f7db0000 000007fe`f7dbd000 C:\Windows\system32\perfdisk.dll 

clip_image020 

SQL2008 Library Registry Key Contains An Invalid File
The debugger shows a similar exception pattern and Process Monitor shows the exact story. When the DLL load attempt took place, the name of was not located. While my scenario is a bad name in the registry I could cause similar problem by changing the security permissions or other property in the registry or on the file. 

clip_image022 

SQL2008 Library Registry Key Contains Invalid Open Entry Point
The debugger shows a similar exception pattern but notice that the counter library is loaded and then the exception is encountered. The performance library was loaded but the Open entry point was not located. Process Monitor is not as clear about this problem. It shows the successful retrieval of the Open key value and loading of the DLL and nothing else and the registry has been updated to disable the SQL Server performance counters for the MSSQL$SQL2008 instance. 

ModLoad: 00000000`6f500000 00000000`6f6c4000 C:\Windows\system32\perf-MSSQL$SQL2008-sqlctr10.0.1600.22.dll
(2680.2604): Unknown exception - code 000006d9 (first chance)

Is This A Registration Issue or DLL Issue?

Using the sxe command the debugger will break-in on the exception. Issuing the k command shows the stack. In this case the stack looks the same as if the DLL load failed. 

00000000`05cadcd0 000007fe`fe3ae247 ADVAPI32!OpenRemoteExtObjectLibrary+0x1d5

00000000`05cadf90 000007fe`fe38a1b3 ADVAPI32!OpenExtObjectLibrary+0xc73

00000000`05cae770 000007fe`fe38cbc2 ADVAPI32!QueryExtensibleData+0x876

00000000`05caea50 000007fe`fe38c6e1 ADVAPI32!PerfRegQueryValue+0x682

Knowing the Library registry key I can set a deferred breakpoint on the DLLMain entry point and debug this further. 

                bp perf_MSSQL_SQL2008_sqlctr10_0_1600_22!DllMain

The breakpoint stops the debugger when the performance library is loaded. Shown below is the stack during the DLLMain invocation. 

00000000`05c2d698 00000000`6f51063d perf_MSSQL_SQL2008_sqlctr10_0_1600_22!DllMain

00000000`05c2d6a0 00000000`7786fd5a perf_MSSQL_SQL2008_sqlctr10_0_1600_22!CRT_INIT+0x29d

00000000`05c2d6e0 00000000`7786591c ntdll!LdrpRunInitializeRoutines+0x1c3

00000000`05c2d8c0 00000000`77865836 ntdll!LdrpLoadDll+0x496

00000000`05c2dbd0 00000000`775441d2 ntdll!LdrLoadDll+0x136

00000000`05c2dec0 000007fe`fe38a366 kernel32!LoadLibraryExW+0x15e

00000000`05c2df50 000007fe`fe38a1b3 ADVAPI32!OpenExtObjectLibrary+0x3d3

00000000`05c2e730 000007fe`fe38cbc2 ADVAPI32!QueryExtensibleData+0x876

00000000`05c2ea10 000007fe`fe38c6e1 ADVAPI32!PerfRegQueryValue+0x682

00000000`05c2eec0 000007fe`fe3a94b3 ADVAPI32!LocalBaseRegQueryValue+0x295

00000000`05c2f010 000007fe`f8ec9a4c ADVAPI32!RegQueryValueExW+0xef

Note:   It is possible that the DLLMain is not invoked. This could happen if a dependent DLL is missing. You can use utilities such as depends.exe to validate the DLL dependencies. However, that condition should show up in Process Monitor as a FILE_NOT_FOUND load failure. 

I can now use the x command to see the exported Open symbol(s). 

x perf_MSSQL_SQL2008_sqlctr10_0_1600_22!*Open*  

00000000`6f50b890 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!OpenSQLPerformanceData8 = <no type information>

00000000`6f50ace0 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!OpenSQLPerformanceData = <no type information>

00000000`6f50b9b0 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!OpenSQLPerformanceData9 = <no type information>  

You should see a series of OpenSQLPerformanceData entry points. Each of these is associated with a specific instance so MSSQL$INST1 could register with ...Data10 and MSSQL$INST2 could register with ...Data25 for example. The Performance registry values provide the Open, Collect and Close entry points. 

The reason the SQL Server performance DLLs have multiple entry points for Open, Collect and Close is to support instances. The Open* function is called with the Linkage\Export value allowing the counters to be properly established for the instance. The Export value is used to determine the full names of shared memory and named objects. However, the Collect function is not provided with an SQL Server instance identifier. By registering wrapper entry points the collection structures pointer is setup and the base function is invoked.

The PerfLib loads the performance DLL and then used the GetProcAddress API to obtain the Open, Collect and Close entry point addresses. 

00000000`05c2df48 000007fe`fe38a380 kernel32!GetProcAddress

00000000`05c2df50 000007fe`fe38a1b3 ADVAPI32!OpenExtObjectLibrary+0x3ed

00000000`05c2e730 000007fe`fe38cbc2 ADVAPI32!QueryExtensibleData+0x876  

Setting a breakpoint on GetProcAddress (bp kernel32!GetProcAddress) you can see the actual search string (2nd parameter) that was taken from the Open, Collect or Close registry value. 

0:009> db 4b840d8

00000000`04b840d8 4f 70 65 6e 53 51 4c 50-65 72 66 6f 72 6d 61 6e OpenSQLPerforman

00000000`04b840e8 63 65 44 61 74 61 00 00-43 6f 6c 6c 65 63 74 53 ceData..  

Stepping over the call and dumping the return register (? @rax or ? @eax) shows the return address of the entry point. If this returns NULL the entry point was not found and the performance library load attempt fails. You may also want to issue the (!gle) debugger command to see the last error context returned from GetProcAddress. 

A call into the OpenPerformanceData might look like the following. For demonstration purposes I modified a registry, string pointer so the OpenSQLPerformanceData call results in an exception. 

(2bac.3548): Access violation - code c0000005 (first chance)

First chance exceptions are reported before any exception handling.

This exception may be expected and handled.

ntdll!RtlInitUnicodeStringEx+0x28:

00000000`77889c28 66f2af repne scas word ptr [rdi] ds:00000000`00000001=????

0:009> k

Child-SP RetAddr Call Site

00000000`05c2db50 000007fe`fe3a945c ntdll!RtlInitUnicodeStringEx+0x28

00000000`05c2db60 00000000`6f510d4e ADVAPI32!RegQueryValueExW+0x90

00000000`05c2dc10 00000000`6f50ad22 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!InitSharedBins+0xfe

00000000`05c2df20 000007fe`fe38a459 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!OpenSQLPerformanceData+0x42

00000000`05c2df50 000007fe`fe38a1b3 ADVAPI32!OpenExtObjectLibrary+0x8ad

00000000`05c2e730 000007fe`fe38cbc2 ADVAPI32!QueryExtensibleData+0x876 

Under a debugger you can see clearly that where the problem is and that it involves the SQL Server performance library DLL. This issue will result in the SQL Server performance library counters being marked disabled and the associated events are added to the Application event log. 

Disabled performance counter data collection from the "MSSQL$SQL2008" service because the performance counter library for that service has generated one or more errors. The errors that forced this action have been written to the application event log. Correct the errors before enabling the performance counters for this service. 

Shared Object or Memory Issues

The SQL Server performance library must obtain proper access to the shared memory and named objects. Using the debugger you can see the handles the process has open or you can use Process Explorer. I prefer Process Explorer for troubleshooting performance monitor counter issues because I can do a find (as shown earlier) to see any process that owns the handle. 

Shown here is the output from !handle 0 7 against the MMC.exe process collecting the SQL Server Batches Per Second counter against MSSQL$SQL2008

Handle 608

  Type Section

  Attributes 0

  GrantedAccess 0x4:

         None

         MapRead

  HandleCount 4

  PointerCount 6

  Name \BaseNamedObjects\SQL_90_MEMOBJ_SQL2008_0

This figure shows the same information from Process Explorer. The shared mutex and two of the possible 100 mapped MEMOBJ sections are present. 

clip_image024 

You can also use the technet\sysinternals tool WinObj.exe to view the BaseNamedObjects for Sections and Mutex types. The PID is the hex, process identifier. 

clip_image026 

clip_image028 

Watching the Mutex activity requires the Windows Debugger. By setting the following breakpoints in a collection process you can see the mutex getting acquired and released on the collection interval. 

bp perf_MSSQL_SQL2008_sqlctr10_0_1600_22!AutoCounterMutex::Get ".echo 'Mutex Get';k;g"

bp perf_MSSQL_SQL2008_sqlctr10_0_1600_22!AutoCounterMutex::Release ".echo 'Mutex Release';k;g" 

'Mutex Get'

Child-SP RetAddr Call Site

00000000`060d7498 00000000`6f9ea18a perf_MSSQL_SQL2008_sqlctr10_0_1600_22!AutoCounterMutex::Get

00000000`060d74a0 00000000`6f9eafeb perf_MSSQL_SQL2008_sqlctr10_0_1600_22!CloseSQLPerformanceData50+0x33a

00000000`060d7520 000007fe`fe06d825 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!CollectSQLPerformanceData+0x23b

00000000`060deec0 000007fe`fe06cbc2 ADVAPI32!QueryExtensibleData+0xb59

00000000`060df1a0 000007fe`fe06c6e1 ADVAPI32!PerfRegQueryValue+0x682 

'Mutex Release'

Child-SP RetAddr Call Site

00000000`060d7498 00000000`6f9ea4b5 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!AutoCounterMutex::Release

00000000`060d74a0 00000000`6f9eafeb perf_MSSQL_SQL2008_sqlctr10_0_1600_22!CloseSQLPerformanceData50+0x665

00000000`060d7520 000007fe`fe06d825 perf_MSSQL_SQL2008_sqlctr10_0_1600_22!CollectSQLPerformanceData+0x23b

00000000`060deec0 000007fe`fe06cbc2 ADVAPI32!QueryExtensibleData+0xb59

00000000`060df1a0 000007fe`fe06c6e1 ADVAPI32!PerfRegQueryValue+0x682

 

Bob Dorr - Principal SQL Server Escalation Engineer