Azure SQL Database
Introduction
Azure SQL Database O11ySource provides visibility into the health, performance, and usage of your SQL databases hosted on Microsoft Azure. It enables proactive detection of performance bottlenecks, resource contention, and anomalies by tracking critical metrics such as CPU usage, DTU consumption, deadlocks, lock waits, query performance, active sessions, and storage utilization.
Getting Started
Compatibility
This AzureSQL O11ySource is compatible with AzureSQL Version 2012 and above.
Data Collection Method
AzureSQL Performance metrics are collected using an internal data collector. It uses a JDBC connection to fetch all the required metrics periodically.
Prerequisites
Dependent Configuration
To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.
Inputs for Configuring Data Source
- Instance Name: Please enter the name of the AzureSQL instance. This should be a unique identifier for the specific AzureSQL deployment you want to monitor.
- DB Details: Please provide the details of DB
- Hostname: Host name needs to be a valid FQDN
- Port: Enter a valid port
- Database: Enter the Database Name
- Credential: This field is required
- Metrics Collection Queries: These queries are utilized for AzureSQL server's metrics.
- Query: Choose the Queries
- Polling Interval [seconds]: How frequently data is gathered. interval should be between 180 -.86400 seconds
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps IP | Azure SQL Server FQDN | 1443* | TCP | Outbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
- Create a login on every SQL Server instance you want to monitor and create a read-only
vunetuser with proper access to your AzureSQL server. - Start AzureSQL, login to AzureSQL database and run:
USE master;
GO
CREATE LOGIN vunet WITH PASSWORD = '[password]';
GO
CREATE USER vunet FOR LOGIN vunet;
GO
GRANT SELECT on sys.master_files to vunet;
GO
GRANT SELECT on sys.dm_os_performance_counters to vunet;
GO
GRANT VIEW SERVER STATE to vunet;
GO
GRANT VIEW ANY DEFINITION to vunet;
GO
GRANT CONNECT ANY DATABASE to vunet;
GO
USE msdb;
GO
CREATE USER vunet FOR LOGIN vunet;
GO
GRANT SELECT on msdb.dbo.sysjobhistory to vunet;
GO
GRANT SELECT on msdb.dbo.sysjobschedules to vunet;
GO
GRANT SELECT on msdb.dbo.sysjobs to vunet;
GO
Configuration Steps
Enablethe AzureSQL O11ySource.- Select the Sources tab and press the
+button to add AzureSQL instance that has to be monitored. - Set up metrics collection configurations. The O11ySource already provides predefined metric collection queries. Users have the flexibility to adjust query intervals, modify existing queries, and introduce new queries as needed.
- Once all the required details are filled, click on
Save and Continue
Metrics Collected
| Name | Description | Data Type |
|---|---|---|
| average_cpu_utilization_in_percent | Average CPU utilization percentage. | Float32 |
| average_data_io_utilization_in_percent | Average Data IO utilization percentage. | Float32 |
| average_log_write_io_utilization_in_percent | Average Log Write IO utilization percentage. | Float32 |
| average_memory_usage_utilization_in_percent | Average memory usage utilization percentage. | Float32 |
| avg_cpu_time_msec | Average CPU time in milliseconds for a query. | Float64 |
| avg_dtu_percent | Average DTU usage percentage. | Float32 |
| avg_elapsed_time_msec | Average elapsed time for a query in milliseconds. | Float64 |
| blocking_session_id | Session ID of the blocker in blocking scenario. | Nullable(UInt32) |
| bs_statement_text | Statement text of the blocking session. | String |
| clerk_type | Type of memory clerk. | String |
| client_host_name | Name of the client host initiating the session. | String |
| command | Command being executed (e.g., SELECT, INSERT). | String |
| connection_start_time | Start time of the database connection. | String |
| counter | Metric or performance counter name. | String |
| counter_type | Type of the counter. | String |
| cpu_time_ms | CPU time consumed in milliseconds. | UInt64 |
| cpu_time_pct | CPU time as a percentage. | Float32 |
| database_name | Name of the Azure SQL database. | String |
| db_buffer_bytes | Buffer cache usage in bytes. | Float64 |
| db_buffer_MB | Buffer cache usage in megabytes. | Float64 |
| db_buffer_pages | Number of database pages in the buffer. | UInt64 |
| db_buffer_percent | Percentage of buffer usage. | Float32 |
| dbname | Name of the database context. | String |
| equality_columns | Columns used in equality conditions. | String |
| exectime | Execution time of the statement. | String |
| execution_count | Number of times the query was executed. | UInt64 |
| execution_time | Total query execution duration. | String |
| file_id | ID of the file. | String |
| file_name | Logical file name. | String |
| file_physical_name | Physical path of the file on disk. | String |
| file_state | State of the file (e.g., online, offline). | String |
| file_type | Type of file (e.g., data, log). | String |
| granted_query_memory_pages | Memory pages granted to the query. | UInt32 |
| host_name | Name of the host machine. | String |
| included_columns | Non-key columns included in an index. | String |
| inequality_columns | Columns used in inequality predicates. | String |
| instance | SQL Server instance name. | String |
| instance_type | Type of SQL instance (e.g., primary, replica). | String |
| io_stall | Total I/O stall in milliseconds. | UInt64 |
| io_stall_queued_read_ms | Time read I/O spent waiting in queue. | UInt64 |
| io_stall_queued_write_ms | Time write I/O spent waiting in queue. | UInt64 |
| io_stall_read_ms | Total stall time on read operations. | UInt64 |
| io_stall_write_ms | Total stall time on write operations. | UInt64 |
| is_hadr_enabled | Indicates if HADR is enabled. | String |
| is_read_only | Indicates if the DB is read-only (1 = Yes). | Int8 |
| last_elapsed_time_msec | Last execution elapsed time in ms. | Float64 |
| last_request_end_time | Time when the last request ended. | String |
| last_request_start_time | Time when the last request started. | String |
| last_worker_time_msec | Last worker thread time in milliseconds. | Float64 |
| logical_filename | Logical name of the file in SQL Server. | String |
| logical_reads | Number of logical reads. | UInt64 |
| login_name | Login name used for the session. | String |
| login_time | Time when the user logged in. | String |
| maximum_cpu_utilization_in_percent | Maximum CPU usage. | Float32 |
| maximum_data_io_utilization_in_percent | Maximum Data IO usage. | Float32 |
| maximum_log_write_io_utilization_in_percent | Maximum Log Write IO usage. | Float32 |
| maximum_memory_usage_utilization_in_percent | Maximum memory usage percentage. | Float32 |
| max_size_mb | Maximum allowed size of the database in MB. | Nullable(Float64) |
| max_wait_time_ms | Maximum wait time in milliseconds. | UInt64 |
| measurement | Metric name or measurement type. | String |
| memory_usage_kb | Memory usage in KB. | UInt64 |
| metrics_group | Grouping or categorization of metrics. | String |
| name | Generic name or identifier. | String |
| nt_user_name | Windows NT user name. | String |
| num_of_bytes_read | Number of bytes read. | Float64 |
| num_of_bytes_written | Number of bytes written. | Float64 |
| num_of_reads | Total number of read operations. | UInt64 |
| num_of_writes | Total number of write operations. | UInt64 |
| objectid | ID of the object referenced. | UInt64 |
| open_transaction | Number of open transactions. | UInt32 |
| other_process_cpu | CPU used by other processes. | Float64 |
| percent_complete | Completion percentage of a task. | Float32 |
| physical_filename | Physical file name. | String |
| program_name | Application name that initiated the request. | String |
| query_hash | Hash of the query text. | UInt64 |
| query_plan_hash | Hash of the query execution plan. | UInt64 |
| query_type | Type of query (e.g., adhoc, proc). | String |
| reads | Total number of reads. | UInt64 |
| read_bytes | Bytes read from disk. | Float64 |
| read_latency_ms | Latency of read operations in ms. | Float64 |
| recovery_model_desc | Recovery model (e.g., FULL, SIMPLE). | String |
| request_id | Request ID within the session. | UInt32 |
| resource_wait_ms | Time spent waiting for a resource. | UInt64 |
| sample_ms | Duration of the sample in milliseconds. | Float64 |
| server_name | SQL Server name. | String |
| session_db_name | Name of the database for the session. | String |
| session_id | Session identifier. | UInt32 |
| session_status | Status of the session (e.g., running). | String |
| signal_wait_time_ms | Time waiting for a signal (thread to run). | UInt64 |
| size_kb | File or object size in KB. | Float64 |
| size_on_disk_bytes | Actual size on disk in bytes. | Float64 |
| source_id | ID for the source of the metric. | String |
| sqlserver_process_cpu | CPU consumed by SQL Server process. | Float64 |
| sqlserver_start_time | Time when SQL Server started. | String |
| sql_instance | SQL instance name. | String |
| statement_text | Query or statement being executed. | String |
| state_desc | Description of the object state. | String |
| status | Status of the object/session. | String |
| stmt_object_name | Name of the object referenced by statement. | String |
| system_idle_cpu | CPU percentage in idle state. | Float64 |
| table_name | Name of the table involved. | String |
| target | Target of the performance counter. | String |
| task_state | State of the task. | String |
| timestamp | Timestamp of the metric record. | DateTime |
| total_buffer | Total memory buffer in use. | UInt32 |
| total_cost | Estimated cost of query execution. | UInt64 |
| total_elapsed_time_ms | Total elapsed time of the query. | Float64 |
| total_elapsed_time_msec | Same as above (duplicate representation). | Float64 |
| total_logical_reads | Total number of logical reads. | UInt64 |
| total_logical_writes | Total number of logical writes. | UInt64 |
| total_physical_reads | Total number of physical reads. | UInt64 |
| total_space_mb | Total space used in MB. | Float64 |
| total_worker_time_msec | Total worker time for execution. | Float64 |
| transaction_isolation_level | Isolation level of the transaction. | String |
| uptime_sec | SQL Server uptime in seconds. | UInt64 |
| user_access_desc | Access level of the user. | String |
| value | Value of the performance counter. | Float64 |
| waiting_tasks_count | Number of tasks waiting on this wait type. | UInt64 |
| wait_category | Category of the wait (e.g., CPU, IO). | String |
| wait_resource | Resource on which the session is waiting. | String |
| wait_time_ms | Time spent waiting in milliseconds. | UInt64 |
| wait_type | Type of wait (e.g., LCK_M_SCH_M). | String |
| writes | Number of write operations. | UInt64 |
| write_bytes | Bytes written to disk. | Float64 |
| write_latency_ms | Write latency in milliseconds. | Float64 |
| sql_version | SQL Server version. | String |
| last_execution_time | Timestamp of last execution. | String |
| last_elapsed_time | Elapsed time of the last execution. | String |
| last_worker_time | Worker thread time of last execution. | String |
