Microsoft
70-467 · Question #10
70-467 Question #10: Real Exam Question with Answer & Explanation
The correct answer is B: Partition the inventory fact table by day, and compress each partition.. Partitioning the inventory fact table by day with compression aligns partition boundaries with the primary query granularity and reduces I/O for a large, frequently loaded dataset.
Question
You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. Tens of millions of new inventory fact records are loaded into the data warehouse weekly, outside of business hours. Most queries against the database are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
Options
- APartition the inventory fact table by month, and compress each partition.
- BPartition the inventory fact table by day, and compress each partition.
- CPartition the inventory fact table by year.
- DPartition the inventory fact table by week.
Explanation
Partitioning the inventory fact table by day with compression aligns partition boundaries with the primary query granularity and reduces I/O for a large, frequently loaded dataset.
Common mistakes.
- A. Monthly partitions are too coarse for frequent day-level queries because the engine cannot eliminate sub-month data within a partition, forcing scans across an entire month's rows when only a single day is requested.
- C. Yearly partitions provide no useful partition elimination for day-level or month-level queries, causing the engine to scan an entire year's data for most workloads and offering no I/O benefit.
- D. Weekly partitions do not align with either the primary day-level or secondary month-level query patterns, preventing effective partition elimination and leaving the most frequent access granularity unoptimized.
Concept tested. SQL Server fact table partitioning granularity and compression
Reference. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
Community Discussion
No community discussion yet for this question.