[BUG] On v2.3.0 SQL Dumps on Passive Mirrored SQL DB

[jaywiseman1971]

Component

Full Dashboard

Performance Monitor Version

2.3.0

SQL Server Version

SQL 2016

Windows Version

Windows Server 2016

Describe the Bug

v2.3.0.zip

Steps to Reproduce

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

Expected Behavior

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

Actual Behavior

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

Error Messages / Log Output

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

Screenshots

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

Additional Context

On v2.3.0 SQL Dumps on Passive Mirrored SQL DB, see attached.

[erikdarlingdata]

Thanks for the dump files. I've analyzed all three and they show the same pattern:

• spid 19s (system spid, not a user connection)
• Severity 22, major=52, minor=42, state=9
• All three dumps within 3 seconds of each other (18:47:50-53)
• No PerformanceMonitor collector activity in the error log near the dump time
• The only PerformanceMonitor reference is a failed log backup at 18:00 (47 minutes before the dump)

The s suffix on spid19s indicates this is an internal SQL Server system thread, not a user query from our collectors. Our collector connections show up as regular spids without the s suffix.

The dump files are mini dumps that only captured the dump generation thread — the actual faulting stack and input buffer aren't available in the .mdmp files.

To help narrow this down further, could you provide:

1. The full SQL Server ERRORLOG (not just the dump excerpts) from the day of the crash — specifically looking for any error messages in the 18:40-18:48 window before the dump
2. The output of SELECT * FROM PerformanceMonitor.config.collection_log WHERE collection_time >= '2026-03-18 18:40' AND collection_time <= '2026-03-18 18:50' ORDER BY collection_time — this will show if any of our collectors were running at crash time
3. The mirroring state — is this the passive/mirror partner where mirrored databases show as RESTORING? If so, which databases are mirrored?

The v2.3.0 database size collector (the one that caused the previous dumps in #535) has d.state = 0 and HAS_DBACCESS() filters that should skip RESTORING databases. But I want to confirm our collectors weren't involved before ruling it out.

[jaywiseman1971]

Attached is the dump file, 100 mb (zipped to 12 mb) and the query results.

Query.csv
sqldumpfile.zip

This server is in passive mode on the database customer.

Best, Jay

[erikdarlingdata]

Thanks Jay — the collection log is very revealing.

Finding

The dump at 18:47:50 correlates exactly with the query_stats_collector finishing:

Time	Collector	Rows	Duration
18:47:03	query_snapshots_collector	1	1,032ms
18:47:50	query_stats_collector	84,487	46,257ms
18:47:50	DUMP — severity 22
18:47:50	query_store_collector	0	344ms

The query_stats_collector returned 84,487 rows in 46 seconds — and the dump happened at the exact same second it completed.

Probable cause

The query stats collector uses CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) to resolve which database each cached plan belongs to. This gets called for every plan in the cache before the d.state = 0 filter removes RESTORING databases. With 84K+ rows, the DMV is being invoked against plan handles that may reference the mirrored/RESTORING database — and on SQL 2016, accessing plan attributes for a RESTORING database can trigger the severity 22 engine crash.

The state filter at WHERE d.state = 0 is an INNER JOIN that runs after the CROSS APPLY — it doesn't prevent the DMV call itself.

Fix approach

We need to filter RESTORING databases before the plan_attributes CROSS APPLY, or wrap the CROSS APPLY in a TRY/CATCH, or pre-filter plan handles using sys.dm_exec_plan_attributes only for databases that are ONLINE.

I'll have a fix ready shortly.

[erikdarlingdata]

Root Cause Confirmed

The collection log you provided pinpoints it exactly. The query_stats_collector finished at 18:47:50 — the same second as the severity 22 dump:

18:47:50  query_stats_collector  84,487 rows  46,257ms  ← CRASH HERE

The collector uses CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) to resolve which database each cached query plan belongs to. This DMV call executes for every plan in the cache (84K+ plans on your server) — including plans compiled in the mirrored/RESTORING database. SQL Server raises a severity 22 fatal error when dm_exec_plan_attributes touches a plan handle associated with a RESTORING database.

The WHERE d.state = 0 filter runs AFTER the CROSS APPLY, so it can't prevent the DMV call from happening.

Proposed Fix Options

We have two approaches to test. Both avoid calling dm_exec_plan_attributes entirely by resolving the database from sys.dm_exec_sql_text instead, which carries the dbid directly on the sql_handle metadata.

Could you test both of these against your passive mirror server and let us know which one (or both) avoids the dumps?

---

Option A: Replace plan_attributes with sql_text dbid

This removes the dm_exec_plan_attributes CROSS APPLY and gets the database_id from sys.dm_exec_sql_text instead, which we're already calling for query text.

Replace the current query in 08_collect_query_stats.sql (the main SELECT starting around line 313) — specifically change:

-- REMOVE THIS:
CROSS APPLY
(
    SELECT
        dbid = CONVERT(integer, pa.value)
    FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
    WHERE pa.attribute = N'dbid'
) AS pa
INNER JOIN sys.databases AS d
  ON pa.dbid = d.database_id
WHERE qs.last_execution_time >= @cutoff_time
AND   d.state = 0
AND   pa.dbid NOT IN
(
    1, 2, 3, 4, 32761, 32767,
    DB_ID(N'PerformanceMonitor')
)
AND   pa.dbid < 32761

-- REPLACE WITH:
INNER JOIN sys.databases AS d
  ON st.dbid = d.database_id
WHERE qs.last_execution_time >= @cutoff_time
AND   d.state = 0
AND   st.dbid NOT IN
(
    1, 2, 3, 4, 32761, 32767,
    DB_ID(N'PerformanceMonitor')
)
AND   st.dbid < 32761

The st alias is from the existing OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st that's already in the query.

---

Option B: Pre-filter with a temp table of ONLINE database IDs

If Option A doesn't resolve it (because dm_exec_sql_text might also have issues), this approach pre-builds a list of safe database IDs and filters before any DMV calls:

Add before the main query:

CREATE TABLE #online_databases (database_id int NOT NULL PRIMARY KEY);

INSERT INTO #online_databases (database_id)
SELECT d.database_id
FROM sys.databases AS d
WHERE d.state = 0
AND   d.database_id NOT IN (1, 2, 3, 4, 32761, 32767, DB_ID(N'PerformanceMonitor'))
AND   d.database_id < 32761;

Then in the main query, replace the plan_attributes CROSS APPLY + sys.databases join with:

INNER JOIN #online_databases AS od
  ON st.dbid = od.database_id

And drop the temp table after:

DROP TABLE IF EXISTS #online_databases;

---

How to test

1. Apply Option A first (simpler change)
2. Run EXEC collect.query_stats_collector @debug = 1; manually
3. If no dump occurs, let it run on the Agent schedule for a few hours
4. If Option A still dumps, try Option B
5. Let us know which one works

Thanks for your patience on this — the collection log data was exactly what we needed to find it.

[erikdarlingdata]

Quick addendum — both options above should also add HAS_DBACCESS(d.name) = 1 to the sys.databases join, matching the pattern used in all other collectors. Updated Option A example:

INNER JOIN sys.databases AS d
  ON st.dbid = d.database_id
  AND d.state = 0
  AND HAS_DBACCESS(d.name) = 1
WHERE qs.last_execution_time >= @cutoff_time
AND   st.dbid NOT IN
(
    1, 2, 3, 4, 32761, 32767,
    DB_ID(N'PerformanceMonitor')
)
AND   st.dbid < 32761

Same addition for Option B's temp table population:

INSERT INTO #online_databases (database_id)
SELECT d.database_id
FROM sys.databases AS d
WHERE d.state = 0
AND   HAS_DBACCESS(d.name) = 1
AND   d.database_id NOT IN (1, 2, 3, 4, 32761, 32767, DB_ID(N'PerformanceMonitor'))
AND   d.database_id < 32761;

[jaywiseman1971]

Can you send the actual updated script you want me to replace on the install directory so I can run it again as an upgrade?

Best, Jay

[erikdarlingdata]

Here's the updated script you can drop in directly:

Download 08_collect_query_stats.sql (click "Download raw file" button on that page)

To apply:

1. Replace install\08_collect_query_stats.sql in your PerformanceMonitor installation directory
2. Run the installer as an upgrade (no --reinstall): PerformanceMonitorInstaller.exe YOURSERVER sa "password" --trust-cert --encrypt=optional
3. Or just run the script directly in SSMS against the PerformanceMonitor database

What changed:

• Removed CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) — the DMV call that was hitting every plan in the cache including plans from your RESTORING mirrored database
• Gets the database_id from sys.dm_exec_sql_text instead (already being called, no extra overhead)
• Added HAS_DBACCESS(d.name) = 1 filter to match all other collectors

Let us know if the dumps stop after applying this.

[jaywiseman1971]

Started dumping again, here's the script output and zip file based on me using your new 08 script.

tweakapplied.zip

D:\hold>cd PerformanceMonitorInstaller-2.3.0

D:\hold\PerformanceMonitorInstaller-2.3.0>PerformanceMonitorInstaller.exe lawrence --trust-cert

Performance Monitor Installation Utility v2.3.0+876dd0be04679ba3d94eb37d6060c5ae9ca1dcfa
Copyright c 2026 Darling Data, LLC
Licensed under the MIT License
https://github.com/erikdarlingdata/PerformanceMonitor

Server: lawrence
Authentication: Windows

Testing connection...
Connection successful!

Found 54 SQL files in: D:\hold\PerformanceMonitorInstaller-2.3.0\install
Using new folder structure (install/ subfolder)

Automated mode: Performing upgrade (preserving existing database)...

Existing installation detected: v2.3.0.0
Checking for applicable upgrades...
No pending upgrades found.

================================================================================
Starting installation...

Executing 01_install_database.sql... √ Success
Executing 02_create_tables.sql... √ Success
Executing 03_create_config_tables.sql... √ Success
Executing 04_create_schedule_table.sql... √ Success
Executing 05_delta_framework.sql... √ Success
Executing 06_ensure_collection_table.sql... √ Success
Executing 07_collect_wait_stats.sql... √ Success
Executing 08_collect_query_stats.sql... √ Success
Executing 09_collect_query_store.sql... √ Success
Executing 10_collect_procedure_stats.sql... √ Success
Executing 11_collect_query_snapshots.sql... √ Success
Executing 12_query_snapshots_create_views.sql... √ Success
Executing 13_query_snapshots_retention.sql... √ Success
Executing 14_collect_memory_stats.sql... √ Success
Executing 15_collect_memory_grant_stats.sql... √ Success
Executing 16_collect_memory_clerks_stats.sql... √ Success
Executing 17_collect_cpu_scheduler_stats.sql... √ Success
Executing 18_collect_cpu_utilization_stats.sql... √ Success
Executing 19_collect_perfmon_stats.sql... √ Success
Executing 20_collect_file_io_stats.sql... √ Success
Executing 21_setup_blocked_process_xe.sql... √ Success
Executing 22_collect_blocked_processes.sql... √ Success
Executing 23_process_blocked_process_xml.sql... √ Success
Executing 24_collect_deadlock_xml.sql... √ Success
Executing 25_process_deadlock_xml.sql... √ Success
Executing 26_blocking_deadlock_analyzer.sql... √ Success
Executing 27_collect_ring_buffer_events.sql... √ Success
Executing 28_collect_system_health_wrapper.sql... √ Success
Executing 29_collect_default_trace.sql... √ Success
Executing 30_collect_trace_management.sql... √ Success
Executing 31_collect_trace_analysis.sql... √ Success
Executing 32_collect_latch_stats.sql... √ Success
Executing 33_collect_spinlock_stats.sql... √ Success
Executing 34_collect_tempdb_stats.sql... √ Success
Executing 35_collect_plan_cache_stats.sql... √ Success
Executing 36_collect_session_stats.sql... √ Success
Executing 37_collect_waiting_tasks.sql... √ Success
Executing 38_collect_server_configuration.sql... √ Success
Executing 39_collect_database_configuration.sql... √ Success
Executing 41_schedule_management.sql... √ Success
Executing 42_scheduled_master_collector.sql... √ Success
Executing 43_data_retention.sql... √ Success
Executing 44_hung_job_monitor.sql... √ Success
Executing 45_create_agent_jobs.sql... √ Success
Executing 46_create_query_plan_views.sql... √ Success
Executing 47_create_reporting_views.sql... √ Success
Executing 48_create_additional_reporting_views.sql... √ Success
Executing 49_health_parser_tables.sql... √ Success
Executing 50_configuration_issues_analyzer.sql... √ Success
Executing 51_collect_running_jobs.sql... √ Success
Executing 52_collect_database_size_stats.sql... √ Success
Executing 53_collect_server_properties.sql... √ Success
Executing 54_create_finops_views.sql... √ Success

================================================================================
Installing community dependencies...

Installing sp_WhoIsActive... √ Success
Query activity monitoring by Adam Machanic (GPLv3)
Installing DarlingData... √ Success
sp_HealthParser, sp_HumanEventsBlockViewer, and others by Erik Darling (MIT)
Installing First Responder Kit... √ Success
sp_BlitzLock and other diagnostic tools by Brent Ozar Unlimited (MIT)

Dependencies installed: 3/3
Executing 98_validate_installation.sql... √ Success

================================================================================
File Execution Summary

Installation: 54 succeeded, 0 failed

================================================================================
Running initial collection to validate installation...

Executing master collector...

[erikdarlingdata]

Option A (dm_exec_sql_text) wasn't enough — the engine still crashes touching sql_text for RESTORING database handles. Switching to the temp table approach.

Download updated 08_collect_query_stats.sql (click "Download raw file")

What changed this time:

• Pre-builds a temp table of ONLINE database IDs (#online_databases) using d.state = 0 + HAS_DBACCESS(d.name) = 1 before any DMV calls happen
• The main query joins to this temp table instead of sys.databases
• No dm_exec_plan_attributes call at all — removed entirely
• The database filter happens before SQL Server evaluates any plan-cache DMVs

Same install process — drop the file in install\ and run the installer, or execute it directly in SSMS.

[erikdarlingdata]

Reproduction Attempt — No Dumps

I spent time building a dedicated mirroring test environment to try to reproduce this. Here's what was done:

Setup:

• Cloned our SQL Server 2016 SP3 Hyper-V VM (Developer Edition)
• Renamed the clone, fixed the SQL Server instance name, disabled pre-existing Agent jobs
• Created a test database (MirrorTest) in FULL recovery on the principal
• Configured certificate-based endpoint authentication on both instances
• Established synchronous database mirroring — MirrorTest confirmed as SYNCHRONIZED/RESTORING on the mirror
• Installed PerformanceMonitor v2.3.0 on the mirror instance (passive/secondary) via the CLI installer
• Connected the Dashboard to the mirror instance
• Deployed the old query_stats_collector (with CROSS APPLY sys.dm_exec_plan_attributes) to specifically test the suspected root cause

Testing:

• Let the collection Agent job run on the normal 1-minute schedule against the mirror instance for an extended period
• Generated ~1,000 unique plan cache entries across multiple databases (DBAtools, hammerdb_tpcc, StackOverflow2010) using dynamic SQL to give the collector real work
• Ran the Dashboard simultaneously to test app-side queries
• Monitored for dumps via sys.dm_server_memory_dumps, sp_readerrorlog, and the file system (SQLDump*.mdmp files)

Result:

• Zero stack dumps produced
• No severity 22 errors
• No errors of any kind from the collectors or Dashboard queries
• The old dm_exec_plan_attributes code ran repeatedly against an instance with a RESTORING mirrored database without issue

Environment difference:

• Our test is on 13.0.6300.2 (SP3 base), yours is on 13.0.7029.3 (SP3 + Azure Connect Pack GDR). The GDR build difference could be a factor, but it's speculative.

Conclusion:
We were unable to reproduce the dump in a clean mirroring environment with the suspected collector code. The original dump was on spid 19s (a system thread, not a user connection), which means it may not be caused by our collector queries at all — the timing correlation could be coincidental.

At this point, the options I can offer are:

1. You investigate further on your end — if the dumps continue, capture the full ERRORLOG for the entire day (not just the dump window) and the output of SELECT * FROM sys.dm_server_memory_dumps. If you can identify what's different about your environment that triggers this, I'm happy to look at a fix.
2. Paid support engagement — if you'd like me to look at your servers directly, that's available through Performance Monitor Support.
3. Stop monitoring the secondary — if the dumps only happen on the passive mirror, the simplest path may be to only run PerformanceMonitor on the principal. Something specific to your environment on that server is causing these dumps, and it doesn't present in our testing.

The dm_exec_plan_attributes removal is still a good defensive change that I'll keep in the codebase regardless, since there's no reason to use it when dm_exec_sql_text already provides the dbid. But I can't confirm it's the fix for your specific issue since we couldn't reproduce the problem in the first place.

[jaywiseman1971]

Option A (dm_exec_sql_text) wasn't enough — the engine still crashes touching sql_text for RESTORING database handles. Switching to the temp table approach.

Better results, still dumping but not as much as before and it seems to stop dumping vs. consistently dumping.

2ndchangescript.zip

Best, Jay

[erikdarlingdata]

Root Cause Found — Not PerformanceMonitor

Jay, I analyzed your dump files from 2ndchangescript.zip using SQLDbgEng from Sean Gallardy to resolve the stack addresses against Microsoft's public symbol server. The fully symbolized call stack from SQLDump0001.mdmp is:

sqlmin!GhostRecordCleanupTask
sqlmin!CGhostCleanupTask::ProcessTskPkt
sqlmin!GhostExorciser::CleanupPage
sqlmin!CleanVersionsOnBTreePage
sqlmin!RemoveBTreePageIfUnchanged
sqlmin!RemoveBTreePageIfUnchangedInternal
sqlmin!IndexPageManager::GetPageForLinkModification
sqlmin!RaiseInconsistencyError        ← corruption detected here
sqldk!ex_raise
sqldk!ex_raise2
sqldk!ex_dump_if_requested            ← stack dump triggered

This is database corruption, not a PerformanceMonitor issue.

The Ghost Cleanup task (GhostExorciser) is a SQL Server internal background thread (which is why it shows up as spid 19s — a system spid, not a user connection). Its job is to remove deleted "ghost" records from index pages. When it encounters a corrupted index page, it raises a severity 22 RaiseInconsistencyError and triggers the stack dump.

This explains:

• Why the dumps are on a system spid (spid 19s), not a user query
• Why our query_stats_collector timing correlation was coincidental
• Why we couldn't reproduce it on our mirroring test environment (our databases aren't corrupted)
• Why the temp table fix "reduced but didn't eliminate" the dumps — the remaining dumps were from Ghost Cleanup, not our collectors

What you need to do

1. Run DBCC CHECKDB on every database on that instance, including system databases:

   DBCC CHECKDB('master') WITH NO_INFOMSGS;
   DBCC CHECKDB('msdb') WITH NO_INFOMSGS;
   DBCC CHECKDB('Customers') WITH NO_INFOMSGS;  -- or check on the principal if this is RESTORING
   DBCC CHECKDB('PerformanceMonitor') WITH NO_INFOMSGS;
   -- repeat for all user databases

2. If CHECKDB finds corruption, restore the affected database from a known good backup
3. If CHECKDB itself crashes (severity 21 kill state), that database is the corrupted one — try DBCC CHECKDB('dbname') WITH PHYSICAL_ONLY first to narrow it down
4. After resolving the corruption, investigate the root cause — check storage health (Dell PERC controller logs on your R740xd), Windows event logs for disk errors, and firmware versions

This is a known pattern — see this Reddit thread where the identical GhostExorciser → IndexPageManager → RaiseInconsistencyError stack was caused by MSDB corruption.

I'm going to close this issue since the dumps are not caused by PerformanceMonitor. If CHECKDB comes back clean on all databases, re-open and we can dig further — but the symbolized stack is unambiguous.