I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account.

Next, I went to add an alert, and I only had the XTP counters.

The counters are also missing in Performance Monitor. What is interesting is that I show the correct SQLAgent counters for each of my three instances.

A quick search found me this blog on MSDN, where it recommends the following:

unlodctr mssqlserver

lodctr perf-mssqlserversqlctr.ini

I had a named instance, so for me I entered:

unlodctr mssql$sql2016

lodctr perfMSSQL$SQL2016sqlctr.ini

from an elevated command prompt. Running the last command again shows the counters loaded.

I also checked my registry, which appeared to be fine:

I next found another blog that noted I might need to resynch WMI, so I ran winmgmt, using the PID from Task Explorer (details tab):

I didn’t see counters at first, but I restarted the instance. Once that was done …

A nice fix, and one I probably won’t forget after this blog.