Interesting facts about SQL Server 2014 Memory optimized tables and Natively compiled stored procedures

 

  • Stored Procedures that reference only memory-optimized tables can be natively compiled into machine code for additional performance improvements
  • Only one MEMORY_OPTIMIZED_DATA Filegroup can be created on each database
  • In Memory OLTL is Lock and Latch Free
  • In-Memory OLTP is available only in Enterprise edition, 64 Bit system
  • In-Memory tables are durable (Table data can be retrieved even during database restart, and Server crash)
  • In-Memory table indexes will be rebuild during database startup
  • In-Memory tables does not allow creating or dropping the new index after creation
  • Clustered Indexes cannot be created on In-Memory tables (Has and Range indexes can be created)
  • Disk based tables cannot be accessed from natively compiled stored procedure
  • In-Memory data is not stored in Data Files but will be stored as Blob-Data
    • Table Structures will be stored in Compiled format in Disk
  • Table can be defined as < SCHEMA_AND_DATA> or < SCHEMA_ONLY> which defines what to restore during SQL Server crash
  • Row size limit of In-Memory table is 8060 bytes
  • Maximum of 8 Indexes are supported on in-memory tables.
  • Sp_spaceused procedure will not be able to get actual space usage details of In-Memory tables but dm_db_xtp_table_memory_stats DMV can be used
  • For Natively compiled stored procedures, plan will not be stored in Procedure Cache
  • In-Memory table operations will not be considered for parallelism
  • For Natively Compiled stored procedure, Execution stats will not be stored by default (in SYS.DM_EXEC_QUERY_STATS). In a way, it helps to improve performance 🙂
  • You will see amazing performance if you select DURABILITY as SCHEMA_ONLY as the data will be stored only in memory and IO will not be used at all.

 

Advertisements
This entry was posted in knowledge, Productivity, SQL, sql2014 and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s