Introduction

One of the most common reasons organizations increase Azure SQL compute resources is poor query performance. When users complain about slow applications, the immediate reaction is often to scale up the database. However, in many cases, a small number of inefficient queries are responsible for the majority of CPU, memory, and I/O consumption.

This is where Query Store becomes invaluable.

Query Store captures query performance history, execution statistics, and execution plans, making it one of the most powerful tools for identifying expensive queries in Azure SQL Database.

In this article, we’ll learn how to use Query Store to identify high-cost queries, analyze their impact, and prioritize optimization efforts.

What is Query Store?

Query Store is a built-in performance monitoring feature in Azure SQL Database and SQL Server that automatically captures:

• Query text
• Execution plans
• Runtime statistics
• Performance history
• Plan changes over time

Unlike traditional DMVs, Query Store retains historical data, allowing you to investigate performance issues even after they occur.

Why Identify Expensive Queries?

A single inefficient query can:

• Consume excessive CPU resources
• Increase database costs
• Generate unnecessary I/O
• Cause blocking and concurrency issues
• Slow down critical business processes

By identifying these queries, DBAs can focus optimization efforts where they deliver the greatest return.

Verify Query Store is Enabled

Before running any analysis, confirm that Query Store is active.

SELECT
    actual_state_desc,
    desired_state_desc
FROM sys.database_query_store_options;

Expected output:

actual_state_desc
------------------
READ_WRITE

If Query Store is disabled, enable it through the Azure Portal or using T-SQL.

Finding Queries Consuming the Most CPU

CPU-intensive queries are often the first candidates for optimization.

SELECT TOP 20
    qt.query_sql_text,
    SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCPUTime,
    SUM(rs.count_executions) AS TotalExecutions
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_plan p
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_sql_text
ORDER BY TotalCPUTime DESC;

What This Query Shows

• Query text
• Total CPU consumption
• Execution frequency

A query executed thousands of times with moderate CPU usage may consume more resources than a query executed only once.

Finding Queries with the Longest Duration

Long-running queries often affect user experience.

SELECT TOP 20
    qt.query_sql_text,
    rs.avg_duration,
    rs.count_executions
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_plan p
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
ORDER BY rs.avg_duration DESC;

Use Cases

Look for:

• Reporting queries
• Large table scans
• Missing indexes
• Poor joins

Finding Frequently Executed Queries

Sometimes the most expensive query is not the slowest query.

A lightweight query executed millions of times can become a major cost driver.

SELECT TOP 20
    qt.query_sql_text,
    SUM(rs.count_executions) AS ExecutionCount
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_plan p
    ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_sql_text
ORDER BY ExecutionCount DESC;

Finding Queries with Multiple Execution Plans

Multiple execution plans can indicate:

• Parameter sniffing
• Data distribution issues
• Statistics changes

SELECT
    q.query_id,
    COUNT(*) AS PlanCount
FROM sys.query_store_plan p
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(*) > 1
ORDER BY PlanCount DESC;

These queries deserve further investigation.

Real-World Example

Suppose Query Store identifies the following query:

SELECT *
FROM MeetingBooking
WHERE MeetingRoomID = @MeetingRoomID;

Query Store reports:

Metric	Value
Total CPU	High
Duration	High
Executions	50,000

Further investigation reveals:

• Missing index on MeetingRoomID
• Large table scan
• Significant CPU consumption

After creating an index:

CREATE INDEX IX_MeetingBooking_MeetingRoomID
ON MeetingBooking(MeetingRoomID);

The query’s CPU consumption drops dramatically.

This simple optimization may eliminate the need to scale up the Azure SQL database.

Using Query Store Reports in Azure Portal

Azure SQL provides built-in Query Store reports:

Top Resource Consuming Queries

Shows:

• CPU usage
• Duration
• Execution count

Regressed Queries

Highlights queries whose performance has degraded over time.

Overall Resource Consumption

Displays database-level resource trends.

These visual reports help quickly identify optimization opportunities.

Best Practices

When analyzing expensive queries:

Focus on Total Resource Consumption

A query consuming 10 ms executed 1 million times may cost more than a query consuming 1 second executed once.

Review Execution Plans

Always inspect execution plans to identify:

• Missing indexes
• Table scans
• Key lookups
• Expensive joins

Track Trends Over Time

Use Query Store history to determine whether performance issues are:

• New
• Intermittent
• Persistent

Optimize Before Scaling

Query tuning is often more cost-effective than increasing compute resources.

Conclusion

Query Store is one of the most valuable performance tuning tools available in Azure SQL Database. By identifying CPU-intensive, long-running, and frequently executed queries, DBAs can focus optimization efforts where they have the greatest impact.

Rather than immediately scaling up database resources, use Query Store to understand how your workload behaves. In many cases, optimizing just a handful of expensive queries can significantly improve performance while reducing Azure SQL costs.

If you’re responsible for Azure SQL performance, reviewing Query Store should be part of your regular database health checks.