Database watcher data collection and datasets (preview)

Applies to: Azure SQL Database Azure SQL Managed Instance

Database watcher collects monitoring data from SQL system views and ingests it into the data store in the form of datasets. Each dataset is formed using the data from one or more SQL system views. For each dataset, there is a separate table in the data store.

Data collection

Database watcher collects monitoring data at periodic intervals using T-SQL queries. Data collected in each execution of a query is called a sample. Sample collection frequency varies by dataset. For example, frequently changing data such as SQL performance counters might be collected every 10 seconds, while mostly static data such as database configuration might be collected every five minutes. For more information, see Datasets.

Database watcher takes advantage of streaming ingestion in Azure Data Explorer and Real-Time Analytics in Microsoft Fabric to provide near real time monitoring. Typically, collected SQL monitoring data becomes available for reporting and analysis in less than 10 seconds. You can monitor data ingestion latency on the database watcher dashboards, using the Ingestion statistics link.

Interaction between database watcher and application workloads

Enabling database watcher is not likely to have an observable impact on the application workload performance. More frequent monitoring queries typically execute in the sub-second range, whereas queries that might require more time, for example to return large datasets, run at infrequent intervals.

USE master;
GO

CREATE OR ALTER FUNCTION dbo.dbw_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = 'default';

IF APP_NAME() IN (N'SQLExternalMonitoring',N'x_ms_reserved_sql_external_monitoring')
    SET @WorkloadGroupName = N'database_watcher_workload_group'

RETURN @WorkloadGroupName;

END;
GO

BEGIN TRY

IF EXISTS (
          SELECT 1
          FROM sys.resource_governor_configuration
          WHERE classifier_function_id <> 0 OR is_enabled <> 0
          )
    THROW 50001, 'Resource Governor is already configured. No changes were made.', 1;

CREATE RESOURCE POOL database_watcher_resource_pool
WITH (MIN_CPU_PERCENT = 5, MAX_CPU_PERCENT = 10, CAP_CPU_PERCENT = 30);

CREATE WORKLOAD GROUP database_watcher_workload_group
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10)
USING database_watcher_resource_pool;

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.dbw_classifier);

ALTER RESOURCE GOVERNOR RECONFIGURE;

END TRY
BEGIN CATCH
    THROW;
END CATCH;

To avoid concurrency conflicts such as blocking and deadlocks between data collection and database workloads running on your Azure SQL resources, the monitoring queries use short lock timeouts and low deadlock priority. If there is a concurrency conflict, priority is given to the application workload queries.

You might observe gaps in the collected data if the overall resource utilization is high, or if concurrency conflicts occur. In these cases, monitoring queries might be deprioritized in favor of application workloads.

Data collection in elastic pools

To monitor an elastic pool, you must designate one database in the pool as the anchor database. Database watcher connects to the anchor database. Because the watcher holds the VIEW SERVER PERFORMANCE STATE permission, system views in the anchor database provide monitoring data for the pool as a whole.

Data collected from the anchor database contains pool-level metrics, and certain database-level performance metrics for every database in the pool, such as resource utilization and request rate metrics for each database. For some scenarios, adding an elastic pool SQL target to monitor an elastic pool as a whole can make it unnecessary to monitor each individual database in the pool.

Certain monitoring data such as pool-level CPU, memory, storage utilization, and wait statistics is only collected at the elastic pool level because it cannot be attributed to an individual database in a pool. Conversely, certain other data such as query runtime statistics, database properties, table and index metadata is only available if you add individual databases as SQL targets.

If you add individual databases from an elastic pool as SQL targets, you should add the elastic pool as a SQL target as well. This way, you get a more complete view of the database and pool performance.

Monitor dense elastic pools

A dense elastic pool contains a large number of databases, but has a relatively small compute size. This configuration lets customers achieve substantial cost savings by keeping the compute resource allocation to a minimum on the assumption that only a small number of databases in the pool are active at the same time.

Compute resources available to database watcher queries in a dense elastic pool are further limited to avoid affecting application queries. Because of this, database watcher might not be able to collect monitoring data from every database in a dense elastic pool.

Data residency

Customers can choose to store collected SQL monitoring data in one of three data store types:

• A database on an Azure Data Explorer cluster. By default, a new Azure Data Explorer cluster is created for each new watcher and is located in the same Azure region as the watcher.

  Customers can choose the specific Azure region in an Azure geography as the location of their Azure Data Explorer cluster and the database. For more information about data replication capabilities in Azure Data Explorer, see Business continuity and disaster recovery overview.

• A database on a free Azure Data Explorer cluster.

  Customers can choose the specific Azure geography, but not the specific Azure region as the location of their free Azure Data Explorer cluster and the database. Data replication to a different region or geography is not supported.

• A database in Real-Time Analytics in Microsoft Fabric.

  Customers cannot choose the geographical location of the database.

To fully control data residency for collected SQL monitoring data, customers must choose a database on an Azure Data Explorer cluster as the data store.

Customers can align the geography and region of their Azure Data Explorer cluster to the geography and region of the Azure SQL resources being monitored. When the Azure SQL resources are located in multiple regions, customers might need to create multiple watchers and multiple Azure Data Explorer clusters to satisfy their data residency requirements.

Datasets

This section describes the datasets available for each SQL target type, including collection frequencies and table names in the data store.

Common columns

For each SQL target type, datasets have common columns, as described in the following tables.

A dataset has both sample_time_utc and collection_time_utc columns if it contains samples observed before the row was collected by database watcher. Otherwise, the observation time and collection time are the same, and the dataset contains only the sample_time_utc column.

For example, the sqldb_database_resource_utilization dataset is derived from the sys.dm_db_resource_stats dynamic management view (DMV). The DMV contains the end_time column, which is the observation time for each row reporting aggregate resource statistics for a 15-second interval. This time is reported in the sample_time_utc column. When database watcher queries this DMV, the result set might contain multiple rows, each with a different end_time. All of these rows have the same collection_time_utc value.

Related content

• Monitor Azure SQL workloads with database watcher (preview)
• Quickstart: Create a database watcher to monitor Azure SQL (preview)
• Create and configure a database watcher (preview)
• Analyze database watcher monitoring data (preview)
• Database watcher FAQ