In Memory OLTP Tables & Natively Compiles SPs Flashcards Preview

70-464 Certification > In Memory OLTP Tables & Natively Compiles SPs > Flashcards

Flashcards in In Memory OLTP Tables & Natively Compiles SPs Deck (16)
Loading flashcards...
1

When is it beneficial to use an In Memory OLTP Table?

With systems that process large numbers of SQL Inserts concurrently

2

When is it beneficial to use a natively compiled stored procedure?

With systems that process heavy calculations in T-SQL

3

Do In Memory OLTP tables have pages?

No

4

Are In Memory OLTP Table Transactions Pessimistic or Optimistic regarding isolation?

Optimistic. They use Row Versioning to handle Isolation (Snapshot isolation level)

5

Is the log file bigger or smaller with OLTP?

Smaller. The before and after versions of the updated rows are held in the memory table.

6

What settings need to be set to enable memory optimized tables?

*SET COMPATIBILITY_LEVEL >= 130
*SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
CREATE AN OPTIMIZED FILEGROUP:
ADD FILEGROUP [GROUPNAME] CONTAINS MEMORY_OPTIMIZED_DATA
ADD FILE (NAME='NAME', FILENAME='C:\PATH') TO FILEGROUP [GROUPNAME]

7

What are the limitations of a Natively Compiled Stored Proc?

Can't access disk based table
Proc must be recompiled after a restart

8

Why must you have at least 1 index for an Memory Optimized OLTP Table?

It is the indexes that connect the rows together

9

How often are changes to indexes on an Memory Optimized OLTP table written to disk?

Never. Only the data rows and changes to the data are written to disk and the log file

10

What is necessary to be able to create the Memory Optimized OLTP table with DURABILITY = SCHEMA_AND_DATA?

A nonclustered primary key

11

What are hash indexes good for with regard to Memory Optimized OLTP Tables?

Single Item Lookups

12

What shouldn't you use hash indexes for?

Like/Range Operations

13

What are some issues with hash indexes?

Static Size determined at creation time
Can suffer from link crawling

14

Which is faster when using inequality predicates, nonclustered index or nonclustered hash index?

Nonclustered Index

15

What is the other name a nonclustered index for a Memory Optimized OLTP table known for?

Range Index (BW-TREE)

16

Do you have to specify the size of the buckets when creating a nonclustered (non hash) index?

No