DMV Comparison for SQL Server 2019 vs Azure SQL Database

Problem

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?

Solution

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'
ORDER BY name
GO

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:

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.

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:

• Backup frequencies.
• Backup storage redundancy options.
• Backup retention policies (can be changed).

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.

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.

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.

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.”

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.

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!

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

dm_cloud_database_epoch

• “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.

dm_database_backup_lineage

• 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.

dm_database_engine_configurations

• 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.

dm_database_external_governance_sync_state

• I found this article that talks about external governance being related to Purview.
• The view contains the following columns:

• Note to self: check Azure SQL Database integration with Purview.
• My assumption: Synchronization status with Purview.

dm_database_hs_log_rate

• 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.

dm_database_managed_identities

• The columns here:

• My assumption: It looks to be related to the Managed Identities: Managed Identities in Microsoft Entra for Azure SQL.

dm_db_data_pool_nodes

• The columns:

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

dm_db_workload_group_resource_stats

• 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.

dm_db_xtp_undeploy_status

• 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 (?)

dm_dist_requests

• The DMV contains the following columns:

• My assumption: This DMV is related to Distributed Transactions or Distributed Request management.

dm_db_storage_pool_nodes

• 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.

dm_dw_quality_clustering

• 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.

dm_dw_quality_delta

• Columns:

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

dm_dw_quality_index

• Columns:

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

dm_dw_quality_row_group

• Columns:

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

dm_exec_distributed_tasks

• 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.

dm_external_governance_sync_state

• 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).

dm_external_governance_synchronizing_objects

• 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.

dm_external_policy_excluded_role_members

• 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.

dm_io_network_traffic_stats

• Columns:

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

dm_os_memory_allocations_filtered

• Columns:

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

dm_os_memory_health_history

• 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.

dm_os_parent_block_descriptors

• 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.

dm_pal_ring_buffers

• 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.

dm_request_phases

• 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.

dm_server_managed_identities

• 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.

dm_tran_orphaned_distributed_transactions

• Columns:

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

dm_tran_distributed_transaction_stats

• 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).

Summary

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

• Review the documentation of each DMV that I mentioned here and use the DMV contents as needed.
• Stay tuned for my next tips about the most important DMVs for Azure SQL Database, per each category mentioned.
• Here are some older tips focused on DMVs:
  • Dynamic Management Views
  • Understanding Dynamic Management Views and Functions in SQL Server 2008
  • All tips: “SQL Server Dynamic Management Views and Functions Tips“

Michelle Gutzait

Michelle is the SQL Server Team Lead at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.