Administering a SQL Database Infrastructure — Question 87

You administer a Microsoft SQL Server 2016 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.
Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention withinserts.
You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log.
Which type of solution should you use?

Answer options

• A. Extended events
• B. Columnstore index
• C. Partitioned tables
• D. Read committed snapshot

Correct answer: C

Explanation

The correct option is C, Partitioned tables, because partitioning allows for managing large datasets efficiently, enabling purging of older partitions without locking the entire table. Options A and D do not address the locking issue during inserts, while option B, Columnstore index, is primarily for optimizing read performance and does not resolve contention from concurrent write operations.