In Memory OLTP Tables & Natively Compiles SPs Flashcards
When is it beneficial to use an In Memory OLTP Table?
With systems that process large numbers of SQL Inserts concurrently
When is it beneficial to use a natively compiled stored procedure?
With systems that process heavy calculations in T-SQL
Do In Memory OLTP tables have pages?
No
Are In Memory OLTP Table Transactions Pessimistic or Optimistic regarding isolation?
Optimistic. They use Row Versioning to handle Isolation (Snapshot isolation level)
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.
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]
What are the limitations of a Natively Compiled Stored Proc?
Can’t access disk based table
Proc must be recompiled after a restart
Why must you have at least 1 index for an Memory Optimized OLTP Table?
It is the indexes that connect the rows together
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
What is necessary to be able to create the Memory Optimized OLTP table with DURABILITY = SCHEMA_AND_DATA?
A nonclustered primary key
What are hash indexes good for with regard to Memory Optimized OLTP Tables?
Single Item Lookups
What shouldn’t you use hash indexes for?
Like/Range Operations
What are some issues with hash indexes?
Static Size determined at creation time
Can suffer from link crawling
Which is faster when using inequality predicates, nonclustered index or nonclustered hash index?
Nonclustered Index
What is the other name a nonclustered index for a Memory Optimized OLTP table known for?
Range Index (BW-TREE)
