sp_dw_* and the Trident Procedures in SQL Server 2025

· by · in

I expect a system procedure with dw in its name in a Fabric SQL analytics endpoint, not in the regular SQL Server product. SQL Server 2025 has more of these procedures than SQL Server 2022 did. I cannot read most of the new definitions over the Dedicated Admin Connection (DAC).

A woman DBA closes a large survey book under a desk lamp, with map cards and pins around it and a distant warehouse visible through the window.

This post is part of the Undocumented Changes in SQL Server 2022 and 2025 series. The contents page lists every part in order.

This is the last SQL Server 2025 post in the undocumented system-object series. The comparison is SQL Server 2022 CU25 to SQL Server 2025 CU9, with a check against the SQL Server 2025 RTM inventory where it matters. CU means cumulative update. RTM means the initial release build. The first SQL Server 2025 count is in the first post on SQL Server 2025. The first post in the whole series is the SQL Server 2022 count.

This post follows the earlier FIDO procedure post. Both groups contain product-specific procedure names, a few readable SQL wrappers, and more extended stored procedures that do not expose their implementation through OBJECT_DEFINITION.

What changed in 2025

The 2019 inventory didn’t contain any object, column, wait type, spinlock, Extended Events event, message, permission or configuration option whose name or description matched sp_dw_, dm_dw_, trident, or TRIDENT_. SQL Server 2022 had 114 matching inventory rows. SQL Server 2025 RTM and CU9 each had 177. This part of the surface was unchanged between 2025 RTM and CU9 in my inventories.

At the object level, the 2022 inventory had 12 sys.dm_dw_* dynamic management views (DMVs), 2 sys.sp_dw_* procedures, and 6 procedure names containing trident. SQL Server 2025 CU9 has 11 sys.dm_dw_* DMVs, 18 sys.sp_dw_* procedures, and 12 procedure names containing trident.

Two 2022 objects are gone in 2025 CU9: sys.dm_dw_locks and sys.sp_dw_physical_manifest_file_table_insert. Its 2022 definition has a comment: “Note that the sys.dm_dw_locks DMV is NOT exposed to customers and is only intended for internal supportability.”

The 2025 additions include 22 procedures, 10 wait types, 38 Extended Events (XE) events, 7 messages, and 1 spinlock. XE means Extended Events. I didn’t find matching permissions or configuration options. I kept those categories in the map because they were part of the search scope.

The dm_dw_* views stayed mostly stable

The readable dm_dw_* definitions are wrappers over internal rowset providers. For example, sys.dm_dw_databases selects from OpenRowset(TABLE DM_DW_DATABASES), and sys.dm_dw_quality_index selects rounded quality columns from OpenRowset(TABLE DM_DW_QUALITY_INDEX). The transaction and resource-manager views use names such as DW_TRAN_MGR_COMMIT_CACHE and DW_RESOURCE_MGR_ACTIVE_TRAN.

That does not tell me whether the regular SQL Server product populates these views. The catalog contains wrappers for what appears to be a data warehouse subsystem. The wrappers use internal table providers instead of ordinary base tables.

The columns appear to contain support counters and cache state. The quality views expose object_id, index_id, partition_id, and quality measures. The transaction-manager views expose values such as bsn, csn, min_active_bsn, state, and tag. The removed sys.dm_dw_locks view exposed lock resource, mode, status, session, transaction, and request identifiers. It is the only dm_dw_* object in this group that disappears in 2025.

The readable Trident procedures are small wrappers

Only four matching procedures have SQL definitions in both 2022 and 2025: sys.sp_discover_trident_table, sys.sp_get_trident_data_location, sys.sp_set_trident_data_location, and sys.sp_drop_trident_data_location. sys.sp_discover_trident_table has a changed object hash in 2025, but after collapsing whitespace the definition is the same as 2022. It accepts @TableName nvarchar(256) and raises error 15817.

sys.sp_set_trident_data_location accepts @storagePath nvarchar(2000) and raises error 15817. sys.sp_get_trident_data_location and sys.sp_drop_trident_data_location both call sp_ensure_trident_frontend. They read or remove the server-level extended property named trident_database_data_storage_root_location.

I queried sys.all_parameters on the 2025 instance for the matching procedures. That read-only query returned parameter metadata for the two SQL stored procedures with arguments. It returned no parameter metadata for the extended stored procedures.

I did not execute any of these procedures. Several names sound like state changes, for example sys.sp_dw_attach_database, sys.sp_dw_detach_database, sys.sp_dw_submit_configuration_change, and sys.sp_trident_create_delta_checkpoint. I did not run them because their names suggest that they can change state. I need the test instance for later comparisons, so this post uses metadata only.

One sp_dw_* procedure is public, but for Fabric

Microsoft documents sys.sp_dw_refresh_ext_table, one of the new 2025 sp_dw_* names. The Learn page says it refreshes data for a specific table within a SQL analytics endpoint. Its Applies to list is SQL analytics endpoint in Microsoft Fabric, mirrored databases in Microsoft Fabric, and SQL database in Microsoft Fabric.[1]

This is consistent with the Fabric metadata-sync documentation. Microsoft says every lakehouse, warehouse, database, and mirrored database in Fabric has one SQL analytics endpoint. The endpoint reads Delta logs from OneLake to keep SQL metadata in sync. The same article says the new metadata sync can be refreshed by portal, REST API, or sys.sp_dw_refresh_ext_table for a specific table.[2]

The public sources use several related product names. They include Fabric Data Warehouse, Warehouse in Microsoft Fabric, SQL analytics endpoint in Microsoft Fabric, SQL database in Microsoft Fabric, mirrored databases in Microsoft Fabric, and OneLake. Microsoft describes Fabric Data Warehouse as a relational warehouse on a data lake foundation, with data stored in Delta tables and T-SQL support. Microsoft describes OneLake as the unified data lake for a Fabric tenant.[3]

I found the word Trident in public Microsoft documentation sources. I did not find a public source that explains the name in this SQL Server and Fabric data-warehouse context. The hits I found were SQL Server 2025 error-message rows and Fabric documentation metadata such as product-trident. Other hits included a Microsoft.Trident provider path in a REST API example and unrelated uses such as browser detection and NetApp Astra Trident. I am not expanding the name. I kept the terms as they appear in the Microsoft sources I searched.[4]

New Trident wait types, Extended Events and messages

The 2025 inventory adds wait types such as TRIDENT_COMMIT_NOTIFICATION, TRIDENT_ONELAKE_CLIENT_PROXY, TRIDENT_DW_REPORT_USAGE_PERIODIC_TASK, and PWAIT_TRIDENT_LINK_NOTIFICATION_WORKITEM_QUEUE. None of the 12 Trident-named wait types appeared in the parsed wait-type table from the live sys.dm_os_wait_stats page. The documentation repository search did not find TRIDENT_ wait names.[5]

The XE event names mention statistics, metadata refresh, Query Store collection, result set cache, resource-estimate feedback, Showplan, and bulk copy program (BCP) activity for Trident data warehouse paths. The event descriptions are short. They associate the internal name with terms such as OneLake, metadata refresh, statistics, result set cache, and Query Insight.

SQL Server 2025 includes message text for cloning a Trident data warehouse (DW) database. Other messages mention creating a table without a workspace on a Trident instance, Trident DW data definition language (DDL) operations, Trident DW BCP, and Trident result set cache recompiles.[6]

Where this leaves the series

The inventories found many undocumented system objects in SQL Server 2022. They also found many additions, removals, and renames in SQL Server 2025. See the SQL Server 2022 count and the SQL Server 2025 count. The SQL Server catalog includes names that resemble Fabric data warehouse terms. The sources I checked do not document how most of those names work in the regular SQL Server product.

The previous post covered the SQL Server 2025 high availability and disaster recovery (HADR) internal views. The removals post covers the broader set of objects that disappeared in SQL Server 2025, including sys.dm_dw_locks and sys.sp_dw_physical_manifest_file_table_insert: SQL Server 2025 removed system objects.

Have you found one of these names in monitoring, or do you know of a public source I missed? Tell me on Bluesky or LinkedIn, and I’ll update the notes.

References

  1. sys.sp_dw_refresh_ext_table (Transact-SQL) - Microsoft Learn. Documents the Fabric-only stored procedure and its syntax, applies-to list, remarks, and permissions. ↩
  2. SQL Analytics Endpoint Metadata Sync - Microsoft Learn. Describes Fabric SQL analytics endpoint metadata sync and the use of sys.sp_dw_refresh_ext_table for table refresh. ↩
  3. What Is Fabric Data Warehouse? and OneLake, the unified data lake - Microsoft Learn. Establishes the public Fabric Data Warehouse, SQL analytics endpoint, and OneLake terminology used in the post. ↩
  4. MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs, and MicrosoftDocs/fabric-docs - GitHub. The Markdown source of Microsoft Learn for SQL Server, Azure and Fabric, searched with whole-word, fixed-string git grep on 2026-09-26. The search results are my own. ↩
  5. sys.dm_os_wait_stats (Transact-SQL) - Microsoft Learn. Source page parsed for wait-type documentation status, with repository searches used for names absent from the table. ↩
  6. MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs, and MicrosoftDocs/fabric-docs - GitHub. The Markdown source of Microsoft Learn for SQL Server, Azure and Fabric, searched with whole-word, fixed-string git grep on 2026-09-26. The search results are my own. ↩
Tags: , , , ,