Queries

Collect your own SQL Server metrics with Custom Queries.

A Custom Query is a SQL query that the QMonitor agent runs on your instances at a fixed interval. The result is stored as a measurement and can be shown in the Custom Metrics dashboard or in your own Custom Dashboards.

Creating a query

Open Instances → Queries and click New query:

FieldDescription
DescriptionA short description, shown in the metrics catalog.
EnabledDisabled queries are not run and are not available in dashboards.
IntervalHow often the agent runs the query.
QueryThe SQL query. It must return one row per series.
MeasurementThe name of the measurement. Letters, numbers, _, . and -.
Tag Columns IncludeComma-separated columns used as tags (the values you filter and group by), e.g. sql_instance,database_name.
Field Columns IncludeComma-separated columns used as values, e.g. data_mb,log_mb.
Time ColumnOptional column with the sample time; empty uses the collection time.

Column names may contain only letters, numbers and _, and the query cannot contain '''.

Running a query

A query runs only on the agents it is assigned to: on the Instances page, open the Queries dialog of an agent and select it. The agent picks up the change within a minute and runs the query on all its instances; the first data arrives after one interval.

Writing a good query

  • Return numbers as integer or float: wrap decimals in CAST(... AS float), otherwise they are stored as text.
  • Never change the type of an existing column: rows with a different type are discarded. To change it, give the column a new name.
  • Name the instance like the standard measurements: REPLACE(@@SERVERNAME, CHAR(92), ':') AS sql_instance.
  • On Azure SQL Database objects such as sys.master_files are not available and fail even behind an IF: run that part only on other editions with IF SERVERPROPERTY('EngineEdition') <> 5 EXEC('...').
  • Keep it light: each query must finish within 8 seconds.

Example: size and I/O of each user database.

IF SERVERPROPERTY('EngineEdition') <> 5
EXEC('
SELECT
    REPLACE(@@SERVERNAME, CHAR(92), '':'') AS sql_instance,
    DB_NAME(f.database_id) AS database_name,
    CAST(SUM(CASE WHEN f.type = 0 THEN f.size ELSE 0 END) AS float) * 8 / 1024 AS data_mb,
    CAST(SUM(CASE WHEN f.type = 1 THEN f.size ELSE 0 END) AS float) * 8 / 1024 AS log_mb,
    SUM(s.num_of_reads) AS reads,
    SUM(s.num_of_writes) AS writes
FROM sys.master_files f
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) s ON s.database_id = f.database_id AND s.file_id = f.file_id
WHERE f.database_id > 4
GROUP BY f.database_id')

with Tag Columns sql_instance,database_name and Field Columns data_mb,log_mb,reads,writes.