SQL Performance Needs Investigation? Review Metrics and Query Store

A database utilization chart tells you when resources were busy. Query Store adds the SQL statements, execution plans, and runtime statistics needed to investigate which queries contributed. Use both views for the same time window.

Reuse the lab database sqldb-cloudtrips from Create Azure SQL Database, or recreate it if it was deleted. The earlier Trip uses the Basic DTU tier. Sign in as its SQL administrator in Query editor and allow your current client IP through the server firewall. Use this exercise on the lab database.

Inspect Utilization

Open sqldb-cloudtrips → Monitoring → Metrics. Use Last hour, one-minute granularity, and compare DTU percentage, CPU percentage, Data IO percentage, and Log IO percentage. Use Average to see the trend and Maximum to look for short peaks. For a vCore database, use the individual CPU/IO metrics available for that tier.

DTUs bundle compute and IO capacity. A high DTU percentage can be driven by one constrained component; inspect the individual metrics before choosing a larger tier. A quiet Basic database can remain near zero during this small exercise.

Screenshot 1: Database utilization chart with the selected time window and CPU/IO measurements.

Capture a Small Query Workload

In Query editor, run:

SELECT actual_state_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

Expect READ_WRITE. Record the capture mode, commonly AUTO. If Query Store is OFF, enable it with ALTER DATABASE CURRENT SET QUERY_STORE = ON;. If it is READ_ONLY, inspect its configuration/storage before continuing.

For this short lab, set capture mode to ALL so a small, inexpensive query is recorded. Create a dedicated test table and execute the marked query ten times:

ALTER DATABASE CURRENT SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

IF OBJECT_ID(N'dbo.ct_monitor_probe', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.ct_monitor_probe (n int NOT NULL PRIMARY KEY);
    INSERT dbo.ct_monitor_probe (n)
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id))
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
END;

DECLARE @i int = 0;
WHILE @i < 10
BEGIN
    EXEC sys.sp_executesql N'/* CloudTrips_SQL_Monitor */
        SELECT SUM(CONVERT(bigint, n)) AS total
        FROM dbo.ct_monitor_probe WHERE n % 7 = 0;';
    SET @i += 1;
END;
EXEC sys.sp_query_store_flush_db;

Immediately restore the capture mode you recorded. For the usual AUTO setting:

ALTER DATABASE CURRENT SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Restore it even if the workload fails. If it was originally ALL, NONE, or CUSTOM, use that original value instead. This exercise produces identifiable query history; ten small queries may barely move the resource chart.

Inspect Query Performance

Open Intelligent Performance → Query Performance Insight on the database and choose a range containing the test. Review CPU, execution count, and duration views, then select a query for its text. This chart can need a couple of hours of captured history to populate.

For immediate inspection, run this Query Store query in Query editor:

SELECT TOP (10)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions) AS executions,
    CAST(SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS decimal(18,2)) AS average_ms,
    CAST(SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS decimal(18,2)) AS average_cpu_ms,
    MAX(rs.last_execution_time) AS last_execution,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'/* CloudTrips_SQL_Monitor */%'
  AND rs.last_execution_time >= DATEADD(hour, -1, SYSUTCDATETIME())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY executions DESC;

The calculation weights each interval’s average by its execution count and converts microseconds to milliseconds. Runtime statistics are aggregated into intervals, so a filter on last execution time is not an exact event-by-event time filter.

Screenshot 2: Query Store results showing the marked statement, query/plan IDs, execution count, and duration. If Query Performance Insight has populated, it can show the same investigation instead.

Duration includes elapsed waiting; CPU time measures processor work. A frequent cheap query can consume more total resources than an occasional expensive one. Investigate query text, plans, waits, and indexes before deciding whether the workload needs a larger database.

Finish

Remove the dedicated table with DROP TABLE IF EXISTS dbo.ct_monitor_probe; and confirm you restored the capture mode. Leave Query Store enabled for continued observation, or restore its original disabled state if you enabled it only for this exercise. Keep or delete the SQL lab resources according to your remaining SQL exercises.