[BUG] sys.dm_xe_session_targets vs sys.dm_xe_database_session_targets in Azure SQL Database

[vpnicholls]

Component

Lite

Performance Monitor Version

2.3.0

SQL Server Version

Microsoft SQL Azure (RTM) - 12.0.2000.8 Feb 7 2026 14:31:25 Copyright (C) 2025 Microsoft Corporation

Windows Version

Windows 11 Enterprise 23H2

Describe the Bug

Logs show:

2026-03-20 14:48:45.176 [ERROR] [Collector] [] deadlocks SQL Error #208: Invalid object name 'sys.dm_xe_session_targets'.
2026-03-20 14:48:45.176 [ERROR] [RemoteCollectorService] Collector 'deadlocks' SQL error #208 for server '' | SqlException: Invalid object name 'sys.dm_xe_session_targets'.

In Azure SQL Database, the XE sessions are created at the database level rather than the instance/server level.

I can see that the XE sessions have already been created successfully. But it seems that the application cannot access the sessions.

The logs show the application is trying to access sys.dm_xe_session_targets. However, the database-level EE sessions are in:

sys.dm_xe_database_session_targets

Steps to Reproduce

1. In Azure SQL Database, generate a blocked session.
2. For the Azure SQL Database, navigate to Blocking > Blocked Process Report, verify that no report is generated.
3. Navigate to logs, search for "blocked_process_report SQL Error"

Expected Behavior

1. The XE session records the blocked process.
2. The blocked process report can be retrieved through Performance Monitor Lite.

Actual Behavior

1. Blocked process report is not available.
2. Invalid object error is written to logs.

Error Messages / Log Output

2026-03-20 14:48:45.176 [ERROR] [Collector]   [<Azure SQL Database name masked by user>] deadlocks SQL Error #208: Invalid object name 'sys.dm_xe_session_targets'.
2026-03-20 14:48:45.176 [ERROR] [RemoteCollectorService] Collector 'deadlocks' SQL error #208 for server '<Azure SQL Database name masked by user>' | SqlException: Invalid object name 'sys.dm_xe_session_targets'.

Screenshots

Additional Context

No response

[erikdarlingdata]

Thanks for the detailed report — the error confirms the collector is hitting the server-scoped sys.dm_xe_session_targets instead of the Azure SQL DB database-scoped sys.dm_xe_database_session_targets.

The code does have Azure SQL DB branching based on EngineEdition == 5, and the XE session creation appears to work correctly (you mentioned the sessions were created successfully). The collection step is what fails.

This could be a timing issue — if the first collection cycle fires before the engine edition is detected from the connection check, the code defaults to the on-prem path.

Could you share the first ~50 lines of the Lite log file from app startup (the lines that show server connectivity checks and engine edition detection)? Looking for entries like:

[ServerManager] Connectivity check passed for server '...'
[RemoteCollectorService] Created and started deadlock XE session (database-scoped, Azure SQL DB)

That will tell us if the edition is being detected before or after the first collection run.

[erikdarlingdata]

I just tested this against a fresh Azure SQL DB instance (pm-xe-test.database.windows.net) and cannot reproduce on the current dev build:

[XeSession] [Azure SQL DB] Created and started blocked process XE session (database-scoped)
[XeSession] [Azure SQL DB] Created and started deadlock XE session (database-scoped)
[Collector] [pm-xe-test.database.windows.net] deadlocks => 0 rows in 196ms
[Collector] [pm-xe-test.database.windows.net] blocked_process_report => 1 rows in 203ms

Both XE sessions were created as database-scoped, and the blocked process report was captured successfully. The code correctly detects EngineEdition 5 and uses sys.dm_xe_database_session_targets / sys.dm_xe_database_sessions.

A couple of questions:

1. Are you on v2.3.0 release or the nightly build? There have been fixes since 2.3.0 that affect Azure SQL DB collection.

2. Did you specify a database name in the connection settings? On Azure SQL DB, Lite needs to connect to the user database (not master) for the database-scoped XE sessions to work. If no database is specified, the connection defaults to master, and the XE sessions/DMVs wouldn't be found there.

3. Could you try the latest nightly build and see if the issue persists?

[erikdarlingdata]

Actually, to be more helpful — could you provide these specifics so I can try to reproduce your exact setup?

1. Connection settings: Did you specify a database name in the Lite connection, or leave it blank? (If blank, it connects to master on Azure SQL DB, which would explain the issue — the database-scoped XE sessions live in the user database, not master.)

2. Full startup log: The first 20-30 lines of your Lite log file from when the server was first added. Looking for the [ServerManager] Connectivity check and [XeSession] lines to see what EngineEdition was detected and whether the XE sessions were created as server-scoped or database-scoped.

3. Are you monitoring the logical server or a specific database? On Azure SQL DB, each database is isolated — you need one Lite connection per database you want to monitor. The XE sessions are database-scoped, so blocking in DatabaseA won't show up if you're connected to DatabaseB.

4. Nightly build: If you can test with the latest nightly, that would confirm whether this was already fixed in a post-2.3.0 change.

The error Invalid object name 'sys.dm_xe_session_targets' specifically means the code took the on-prem path instead of the Azure SQL DB path. The most common reason would be the EngineEdition not being detected as 5 at collection time.

[vpnicholls]

Ah, yes, you're right - this is a user error... I was connecting to the logical server and not the individual database.

I have edited the connection (in the main release, not the overnight build) to use the database. And I can now generate a block and get the report (without the original error showing in the logs).

Case closed now, I believe. But please let me know if you have any unanswered questions and would still like me to gather any of the diagnostics, etc.

Incidentally, my logs look much cleaner now. There were a few other things I had on my list that are no longer showing up.

[erikdarlingdata]

@vpnicholls No, that's fine. Glad it wasn't yet another thing that I thought I had fixed that I didn't fix. I did run into a similar issue some time ago in Azure testing and would have been very surprised if it were back for some reason. Thanks for reporting as always!