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.