Question

You manage an Azure SQL Managed Instance hosting a 2 TB sales database. The Orders table contains eight years of data and grows by roughly 20 million rows per month. Analysts run reports that almost always filter on an OrderDate range covering a single quarter, but queries currently scan the entire table, causing high I/O and slow response times. You must reduce query I/O for these date-filtered reports while keeping the ability to efficiently remove data older than seven years each year. Which approach should you implement?

Accepted Answer

Implement table partitioning on the Orders table using a partition function on OrderDate with a partition scheme mapping partitions to filegroups.

Answer

Enable page-level data compression on the Orders table and its indexes.

Answer

Create a nonclustered columnstore index on the Orders table.

Answer

Move the database to the Hyperscale service tier to increase available storage and I/O throughput.