Breyta

Deila með


Monitor your SQL deployments with SQL Insights (preview)

Applies to: Azure SQL Database Azure SQL Managed Instance SQL Server on Azure VM

Important

SQL Insights (preview) will be retired on 31 December 2024. We recommend that you transition to database watcher for Azure SQL (preview) or another database monitoring solution by that date.

Database watcher is the recommended monitoring solution for scenarios that require low data collection latency, estate-level monitoring, comprehensive monitoring data including query-level details, and support for advanced analytics on collected monitoring data. At this time, database watcher supports Azure SQL Database and Azure SQL Managed Instance.

After 31 December 2024, SQL Insights (preview) will not be supported and will not be available in the Azure portal. You will retain any existing monitoring data collected by SQL Insights in your Log Analytics workspace.

SQL Insights (preview) is a monitoring solution for products in the Azure SQL family. SQL Insights uses dynamic management views to expose the data that you need to monitor health, diagnose problems, and tune performance.

SQL Insights performs all monitoring remotely. Monitoring agents on dedicated virtual machines connect to your SQL resources and remotely gather data. The gathered data is stored in Azure Monitor Logs to enable easy aggregation, filtering, and trend analysis. You can view the collected data from the SQL Insights workbook template, or you can delve directly into the data by using log queries.

The following diagram details the steps taken by information from the database engine and Azure resource logs, and how they can be surfaced. For a more detailed diagram of Azure SQL logging, see Monitoring and diagnostic telemetry.

Diagram showing how database engine information and resource logs are surfaced through AzureDiagnostics and Log Analytics.

Pricing

There's no direct cost for SQL Insights (preview). All costs are incurred by the virtual machines that gather the data, the Log Analytics workspaces that store the data, and any alert rules configured on the data.

Virtual machines

For virtual machines, you're charged based on the pricing published on the virtual machines pricing page. The number of virtual machines that you need varies based on the number of connection strings you want to monitor. We recommend allocating one virtual machine of size Standard_B2s for every 100 connection strings. For more information, see Azure virtual machine requirements.

Log Analytics workspaces

For the Log Analytics workspaces, you're charged based on the pricing published on the Azure Monitor pricing page. The Log Analytics workspaces that SQL Insights uses incur costs for data ingestion, data retention, and (optionally) data export.

Exact charges vary based on the amount of data ingested, retained, and exported. The amount of this data varies based on your database activity and the collection settings defined in your monitoring profiles.

Alert rules

For alert rules in Azure Monitor, you're charged based on the pricing published on the Azure Monitor pricing page. If you choose to create alerts with SQL Insights (preview), you're charged for any alert rules created and any notifications sent.

Supported versions

SQL Insights (preview) supports the following environments:

  • Azure SQL Database
  • Azure SQL Managed Instance
  • SQL Server 2012 and later versions running on:

SQL Insights (preview) has no support or has limited support for the following:

  • Non-Azure instances: SQL Server running on environments outside of Azure isn't supported, including on-premises and virtual machines.
  • Azure SQL Database elastic pools: Metrics can't be gathered for elastic pools or for databases within elastic pools.
  • Azure SQL Database low service tiers: Metrics can't be gathered for databases on Basic, S0, S1, and S2 service tiers.
  • Azure SQL Database serverless tier: Metrics can be gathered for databases through the serverless compute tier. However, the process of gathering metrics resets the autopause delay timer, preventing the database from entering an autopaused state.
  • Secondary replicas: Metrics can be gathered for only a single secondary replica per database. If a database has more than one secondary replica, only one can be monitored.
  • Authentication with Microsoft Entra ID (formerly Azure Active Directory): The only supported method of authentication for monitoring is SQL authentication. For SQL Server on Azure Virtual Machines, authentication through Active Directory on a custom domain controller isn't supported.

Regional availability

SQL Insights (preview) is available in all Azure regions where Azure Monitor is available, except for Azure Government and national clouds.

Open SQL Insights

To open SQL Insights (preview):

  1. In the Azure portal, go to the Azure Monitor menu.
  2. In the Insights section, select SQL (preview).
  3. Select a tile to load the experience for the SQL resource that you're monitoring.

Screenshot that shows SQL Insights in the Azure portal.

For more instructions, see Enable SQL Insights (preview) and Troubleshoot SQL Insights (preview).

Note

On-premises SQL Server instances are not currently supported by SQL Insights (preview) - the label in the Azure portal can be ignored.

Collected data

SQL Insights performs all monitoring remotely. No agents are installed on the virtual machines running SQL Server.

SQL Insights uses dedicated monitoring virtual machines to remotely collect data from your SQL resources. Each monitoring virtual machine has the Azure Monitor agent and the Workload Insights (WLI) extension installed.

The WLI extension includes the open-source Telegraf agent. SQL Insights uses data collection rules to specify the data collection settings for Telegraf's SQL Server plug-in.

Different sets of data are available for Azure SQL Database, Azure SQL Managed Instance, and SQL Server. The following tables describe the available data. You can customize which datasets to collect and the frequency of collection when you create a monitoring profile.

The tables have the following columns:

  • Friendly name: Name of the query as shown in the Azure portal when you're creating a monitoring profile.
  • Configuration name: Name of the query as shown in the Azure portal when you're editing a monitoring profile.
  • Namespace: Name of the query as found in a Log Analytics workspace. This identifier appears in the InsighstMetrics table on the Namespace property in the Tags column.
  • DMVs: Dynamic managed views that are used to produce the dataset.
  • Enabled by default: Whether the data is collected by default.
  • Default collection frequency: How often the data is collected by default.

Data for Azure SQL Database

Friendly name Configuration name Namespace DMVs Enabled by default Default collection frequency
DB wait stats AzureSQLDBWaitStats sqlserver_azuredb_waitstats sys.dm_db_wait_stats No Not applicable
DBO wait stats AzureSQLDBOsWaitstats sqlserver_waitstats sys.dm_os_wait_stats Yes 60 seconds
Memory clerks AzureSQLDBMemoryClerks sqlserver_memory_clerks sys.dm_os_memory_clerks Yes 60 seconds
Database I/O AzureSQLDBDatabaseIO sqlserver_database_io sys.dm_io_virtual_file_stats
sys.database_files
tempdb.sys.database_files
Yes 60 seconds
Server properties AzureSQLDBServerProperties sqlserver_server_properties sys.dm_os_job_object
sys.database_files
sys.databases
sys.database_service_objectives
Yes 60 seconds
Performance counters AzureSQLDBPerformanceCounters sqlserver_performance sys.dm_os_performance_counters
sys.databases
Yes 60 seconds
Resource stats AzureSQLDBResourceStats sqlserver_azure_db_resource_stats sys.dm_db_resource_stats Yes 60 seconds
Resource governance AzureSQLDBResourceGovernance sqlserver_db_resource_governance sys.dm_user_db_resource_governance Yes 60 seconds
Requests AzureSQLDBRequests sqlserver_requests sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
No Not applicable
Schedulers AzureSQLDBSchedulers sqlserver_schedulers sys.dm_os_schedulers No Not applicable

Data for Azure SQL Managed Instance

Friendly name Configuration name Namespace DMVs Enabled by default Default collection frequency
Wait stats AzureSQLMIOsWaitstats sqlserver_waitstats sys.dm_os_wait_stats Yes 60 seconds
Memory clerks AzureSQLMIMemoryClerks sqlserver_memory_clerks sys.dm_os_memory_clerks Yes 60 seconds
Database I/O AzureSQLMIDatabaseIO sqlserver_database_io sys.dm_io_virtual_file_stats
sys.master_files
Yes 60 seconds
Server properties AzureSQLMIServerProperties sqlserver_server_properties sys.server_resource_stats Yes 60 seconds
Performance counters AzureSQLMIPerformanceCounters sqlserver_performance sys.dm_os_performance_counters
sys.databases
Yes 60 seconds
Resource stats AzureSQLMIResourceStats sqlserver_azure_db_resource_stats sys.server_resource_stats Yes 60 seconds
Resource governance AzureSQLMIResourceGovernance sqlserver_instance_resource_governance sys.dm_instance_resource_governance Yes 60 seconds
Requests AzureSQLMIRequests sqlserver_requests sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
No NA
Schedulers AzureSQLMISchedulers sqlserver_schedulers sys.dm_os_schedulers No Not applicable

Data for SQL Server

Friendly name Configuration name Namespace DMVs Enabled by default Default collection frequency
Wait stats SQLServerWaitStatsCategorized sqlserver_waitstats sys.dm_os_wait_stats Yes 60 seconds
Memory clerks SQLServerMemoryClerks sqlserver_memory_clerks sys.dm_os_memory_clerks Yes 60 seconds
Database I/O SQLServerDatabaseIO sqlserver_database_io sys.dm_io_virtual_file_stats
sys.master_files
Yes 60 seconds
Server properties SQLServerProperties sqlserver_server_properties sys.dm_os_sys_info Yes 60 seconds
Performance counters SQLServerPerformanceCounters sqlserver_performance sys.dm_os_performance_counters Yes 60 seconds
Volume space SQLServerVolumeSpace sqlserver_volume_space sys.master_files Yes 60 seconds
SQL Server CPU SQLServerCpu sqlserver_cpu sys.dm_os_ring_buffers Yes 60 seconds
Schedulers SQLServerSchedulers sqlserver_schedulers sys.dm_os_schedulers No Not applicable
Requests SQLServerRequests sqlserver_requests sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
No Not applicable
Availability replica states SQLServerAvailabilityReplicaStates sqlserver_hadr_replica_states sys.dm_hadr_availability_replica_states
sys.availability_replicas
sys.availability_groups
sys.dm_hadr_availability_group_states
No 60 seconds
Availability database replicas SQLServerDatabaseReplicaStates sqlserver_hadr_dbreplica_states sys.dm_hadr_database_replica_states
sys.availability_replicas
No 60 seconds