RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

The 1% Solution

Posted by rjssqlservernotes on March 19, 2014

The new SQL Server 2014 In-Memory OLTP feature is intended to provide a solution to a very specific problem, it is not intended to be a generic performance enhancement for general database environments (this is not your daddy’s PINTABLE command). The specific “targeted scenarios” for the use of In-Memory OLTP were:

  • High-End OLTP workloads with extreme high write rate, like ticker data in stock trading, session state for very large websites or other applications with high change rates to data causing synchronization issues on block/page level
  • Applications where the business logic is realized as stored procedures running on SQL Server. We usually look at Web applications using such type of architecture.
Source: Juergen Thomas, Microsoft

The In-Memory database engine is a distinct component that runs in parallel with the traditional database engine and supports the execution of precompiled stored procedures. Precompiled stored procedures have many advantages with which come limitations:

No Cursors
No multi-row inserts
No CTEs
No sub-queries
Can only access In-Memory OLTP tables

In-Memory OLTP is available for commercial use only in Enterprise Edition and requires servers configured with large amounts of memory (the entire In-Memory configured table and its projected growth must fit into available memory along with everything else that’s already using memory) and significant CPU horsepower.

The new SQL Server 2014 In-Memory OLTP feature is exciting for a PTO DBA to ponder; however, implementation will (and should) be limited to very intensive OLTP environments with big budgets – the SQL Server elite.

Advertisements

Sorry, the comment form is closed at this time.

 
%d bloggers like this: