Which two actions should you perform?

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.
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.

View Answer

Answer: AB

Explanation:

Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.

SQL Server In-Memory OLTP helps improve performance of OLTP applications through efficient, memory-optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In-Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored­procedures

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables