Monitor your SQL deployments with SQL insights (preview)

SQL insights is a comprehensive solution for monitoring any product 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.

Pricing

There is no direct cost for SQL insights. 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 will vary 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. See Azure virtual machine requirements for more details.

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 will incur costs for data ingestion, data retention, and (optionally) data export.

Exact charges will vary based on the amount of data ingested, retained, and exported. The amount of this data will vary 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, you're charged for any alert rules created and any notifications sent.

Supported versions

SQL insights supports the following versions of SQL Server:

  • SQL Server 2012 and newer

SQL insights supports SQL Server running in the following environments:

  • Azure SQL Database
  • Azure SQL Managed Instance
  • SQL Server on Azure Virtual Machines (SQL Server running on virtual machines registered with the SQL virtual machine provider)
  • Azure VMs (SQL Server running on virtual machines not registered with the SQL virtual machine provider)

SQL insights has no support or has limited support for the following:

  • Non-Azure instances: SQL Server running on virtual machines outside Azure is not supported.
  • 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 will reset the auto-pause delay timer, preventing the database from entering an auto-paused 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 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 is not supported.

Opening SQL insights

To open SQL insights:

  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 and Troubleshoot SQL insights.

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

Next steps