70-465 Question #49: Real Exam Question with Answer & Explanation

Question

During performance testing, you discover that database INSERT operations against the Inventory table are slow. You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. What should you recommend?

Options

• APartition the nonclustered index.
• BPartition the Inventory table.snapshot replication
• CCreate a column store index.Master Data Services
• DDrop the clustered index.change data capture

Explanation

When INSERT operations are slow on a table, partitioning the nonclustered index can reduce contention and improve write performance by distributing index maintenance across partitions.

Common mistakes.

• B. Partitioning the Inventory table alone without aligning the nonclustered indexes may not resolve INSERT slowness caused by index maintenance overhead, and table partitioning addresses data storage distribution rather than directly optimizing write performance on existing indexes.
• C. Columnstore indexes are optimized for analytical read (OLAP) workloads involving large scans and aggregations; they introduce additional overhead during DML operations like INSERT, making write performance worse rather than better.
• D. Dropping the clustered index would convert the table to a heap, which may speed up some inserts but eliminates the benefits of ordered data retrieval, can worsen read performance significantly, and is generally not a recommended production solution for improving INSERT performance.

Concept tested. Partitioned nonclustered index optimization for INSERT performance

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes

No community discussion yet for this question.