Data Management Views (DMVs) Comparison for SQL Server 2019 and Azure SQL Database

By:   |   Updated: 2024-04-08   |   Comments   |   Related: > Dynamic Management Views and Functions


I am used to reviewing Data Management Views (DMVs) on my SQL Server 2019 Instance, either on-prem or on a VM in the cloud. Now, my SQL databases have been migrated to Azure SQL Databases, and some of those DMVs I've been using no longer exist or do not return any valuable data. What DMVs were added to Azure SQL Databases, and which DMVs are missing from Azure SQL Databases but still exist on SQL Server 2019 or other versions?


First, let's understand which DMVs exist on the SQL Server instance and which on the Azure SQL Database.

To get the list of all the DMVs on any instance, we can execute the following query once from any database:

SELECT schema_name(schema_id) as schema_name, name
FROM sys.system_objects
WHERE name LIKE 'dm_%' and type = 'V'

If we run the above query on a SQL Server 2019 instance, we will get 275 results; on Azure SQL database, we will get 251.

Let's see which ones are different:

Exist Only on a SQL Server Instance Exist Only on Azure SQL Database
dm_broker_connections dm_change_feed_errors
dm_cache_hit_stats dm_change_feed_log_scan_sessions
dm_cache_size dm_cloud_database_epoch
dm_cache_stats dm_column_encryption_enclave_properties
dm_column_encryption_enclave_operation_stats dm_continuous_copy_status
dm_cryptographic_provider_properties dm_database_backup_lineage
dm_db_external_language_stats dm_database_backups
dm_db_external_script_execution_stats dm_database_engine_configurations
dm_db_mirroring_auto_page_repair dm_database_external_governance_sync_state
dm_db_mirroring_connections dm_database_external_policy_actions
dm_db_mirroring_past_actions dm_database_external_policy_principal_assigned_actions
dm_db_rda_migration_status dm_database_external_policy_principals
dm_db_rda_schema_update_status dm_database_external_policy_role_actions
dm_distributed_exchange_stats dm_database_external_policy_role_members
dm_exec_query_parallel_workers dm_database_external_policy_roles
dm_filestream_non_transacted_handles dm_database_hs_log_rate
dm_hadr_ag_threads dm_database_managed_identities
dm_hadr_auto_page_repair dm_database_replica_states
dm_hadr_automatic_seeding dm_db_data_pool_nodes
dm_hadr_availability_group_states dm_db_objects_impacted_on_version_change
dm_hadr_availability_replica_states dm_db_resource_governor_configuration
dm_hadr_cluster dm_db_resource_stats
dm_hadr_cluster_members dm_db_storage_pool_nodes
dm_hadr_cluster_networks dm_db_wait_stats
dm_hadr_database_replica_cluster_states dm_db_workload_group_resource_stats
dm_hadr_database_replica_states dm_db_xtp_undeploy_status
dm_hadr_db_threads dm_dist_requests
dm_hadr_instance_node_map dm_dw_quality_clustering
dm_hadr_name_id_map dm_dw_quality_delta
dm_hadr_physical_seeding_stats dm_dw_quality_index
dm_io_backup_tapes dm_dw_quality_row_group
dm_io_cluster_shared_drives dm_elastic_pool_resource_stats
dm_io_cluster_valid_path_names dm_exec_distributed_tasks
dm_logpool_hashentries dm_exec_requests_history
dm_logpool_stats dm_external_data_processed
dm_os_buffer_pool_extension_configuration dm_external_governance_sync_state
dm_os_child_instances dm_external_governance_synchronizing_objects
dm_os_cluster_nodes dm_external_policy_cache
dm_os_cluster_properties dm_external_policy_excluded_role_members
dm_os_enumerate_fixed_drives dm_geo_replication_link_status
dm_os_host_info dm_io_network_traffic_stats
dm_os_loaded_modules dm_operation_status
dm_os_process_memory dm_os_memory_allocations_filtered
dm_os_server_diagnostics_log_configurations dm_os_memory_health_history
dm_os_sys_memory dm_os_out_of_memory_events
dm_os_virtual_address_dump dm_os_parent_block_descriptors
dm_os_windows_info dm_pal_ring_buffers
dm_pal_cpu_stats dm_request_phases
dm_pal_disk_stats dm_resource_governor_resource_pools_history_ex
dm_pal_net_stats dm_resource_governor_workload_groups_history_ex
dm_pal_processes dm_server_external_policy_actions
dm_pal_spinlock_stats dm_server_external_policy_principal_assigned_actions
dm_pal_vm_stats dm_server_external_policy_principals
dm_pal_wait_stats dm_server_external_policy_role_actions
dm_repl_articles dm_server_external_policy_role_members
dm_repl_schemas dm_server_external_policy_roles
dm_repl_tranhash dm_server_managed_identities
dm_repl_traninfo dm_tran_distributed_transaction_stats
dm_resource_governor_configuration dm_tran_orphaned_distributed_transactions
dm_resource_governor_external_resource_pool_affinity dm_user_db_resource_governance
dm_resource_governor_external_resource_pools dm_xe_database_session_event_actions
dm_resource_governor_resource_pool_affinity dm_xe_database_session_events
dm_resource_governor_resource_pool_volumes dm_xe_database_session_object_columns
dm_server_audit_status dm_xe_database_session_targets
dm_server_memory_dumps dm_xe_database_sessions

Microsoft provides documentation about most of the above DMVs for our convenience. However, some of the new or unused DMVs are still not documented. We can only assume by their name what information they may or will contain in the future, but we will need to query them to check if they contain any valuable data.

To make it easier, this tip will explain each DMV on Azure SQL Databases (if documented) and have been grouped by specific categories:

  1. Performance
  2. High Availability, Disaster Recovery, Backup, and Maintenance
  3. Resource Governor
  4. Extended Events
  5. Security
  6. Synapse Link
  7. Compatible with Synapse
  8. Undocumented or Not Supported by the Azure SQL Database Version - I will try to make some assumptions about what the DMV is about, if possible.

The following sections contain descriptions of each DMV (if officially documented) and a link to the Microsoft documentation.

In future tips, I will provide some examples around the important DMVs below, per category, so stay tuned!

Category #1: Performance

The performance DMVs assist us in monitoring specific performance issues.

DMV Name / Link to Documentation Sub Category Description
dm_db_resource_stats Performance Returns CPU, I/O, and memory consumption for a database in Azure SQL Database
dm_db_wait_stats Performance Returns information about all the waits encountered by threads that are currently being executed
dm_elastic_pool_resource_stats Performance Returns resource usage statistics for the elastic pool containing the current database on an Azure SQL Database logical server. This includes CPU, Data IO, Log IO, storage consumption, and concurrent request/session utilization by the pool. The view returns the same data in any database in the same elastic pool.
dm_os_out_of_memory_events Performance Returns a log of out of memory (OOM) events.

Category #2: High Availability (HA), Disaster Recovery (DR), Backup, and Maintenance

The Microsoft documentation about Azure SQL Database HA and DR states, "Azure SQL Database service automatically ensures all the databases are online, healthy, and constantly strives to achieve the published SLA."

It talks about the different types of availability configurations for Azure SQL Databases.

In a nutshell, Azure SQL Database automatically maintains availability, but you can have additional setup, such as:

  • Enable failover groups for DR or reporting purposes.
  • Enable geo-replication to have your readable secondary in a different Azure region.

These views will return information about the current setup.

For Azure SQL database backups, they are automatically managed by Azure, and the backup storage can be configured for additional redundancy (geo-, local, or zone redundancies). The T-logs will be backed up at a frequency that depends on the machine size and the data volumes.

Refer to the following for Microsoft documentation:

DMV Name / Link to Documentation Sub Category Description
dm_continuous_copy_status Geo Replication This view has been superseded by sys.dm_geo_replication_link_status and is preserved for backward compatibility. Contains a row for each replication link between primary and secondary databases in a geo-replication partnership
dm_geo_replication_link_status Availability Contains a row for each replication link between primary and secondary databases in a geo-replication partnership
dm_database_backups Backup Returns information about backups of a database in an Azure SQL Database server
dm_database_replica_states Availability Returns state information for each database that participates in primary and secondary replicas
dm_db_objects_impacted_on_version_change Maintenance This database-scoped system view is designed to provide an early warning system to determine objects that will be impacted by a major release upgrade in Azure SQL Database
dm_operation_status Database level changes Returns information about operations performed on databases in an Azure SQL Database server

Category #3: Resource Governor

The Resource Governor allows for managing system resource consumption by classifying workloads and assigning resource limits to each workload group. Microsoft uses the Resource Governor on Azure SQL Databases to help govern resource utilization, such as IOPs (local and remote), CPU, memory, worker counts, session counts, memory grant limits, and the maximum number of concurrent requests. Some of the following DMVs will not provide valuable data on a single Azure SQL Database (i.e., returns 0's) but may show some important information when including your database in an elastic pool.

DMV Name / Link to Documentation Sub Category Description
dm_db_resource_governor_configuration Resource Governor Returns a row that contains the current in-memory configuration state of Resource Governor
dm_resource_governor_resource_pools_history_ex Resource Governor Each row represents a periodic snapshot of resource pool statistics in Azure SQL Database and Azure SQL Managed Instance
dm_resource_governor_workload_groups_history_ex Resource Governor Each row represents a periodic snapshot of workload group statistics in Azure SQL Database
dm_user_db_resource_governance Resource Governance Returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool

Category #4: Extended Events

In this article, Microsoft explains how Extended Events can assist in collecting events happening inside the database. It is lightweight, configurable, and would not affect overall performance. The following DMVs return information about the Extended Events.

DMV Name / Link to Documentation Sub Category Description
dm_xe_database_session_event_actions Database Sessions Returns information about event session actions for active database-scoped sessions. Actions are executed when events are fired
dm_xe_database_session_events Database Sessions Returns information about session events for active database-scoped session
dm_xe_database_session_object_columns Database Sessions Shows the configuration values for objects that are bound to an active database-scoped session
dm_xe_database_session_targets Database Sessions Returns information about active database-scoped session targets
dm_xe_database_sessions Database Sessions Returns information about active database-scoped extended events sessions

Category #5: Security

Always Encrypted "allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine."

Database External Policy is a set of rules that define which operations can be performed when connecting to the database from outside Azure.

DMV Name / Link to Documentation Sub Category Description
dm_column_encryption_enclave_properties Always Encrypted Undocumented by itself. Closer description: Returns performance counters for the secure enclave for Always Encrypted
dm_database_external_policy_actions Database External Policy List all available data actions, independent of being used or not (basically static, returns what could be used)
dm_database_external_policy_principal_assigned_actions Database External Policy sys.dm_server_external_policy_principal_assigned_actions and sys.dm_database_external_policy_principal_assigned_actions This set of views joins all 5 from the link provided and, as a result, lists the AAD accounts (currently only the SIDs) together with the external roles they are assigned to and the data actions that those roles provide
dm_database_external_policy_principals Database External Policy Contain a list of all principals (all types of AAD accounts) that have at least the Connect-Action assigned to them
dm_database_external_policy_role_actions Database External Policy Links external_actions with external_roles
dm_database_external_policy_role_members Database External Policy Links external_principals with external_roles
dm_database_external_policy_roles Database External Policy All external roles (Action Groups in Purview) that are in use for the current scope (server or database)
dm_external_policy_cache Server External Policy Helps check if external policies are enabled
dm_server_external_policy_actions Server External Policy List all available data actions, independent of being used or not
dm_server_external_policy_principal_assigned_actions Server External Policy Lists all database and server principals created from Microsoft Entra ID (formerly Azure Active Directory), joined with roles, joined with their data actions
dm_server_external_policy_principals Server External Policy Lists all database and server principals created from Microsoft Entra ID (formerly Azure Active Directory), which are given connect permissions through external policies
dm_server_external_policy_role_actions Server External Policy Lists the links between the roles and actions and can be used to join the two DMVs sys.dm_server_external_policy_roles and sys.dm_server_external_policy_actions
dm_server_external_policy_role_members Server External Policy Lists all database and server principals created from Microsoft Entra ID (formerly Azure Active Directory) assigned to a given role on a given resource scope
dm_server_external_policy_roles Server External Policy List all available roles, independent of being used or not. Not all roles listed may apply to SQL Server

Category #6: Synapse Link

Azure Synapse Link for SQL "enables near real-time analytics over operational data in Azure SQL Database or SQL Server 2022."

DMV Name / Link to Documentation Sub Category Description
dm_change_feed_errors Synapse Link Returns recent errors from the Azure Synapse Link feature, including change feed, snapshot, or incremental change publish processes
dm_change_feed_log_scan_sessions Synapse Link Returns activity for the Azure Synapse Link for SQL change feed

Category #7: Compatible with Synapse

However not documented, it seems Microsoft is keeping some DMVs in the Azure SQL Database to maintain compatibility with Synapse Analytics. In my next tips, I will check to see if those DMVs can be valuably used in Azure SQL Databases.

DMV Name / Link to Documentation Sub Category Description
dm_exec_requests_history Synapse "From Microsoft Support perspective we share the DMV "sys.dm_exec_requests_history" for ticket purposes to help customers better troubleshooting the SQL requests." (in Synapse)
dm_external_data_processed Synapse To see how much data was processed during the current day, week, or month (In Synapse)

Category #8: Undocumented / Not Supported on Azure SQL Database

In this section, I have provided the list of DMVs that are not documented. For some of the below, we can assume the expected data they may or will contain in the future. For convenience, I have added the "Assumed Category" and "potential description" for the ones I could understand. Please take my understanding of the below with a grain of salt and keep monitoring the Microsoft documentation for new content. Also, note that most of those DMVs are empty. Hence, it is harder to guess what they are or are meant to be for.

In subsequent tips, I intend to test some of my assumptions by trying to simulate situations that will populate those views.

Do you think I am wrong in my assumption? Leave us a comment!

DMV Name (see potential description of each DMV below) Assumed Category
dm_cloud_database_epoch Backup
dm_database_backup_lineage Backup
dm_database_engine_configurations Database/Instance configurations
dm_database_external_governance_sync_state Purview
dm_database_hs_log_rate HA/DR
dm_database_managed_identities Security (Entra)
dm_db_data_pool_nodes Elastic Pool
dm_db_workload_group_resource_stats Performance
dm_db_xtp_undeploy_status XTP Storage
dm_dist_requests Distributed transactions/requests
dm_db_storage_pool_nodes Elastic Pool
dm_dw_quality_clustering Data Quality
dm_dw_quality_delta Data Quality
dm_dw_quality_index Data Quality
dm_dw_quality_row_group Data Quality
dm_exec_distributed_tasks Distributed transactions/requests
dm_external_governance_sync_state Purview
dm_external_governance_synchronizing_objects Purview
dm_external_policy_excluded_role_members Security
dm_io_network_traffic_stats Network
dm_os_memory_allocations_filtered OS performance
dm_os_memory_health_history OS performance
dm_os_parent_block_descriptors OS performance
dm_pal_ring_buffers Memory
dm_request_phases Distributed transactions/requests
dm_server_managed_identities Security (Entra)
dm_tran_orphaned_distributed_transactions Distributed transactions/requests
dm_tran_distributed_transaction_stats Distributed transactions/requests

The following section provides a description of the content of each undocumented DMV, some additional explanation (if applicable), and my assumption about the role of the DMV. Again, since there is no documentation yet at the time of the writing, my assumptions may be off.

Potential Description of the Undocumented DMVs


  • "An epoch represents a cutoff point of historical data within the database." On my instance, I queried this table on March 17 and got the following results:
  • Shooting in the dark, I found the "8589934592" number here. This is related to the Service Fabric backups.
  • You will find this number in the JSON:
  • "Azure Service Fabric is a distributed systems platform that makes it easy to package, deploy, and manage scalable and reliable microservices and containers. Service Fabric also addresses the significant challenges in developing and managing cloud native applications."
  • My assumption Based on the above, this is related to the last snapshot backup of the Azure SQL Database service.


  • Querying the table shows the following:
  • What is the difference between the above and sys.dm_database_backups?
  • Let's see the latest backup information compared between the two DMVs:
  • Scrolling right for more columns:
  • Now let's look at the earliest backup information (to see if the two tables contain the same older data):
  • My assumption: My vague assumption is that dm_database_backup_lineage DMV could be the database's backup metadata history table. This will probably need to be tested over time.


  • This one seems to be easy, database-level configurations:
  • I assume that "TF" is Trace Flag, and the last two are self-explanatory.
  • My assumption: Database or instance-level configurations.


  • I found this article that talks about external governance being related to Purview.
  • The view contains the following columns:
Column_name Type Computed Length Prec Scale
database_id int no 4 10 0
sync_scope smallint no 2 5 0
sync_scope_desc nvarchar no 120    
sync_state smallint no 2 5 0
sync_state_desc nvarchar no 120    
user_initiated_sync smallint no 2 5 0
sync_percent_complete smallint no 2 5 0
current_sync_token nvarchar no 256    
next_sync_token nvarchar no 256    
last_reference_fetch_success_time_utc datetime no 8    
last_reference_fetch_attempt_time_utc datetime no 8    
last_reference_fetch_error int no 4 10 0
last_blob_fetch_success_time_utc datetime no 8    
last_blob_fetch_attempt_time_utc datetime no 8    
last_blob_fetch_error int no 4 10 0
last_sync_success_time_utc datetime no 8    
last_synchronizing_success_time_utc datetime no 8    
last_synchronizing_attempt_time_utc datetime no 8    
last_synchronizing_error int no 4 10 0
  • Note to self: check Azure SQL Database integration with Purview.
  • My assumption: Synchronization status with Purview.


  • The DMV contains the following columns:
  • Does "hs" stand for Harmonious Services?
  • My assumption: It looks to me that it is related to the log synchronization between the HA/DR replicas of the database.


  • The columns here:


  • The columns:
  • Note to self: test with Elastic Pool.
  • My assumption: Has logical data related to Elastic Pool, maybe metadata on each logical node.


  • Columns:
  • My biggest question is – why is this DMV empty?
  • An Azure SQL Database resource group is " a logical container into which Azure resources are deployed and managed."
  • My assumption: It seems like it will contain the resource utilization statistics by the Azure SQL Database Resource Group (actual instance?).
  • It may assist in calculating the DTU Utilization for Azure SQL Database, where applicable.


  • XTP Storage Object: "The SQL Server XTP Storage performance object contains counters related to on-disk storage for In-Memory OLTP in SQL Server."
  • The columns and example data:
  • According to Wikipedia, "undeploy" means to undo the deployment of; to revert to a state prior to deployment of.
  • My assumption: The rollback point in the T-log for on-disk storage for In-Memory data (?)


  • The DMV contains the following columns:
  • My assumption: This DMV is related to Distributed Transactions or Distributed Request management.


  • Here are the columns:
  • As opposed to dm_db_data_pool_nodes DMV:
  • Note to self: test this with Elastic Pool.
  • My assumption: As far as I understand the technology behind the Azure SQL Database, I would assume that an Elastic Pool also has a set of Storage Pools. Hence, this DMV contains the physical storage metadata for the Elastic Pool.


  • The columns:
  • My assumption: This view seems to be compatible with the future Datawarehouse Partitioning and/or Columnstore usage statistics, potentially related to Partitioned Clustered indexes or otherwise related to a Data Quality feature.


  • Columns:
  • My assumption: Also compatible with future Datawarehouse, potentially related to Delta Files and data quality/statistics.


  • Columns:
  • My assumption: In the same family of all the dm_dw_quality DMVs, potentially for all indexes.


  • Columns:
  • My assumption: Same as above, potentially related to data quality related to data grouping.


  • Columns:
  • My assumption: It seems like a "child" view to dm_dist_requests, with more information about the management/metadata of the distributed transactions or requests.


  • This DMV contains many columns. Some of them are:
  • In relation to dm_database_external_governance_sync_state, this DMV contains exactly the same columns.
  • My assumption: I have no idea what the difference is between this DMV and the same database-specific one (dm_database_external_governance_sync_state), as they contain the exact same columns and are both empty. Maybe they will contain the same data but can be viewed by different users based on their security level (i.e., an admin vs. a database owner).


  • Columns:
  • My assumption: Synchronization with Purview per object. I do not see any potential reference (similar column name) to the dm_external_governance_sync_state DMV, as I would expect to see.


  • Columns:
  • My assumption: A future ability to exclude external role members from a policy via a function (condition based). This capability exists in Azure Entra.


  • Columns:
  • My assumption: Collects network traffic statistics per network protocol, per type (sent/received) – count, average, bytes, min, max, etc.


  • Columns:
  • My assumption: Shows memory allocation per file (Database file? Delta file?)


  • Columns and data:
  • The allocation_potential_memory_mb column is documented for the sys.dm_os_out_of_memory_events table: "Memory available to the database engine instance for new allocations, in megabytes."
  • If we look at Severity Levels in SQL Server: "Informational messages that return status information or report errors that aren't severe. The Database Engine doesn't raise system errors with severities of 0 through 9."
  • My assumption: The DMV contains informational data about free memory for new allocations in MBs.


  • Columns:
  • My clue is the OOM (Out Of Memory?).
  • My assumption: Blocking information on the underlying operating system by another process or another process on the same instance. I put my bet on the first option.


  • PAL – Performance Analysis of Logs. "PAL tool reads in a performance monitor counter log and analyzes it using complex, but known thresholds (provided)."
  • The table contains the same columns as sys.dm_os_ring_buffers but is empty:
  • The ring buffers are described here: "The sys.dm_os_ring_buffers dynamic management view (DMV). The ring buffers are created during SQL Server startup, and record alerts within the SQL Server system for internal diagnostics."
  • My assumption: The DMV provides ring buffer information used by or to be used by PAL.


  • Columns:
  • My assumption: Since I see "dist," I assume this is related to Distributed Requests or Distributed Transactions and contains information about the different phases of those requests.


  • This DMV has the same columns as the same DMV on the database level:
  • My assumption: The DMV contains data about the server-level Managed Identities.


  • Columns:
  • My assumption: This DMV contains data about orphaned processes from distributed transactions or requests.


  • Partially documented here.
  • My assumption: Returns information about Distributed Transactions statistics in SQL Server (does not apply to Azure SQL Database, but table exists there).


In this tip, I have provided a list of the Data Management Views that exist on Azure SQL Database, but not on a SQL Server instance. This is only part 1 of a series. Here are some of the next parts I intend to write about in this series:

  • Testing content of some of the undocumented DMVs by simulating respective scenarios with Azure SQL Databases:
    • Performance
    • Security
    • Distributed Transactions/Requests
    • Purview integration
    • Elastic Pool
  • The most important Azure SQL Database DMVs that do not exist in a SQL Server instance and what we can do with them.

Stay tuned!

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Michelle Gutzait Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-04-08

Comments For This Article

get free sql tips
agree to terms