How to improve query performance in Azure SQL Database with large datasets?

Question

How to improve query performance in Azure SQL Database with large datasets?

Advika Sinha 20

Hi everyone,

I'm working with an Azure SQL Database that contains a few large tables (over 10 million rows each), and I'm noticing performance issues when running complex joins and aggregations.

I've already implemented indexing on the commonly queried columns, but some queries still take too long. I'm using the Standard S3 tier.

Could you please guide me on:

Best practices to optimize query performance in Azure SQL Database for large datasets?

Whether moving to a higher tier (like Premium or Hyperscale) would help significantly?

How to monitor and analyze slow queries more effectively in Azure?

Any tools or approaches specific to Azure that you'd recommend would be really helpful.

Thanks in advance!

Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-11T21:44:15.53+00:00

With the scant information you have given, it is impossible to say what is the best for you. Your queries will certainly run faster if you move to a larger SKU. But it will also be more expensive.

Since I don't see your queries, I have no ideas if they can be improved or not. If you can share a slow query together with its execution plan, we can take look at it. To share the plan, you can use https://www.pastetheplan.com.

Accepted answer

0 additional answers

Your answer

Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-11T21:44:15.53+00:00

With the scant information you have given, it is impossible to say what is the best for you. Your queries will certainly run faster if you move to a larger SKU. But it will also be more expensive.

Since I don't see your queries, I have no ideas if they can be improved or not. If you can share a slow query together with its execution plan, we can take look at it. To share the plan, you can use https://www.pastetheplan.com.

Answer 1

Hi @Advika Sinha

Thank you for reaching out regarding the performance challenges you're experiencing with large tables in your Azure SQL Database. Working with datasets exceeding 10 million rows can indeed

1. Index Optimization

Ensure clustered indexes are used effectively, especially on primary keys.

Use non-clustered indexes for frequently queried columns.

Consider covering indexes to reduce key lookups.

Avoid excessive indexing, as it can slow down write operations.

2. Partitioning & Columnstore Indexes

Partition large tables based on date or category to improve query efficiency.

Use Clustered Columnstore Indexes for analytical workloads to reduce storage and improve performance.

3. Query Optimization

Optimize JOINs by ensuring indexed columns are used in join conditions.

Use query hints like OPTION (HASH JOIN) or OPTION (MERGE JOIN) when appropriate.

Avoid SELECT *—instead, retrieve only necessary columns.

4. Scaling Considerations

Moving to Premium or Hyperscale tiers can significantly improve performance by providing more CPU and memory.

Hyperscale is ideal for very large databases with dynamic scaling needs.

5. Monitoring & Analysis

Use Query Store to analyze slow queries and execution plans.

Monitor sys.dm_exec_query_stats for high CPU-consuming queries.

Leverage Azure SQL Database Advisor for automated tuning recommendations.

Please go through these Documentations that might help you:

https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql

https://techcommunity.microsoft.com/blog/azuredbsupport/lesson-learned-481-query-performance-analysis-tips/4088795

https://www.sqlservercentral.com/articles/azure-sql-performance-tuning-mastering-indexes-partitioning-and-clustered-columnstore

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

How to improve query performance in Azure SQL Database with large datasets?

0 additional answers

Your answer