Performance Improvement Strategy for Azure SQL Database with Billion-Row Tables

Question

We are currently dealing with a high-volume Azure SQL Database environment where a few core tables contain billions of records, and approximately 20 million new records are added every day. Due to this continuous data growth, both the application layer and Power BI reports are facing performance issues, including query timeouts, slow report refreshes, and delayed data retrieval.

To support long-term scalability, we need to move away from only reactive tuning and implement a structured performance improvement strategy covering database design, partitioning, indexing, query optimization, reporting architecture, archival strategy, and monitoring.

The main challenges are:

1. Some Azure SQL tables have already reached billions of rows.
2. Around 20 million records are added daily.
3. Application queries are timing out while fetching data.
4. Power BI reports are failing or taking too long to refresh.
5. Existing indexes may not be enough for this scale.
6. Historical data and current data are being queried from the same large tables.
7. Index maintenance, statistics updates, and query plans may not be optimized for billion-row workloads.
8. Long-running analytical/reporting queries may be affecting transactional/application workloads.

At this volume, normal indexing alone may not solve the issue. We need a proper data lifecycle and performance architecture.

Answer 1

Hey @Mallikarjun appani ,

it sounds like you’ve got a massive Azure SQL Database workload – billions of rows with ~20 million new records daily – and you’re hitting timeouts in your app and slow or failed Power BI refreshes. To go beyond reactive tuning, here’s a structured, end-to-end performance improvement strategy:

1. Pick the right service tier & architecture • Consider Hyperscale if you need virtually unlimited storage and super-fast backups. • For mixed OLTP/analytics, Business Critical gives you in-memory OLTP and low I/O latency. • Use Read-Scale-Out replicas (Hyperscale read replica or Business Critical read replica) to offload reporting queries.
2. Database design & partitioning • Implement date-based horizontal partitioning (sliding-window pattern). This lets you switch out or truncate old partitions instantly for purge/archive. • Place recent (“hot”) partitions on faster filegroups, older (“cold”) partitions on cheaper storage if you need tiered IO. • Ensure your queries filter on the partitioning column so that partition elimination occurs.
3. Indexing & statistics • For heavy analytical queries, create a clustered columnstore index; for hybrid workloads, use a nonclustered columnstore index on cold/historical data. • Add targeted nonclustered or filtered indexes on your most-common predicates (e.g. “WHERE date >= …”). • Enable AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC so the optimizer has up-to-date histograms. • Schedule regular index maintenance (rebuild/reorganize) and stats updates via Azure Automation during off-peak windows.
4. Query optimization & batching • Use Query Store and Query Performance Insight to find your top CPU/IO-consuming and timeout-prone queries. Analyze their execution plans and look for scans, spills, or parameter-sniffing issues. • Rewrite long-running queries to push filters as early as possible, avoid SELECT *, and eliminate unnecessary JOINs. • Batch your inserts/updates in explicit transactions rather than row-by-row operations to reduce transaction log pressure and IO overhead.
5. Reporting architecture & Power BI best practices • Offload heavy aggregations to pre-aggregated summary tables or materialized views with columnstore indexes. • Use Power BI incremental refresh for your date-partitioned tables: import “hot” partitions, DirectQuery or pre-aggregated model for “cold” partitions. • Consider Azure Analysis Services or Azure Synapse Analytics for complex semantic models and large dataset caching.
6. Archival & data lifecycle • Use the sliding-window partition technique to easily switch out old partitions into an archive table or separate archive database. • Purge data in small batches (or TRUNCATE entire partitions) during off-peak hours to avoid blocking and log-burst issues. • For very old data you rarely query, offload to Azure Data Lake or Synapse external tables.
7. Monitoring & automated tuning • Turn on Automatic Tuning for index creation/drop and plan correction. • Continuously monitor wait stats (PAGEIOLATCH, WRITELOG, CXPACKET), DMVs (sys.dm_db_wait_stats, sys.dm_exec_requests), and Azure Monitor metrics (CPU, DTU/vCore, IO). • Use Intelligent Insights and Query Performance Insight to catch emerging performance anomalies and get AI-driven recommendations.

Hope this gives you a repeatable, proactive framework to scale your Azure SQL deployment and keep both your app and Power BI reports snappy!

Reference list

1. Tune apps & DBs for performance in Azure SQL Database https://learn.microsoft.com/azure/azure-sql/database/performance-guidance?view=azuresql
2. Best practices for very large DB architectures (partitioning, batching) https://learn.microsoft.com/azure/azure-sql/database/performance-guidance?view=azuresql#best-practices-for-very-large-database-architectures-in-azure-sql-database
3. Optimize index performance https://learn.microsoft.com/azure/well-architected/performance-efficiency/optimize-data-performance?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider#optimize-index-performance
4. Optimize database queries https://learn.microsoft.com/azure/well-architected/performance-efficiency/optimize-data-performance?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider#optimize-database-queries
5. High IO Utilization troubleshooting https://supportability.visualstudio.com/AzureSQLDB/_wiki/wikis/AzureSQLDB.wiki/400633/SQL Database/Troubleshooting Guides/Performance and Query Execution/High IO Utilization
6. Architecture best practices for Azure SQL Database https://learn.microsoft.com/azure/well-architected/service-guides/azure-sql-database?wt.mc_id=knowledgesearch_inproduct_azure-cxp-community-insider#performance-efficiency
7. Best Practices for Purging Large Records https://supportabilityhub.microsoft.com/solutions/apollosolutions/121550d4-b6ca-04bf-2409-85a9871f11c2/58f5ea8a-fc5b-4547-8e55-431ce59d27ca
8. Partitioning guide https://learn.microsoft.com/azure/architecture/best-practices/data-partitioning

Answer 2

The AI has already answered by giving a number of generic recommendations, of which some may fit and others may be off-target.

To be blunt, this is not really a question suitable for a public forum. It more sounds like something I would get in the mail inquiring for my consultancy services. My reply would then be to suggest that we have an initial meeting of 1-2 hours, so I can get a better understanding of the situation and your system. Then I would probably ask you to collect data from your database: Table and index definitions, Query Store data etc that I would then analyse. No, I am not trying to pimp my consultancy business here, but I'm just giving an outline of how broad your question is, and what effort it would take to give a quality answer.

For the situation at hand, I like to know what service level you have. You are on Hyperscale, aren't you? (Because with those data volumes you should). You have implemented columnstore indexes, haven't you? (It sounds like they could be beneficial.)

If you have identified specific queries that are slow, trying to help you to improve their performance is certainly within the realm of this forum. We will still ask for information like query plans etc, but it still manageable.

Answer 3

A structured performance strategy for billion-row Azure SQL Database workloads must combine data partitioning, workload separation, query/index tuning, caching, and continuous monitoring. The goal is to reduce per-query data touched, minimize round trips, and isolate OLTP from heavy analytics.

1. Data and workload profiling

• Profile data and queries to understand volume, growth, and access patterns:
  • Examine table structures, relationships, and data types.
  • Analyze data volume and growth (billions of rows, +20M/day) to identify scalability limits.
  • Capture data distribution and skew to choose appropriate partitioning keys and indexes.
  • Identify which queries are OLTP (short, selective) vs. analytical/reporting (large scans, aggregates).
• Use Azure Monitor, Application Insights, and SQL insights to collect query performance, CPU, IO, and wait statistics.
• Use Query Performance Insight and query plans to identify slow queries, scans, and missing indexes.

References: profiling and monitoring guidance is described in:

• Profile data
• Architecture strategies for optimizing data performance

2. Partitioning and data lifecycle

• Implement horizontal partitioning (sharding) for very large tables:
  • Partition by a key aligned with access patterns, typically date/time for time-series or fact tables.
  • Use ranges (for example, daily/monthly partitions) so most queries hit only recent partitions.
  • For datasets too large for a single database, use cross-database sharding to distribute data across multiple Azure SQL databases.
• Consider functional partitioning:
  • Split different application functions into separate databases (for example, transactional OLTP vs. reporting/analytics vs. archival).
  • Each database can be scaled independently to match its workload.
• Implement a data lifecycle:
  • Keep only the hot/operational window (for example, last 3–12 months) in the primary OLTP tables.
  • Move older data to separate archival tables or databases (possibly with lower compute tiers) that are queried less frequently.
  • Ensure reporting tools (Power BI) use the appropriate source: hot store for current data, archive for historical.

References: partitioning and scale-out strategies are described in:

• Architecture strategies for optimizing data performance
• Partition data
• Tune applications and databases for performance in Azure SQL Database

3. Indexing strategy for billion-row tables

• Design indexes based on real query patterns and data distribution:
  • Use Query Performance Insight and DMVs to identify missing indexes and heavy scans.
  • Add targeted nonclustered indexes to support common filters and joins; avoid over-indexing.
• Use missing-index DMVs to identify high-impact indexes:
  • Evaluate suggested indexes from DMVs and create only those that significantly reduce cost.
• Maintain indexes regularly:
  • Reorganize or rebuild heavily fragmented indexes on large tables to maintain seek performance.
  • Ensure statistics are up to date so the optimizer can choose efficient plans.
• For large sort operations (ORDER BY, range queries), ensure appropriate indexes exist on the sort/filter columns.

References: index and query tuning guidance is described in:

• Tune your database
• Architecture strategies for optimizing data performance

4. Query and application design

• Reduce chatty behavior and round trips:
  • Batch ad hoc queries or move them into stored procedures.
  • Use stored procedures to improve plan reuse and reduce network overhead.
• Optimize queries:
  • Ensure selective WHERE clauses; avoid full scans on billion-row tables when possible.
  • Avoid “SELECT *” on large tables; project only required columns.
  • Ensure join predicates are indexed and use appropriate join types.
• Batch writes for high-ingest workloads:
  • Use explicit transactions and batched inserts instead of row-by-row autocommit.
  • Experiment with batching models to balance IO load and consistency requirements.
• Separate OLTP and reporting queries:
  • Move long-running analytical/reporting queries off the primary OLTP database where possible (for example, to a reporting/sharded database or another data store).

References: application and batching guidance is described in:

• Tune your application
• Tune applications and databases for performance in Azure SQL Database

5. Caching and read scaling

• Introduce an application-tier cache for read-heavy workloads:
  • Use Azure Cache for Redis to cache frequently accessed, relatively static data.
  • This reduces CPU and read IO on the database and can lower the required compute size.
  • Carefully evaluate consistency requirements; cached data may be slightly stale.
• Consider read replicas or separate read-optimized databases where supported to offload reporting reads from the primary.

References: caching and read optimization are described in:

• Tune applications and databases for performance in Azure SQL Database
• Architecture strategies for optimizing data performance

6. Reporting and Power BI architecture

• Avoid pointing Power BI directly at the hottest OLTP tables for heavy, full-scan reports:
  • Use a dedicated reporting database or data mart fed from the OLTP system (for example, via ETL/ELT or change-based loads).
  • Partition reporting tables similarly (for example, by date) to support incremental refresh.
• Optimize queries used by Power BI:
  • Ensure they are set-based, use appropriate filters, and leverage indexes.
  • Limit the data volume per refresh (for example, incremental refresh by date range instead of full reloads).
• For very large historical datasets, consider moving some analytics workloads to a more analytics-oriented store (for example, a data warehouse or lakehouse) while keeping Azure SQL focused on operational workloads.

References: data-store selection and optimization are described in:

• Architecture strategies for optimizing data performance

7. Archival and historical data strategy

• Separate current vs. historical data:
  • Keep current data in primary OLTP tables optimized for transactional performance.
  • Move historical data to separate tables or databases with different indexing and storage strategies.
• Align archival with partitioning:
  • Drop or switch out old partitions instead of deleting row-by-row to manage table size efficiently.
• Ensure reporting and applications are aware of where to query historical vs. current data.

References: partitioning and lifecycle concepts are described in:

• Partition data
• Architecture strategies for optimizing data performance

8. Continuous monitoring and tuning

• Use Azure Monitor, Application Insights, and SQL insights to:
  • Track query duration, timeouts, DTU/vCore utilization, IO, and waits.
  • Identify regressions after schema or index changes.
• Use automatic tuning where appropriate:
  • Enable automatic index management (create/drop) to help maintain optimal indexes.
• Periodically run configuration/design assessment scripts:
  • Use the open-source T-SQL script for improving database configuration and design in Azure SQL Database to get best-practice recommendations.
• Regularly review top resource-consuming queries and adjust indexes, partitioning, or query logic as needed.

References: monitoring and tuning automation are described in:

• Architecture strategies for optimizing data performance
• Tune applications and databases for performance in Azure SQL Database

This combined approach—profiling, partitioning/sharding, targeted indexing, query and application tuning, caching, workload separation, archival, and continuous monitoring—provides a sustainable performance architecture for Azure SQL Database at billion-row scale with tens of millions of daily inserts.

---

References:

• Tune applications and databases for performance in Azure SQL Database
• Architecture strategies for optimizing data performance
• Partition data
• Profile data