Optimize Query Performance on a 2 Billion Row Table in Azure SQL Database?

samyvel samyvel 20 Reputation points
2025-09-10T21:17:49.1766667+00:00

I’m working with an Azure SQL Database (Hyperscale tier) that has a single table holding around 2 billion rows of time-series data (telemetry logs). Queries filtering by date range are becoming slower, even with indexes on the timestamp column.

Should I use table partitioning by date to improve query performance?

Are there limitations in Azure SQL Database for partitioned tables compared to SQL Server on-prem?

Would partitioning impact existing clustered/non-clustered indexes?

Is there any Microsoft-recommended best practice for handling very large time-series tables in Azure SQL?

Azure SQL Database
0 comments No comments

Answer accepted by question author
Azar 31,280 Reputation points MVP Volunteer Moderator
2025-09-10T21:21:45.1133333+00:00

Hi there

Yes, you can use table partitioning in Azure SQL Database for time-series data, usually by date ranges. It helps with managing large tables and can speed up queries if your filters align with the partition key. Just be sure your clustered index is aligned with the partition column, and consider columnstore indexes for analytic workloads. If queries are still slow, you might also look at Azure Synapse or Data Explorer depending on your scenario.

If this helps kindly accept the answer thanks much.

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 137.9K Reputation points MVP Volunteer Moderator
    2025-09-10T21:37:15.6166667+00:00

    Partitioning generally does not help to improve query performance. Rather, it can degrade performance of queries that do not include any condition on the partitioning column. The main reason for partitioning a table is that it makes it very efficient to age out old data, as this can be done with partition switching.

    The fact that your queries are only getting slower suggests that the index on that timestamp column is not used. Have you checked the query plan for your queries.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.