Exam 70-762 topic 1 question 153 discussion

Actual exam question from Microsoft's 70-762

Question #: 153
Topic #: 1

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.

Show Suggested Answer

Suggested Answer: AB 🗳️
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

by Slava_bcd81 at Aug. 17, 2020, 7:15 a.m.

Comments

Submit Cancel

Cococo

Highly Voted 4 years, 10 months ago

According to the study guide, 70-762 on page 307 "A common scenario for this workload is the Internet of Things in which multiple sensors are sending data to SQL Server....The use of memory-optimized tables in this scenario eliminates contention between read and write operations and retrieves data with lower latency, while the use of natively compiled stored procedures enables code to execute faster.." So, I think A + B (Page IO Latches (not locks) because of outstanding I/O and can be fixed if table is in the memory and SPs are natively compiled)

upvoted 10 times

...

giuPigna

Highly Voted 4 years, 8 months ago

ColumnStore answers are wrong because of lot insert: "The database records metrics from over 100 million devices every minute". So correct answer is AB.

upvoted 5 times

...

Slava_bcd81

Most Recent 4 years, 11 months ago

I think: - A is n/a for PAGELATCH IO - C is applicable as data in columnstore is compressed, so IO is faster + due to "deltastore" + specific delition columnstore must be faster

upvoted 1 times

melvin9900

4 years, 11 months ago

Changing all tables to clustered columnstore indexes will cause downtime ?

upvoted 1 times

Slava_bcd81

4 years, 11 months ago

should not cause any significant downtime that would breach 99,99% uptime demand

upvoted 1 times

julie2020

4 years, 11 months ago

so, B and C ??

upvoted 1 times

...