How to Diagnose and Resolve SQLPerformanceCounterFix Errors

Repair strategy (idempotent)

This script attempts non-destructive repairs in a safe order:

1. Try to rebuild SQL Server counters using SQL Server’s DLLs if available.
2. If that fails, rebuild all Windows performance counters using lodctr /R.
3. Re-register the SQL Server performance counter library via unlodctr/lodctr.
4. Refresh the performance counter configuration and restart services.

powershell
 ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf” data-copycode=“true” role=“button” aria-label=“Copy Code”>Copy CodeCopied
param(
  [string]\(SqlInstanceName</span><span> = </span><span class="token" style="color: rgb(163, 21, 21);">"MSSQLSERVER"</span><span class="token" style="color: rgb(57, 58, 52);">,</span><span>   </span><span class="token" style="color: rgb(0, 128, 0); font-style: italic;"># change if named instance</span><span> </span><span>  </span><span class="token">[switch]</span><span class="token" style="color: rgb(54, 172, 170);">\)RestartSQL = \(true</span><span class="token" style="color: rgb(57, 58, 52);">,</span><span> </span><span>  </span><span class="token">[switch]</span><span class="token" style="color: rgb(54, 172, 170);">\)RebuildAll = \(false</span><span> </span><span></span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span> <span></span><span class="token" style="color: rgb(0, 0, 255);">function</span><span> Run-Command</span><span class="token" style="color: rgb(57, 58, 52);">(</span><span class="token" style="color: rgb(54, 172, 170);">\)cmd, \(failOnError</span><span>=</span><span class="token" style="color: rgb(54, 172, 170);">\)true) {
  Write-Output “Running: \(cmd</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> </span><span>  </span><span class="token" style="color: rgb(54, 172, 170);">\)proc = Start-Process -FilePath “cmd.exe” -ArgumentList ”/c \(cmd</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>NoNewWindow </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>Wait </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>PassThru </span><span>  </span><span class="token" style="color: rgb(0, 0, 255);">if</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">(</span><span class="token" style="color: rgb(54, 172, 170);">\)proc.ExitCode -ne 0) {
    Write-Warning “Command failed with exit code \(</span><span class="token" style="color: rgb(57, 58, 52);">(</span><span class="token" style="color: rgb(54, 172, 170);">\)proc.ExitCode): \(cmd</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> </span><span>    </span><span class="token" style="color: rgb(0, 0, 255);">if</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">(</span><span class="token" style="color: rgb(54, 172, 170);">\)failOnError) { throw “Command failed” }
  }
}

# 1) Try to unregister and re-register SQL counters
try {
  \(perfName</span><span> = </span><span class="token" style="color: rgb(54, 172, 170);">\)SqlInstanceName -replace ’^MSSQLSERVER\('</span><span class="token" style="color: rgb(57, 58, 52);">,</span><span class="token" style="color: rgb(163, 21, 21);">'MSSQLSERVER'</span><span> </span><span>  Run-Command </span><span class="token" style="color: rgb(163, 21, 21);">"unlodctr </span><span class="token" style="color: rgb(54, 172, 170);">\)perfName“ -failOnError:\(false</span><span> </span><span>  </span><span class="token" style="color: rgb(0, 128, 0); font-style: italic;"># Locate the SQL counter DLL (common locations)</span><span> </span><span>  </span><span class="token" style="color: rgb(54, 172, 170);">\)possible = @(
    ”\(env</span><span class="token" style="color: rgb(163, 21, 21);">:ProgramFiles\Microsoft SQL Server\110\Shared\sqlctr.dll"</span><span class="token" style="color: rgb(57, 58, 52);">,</span><span> </span><span>    </span><span class="token" style="color: rgb(163, 21, 21);">"</span><span class="token" style="color: rgb(54, 172, 170);">\)env:ProgramFiles\Microsoft SQL Server\120\Shared\sqlctr.dll”,
    “\(env</span><span class="token" style="color: rgb(163, 21, 21);">:ProgramFiles\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlctr.dll"</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span>  </span><span class="token" style="color: rgb(54, 172, 170);">\)dll = \(possible</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">|</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">Where-Object</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">Test-Path</span><span> </span><span class="token" style="color: rgb(54, 172, 170);">\) } | Select-Object -First 1   if (\(dll</span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>    Run-Command </span><span class="token" style="color: rgb(163, 21, 21);">"lodctr /M:</span><span class="token" style="color: rgb(54, 172, 170);">\)dll”
  } else {
    Write-Warning “SQL counter DLL not found; will attempt global rebuild if allowed.”
    if (-not \(RebuildAll</span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">throw</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"DLL missing"</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span></span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">catch</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">Write-Warning</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"Per-instance re-registration failed: </span><span class="token" style="color: rgb(54, 172, 170);">\)“
}

# 2) Optionally rebuild all counters
if (\(RebuildAll</span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>  </span><span class="token" style="color: rgb(0, 0, 255);">try</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>    Run-Command </span><span class="token" style="color: rgb(163, 21, 21);">"lodctr /R"</span><span> </span><span>    Run-Command </span><span class="token" style="color: rgb(163, 21, 21);">"winmgmt /resyncperf"</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">catch</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>    </span><span class="token" style="color: rgb(57, 58, 52);">Write-Warning</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"Global rebuild failed: </span><span class="token" style="color: rgb(54, 172, 170);">\)”
  }
}

# 3) Restart SQL Server service(s) to reload counters
if (\(RestartSQL</span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>  </span><span class="token" style="color: rgb(54, 172, 170);">\)svcName = if (\(SqlInstanceName</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">-eq</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"MSSQLSERVER"</span><span class="token" style="color: rgb(57, 58, 52);">)</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"MSSQLSERVER"</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">else</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"MSSQL`\)\(SqlInstanceName</span><span class="token" style="color: rgb(163, 21, 21);">"</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span>  </span><span class="token" style="color: rgb(0, 0, 255);">try</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>    </span><span class="token" style="color: rgb(57, 58, 52);">Restart-Service</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>Name </span><span class="token" style="color: rgb(54, 172, 170);">\)svcName -Force -ErrorAction Stop     Write-Output “Service \(svcName</span><span class="token" style="color: rgb(163, 21, 21);"> restarted."</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">catch</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>    </span><span class="token" style="color: rgb(57, 58, 52);">Write-Warning</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"Could not restart </span><span class="token" style="color: rgb(54, 172, 170);">\)svcName: $”
  }
}

# 4) Validate
try {
  \(test</span><span> = </span><span class="token" style="color: rgb(57, 58, 52);">Get-Counter</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>ListSet </span><span class="token" style="color: rgb(163, 21, 21);">"SQLServer:Buffer Manager"</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>ErrorAction Stop </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">Write-Output</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"Validation succeeded: SQL counters available."</span><span> </span><span></span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span><span class="token" style="color: rgb(0, 0, 255);">catch</span><span> </span><span class="token" style="color: rgb(57, 58, 52);">{</span><span> </span><span>  </span><span class="token" style="color: rgb(57, 58, 52);">Write-Warning</span><span> </span><span class="token" style="color: rgb(163, 21, 21);">"Validation failed; counters still missing."</span><span> </span><span>  </span><span class="token" style="color: rgb(0, 0, 255);">exit</span><span> 2 </span><span></span><span class="token" style="color: rgb(57, 58, 52);">}</span><span> </span></code></div></div></pre> <h2>Scheduling and alerts</h2> <ul> <li>Use Task Scheduler to run the script under an account with administrative rights. Set triggers (e.g., daily or on system boot).</li> <li>Add email or webhook alerting when the script reports failures (use Send-MailMessage or Invoke-RestMethod to a monitoring endpoint).</li> <li>Keep a rotating backup of the perf counter exports; monitor disk usage.</li> </ul> <h2>Troubleshooting tips</h2> <ul> <li>If lodctr /R reports "unable to rebuild performance counter setting from system backup store", ensure you run as Administrator and that the system has the necessary .ini files in the Windows directory.</li> <li>Check Event Viewer (Application/System) for perf counter or WMI errors.</li> <li>If only one SQL instance is affected, re-register that instance's counters instead of a global rebuild.</li> <li>Reinstalling or repairing the SQL Server shared feature (client tools/shared components) can restore missing counter DLLs.</li> </ul> <h2>Example: one-line run for a named instance</h2> <p>PowerShell one-liner to run the script with a named instance and force a global rebuild if needed:</p> <pre><div class="XG2rBS5V967VhGTCEN1k"><div class="nHykNMmtaaTJMjgzStID"><div class="HsT0RHFbNELC00WicOi8"><i><svg width="16" height="16" fill="none" xmlns="http://www.w3.org/2000/svg"><path fill="currentColor" fill-rule="evenodd" clip-rule="evenodd" d="M15.434 7.51c.137.137.212.311.212.49a.694.694 0 0 1-.212.5l-3.54 3.5a.893.893 0 0 1-.277.18 1.024 1.024 0 0 1-.684.038.945.945 0 0 1-.302-.148.787.787 0 0 1-.213-.234.652.652 0 0 1-.045-.58.74.74 0 0 1 .175-.256l3.045-3-3.045-3a.69.69 0 0 1-.22-.55.723.723 0 0 1 .303-.52 1 1 0 0 1 .648-.186.962.962 0 0 1 .614.256l3.541 3.51Zm-12.281 0A.695.695 0 0 0 2.94 8a.694.694 0 0 0 .213.5l3.54 3.5a.893.893 0 0 0 .277.18 1.024 1.024 0 0 0 .684.038.945.945 0 0 0 .302-.148.788.788 0 0 0 .213-.234.651.651 0 0 0 .045-.58.74.74 0 0 0-.175-.256L4.994 8l3.045-3a.69.69 0 0 0 .22-.55.723.723 0 0 0-.303-.52 1 1 0 0 0-.648-.186.962.962 0 0 0-.615.256l-3.54 3.51Z"></path></svg></i><p class="li3asHIMe05JPmtJCytG wZ4JdaHxSAhGy1HoNVja cPy9QU4brI7VQXFNPEvF">powershell</p></div><div class="CF2lgtGWtYUYmTULoX44"><button type="button" class="st68fcLUUT0dNcuLLB2_ ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ CPXAhl7VTkj2dHDyAYAf" data-copycode="true" role="button" aria-label="Copy Code"><svg viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg"><path fill="currentColor" fill-rule="evenodd" clip-rule="evenodd" d="M9.975 1h.09a3.2 3.2 0 0 1 3.202 3.201v1.924a.754.754 0 0 1-.017.16l1.23 1.353A2 2 0 0 1 15 8.983V14a2 2 0 0 1-2 2H8a2 2 0 0 1-1.733-1H4.183a3.201 3.201 0 0 1-3.2-3.201V4.201a3.2 3.2 0 0 1 3.04-3.197A1.25 1.25 0 0 1 5.25 0h3.5c.604 0 1.109.43 1.225 1ZM4.249 2.5h-.066a1.7 1.7 0 0 0-1.7 1.701v7.598c0 .94.761 1.701 1.7 1.701H6V7a2 2 0 0 1 2-2h3.197c.195 0 .387.028.57.083v-.882A1.7 1.7 0 0 0 10.066 2.5H9.75c-.228.304-.591.5-1 .5h-3.5c-.41 0-.772-.196-1-.5ZM5 1.75v-.5A.25.25 0 0 1 5.25 1h3.5a.25.25 0 0 1 .25.25v.5a.25.25 0 0 1-.25.25h-3.5A.25.25 0 0 1 5 1.75ZM7.5 7a.5.5 0 0 1 .5-.5h3V9a1 1 0 0 0 1 1h1.5v4a.5.5 0 0 1-.5.5H8a.5.5 0 0 1-.5-.5V7Zm6 2v-.017a.5.5 0 0 0-.13-.336L12 7.14V9h1.5Z"></path></svg>Copy Code</button><button type="button" class="st68fcLUUT0dNcuLLB2_ WtfzoAXPoZC2mMqcexgL ffON2NH02oMAcqyoh2UU MQCbz04ET5EljRmK3YpQ GnLX_jUB3Jn3idluie7R"><svg fill="none" viewBox="0 0 24 24" xmlns="http://www.w3.org/2000/svg"><path fill="currentColor" fill-rule="evenodd" d="M20.618 4.214a1 1 0 0 1 .168 1.404l-11 14a1 1 0 0 1-1.554.022l-5-6a1 1 0 0 1 1.536-1.28l4.21 5.05L19.213 4.382a1 1 0 0 1 1.404-.168Z" clip-rule="evenodd"></path></svg>Copied</button></div></div><div class="mtDfw7oSa1WexjXyzs9y" style="color: var(--sds-color-text-01); font-family: var(--sds-font-family-monospace); direction: ltr; text-align: left; white-space: pre; word-spacing: normal; word-break: normal; font-size: var(--sds-font-size-label); line-height: 1.2em; tab-size: 4; hyphens: none; padding: var(--sds-space-x02, 8px) var(--sds-space-x04, 16px) var(--sds-space-x04, 16px); margin: 0px; overflow: auto; border: none; background: transparent;"><code class="language-powershell" style="color: rgb(57, 58, 52); font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; direction: ltr; text-align: left; white-space: pre; word-spacing: normal; word-break: normal; font-size: 0.9em; line-height: 1.2em; tab-size: 4; hyphens: none;"><span>PowerShell </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>ExecutionPolicy Bypass </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>NoProfile </span><span class="token" style="color: rgb(57, 58, 52);">-</span><span>Command </span><span class="token" style="color: rgb(163, 21, 21);">"& { .\Repair-SQLPerf.ps1 -SqlInstanceName 'MSSQL</span><span class="token" style="color: rgb(54, 172, 170);">\)SQL2019’ -RebuildAll }”

What success looks like

• Get-Counter returns SQL Server categories and counters.
• SQL Server monitoring tools (Performance Monitor, Query Store diagnostics) show counters.
• No recurring perf counter related events in System/Application logs after repair.

If you want, I can generate a ready-to-run .ps1 file tailored to a specific SQL Server version and instance name.