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
Q

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

A

With systems that process large numbers of SQL Inserts concurrently

2
Q

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

A

With systems that process heavy calculations in T-SQL

3
Q

Do In Memory OLTP tables have pages?

A

No

4
Q

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

A

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

5
Q

Is the log file bigger or smaller with OLTP?

A

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

6
Q

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

A

*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
Q

What are the limitations of a Natively Compiled Stored Proc?

A

Can’t access disk based table

Proc must be recompiled after a restart

8
Q

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

A

It is the indexes that connect the rows together

9
Q

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

A

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

10
Q

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

A

A nonclustered primary key

11
Q

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

A

Single Item Lookups

12
Q

What shouldn’t you use hash indexes for?

A

Like/Range Operations

13
Q

What are some issues with hash indexes?

A

Static Size determined at creation time

Can suffer from link crawling

14
Q

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

A

Nonclustered Index

15
Q

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

A

Range Index (BW-TREE)

16
Q

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

A

No