Optimization: tuning Azure SQL Database workloads with Query Store and automatic tuning

#Why start here

In most databases, a small number of queries account for most CPU and IO. Fixing three of them often saves more than a tier upgrade, and the saving lasts.

#1. Check Query Store is capturing

SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb, query_capture_mode_desc
FROM sys.database_query_store_options;

If actual_state_desc is READ_ONLY (often because storage is full), raise the size or clean up:

ALTER DATABASE CURRENT SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048, QUERY_CAPTURE_MODE = AUTO);
ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

#2. Top queries by total CPU (last 7 days)

SELECT TOP 20
    q.query_id,
    SUM(rs.count_executions)                                   AS executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000          AS total_cpu_ms,
    SUM(rs.avg_logical_io_reads * rs.count_executions)         AS total_reads,
    MAX(rs.avg_duration) / 1000                                AS max_avg_duration_ms,
    COUNT(DISTINCT p.plan_id)                                  AS plans,
    LEFT(qt.query_sql_text, 200)                               AS query_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p        ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time > DATEADD(day, -7, SYSUTCDATETIME())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

Swap the ORDER BY for total_reads (IO-bound databases) or duration. A query with several plans (plans > 1) and varying duration is a plan-regression suspect.

#3. Common fixes for top queries

#4. Index recommendations

SELECT name, type, reason, score,
       JSON_VALUE(details, '$.implementationDetails.script') AS script
FROM sys.dm_db_tuning_recommendations
ORDER BY score DESC;

Also check sys.dm_db_missing_index_details, but treat suggestions with care: they don't consider write cost or overlapping indexes. Merge similar suggestions into one index.

Find unused indexes that cost writes:

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID() AND i.is_primary_key = 0 AND i.is_unique = 0
  AND s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;

Usage stats reset on restart/failover, so judge over a long uptime.

#5. Automatic tuning

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);   -- plan regression correction
-- optional, review first:
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = OFF);
SELECT name, desired_state_desc, actual_state_desc, reason_desc FROM sys.database_automatic_tuning_options;

FORCE_LAST_GOOD_PLAN is the safe one: it reverts to the previous plan when a new plan is clearly slower and checks the result. Automatic index creation is useful for databases without a DBA. Many teams prefer reviewing recommendations and deploying indexes through their normal change process.

#6. Force a plan manually (when needed)

EXEC sp_query_store_force_plan @query_id = 4127, @plan_id = 9981;
-- later, once the root cause is fixed:
EXEC sp_query_store_unforce_plan @query_id = 4127, @plan_id = 9981;

#7. Measure the gain

Compare total CPU of the fixed query, the database's avg_cpu_percent and p95 duration before and after. Only then consider scaling down (see the elastic pools and serverless post).

Warning

Create indexes on large tables with ONLINE = ON (and RESUMABLE = ON where supported) during quieter periods. An offline index build on a busy table blocks the very queries you're trying to speed up.

#Checklist

• Query Store in READ_WRITE with enough space
• Top 20 queries by CPU, reads and duration reviewed
• Fixes applied to the top few; plans compared
• Index recommendations reviewed; unused indexes found
• FORCE_LAST_GOOD_PLAN enabled
• Before/after measured, then scaling reconsidered