SQL Server – Installation – Error – “Cannot open registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.”

Background

Ever so often one runs into errors in the area of SQL Server and Performance Counters.

Errors

One of the places where the errors shows up is the MS Windows Event Viewer :-

MS Windows Event Viewer

Log Name :- Application

  1.  Event ID – 8316
    • Event ID :- 8316
    • Source :- MSSQLServer
    • Details :- Cannot open registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.
  2. Event ID – 8317
    • Event ID :- 8317
    • Source :- MSSQLServer
    • Details :- Cannot query value ‘First Counter’ associated with registry key ‘HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance’. SQL Server performance counters are disabled.
  3. Event ID – 3003
    • Event ID :- 3003
    • Source :- LoadPerf
    • Details :- Unable to install counter strings because the SYSTEM\CurrentControlSet\Services\MSSQLServer\Performance key could not be opened or accessed. The first DWORD in the Data section contains the Win32 error code.
  4. Event ID – 3009
    • Event ID :- 3009
    • Source :- LoadPerf
    • Details :- Installing the performance counter strings for service MSSQLServer (SQL Server (MSSQLSERVER)) failed. The first DWORD in the Data section contains the error code.

Remediation

Outline

  1. SQL Server Instance
    • For that specific SQL Server Instance, locate folder where performance counter files are kept and reload them
  2. System
    • Reload OS System counters
      • In command window, change folder to %windows%\system32
      • Reload all OS System Counters
  3. Performance Counters
    • Identify disabled Performance Counters
  4.  Registry
    • In MS Windows Registry, repair or create missing folders and items

Tasks

SQL Server Instance

Script


 @echo off

setlocal

REM Please adjust for you specific SQL Server Instance
set "_folderTarget=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn"

set "_fileExtIni=*.ini"

set _appPgmList=echo

set _appPgmApply=lodctr

set "_service=mssqlserver"

ECHO Unload Performance Counter for %_service%

unlodctr "%_service%"

ECHO Performance Counter for %_service% unloaded

ECHO Targeted Folder %_folderTarget%
ECHO ==============================

forfiles /P "%_folderTarget%." /s  /m %_fileExtIni% /c ^"cmd /c ^
  %_appPgmList% ^
  file name is @FILE ^
^"

REM Apply lodctr

@echo
@echo

forfiles /P "%_folderTarget%." /s  /m %_fileExtIni% /c ^"cmd /c ^
   %_appPgmApply% @PATH ^
^"

endlocal

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

net stop "Performance Logs & Alerts"
net start "Performance Logs & Alerts"

endlocal

OS

Outline

  1. LODCTR
    • Navigate to System Folders
      • SYSTEMROOT
        • %SYSTEMROOT%\SYSTEM32
        • %SYSTEMROOT%\SYSWOW64
    • Issue LODCTR /r
  2. Winmgmt
    • winmgmt.exe /RESYNCPERF

Performance Counters

Is Performance Counter Disabled ?

Script

Syntax

lodctr /q

Sample

lodctr /q | find /i "Performance Counters" | find /i "sql"

Output
Output – Textual

>lodctr /q | find /i "Performance Counters" | find /i "sql"
[.NET Data Provider for SqlServer] Performance Counters (Enabled)
[msftesql] Performance Counters (Enabled)
[msftesqlFD] Performance Counters (Enabled)
[msftesqlIDX] Performance Counters (Enabled)
[MSSQLSERVER] Performance Counters (Enabled)
[MSSQLServerOLAPService] Performance Counters (Enabled)
[SQLBrowser] Performance Counters (Enabled)
[SQLSERVERAGENT] Performance Counters (Enabled)

Output – Image

lodctr.q.20190309.0520PM

Explanation
  1. lodctr
    • If the Counter is disabled, it will be flagged, as such

 

Registry

Regedit

Using regedit, review registry entries

Outline

  1. Registry Key
    • Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
    • Please make sure the performance branches exists under the SQL Server services
      • Default Instance
        • MSSQLSERVER\Performance
        • SQLSERVERAGENT\Performance
      • Named Instance
        • MSSQL$[Instance-Name]\Performance
        • SQLSERVERAGENT$[Instance-Name]\Performance
    • Under each Performance branch, you want to look for
      • Engine
        • PerfIniFile
          • Default Instance
            • sqlctr.ini
          • Named Instance
            • perf-<instance name>sqlctr.ini
      • SQL Server Agent
        • PerfIniFile
          • Default Instance
            • sqlagtctr.ini
          • Named Instance
            • perf-<instance name>sqlagtctr.ini
  2. Create Performance node if absent
  3. Grant permissions to the account running the SQL Server Service ( Engine / Agent / etc )

Registry Entries

Registry Entries – SQL Server Engine
Registry Entries – SQL Server Engine – Initial

MSSQLServer.Engine.01.20190308.1105PM.PNG

Registry Entries – SQL Server Engine – Revised

performanceCounter.sqlServerEngine.20190309.0540PM.PNG

Registry Entries – SQL Server Agent
Registry Entries – SQL Server Agent – Initial

registry.sqlServerAgent.20190308.1101PM.PNG

 

Registry Entries – SQL Server Agent – Revised

performanceCounter.sqlServerAgent.20190309.0533PM.PNG

 

 

References

  1. loganathanvm
    • System counters corrupted issue or LODCTR /R Error Code 2.
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s