In Memory Tables

Question 1

13 Syntax Limits of Natively Complied Stores Procedures

Answer 1

1. Cannot Access Tempdb
2. Cannot use Cursors
3. Cannot use CASE statement
4. Cannot use MERGE statement
5. Cannot use SELECT INTO clause
6. Cannot use PERCENT or WITH TIES in TOP clause
7. Cannot use DISTINCT with Aggregates
8. Cannot use the following operators: INTERSECT, EXCEPT, APPLY, PIVOT, UNPIVOT, LIKE, CONTAINS
9. Cannot use Common Table Expressions
10. Cannot use multi row insert statements
11. Cannot use EXECUTE WITH RECOMPILE
12. Cannot use Views
13. Cannot use FROM Clause in update statement

Question 2

What system stores procedure do you use to enable collection of stored procedure level stats on natively compiled stored procedures?

Answer 2

Sys.sp_xtp_control_proc_exec_stats

Question 3

What system stored procedure do you use to enable the collection of query level stats on natively compiled stored procedures?

Answer 3

Sys.sp_xtp_control_query_exec_stats

Question 4

How would you explicitly tell SQL to make data durable for an in memory table?

Answer 4

Use the DURABILITY = SCHEMA_AND_DATA

For instance:
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA

Question 5

What are the durability options to make in memory tables more performant?

Answer 5

1. Set the durability option on the table creation

Example DURABLITY = SCHEMA_ONLY

2. Set the delayed durability option at the database level

Example ALTER DATABASE WorldWideTraders SET DELAYED_DURABILITY = ALLOWED

3. Set the delayed durability at the transaction level

COMMIT TRANSACTION WITH (DELAYED_DURABLITY = ON)

Question 6

What are good cases for natively compiled stored procedures?

Answer 6

1. Applications needing the best possible performance
2. Queries that execute frequently
3. Aggregation
4. Nested Koop joins
5. Multi statement select, insert, update or delete operations
6. Complex expressions
   Procedural logic such as conditions and loops

Question 7

Use Cases for In Memory Tables

Answer 7

1. High data ingestion rates
2. High Volume, high performance data reads
3. Complex business logic in stored procedures
4. Real time data access
5. Session state management
6. Applications relying heavily on temporary tables, table variables and table valued parameters
7. ETL operations