70-463 · Question #133
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most…
The correct answer is D. Partition the fact table by day, and compress each partition. Partitioning by day with compression is the best strategy given the query patterns. Since data is frequently queried at the day level, day-level partitions enable partition elimination - SQL Server reads only the relevant day's partition rather than scanning the entire table…
Question
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- APartition the fact table by month, and compress each partition.
- BPartition the fact table by week.
- CPartition the fact table by year.
- DPartition the fact table by day, and compress each partition.
How the community answered
(26 responses)- A8% (2)
- B12% (3)
- C23% (6)
- D58% (15)
Explanation
Partitioning by day with compression is the best strategy given the query patterns. Since data is frequently queried at the day level, day-level partitions enable partition elimination - SQL Server reads only the relevant day's partition rather than scanning the entire table, directly maximizing query performance. Adding row or page compression to each partition reduces I/O further, improving performance for the large read workloads from reports and cube processing. Monthly partitions (option A) are too coarse for frequent day-level queries and offer less partition elimination. Weekly (option B) and yearly (option C) partitions also fail to align with the primary day-level query granularity.
Topics
Community Discussion
No community discussion yet for this question.