Author: Kalen Delaney
Publisher: Red Gate Books
Pages: 218
ISBN: 9781910035030
Audience: DBAs and developers
Rating: 5
Reviewer: Ian Stirk

Chapter 7 Native Compilation of Tables and Stored Procedures
Just as In-Memory tables are compiled, stored procedures can also be compiled to run faster than the corresponding interpreted stored procedures. Natively compiled stored procedures can only run against In-Memory tables, and are good for aggregation, nested-loop joins, and procedural logic.
The chapter opens with a look at what native compilation is. It converts T-SQL to native CPU instructions, these have the dual advantage of having fewer instructions (CPU cycles) and requiring no further interpretation or compilation, compared with interpreted T-SQL. Specifically, the Visual C compiler is used to create native code. In-Memory tables and natively compiled stored procedures are compiled at create time, if SQL Server is restarted they are compiled again on first use.
The chapter digs deeper into natively compiled stored procedures, which must specify WITH NATIVE_COMPILATION, WITH SCHEMABINDING, and BEGIN ATOMIC. The query plan is compiled into the DDL, so if the underlying In-Memory tables need to change, the natively compiled stored procedures need to be dropped and recreated. The T-SQL permitted typically reflects simple OLTP processing, in fact there's a list of allowed T-SQL commands – this being smaller than the T-SQL that's not allowed!
Luckily, parameter sniffing is not a problem with natively compiled stored procedures, since all parameters are considered to have UNKNOWN values. There are helpful diagrams illustrating both the compilation and execution of natively compiled stored procedures.
There's a useful section on the optimization considerations of natively compiled stored procedures, including:

• Index access paths (if a good index can't be found, all the rows will be read. Hash index: can't be used if results need to be ordered, all key columns need to be provided, and it needs to be an equality search. Range index: can't be scanned in reserve order – need another index. Can have hash and range index on same columns)
• No parallel plans allowed
• No auto-update of statistics (Statistics on In-Memory tables are not automatically updated, since it would degrade performance. Instead the statistics should be updated separately, note that plans are not recreated, so the stored procedure should be dropped and recreated to create a new plan based on the updated statistics)

The chapter ends with a performance comparison of traditional interpreted stored procedures and natively compiled stored procedures, running against In-Memory and disk-based tables, for various transaction sizes. The results show that running natively compiled stored procedures against In-Memory tables (that are SCHEMA_ONLY, no logging) are fastest. The commonly used DMV for collecting query metrics (sys.dm_exec_query_stats) is disabled for natively compiled stored procedures, it can be enabled for monitoring and debugging, but will affect performance.
This chapter takes a look at what native compilation is, why it results in faster code, and considers its limitations, and side-effects. Various performance optimization considerations are discussed. Examples code is provided to compare the effect of In-Memory tables and natively compiled stored procedures with their traditional counterparts (disk-based tables and interpreted stored procedures).
There is a small error on p 171, which states "... the improvement is greater than 20%.", this should read "...the improvement is greater than 20 times."

Chapter 8 SQL Server Support and Manageability
The chapter opens with a look at the features that support In-Memory OLTP e.g. backup/restore, AlwaysOn, but not mirroring. Next the various advisors are discussed. The Native Compilation Advisor checks if a stored procedure can be natively compiled (remember it uses a subset of T-SQL), and reports on any violations. The Memory Optimizer Advisor performs the same task for tables.
The chapter continues with a look at memory allocation and management. When there is pressure on memory, the In-Memory OLTP manager will aggressively reclaim space from old row versions. It is suggested that the amount of disk-based table memory is doubled when moving the table to In-Memory OLTP.
Memory can be managed via the Resource Governor, and suggestions for this are provided. Additionally, a new database dashboard report can be used to provide a snapshot of the current memory usage, which can be useful for monitoring purposes.
The chapter then looks at enhancements to existing metadata objects, these include:

• Catalog view enhancements (e.g. sys.tables, sys.indexes)
• DMOs (many are given with a usage summary provided)
• XEs (useful for monitoring and troubleshooting)
• Performance counters (various main categories are described briefly)

Next, best practices for designing In-Memory tables and indexes are given. Since many suggestions are given throughout the book, this is a recap. The author acknowledges due to the newness of the technology, best practices are still being formulated. Suggestions for best practices include:

• Use collation at the column level
• Do not under or over estimate bucket count in hash indexes
• For columns with low cardinality (many duplicates) create range index
• Consider updating the statistics and dropping/recreating natively compiled stored procedures, when the data distribution changes

The chapter ends with a look at migrating to In-Memory OLTP. The preferred applications to migrate are the ones that take specific advantage of the new functionality i.e. those with bottlenecks that relate to lock/latch contention, I/O and logging, and/or hardware resource limitations. These applications may have lots of inserts, selects and/or be CPU intensive. Unsuitable migration candidates include those that depend on T-SQL that can't be migrated, those with memory limitations, non-OLTP workloads, and applications that depend on locking behaviour.

A brief list of companies that have seen significant performance improvements from using In-Memory OLTP is given, together with the relevant website links to the case studies – this might prove useful in your own research.
Finally, the Analysis, Migrate and Report (AMR) tool is discussed. This tool uses a defined workload (created via the Management Data Warehouse [MDW] tool) to determine the best tables and stored procedures to migrate to In-Memory. Various useful reports are produced.
This chapter contains a miscellany of topics loosely tied together. Some useful best practices are reiterated, and helpful guidance on the type of applications to migrate provided. The AMR and the advisors tools should prove useful starting points in the migration process. Perhaps more detail could have been provided for the use of XEs and the AMR tool, but these are minor concerns.

Conclusion
This book covers a well-defined topic in-depth, with the central aim of showing you how In-Memory technology is implemented internally, together with some best practices for its usage. The book is both cohesive and coherent, probably due to having been written by a single author. The whole book contains useful links between the chapters, helpful diagrams to support the discussions, useful website links for further information, and code to help cement your own understanding
Although the book's introduction says you don't need to be a SQL Server expert to read it, it quickly becomes apparent you are going to go deep into the subject matter.

If you want to get up to speed with the new In-Memory OLPT technology, the major reason for moving to SQL Server 2014, I cannot recommend this book highly enough – in my opinion this is how a technology book should be written, concise and with depth.