Undocumented DMVs Added in SQL Server 2022
A new dynamic management view (DMV) can look useful even when Microsoft Learn has no page for it, or when its page is for another product. DMVs are the sys.dm_* views and functions that expose server state.
In the first post in this series, I counted 45 SQL Server 2022 views and functions where I couldn’t find documentation. I checked Learn URLs and the Learn search API, and I searched the Microsoft documentation source repositories for SQL Server, Azure and Fabric.[1] Part 1 already mentioned the database external policy twins, sys.dm_external_policy_cache, the 12 sys.dm_dw_* views, sys.dm_column_encryption_enclave_properties, sys.dm_os_out_of_memory_events and sys.dm_database_backups, so I’m not going to re-cover them here.
This post covers most of the rest. sys.fn_filelog gets its own post later in the series, and I haven’t covered sys.dm_tran_orphaned_distributed_transactions. By name and definition, the objects here relate to request diagnostics, in-memory OLTP checks, governance metadata, change data capture (CDC), ledger, and a few names that SQL Server 2025 CU9 no longer has. CDC is the feature that records row changes for tracked tables.
Request-phase diagnostics
Four objects look related to distributed request tracking: sys.dm_dist_requests, sys.dm_request_phases, sys.dm_request_phases_exec_task_stats and sys.dm_request_phases_task_group_stats. All four exist in SQL Server 2022 CU25 and SQL Server 2025 CU9. A read-only SELECT COUNT_BIG(*) against each one on the 2025 instance completed and returned zero rows.
| Object | 2022 definition | 2022 columns | 2025 CU9 SELECT |
|---|---|---|---|
sys.dm_dist_requests |
Selects session_id, dist_statement_hash, dist_statement_id, dist_client_id from OpenRowset(TABLE SYSDISTREQUESTS). |
session_id smallint NOT NULL, dist_statement_hash binary NULL, dist_statement_id uniqueidentifier NULL, dist_client_id uniqueidentifier NULL |
Runs, 0 rows |
sys.dm_request_phases_task_group_stats |
Selects task group rows from OpenRowset(TABLE SYSREQUESTPHASESTASKGRPSTATS). |
dist_request_id uniqueidentifier NOT NULL, id nvarchar NOT NULL, dist_statement_id uniqueidentifier NOT NULL, state_desc nvarchar NOT NULL, start_time bigint NOT NULL, end_time bigint NOT NULL, input_dop int NOT NULL, output_dop int NOT NULL, operation_type nvarchar NULL, task_retries int NULL, parent_ids nvarchar NULL |
Runs, 0 rows |
sys.dm_request_phases_exec_task_stats |
Aggregates rows from OpenRowset(TABLE SYSREQUESTPHASESEXECTASKSTATS) by request and phase id. |
dist_request_id uniqueidentifier NOT NULL, id nvarchar NOT NULL, min_time_ms bigint NULL, max_time_ms bigint NULL, avg_time_ms bigint NULL, stdev_time_ms float NULL, total_bytes_processed bigint NULL, min_rows bigint NULL, max_rows bigint NULL, avg_rows bigint NULL, stdev_rows float NULL, total_rows bigint NULL, error_id nvarchar NULL |
Runs, 0 rows |
sys.dm_request_phases |
Joins the task group and execution task views, converts the internal timestamp columns to datetime, and adds elapsed time. |
The task group columns plus start_time datetime NULL, end_time datetime NULL, total_elapsed_time_ms bigint NULL, row counts, timing aggregates and error_id nvarchar NULL |
Runs, 0 rows |
The names and columns suggest a per-request phase timeline: phase ids, parent ids, input and output DOP, row movement counts, bytes processed and retry counts. DOP means degree of parallelism. These are inferences from the definitions and column names, not a published contract.
sys.dm_exec_requests_history sits near this group, but I did not count it as undocumented after the reclassification pass. Microsoft mentions it in one Azure Synapse known-issues workaround for serverless SQL pools.[2] Its SQL Server 2022 definition selects from master.sys.polaris_executed_requests_history and master.sys.polaris_executed_requests_text. The 2025 instance returned zero rows.
A cheaper-looking XTP hash index check
sys.dm_db_xtp_hash_index_approx_stats is notable because its parameters and columns resemble a bounded version of a documented hash index check. XTP is SQL Server’s in-memory OLTP engine. SQL Server already documents sys.dm_db_xtp_hash_index_stats for hash index bucket tuning.[3]
The same documented DMV scans the whole table, and Microsoft warns that it can take a long time on large tables. The undocumented 2022 function takes two optional integer parameters, @maxComputeTime and @maxRowsToScanPerBucket, then selects from OpenRowset(TABLE XTP_HASH_IDX_APPROX_STATS, @maxComputeTime, @maxRowsToScanPerBucket) and filters object_id > 0.
| Object | 2022 columns | 2025 CU9 SELECT |
|---|---|---|
sys.dm_db_xtp_hash_index_approx_stats |
object_id int NOT NULL, xtp_object_id int NOT NULL, index_id int NOT NULL, bucket_count bigint NOT NULL, scanned_bucket_count bigint NOT NULL, approx_filled_count bigint NOT NULL, approx_avg_chain_length bigint NOT NULL, approx_max_chain_length bigint NOT NULL, five bucket-range counters from 1_10 through greater_than_10000, and buckets_with_chain_length_greater_than_max_rows_to_scan_per_bucket bigint NOT NULL |
Runs, 0 rows on the 2025 instance |
The parameters and column names suggest a bounded approximation of the documented hash index stats. They may reduce the work required for a large memory-optimized table. I did not test that behaviour, and I did not find a public description for the approximation rules.
CDC exposes a DDL flag
sys.fn_cdc_is_ddl_handling_enabled is a scalar function. It takes @object_id int and returns bit. Its 2022 definition checks sys.fn_cdc_is_table_enabled(@object_id), then reads cdc.change_tables for a row where source_object_id matches, is_enable_extended_ddl_handling = 1 and is_active = 1.
Microsoft documents CDC setup. Enabling CDC creates the cdc schema, metadata tables and functions in the database.[4] The undocumented function did not run in master on the 2025 instance because cdc.change_tables did not exist there. The failed call only shows that the function expects cdc.change_tables to exist.
| Object | 2022 return shape | 2025 CU9 SELECT |
|---|---|---|
sys.fn_cdc_is_ddl_handling_enabled |
Scalar bit |
SELECT sys.fn_cdc_is_ddl_handling_enabled(OBJECT_ID(N'sys.objects')) failed in master with invalid object name cdc.change_tables |
I am not reading too much into the name. The function body only shows that one CDC metadata flag is involved.
Governance metadata and external classifications
SQL Server 2022 added Microsoft Purview access policy integration. The What’s New page points to Purview policy provisioning for SQL Server enabled by Azure Arc.[5] The public SERVERPROPERTY documentation also lists IsExternalGovernanceEnabled. That property returns whether Microsoft Purview access policies are enabled.[6]
The undocumented governance views split into two styles. sys.dm_external_governance_sync_state and sys.dm_database_external_governance_sync_state select from internal OpenRowset sources. The sys.external_governance_* catalog views read sys.sysobjvalues, require external governance to be enabled, and check permissions with has_perms_by_name.
| Object | 2022 columns | 2025 CU9 SELECT |
|---|---|---|
sys.dm_external_governance_sync_state |
20 columns in 2022 (19 in 2025 CU9): sync token, blob reference, database id, sync state, sync scope, percent complete and last attempt/success/error timestamps | Runs, 0 rows |
sys.dm_database_external_governance_sync_state |
Same shape: 20 columns in 2022, 19 in 2025 CU9 (current_blob_references is gone from both) |
Runs, 0 rows |
sys.external_governance_classification_attributes |
object_id int NOT NULL, type char NULL, type_desc nvarchar NULL, object_attributes nvarchar NULL |
Runs, 0 rows |
sys.external_governance_classifications |
classification nvarchar NULL, classification_id uniqueidentifier NULL |
Runs, 0 rows |
sys.external_governance_classifications_mapping |
class int NOT NULL, class_desc varchar NOT NULL, major_id int NOT NULL, minor_id int NOT NULL, classification_id uniqueidentifier NULL |
Runs, 0 rows |
sys.external_governance_sensitivity_classifications |
class int, class_desc, major_id, minor_id, label, label id, information type, information type id, rank and rank description |
Runs, 0 rows |
sys.external_governance_sensitivity_labels |
label nvarchar NULL, label_id uniqueidentifier NULL |
Runs, 0 rows |
sys.external_governance_sensitivity_labels_mapping |
class int NOT NULL, class_desc varchar NOT NULL, major_id int NOT NULL, minor_id int NOT NULL, label_id uniqueidentifier NULL |
Runs, 0 rows |
The names overlap with SQL Data Discovery and Classification terms such as labels and information types.[7] The definitions also show separate sys.sysobjvalues value classes for external governance label and information type metadata. I couldn’t find reference pages for these views.
Microsoft says Purview access policies are discontinued in SQL Server 2025, with fixed server roles as the replacement.[8] The catalog objects still exist in SQL Server 2025 CU9, but a catalog object is not proof that the feature is active.
Objects that vanished by 2025
Five of the undocumented 2022 objects I checked are gone from SQL Server 2025 CU9: sys.dm_toad_tuning_zones, sys.dm_toad_work_items, sys.dm_toad_work_item_handlers, sys.dm_xcs_enumerate_blobdirectory and sys.fn_xcs_get_file_rowcount. I did not find a public source for the TOAD or XCS names, so I am leaving them as names.
| Object | 2022 definition | 2022 columns | 2025 CU9 SELECT |
|---|---|---|---|
sys.dm_toad_tuning_zones |
Selects all columns from OpenRowset(TABLE DM_TOAD_TUNING_ZONES). |
zone_type nvarchar NOT NULL, entry_data nvarchar NULL, priority_score bigint NOT NULL, actions_discovered bigint NOT NULL, actions_scheduled bigint NOT NULL, total_actions_scheduled bigint NOT NULL |
Object not found |
sys.dm_toad_work_items |
Selects all columns from OpenRowset(TABLE DM_TOAD_WORK_ITEMS). |
message_id int NOT NULL, producer_id int NOT NULL, producer_type nvarchar NOT NULL, workitem_type nvarchar NOT NULL, workitem_version int NOT NULL, body_size int NOT NULL, retry_count int NOT NULL |
Object not found |
sys.dm_toad_work_item_handlers |
Selects all columns from OpenRowset(TABLE DM_TOAD_WORK_ITEM_HANDLERS). |
message_id int NOT NULL, producer_id int NOT NULL, producer_type nvarchar NOT NULL, workitem_type nvarchar NOT NULL, task_address varbinary NOT NULL, state int NOT NULL |
Object not found |
sys.dm_xcs_enumerate_blobdirectory |
Takes container path, data source, token, object id and partition inference parameters, then reads OpenRowset(TABLE DM_XCS_ENUM_BLOBDIRECTORY, ...). |
container_path nvarchar NULL, relative_path nvarchar NULL, etag nvarchar NULL, size_in_bytes bigint NOT NULL, last_modified_date datetime NOT NULL, part_key_name nvarchar NULL, part_key_val nvarchar NULL |
Object not found |
sys.fn_xcs_get_file_rowcount |
Takes object, container, file and token parameters, then reads OpenRowset(TABLE FN_XCS_GET_FILE_ROWCOUNT, ...). |
row_count bigint NULL |
Object not found |
These five objects were present in the SQL Server 2022 definitions I checked and absent from SQL Server 2025 CU9. Scripts that use undocumented objects can break when those objects change or disappear.
Ledger and external table leftovers
sys.fn_ledger_retrieve_digests_from_url exists in both 2022 and 2025. It takes a path and returns one digest nvarchar NULL column from OpenRowset(TABLE LEDGER_DIGESTS_FROM_URL, @path). SQL Server ledger is documented for SQL Server 2022 and later. Microsoft describes it as a tamper-evidence feature for database data.[9] A read-only probe with an empty path on the 2025 instance failed with “Invalid path specified for a ledger digest URL,” so I did not get a row count.
sys.external_table_partitioning_columns also exists in both versions. It returns object_id int NOT NULL, column_id int NOT NULL and ordinal_id bigint NULL. The definition reads sys.sysobjvalues rows with value class 156, which the definition comments name as external table virtual columns. On the 2025 instance it returned zero rows.
I also found sys.external_stream_columns, with object_id int NOT NULL and column_id int NOT NULL. Its definition joins sys.columns to sys.objects$ where the object type is ES. It still exists in 2025 CU9 and returned zero rows in my test. I am not treating that as evidence that SQL Server supports creating those objects in the tested edition.
What I might actually use
I am not building automation on these without a support statement. For troubleshooting, I am still keeping two names in my notebook.
The first is sys.dm_db_xtp_hash_index_approx_stats, because it looks like a bounded version of an already documented memory-optimized hash index check. The second is sys.fn_cdc_is_ddl_handling_enabled, because its definition exposes a CDC metadata flag that I did not find in public documentation.
For the remaining objects, I treat the definitions as information about SQL Server internals rather than as supported troubleshooting interfaces. Five of them are already gone in SQL Server 2025 CU9, so I wouldn’t build automation on the TOAD or XCS names.
Next, I am looking at undocumented wait types in SQL Server 2022.
Have you seen one of these objects return rows on a real workload, or do you know of documentation my searches missed? Tell me on Bluesky or LinkedIn, and I’ll update the notes.
References
- MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. The Markdown source of Microsoft Learn for SQL Server, Azure and Fabric. I searched these repositories for each featured object name as a whole word on 2026-09-26. ↩
- Known issues - Azure Synapse Analytics - Microsoft Learn. Mentions
sys.dm_exec_requests_historyas a workaround for viewing historical query execution details in Synapse serverless SQL pool. ↩ - sys.dm_db_xtp_hash_index_stats (Transact-SQL) - Microsoft Learn. Documents the supported XTP hash index DMV and warns that it scans the whole table. ↩
- Enable and Disable change data capture - SQL Server - Microsoft Learn. Describes CDC setup and the metadata objects created when CDC is enabled. ↩
- What’s New in SQL Server 2022 - Microsoft Learn. Lists Microsoft Purview integration and Purview access policies as SQL Server 2022 features. ↩
- SERVERPROPERTY (Transact-SQL) - Microsoft Learn. Documents
IsExternalGovernanceEnabledas the property that reports whether Microsoft Purview access policies are enabled. ↩ - SQL Data Discovery & Classification - SQL Server - Microsoft Learn. Describes labels and information types used by SQL data classification. ↩
- What’s New in SQL Server 2025 - Microsoft Learn. States that Purview access policies are discontinued in SQL Server 2025 and points to fixed server roles instead. ↩
- Ledger Overview - SQL Server - Microsoft Learn. Describes ledger as a tamper-evidence feature and lists SQL Server 2022 and later under Applies to. ↩