Overview

Information in this article is related to using CloudMonix for monitoring Azure SQL Data Warehouse.


The article covers the following topics:

  • common use cases where CloudMonix can help with monitoring and automation

  • what happens during monitoring cycle

  • what is needed to connect to and monitor Azure SQL Data Warehouse

  • what metrics can be tracked, visualized and monitored

  • what automated and auto-scaling actions can be executed


Why use CloudMonix for Azure SQL Data Warehouse?

Popular usages of CloudMonix include the following examples:

  • Auto-scaling the SQL Data Warehouse up or down, according to load or other criteria

  • Pause and resume the Warehouse according to the schedule or other criteria

  • Monitoring Uptime and Availability

  • Monitoring active loads

  • Tracking the number and statuses of connections, sessions and requests

  • Tracking the longest running queries, and overall utilization

  • Tracking detailed statistics regarding memory and utilization from the perspective of the resource governor

Monitoring Cycle

In every monitoring cycle, CloudMonix will attempt to connect to monitored database and retrieve data behind configured metrics. Most of the metrics are retrieved from Dynamic Management Views (DMVs). CloudMonix also will also attempt to open a new connection in every cycle to measure Response Time (if that metric is enabled).


After each monitored cycle is completed, CloudMonix will evaluate and possibly execute any automated actions and auto-scaling operations.

Configuration

Azure SQL Data Warehouse monitoring can be configured either via Setup Wizard or using the “Add New” button in the dashboard. It’s recommended to use Setup Wizard when configuring permissions for the first time, as this will simplify authorization. Learn more about authorizing with Setup Wizard here.



While the Setup Wizard will detect SQL Azure databases, in order for CloudMonix to connect to and monitor these databases, it needs username/password credentials for each database.


Username & Password

Before providing usernames/passwords, ensure that you are familiar and are comfortable with security precautions taken by the CloudMonix team to safeguard your credentials.

Vast majority of metrics that CloudMonix collects for SQL Azure require that the username has DATABASE VIEW STATE permissions in the given database. Some specific metrics, such as server-based performance counters and blocking (deadlock) queries, require that the username is a server administrator.


Creating username with VIEW STATE permission

If SA (system administrator) credentials cannot be provided, it is simplest to create a special login with DATABASE VIEW STATE permissions for CloudMonix to use. This login can later be restricted from being able to access sensitive data.


-- in the master database
CREATE LOGIN CloudMonixClient WITH PASSWORD = 'your_password_here'
CREATE USER CloudMonixClient FROM LOGIN CloudMonixClient;
 
--in each database that needs to be monitored
CREATE USER CloudMonixClient FROM LOGIN CloudMonixClient;
GRANT VIEW DATABASE STATE TO CloudMonixClient;




SQL Azure Firewall

CloudMonix monitoring services are hosted in Azure and utilize static IPs to perform monitoring. Those SQL Azure users who restrict access to their databases even to Azure-hosted services, will need to white-list in their firewall IP addresses specified in this article.



Configuration Dialog

When adding SQL Azure database resource manually, without the wizard, ensure that correct server name is provided. Valid credentials with VIEW DATABASE STATE permissions and correct server name will allow CloudMonix to detect and populate name of all monitor-able databases into the dropdown on configuration dialog.


 

FVEZx5H653yECbQSBQ91rDXnsmkFugL6CQ.png


Metrics


Every diagnostic data point that CloudMonix retrieves from the monitored resource is considered a metric in CloudMonix. Refer to the Metrics article to learn more about metrics in general.


CloudMonix retrieves most of the metrics from Dynamic Management Views (DMVs). Some metrics are captured by directly querying the SQL Warehouse.


CloudMonix provides a default template for monitoring Azure SQL Data Warehouse.


 

a9ZCtiqDejal3PW1y5xV-a8DQj7VmdY_Lw.png


The metrics can be added, removed and customized in the Metrics tab in the resource configuration dialog.


Built-in Metrics

ResourceStatus

Identifies the last state of the monitored resource. This is a critical metric that is captured for most types of resources that CloudMonix tracks. It is used for Uptime reports and should not be removed.

  • Data Type: string

  • Possible values: Ready, Down, Unknown

  • Included in sample profile: yes, tracked in both templates as a metric called Status

  • Included in default alerts: yes, in an alert called ResourceOutage (Error) Status other than Ready sustained for at least 3 min.

Statuses are determined according to the following rules:


  • Ready - successfully connected to the resource

  • Down - there was an error when trying to retrieve data from the resource

  • Unknown - can’t connect to the resource (e.g. because of invalid credentials)


ResponseTime

Tracks the time in milliseconds to open a connection to the SQL Warehouse database.

  • Data Type: double

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseSqlAzureWarehouseActiveConnections

Tracks a list of currently open connections. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: array of objects with the following properties:

  • SessionId (string): Unique numeric id associated with the session in which this query was run.

  • RequestId (string): Key for this view. Unique numeric id associated with the request.

  • Status (string): Current status of the request. Possible values: Pending, Authorizing, AcquireSystemResources, Initializing, Plan, Parsing, AquireResources, Running, Cancelling, Complete, Failed, Cancelled.

  • StartTime (DateTime): Time at which the request execution was started. Equals 0 for queued requests; otherwise, valid datetime smaller or equal to current time.

  • EndTime (DateTime): Time at which the request execution completed, failed, or was cancelled. Null for queued or active requests; otherwise, a valid datetime smaller or equal to current time.

  • ElapsedTimeMsec (int): Time elapsed in execution since the request was started, in milliseconds. Between 0 and the difference between start_time and end_time. The maximum value in milliseconds is equivalent to 24.8 days.

  • Label (string): Optional label string associated with some SELECT query statements.

  • ErrorId (string): Unique id of the error associated with the request, if any.

  • Command (string): Holds the full text of the request as submitted by the user.

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called OpenConnections

  • Included in default alerts: no


DatabaseSqlAzureWarehouseActiveLoadCount

Tracks count of active loads. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called ActiveLoadCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseActiveRequestCount

Tracks idle session count. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called ActiveRequestCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseActiveRequests

Tracks a list of currently executing requests (up to 100 records). Information retrieved from sys.dm_pdw_exec_requests view.

  • Data Type: array of objects with the following properties:

  • SessionId (string): Unique numeric id associated with the session in which this query was run.

  • RequestId (string): Key for this view. Unique numeric id associated with the request.

  • Status (string): Current status of the request. Possible values: Pending, Authorizing, AcquireSystemResources, Initializing, Plan, Parsing, AquireResources, Running, Cancelling, Complete, Failed, Cancelled.

  • StartTime (DateTime): Time at which the request execution was started. Equals 0 for queued requests; otherwise, valid datetime smaller or equal to current time.

  • EndTime (DateTime): Time at which the request execution completed, failed, or was cancelled. Null for queued or active requests; otherwise, a valid datetime smaller or equal to current time.

  • ElapsedTimeMsec (int): Time elapsed in execution since the request was started, in milliseconds. Between 0 and the difference between start_time and end_time. The maximum value in milliseconds is equivalent to 24.8 days.

  • Label (string): Optional label string associated with some SELECT query statements.

  • ErrorId (string): Unique id of the error associated with the request, if any.

  • Command (string): Holds the full text of the request as submitted by the user.

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called ActiveRequests

  • Included in default alerts: no


DatabaseSqlAzureWarehouseIdleSessionCount

Tracks idle session count. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called IdleSessionCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseQueuedLoadCount

Tracks count of queued up loads. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called QueuedLoadCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseQueuedRequestCount

Tracks queued request count. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called QueuedRequestCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseQueuedRequests

Tracks a list of currently executing requests.  Information retrieved from sys.dm_pdw_exec_requests view.

  • Data Type: int

  • Can be accessed only through aggregation using Expressions, see Evaluating data in sets\arrays (advanced) section to learn more.

  • Included in sample profile: yes, tracked as a metric called QueuedRequests

  • Included in default alerts: no


DatabaseSqlAzureWarehouseResourceGovernor

Tracks a list of of the differences in memory resource allocation in detail from the perspective of the resource governor. Information retrieved from sys.dm_pdw_nodes_resource_governor_workload_groups, sys.dm_pdw_nodes_resource_governor_resource_pools  and sys.dm_pdw_nodes views. See this article to learn more.

  • Data Type: array of objects with the following properties:

  • Pool (string): The name of the resource pool.

  • PoolMaxMemoryMB (double): The maximum amount of memory, in megabytes, that the resource pool can have. This is based on the current settings and server state.

  • Group (string): Name of the workload group.

  • GroupImportance (string): Current configuration value for the relative importance of a request in this workload group. Possible values: Low, Medium (default), or High.

  • MaxMemoryGrantedMB (double): The maximum size of memory granted to a single request (in Megabytes). Calculated based on request_max_memory_grant_percent and max_memory_kb values.

  • Node (string): Logical name of the node.

  • NodeType (string): Type of the node. Possible values: Compute, Control, Management.

  • TotalRequests (long): Cumulative count of completed requests in the workload group.

  • TotalQueuedRequests (long): Cumulative count of requests queued after the GROUP_MAX_REQUESTS limit was reached.

  • ActiveRequests (long): Current request count.

  • ActiveQueuedRequests (long): Current queued request count.

  • Included in sample profile: yes, tracked as a metric called ResourceGovernorStats

  • Included in default alerts: no


DatabaseSqlAzureWarehouseTopQueries

Tracks a list of top 10 longest executing requests over the last hour.  Information retrieved from sys.dm_pdw_exec_requests view.

  • Data Type: array of objects with the following properties:

  • SessionId (string): Unique numeric id associated with the session in which this query was run.

  • RequestId (string): Key for this view. Unique numeric id associated with the request.

  • Status (string): Current status of the request. Possible values: Pending, Authorizing, AcquireSystemResources, Initializing, Plan, Parsing, AquireResources, Running, Cancelling, Complete, Failed, Cancelled.

  • StartTime (DateTime): Time at which the request execution was started. Equals 0 for queued requests; otherwise, valid datetime smaller or equal to current time.

  • EndTime (DateTime): Time at which the request execution completed, failed, or was cancelled. Null for queued or active requests; otherwise, a valid datetime smaller or equal to current time.

  • ElapsedTimeMsec (int): Time elapsed in execution since the request was started, in milliseconds. Between 0 and the difference between start_time and end_time. The maximum value in milliseconds is equivalent to 24.8 days.

  • Label (string): Optional label string associated with some SELECT query statements.

  • ErrorId (string): Unique id of the error associated with the request, if any.

  • Command (string): Holds the full text of the request as submitted by the user.

  • Included in sample profile: yes, tracked as a metric called TopQueries

  • Included in default alerts: no


DatabaseSqlAzureWarehouseTotalSessionCount

Tracks total session count. Information retrieved from sys.dm_pdw_sys_info view.

  • Data Type: int

  • Included in sample profile: yes, tracked as a metric called TotalSessionCount

  • Included in default alerts: no


DatabaseSqlAzureWarehouseUsage

Tracks performance-related metrics exposed by Azure ARM API for given Warehouse database, such as CPU percentage (Average), Failed Connections (Total) or DQU used (Average). Values are aggregated in 5 min. windows.


  • Data Type: double

  • Included in sample profile: yes, tracked as metrics:

    • DataIO: Data IO percentage (Average)

    • DWU: DWU percentage (Average)

    • FailedConnectionCount: Failed Connections (Total)

    • Size: Total Database Size (Maximum)

    • SuccessfulConnectionCount: Successful Connections (Total)

  • Included in default alerts: no


DatabaseSqlNumeric

Captures numeric value from custom user-defined SQL statement. This metric is very useful in cases user-owned data value is required to be alerted on. Alternatively, this metric can track specific values in DMVs that existing set of DMV-based metrics does not track.

It is important the user-defined SQL statement can execute quickly. It is also important that the statement returns a single scalar numeric value.

Example:

 

SELECT COUNT(*) FROM Customers

 

Queries returning NULLs, non-numeric data, multiple rows, and etc. will not work properly.

Note: it is sometimes a case when data from this metric needs to be evaluated side by side with data in other resources. (For example, current count of customers may be used as a factor in scaling actions for Azure Web Roles). In such a case, use LinkedMetric to import data from Azure SQL Data Warehouse metrics into other resources.


  • Data Type: double

  • Included in sample profile: no

  • Included in default alerts: no


DatabaseSqlQuery

Captures a table-based collection of data retrieved via user-defined SQL statement. This metric is very useful in cases user-owned data value is required to be alerted on. Alternatively, this metric can track specific values in DMVs that existing set of DMV-based metrics does not track.


Note: It is important the user-defined SQL statement can execute quickly. It is also important that the size of the result set is reasonable (no more than a few hundred rows and within 20-30kb). Only one result set can be returned. Furthermore, ensure that column names of the result set do not contain spaces, start with numeric characters, and contain non-alphanumeric characters. Column names should also not duplicate. Column names of the resulting dataset will be converted to C# object properties before being visualized in the dashboard.

  • Data Type: array of objects (rows with properties as defined by the SQL statement)

  • Included in sample profile: no

  • Included in default alerts: no

Alerts


Users can create alerts based on changes in any value tracked by CloudMonix (including custom metrics). Each resource template includes alerts which are suitable for a given resource. The predefined alerts for Azure SQL Data Warehouse are listed in the Metrics section. Refer to the Alerts article to learn more about alerts in general.


Alerts are available during the Trial period or in Professional and Ultimate plans only.


Auto-scaling and Actions


Automation features (Actions) allow users to set up powerful reactive, proactive and scheduled actions and auto-scaling rules. CloudMonix can execute actions and scale adjustments when a specific monitoring condition occurs or according to a schedule.


Automation features are available during the Trial period or in the Ultimate plan only.


Sample usages:

  • Auto-scaling the SQL Data Warehouse up or down, according to load or other criteria

  • Pause and resume the Warehouse according to the schedule or other criteria


Every resource that uses CloudMonix auto-scaling should define Scale-down cooling period, Scale-up cooling period, and Sustained period values.


The Azure SQL Data Warehouse resources are scaled up (i.e. scaled vertically) by executing the resizing action and changing the tier.


 

Z1inwyHNtOD_n1_Yw1nK9JI5IlQEdhXMzg.png


Refer to the Actions and Auto-scaling articles to learn more.

Built-in SQL Data Warehouse Actions

AzureSqlDbResize

Resizes the SQL Data Warehouse by executing the ALTER DATABASE db_name MODIFY (SERVICE_OBJECTIVE = new_value) command.

  • Included in default actions: no


AzureSqlWarehousePause

Pauses the SQL Warehouse operation by calling the Pause method.

  • Included in default actions: no


AzureSqlWarehouseResume

Resumes the SQL Warehouse operation by calling the Resume method.

  • Included in default actions: no


CustomSqlDatabaseScript

CloudMonix will execute on the target SQL Warehouse the custom SQL script specified in the action definition. That action is especially useful when used in combination with other CloudMonix features, such as metrics or schedules.


The scripts are stored in the CloudMonix Script Library and can be used across multiple resources.


  • Included in default actions: no