Developing SQL Databases — Question 128

You manage a database that supports an Internet of Things (IoS) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995% uptime.
The database uses a table named Checkins that is 100 gigabytes (GB) in size. The Checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use stored procedures for all access to the tables.
You observe that the wait type PAGELATCH_IO causes large amounts of blocking.
You need to resolve the blocking issues while minimizing downtime for the database.
Which two actions should you perform? Each correct answer presents part of the solution.

Answer options

• A. Convert all stored procedures that access the Checkins table to natively compiled procedures.
• B. Convert the Checkins table to an In-Memory OLTP table.
• C. Convert all tables to clustered columnstore indexes.
• D. Convert the Checkins table to a clustered columnstore index.

Correct answer: A, B

Explanation

The correct answers are A and B because converting stored procedures to natively compiled procedures and changing the Checkins table to an In-Memory OLTP table both enhance performance and reduce blocking. Options C and D do not directly address the blocking issues related to the stored procedures and the current table structure, which is critical for resolving the identified problem.