Monitor SQL Server enabled by Azure Arc (preview)
Applies to:
SQL Server
Monitor SQL Server enabled by Azure Arc with performance dashboard in the Azure portal. Performance metrics are automatically collected from DMV datasets on eligible instances of SQL Server enabled by Azure Arc and sent to the Azure telemetry pipeline for near real-time processing.
Note
As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.
The latest updates are available in the Release notes - SQL Server enabled by Azure Arc.
During the feature preview, monitoring is available for free. Fees for this feature after general availability are to be determined.
To view metrics in the portal:
- Select an instance of SQL Server enabled by Azure Arc
- Select Monitoring > Performance Dashboard
Monitoring is automatic, assuming all prerequisites are met.
Prerequisites
In order for monitoring data to be collected on a SQL Server enabled by Azure Arc and view the performance metrics in Azure, the following conditions must be met:
- The version of Azure Extension for SQL Server (WindowsAgent.SqlServer) is v1.1.2504.99 or later
- SQL Server enabled by Azure Arc is running on Windows operating system
- SQL Server running on Windows Server 2012 R2 and older versions aren't supported
- SQL Server enabled by Azure Arc is a Standard or Enterprise Edition
- SQL Server version must be 2016 or later
- The server has connectivity to
*.<region>.arcdataservices.com
(for more information, see Network Requirements ) - The license type on the SQL Server enabled by Azure Arc is set to "License with Software Assurance" or "Pay-as-you-go"
- To view the performance dashboard in the Azure portal, you must be assigned an Azure role with the action
Microsoft.AzureArcData/sqlServerInstances/getTelemetry/
assigned. For convenience, you can use the built-in role "Azure Hybrid Database Administrator - Read Only Service Role", which includes this action. (For more information, see Learn more about Azure built-in roles)
Current Limitations
Failover cluster instances (FCI) aren't supported at this time.
Disable or enable collection
Important
In order to disable or enable data collection, the sqlServer
extension must be on v1.1.2504.99 or later.
Using the Azure portal
- On the resource page for a SQL Server enabled by Azure Arc, select the Performance Dashboard (preview) section.
- At the top of the Performance Dashboard page, select Configure. The portal opens Configure monitoring settings on the right-hand side.
- In Configure monitoring settings, toggle the option for monitoring data collection on or off.
- Select Apply settings.
Using the Azure CLI
Disable monitoring data collection
To disable monitoring data collection for your SQL Server enabled by Azure Arc, run the following command in the Azure CLI . Replace the placeholders for subscription ID, resource group, and resource name:
az resource update --ids "/subscriptions/<sub_id>/resourceGroups/<resource_group>/providers/Microsoft.AzureArcData/SqlServerInstances/<resource_name>" --set 'properties.monitoring.enabled=false' --api-version 2023-09-01-preview
Enable monitoring data collection
To enable the monitoring data collection for a SQL Server enabled by Azure Arc, run the following command in the Azure CLI. Replace the placeholders for subscription ID, resource group, and resource name:
az resource update --ids "/subscriptions/<sub_id>/resourceGroups/<resource_group>/providers/Microsoft.AzureArcData/SqlServerInstances/<resource_name>" --set 'properties.monitoring.enabled=true' --api-version 2023-09-01-preview
This command might run successfully, but all [prerequisites]](#prerequisites) must be met for monitoring data to be collected and shown in the Azure portal.
Collected data
The following lists reflect the monitoring data that is collected from DMV datasets on SQL Server enabled by Azure Arc when the monitoring feature is enabled. No personally identifiable information (PII), end-user identifiable information (EUII), or customer content is collected.
Active sessions
Description: Sessions running a request, is a blocker, or has an open transaction.
Dataset Name: SqlServerActiveSessions
Collection Frequency: 30 seconds
Collected Fields:
- connection_id
- database_id
- database_name
- machine_name
- sample_time_utc
- session_id
- session_status
- sql_server_instance_name
CPU Utilization
Description: CPU utilization over time.
Dataset Name: SqlServerCPUUtilization
Collection Frequency: 10 seconds
Collected Fields:
- avg_cpu_percent
- idle_cpu_percent
- machine_name
- other_process_cpu_percent
- process_sample_time_utc
- sample_time_utc
- sql_process_cpu_percent
- sql_server_instance_name
Database properties
Description: Includes database options and other database metadata.
Dataset Name: SqlServerDatabaseProperties
Collection Frequency: 5 minutes
Collected Fields:
- collation_name
- collection_time_utc
- compatibility_level
- containment_desc
- count_suspect_pages
- create_date
- database_id
- database_name
- delayed_durability_desc
- force_last_good_plan_actual_state
- is_accelerated_database_recovery_on
- is_auto_create_stats_on
- is_auto_shrink_on
- is_auto_update_stats_async_on
- is_auto_update_stats_on
- is_broker_enabled
- is_cdc_enabled
- is_change_feed_enabled
- is_distributor
- is_encrypted
- is_in_standby
- is_ledger_on
- is_merge_published
- is_parameterization_forced
- is_primary_replica
- is_published
- is_read_committed_snapshot_on
- is_read_only
- is_subscribed
- last_good_checkdb_time
- log_reuse_wait_desc
- machine_name
- notable_db_scoped_configs
- page_verify_option_desc
- query_store_actual_state_desc
- query_store_query_capture_mode_desc
- recovery_model_desc
- sample_time_utc
- snapshot_isolation_state
- sql_server_instance_name
- state_desc
- updateability
- user_access_desc
Database storage utilization
Description: Includes its storage usage and persistent version store.
Dataset Name: SqlServerDatabaseStorageUtilization
Collection Frequency: 1 minute
Collected Fields:
- collection_time_utc
- count_data_files
- count_log_files
- data_size_allocated_mb
- data_size_used_mb
- database_id
- database_name
- is_primary_replica
- log_size_allocated_mb
- log_size_used_mb
- machine_name
- online_index_version_store_size_mb
- persistent_version_store_size_mb
- sample_time_utc
- sql_server_instance_name
Memory utilization
Description: Memory clerks and memory consumption by the clerk.
Dataset Name: SqlServerMemoryUtilization
Collection Frequency: 10 seconds
Collected Fields:
- machine_name
- memory_size_mb
- memory_clerk_name
- memory_clerk_type
- sample_time_utc
- sql_server_instance_name
Performance counters (common)
Description: Includes common performance counters recorded by SQL Server.
Dataset Name: SqlServerPerformanceCountersCommon
Collection Frequency: 1 minute
Collected Counters:
- Active Temp Tables
- Active Transactions
- Background writer pages/sec
- Batch Requests/sec
- Buffer cache hit ratio
- Cache Hit Ratio
- Checkpoint pages/sec
- Errors/sec
- Free Space in tempdb (KB)
- Granted Workspace Memory (KB)
- Latch Waits/sec
- Lazy writes/sec
- Lock Memory (KB)
- Locked page allocations (KB)
- Log Bytes Flushed/sec
- Log Flushes/sec
- Logical Connections
- Logins/sec
- Logouts/sec
- Number of Deadlocks/sec
- OS available physical memory (KB)
- Out of memory count
- Page life expectancy
- Page reads/sec
- Page writes/sec
- Process physical memory in use (KB)
- Process physical memory low
- Processes blocked
- Readahead pages/sec
- SQL Attention rate
- SQL Compilations/sec
- SQL Re-Compilations/sec
- System memory signal state high
- System memory signal state low
- Target Server Memory (KB)
- Temp Tables Creation Rate
- Total Server Memory (KB)
- Transactions/sec
- User Connections
- Write Transactions/sec
Performance counters (detailed)
Description: Includes detailed performance counters recorded by SQL Server.
Dataset Name: SqlServerPerformanceCountersDetailed
Collection Frequency: 1 minute
Collected Counters:
- Average Wait Time (ms)
- Backup/Restore Throughput/sec
- Bulk Copy Rows/sec
- Bulk Copy Throughput/sec
- Cache Object Counts
- Connection Memory (KB)
- Data File Size (KB)
- Database pages
- Errors/sec
- Failed Auto-Params/sec
- Free list stalls/sec
- Large page allocations (KB)
- Local node page lookups/sec
- Lock Timeouts (timeout > 0)/sec
- Log File Size (KB)
- Log File Used Size (KB)
- Log Flush Wait Time
- Log Growths
- Log Shrinks
- Optimizer Memory (KB)
- Page lookups/sec
- Percent Log Used
- Process virtual memory low
- Remote node page lookups/sec
- Shrink Data Movement Bytes/sec
- Temp Tables For Destruction
- Version Cleanup rate (KB/s)
- Version Generation rate (KB/s)
- Version Store Size (KB)
- XTP Memory Used (KB)
Storage I/O
Description: Includes cumulative IOPS, throughput, and latency statistics.
Dataset Name: SqlServerStorageIO
Collection Frequency: 10 seconds
Collected Fields:
- database_id
- database_name
- file_id
- file_max_size_mb
- file_size_mb
- file_type
- io_stall_queued_read_ms
- io_stall_queued_write_ms
- io_stall_read_ms
- io_stall_write_ms
- machine_name
- num_of_bytes_read
- num_of_bytes_written
- num_of_reads
- num_of_writes
- sample_time_utc
- size_on_disk_bytes
- sql_server_instance_name
Wait stats
Note
Wait stats cannot be visualized on the performance dashboard at this time. Support for wait stats will be added in a future release.
Description: Includes wait types and wait statistics for the database engine instance.
Dataset Name: SqlServerWaitStats
Collection Frequency: 10 seconds
Collected Fields:
- machine_name
- max_wait_time_ms
- resource_wait_time_ms
- sample_time_utc
- signal_wait_time_ms
- sql_server_instance_name
- wait_category
- wait_time_ms
- wait_type
- waiting_tasks_count