sp_BlitzFirst performance counters error on managed instances

[codykonior]

Version of the script
8.11, so current

What is the current behavior?
Running sp_BlitzFirst on a managed instance with no parameters gives a lot of errors on null columns for perfmon.

This returns no rows:

SELECT * FROM sys.dm_os_performance_counters WHERE cntr_value IS NULL OR object_name IS NULL OR instance_name IS NULL OR cntr_value IS NULL OR cntr_type IS NULL

If the current behavior is a bug, please provide the steps to reproduce.

Setting up configuration variables
Now starting diagnostic analysis
Checking for Read intent databases to exclude
Determining SQL Server version.
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'object_name', table 'tempdb.dbo.#PerfmonStats_______________________________________________________________________________________________________00000006BEE9'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_BlitzFirst, Line 1216 [Batch Start Line 0]
Cannot insert the value NULL into column 'object_name', table 'tempdb.dbo.#PerfmonCounters____________________________________________________________________________________________________00000006BEEA'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(and many more like it)

What is the expected behavior?
No error.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Managed instances only. Unknown if it worked previously.

I'll dig into this later.

[codykonior]

It's because the code works out the instance prefix used for performance counters, and selects/inserts rows based on that, but none are found.

This is because on managed instances the prefix is randomised. Luckily the code is already in here to detect it for SQL Azure. The managed instance format is the same so I've submitted the PR.

[BrentOzar]

Thanks for the pull request! Looks good. Merging into the dev branch, will be in the December release with credit to you in the release notes.