Author: Dmitri Korotkevitch 
Publisher: Apress
ISBN: 978-1484227718
Print: 1484227719
Kindle:B075D4ZNWG

Chapter 11 Garbage Collection

This chapter opens with an overview of the Garbage Collection process. Updates create a new row rather than update data, deletes mark a row as deleted via the EndTs column. All these processes leave data that needs to be removed – this is done via Garbage Collector. The Garbage Collector analyzes a list of completed transactions, and with the Oldest Active Transaction Timestamp determines which data can be collected. A helpful Garbage Collection workflow diagram is provided.

The chapter ends with a look at the various DMVs that can be used to investigate the Garbage Collection process, and example code provided. The DMVs discussed are:  

• sys.dm_xtp_gc_stats statistics about the garbage collection process

• sys.dm_xtp_gc_queue_stats information about garbage collector worker queues

• sys.dm_db_xtp_gc_cycle_stats info about the last garbage collection execution cycles

• sys.dm_db_xtp_index_stats includes several garbage collection-related metrics 

This chapter provides an interesting discussion about the Garbage Collection process. Useful example code is provided to investigate the process. I note DMVs are described throughout the book as Data Management Views instead of Dynamic Management Views.

Chapter 12 Deployment and Management

This chapter opens with a look at hardware and how it impacts In-Memory OLTP. In-Memory OLTP typically uses hardware more efficiently, so it might be feasible to use mid-level servers. It’s also highly scalable, so adding CPUs, memory, and IO drives can have a noticeable and predictable impact on performance. The impact of increasing CPUs, memory, and the IO subsystem are all discussed.

The chapter continues with a look at administration and monitoring. It’s important to remember that In-Memory tables need to share resources with other SQL Server components. Resources can be allocated via the Resource Governor, and examples code for this is given and discussed. Monitoring is largely done via DMVs, and again sample code is discussed.

The chapter next looks at how In-Memory OLTP integrates with Query Store to record performance metrics. Unlike classic SQL code, recording metrics needs to be enabled – this is disabled by default since it can impact the performance of In-Memory OLTP.

The chapter ends with a look at the various system objects that can report on In-Memory OLTP structures, with some useful example code. These include: catalog views, DMVs, Extended Events, and Performance Counters.

This chapter provides a useful discussion on how hardware impacts In-Memory OLTP. Additionally, admin and monitoring features are discussed. I’m not convinced deployment is discussed. Useful code is provided to identify hash indexes with suboptimal bucket counts. Code is supplied to identify the oldest active transactions, these are often the cause of problems e.g. preventing the Garbage Collector working – however, adding the transaction start time (or calculating the duration) would increase the code’s usefulness.

Chapter 13 Utilizing In-Memory OLTP

In many ways, this is the most important chapter of the book, all the previous chapters have been building towards it, using experience to provide best practices and recommendations.

The chapter opens with a look at design considerations for using In-Memory OLTP. Existing systems may need to undergo changes before migrating to In-Memory OLTP. Cost/benefit analysis should be performed, together with adequate testing.

Some tools that help with migration to In-Memory OLTP are included in SQL Server, and these are discussed in an appendix. Some remaining limitations of In-Memory OLTP are listed, and some solutions are given, with code examples, including:  

• Off-Row Storage

• Unsupported Data Types

• Index Considerations

• Continuous Maintenance 

Next, the chapter discusses using In-Memory OLTP with mixed workloads. It’s suggested to split a table into 2, one table containing the latest ‘hot’ data stored In-Memory, and another on-disk table containing the historic data. The data tables can be combined in a view to hide the storage detail from the client applications. This approach also allows differing index strategies to be applied. Code examples are provided throughout.

The chapter ends with an interesting section on using In-Memory OLTP for non-obvious use cases, these include: processing batches of data, replacing temp and staging tables, and using In-Memory OLTP for session state.

This chapter provides a very useful insight into using In-Memory OLTP in a practical manner. The section on overcoming the limitations and using In-Memory OLTP in mixed workloads are particularly useful.

Appendices  

• Appendix A - Memory Pointers Management

• Appendix B - Page Splitting and Page Merging in Nonclustered Indexes

• Appendix C - Analyzing the States of Checkpoint Files

• Appendix D - In-Memory OLTP Migration Tools 

The last appendix is especially useful, providing brief instructions on using the various included tools to identify the tables and stored procedures that would benefit most from migration to In-Memory OLTP. Noticeably, this can now be done easily using the Transformation Performance Analysis Overview report, rather than the previously cumbersome installation of the Management Data Warehouse (MDW) tool.

Conclusion

This book has well-written discussions, helpful examples, diagrams, website links, inter-chapter links, and useful chapter summaries. It contains plenty of practical code to help you understand the subject matter.

In-Memory OLTP is the primary reason to move from older versions of SQL Server (pre 2014), it typically provides much faster processing. Dmitri Korotkevitch maintains a good flow between chapters, discussing what In-Memory OLTP is and how to implement it, culminating in a collection of impressive recommendations and best practices.

Any criticisms I have are exceedingly minor. The book contains around 70% overlap with the author’s first edition (for SQL Server 2014), and also the broader Pro SQL Server Internals (2^nd ed) book (see my review of the first edition), which for me, that is the best SQL Server internals book I’ve read.

In-Memory OLTP was previously only available in the Enterprise and Developer editions of SQL Server, however, since SQL Server 2016 Service Pack 1 it is now available in all editions of SQL Server (including Express) - so there’s no excuse for not investigating it.

If you want a practical discussion on In-Memory OLTP, including recommendations and best practices, I can highly recommend this erudite book.

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.