Azure SQL Managed Instance
Introduction
Azure SQL MI O11ySource is a comprehensive solution designed to provide visibility, analysis, and actionable insights into the performance, behavior, and health of your Azure SQL Managed Instance infrastructure. It follows a systematic approach to gather, monitor, and interpret metrics, and key indicators from Azure SQL Managed Instances, enabling deep visibility into database operations.
Getting Started
Compatibility
This Azure SQL Managed Instance O11ySource is compatible with AzureSQL Version 2012 and above.
Data Collection Method
Metrics are collected using a data collector deployed within vuSmartMaps platform which uses JDBC to retrieve performance and usage data.
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
- Azure SQL Managed Instance: Azure SQL Managed Instance address needs to be a valid FQDN
- Port: Enter a valid port
- Credential: This field is required
- Metrics Collection Queries: These queries are utilized for AzureSQL server's metrics.
- Query: Write the query to be exeuted on customer DB
- Polling Interval [seconds]: How frequently data is gathered. interval should be between 180 -.86400 seconds
- Custom Queries: Add custom queries here to retrieve metrics and data from the Oracle database as needed.
- Query Name: The name of the query.
- Query Topic: Assign a query topic to route query data to a specific destination topic. Ensure the target topic is created prior to configuring the source.
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps IP | FQDN or IP address of the Azure SQL Managed Instance | 1433* | TCP | Outbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
- Create a login on every Azure SQL Managed Instance you want to monitor and create a read-only
vunetuser with proper access to your server. - Start SQL Management Studio, login to Azure SQL Managed Instance and run:
USE master;
CREATE LOGIN vunet WITH PASSWORD = '[password]';
CREATE USER vunet FOR LOGIN vunet;
GRANT SELECT on sys.master_files to vunet;
GRANT SELECT on sys.dm_os_performance_counters to vunet;
GRANT VIEW SERVER STATE to vunet;
GRANT VIEW ANY DEFINITION to vunet;
GRANT CONNECT ANY DATABASE to vunet;
USE msdb;
CREATE USER vunet FOR LOGIN vunet;
GRANT SELECT on msdb.dbo.sysjobhistory to vunet;
GRANT SELECT on msdb.dbo.sysjobschedules to vunet;
GRANT SELECT on msdb.dbo.sysjobs to vunet;
Configuration Steps
Enablethe Azure SQL Managed Instance O11ySource.- Select the Sources tab and press the
+button to add the 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 |
| full_backup_size_bytes | Size of the last full backup of the database in bytes. | Float64 |
| last_full_backup_time | Timestamp of the last full backup, in milliseconds since the epoch. | UInt64 |
| last_transaction_log_backup_time | Timestamp of the last transaction log backup, in milliseconds since the epoch. | UInt64 |
| transaction_log_backup_size_bytes | Size of the last transaction log backup in bytes. | UInt64 |
| recovery_model | Recovery model of the database (e.g., Full, Simple, Bulk-logged). | LowCardinality(String) |
