Exam 70-764 topic 1 question 80 discussion

Actual exam question from Microsoft's 70-764

Question #: 80
Topic #: 1

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?

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

Show Suggested Answer

Suggested Answer: C 🗳️

by TheSwedishGuy at Jan. 24, 2020, 3:02 p.m.

Comments

Submit Cancel

Slava_bcd81

4 years, 9 months ago

interesting how can the deletions can cause contention with inserts, I can imagine that only if there is no index on the Data column

upvoted 2 times

Zikato

4 years, 5 months ago

When you take more than 5k locks of the same granularity - it will escalate to a table lock, which will prevent all modifications. When partitioning the table, you can just switch out and delete the oldest partition - meta data operation only.

upvoted 2 times

...

TheSwedishGuy

5 years, 5 months ago

The partitioned table increases performance on very large tables by dividing them into smaller parts. This reduces locks but does not get rid of them totally.

upvoted 2 times

...