Introduction
Note: The attached code was used to accomplish the steps in the Database Configuration section below, which is a prerequisite to the DPA alert configuration.
Currently, when DPA monitors databases within an Azure Elastic Pool, each of the databases are registered independently. This means that DPA collects metrics for each of the databases individually. However, some users have also expressed the desire to view higher level Elastic Pool metrics within DPA as well. We have found a way to use custom metrics to collect the elastic pool information from the elastic_pool_resource_stats system view in the master database.
DPA Configuration
Once the database configuration (next section) has been completed, the elastic pool data is then available to query from the DPA monitoring user and a Custom Metric like this can be used. Note the use of the "MasterDB.elastic_pool_resource_stats" table, which is defined in Database Configuration section below. Note that this query in the alert takes an average of the last 10 min for these metrics, but adjust as needed.
The query in this screenshot is as follows, and note that this version collects CPU percent. There are quite a few other metrics that can be done this way as well, including Data I/O Percent, Log Write Percent, Storage Percent, Worker Percent, Max Session Percent, etc. Review the Microsoft documentation for details of columns that are available in the elastic_pool_resource_stats table:
SELECT AVG(avg_cpu_percent)
FROM MasterDB.elastic_pool_resource_stats
WHERE start_time >= DATEADD(minute, -10, CURRENT_TIMESTAMP); -- average metrics for last 10 min
Database Configuration
Even though the Custom Metric definition uses a fairly straightforward query, there is database configuration external to DPA that is required for this to work. Unfortunately, it is not as simple as writing a query to retrieve data from the master database as the system views in the master database are not available to the DPA's monitoring user in an Azure SQL DB environment. There are a few tricks we need to employ so DPA can query the master database. Essentially, you need to create an external table reference from the database DPA is monitoring, to the master database and systems views we want to query. The attached code was used to accomplish this.
