MOSL – Memory Optimised Scripts Library

SQL Server 2014 was one amazing release, which had a very interesting codename – “Hekaton”, meaning that the principal news in that release were the Memory-Optimised tables (aka In-Memory, aka In-Memory OLTP aka Hekaton) and even though for me personally the biggest highlight were the Columnstore Indexes, I saw the first release of the Memory-Optimised feature as the very early feature-incomplete version of the potentially incredible product. I have blogged about my thoughts in 2014 in the post Hekaton 2014 – The Saviour vs The Lost Cause.
Time goes and over 2 years later we already have a SQL Server 2016 with awesome improvements for the Memory-Optimized tables, with functions, triggers, etc now being memory
Besides that, we can finally alter the memory-optimised tables and even add a columnstore index for amazing performance. :)

Well, if you are following my blog or if you are using Columnstore Indexes professionally, you should been using the CISL (Columnstore Indexes Scripts Library).

MOSL – Memory Optimised Scripts Library

Today, I am announcing the initial availability of the brand new library for diving into the internals of SQL Server the MOSL – Memory Optimised Scripts Library, the library that will help you to discover, maintain & troubleshoot the Hekaton in SQL Server 2014, 2016 & Azure SQLDatabase. At the moment, I am releasing the scripts for SQL Server 2014 but at the PASS Summit I am hoping to have all 3 versions online.

I am inviting everyone working with Memory-Optimised Technology to join me in this project, which is targeted on helping everyone who is working or aspiring to work with this amazing and ground-breaking technology.

At this moment of the announcement, I am making available a preview version of SQL Server 2014 & SQL Server 2016 scripts. As with my other library, I am making the scripts with 2 versions – the direct querying script, the 2nd version of the script is the stored procedure
The current scripts are here as following:

1. CheckpointFiles.sql – provides information on the Checkpoint Pair Files, with overview as well as the detailed information on each of the files, along with their size, number of inserted & deleted rows and even Begin & End TSN.

2. Database.sql – provides with the information on the Database configuration for the Memory Optimised Objects, File Path, Number of Memory Optimised tables and the Resource Governor Configuration that is associated with this database.

3. garbage_collector.sql – gives basic statistics on the Garbage Collector. Can be quite useful when monitoring a Memory-Optimized Database Behaviour.

4. hash_indexes.sql – allows you to discover the important facts about hash indexes of the memory optimised tables, such as Buckets Numbers (Total & Empty), Chain Length (Average & Maximum), and the last statistics update (this is very useful for SQL Server 2014, since there is no automated statistics recalculation support)

5. loaded_modules.sql – tries to decode the loaded DLLs in the memory, by their respective Object ID

6. memory_optimised_objects.sql – gives you an overview of all memory optimised objects (tables, stored procedures, etc) and their basic informations. Plus it shows the dependencies including memory optimised objects, such as the stored procedures or views that are using particular memory optimised table.

7. memory_optimised_tables.sql – provides the detailed information on the memory-optimised tables, with details on the memory usage by the table and its indexes, numbers and types of the indexes.

8. sqlserver_instance_info.sql – gives information on your current SQL Server version, the newest SQL Server Cumulative Updates & Service Packs, plus the details on the solved Memory-Optimised bugs that were fixed in the versions after installed one.

Coming up later this year:
Suggested_Objects.sql – List of the objects that can and should be converted to memory optimised ones.
Maintenance.sql – Maintenance Suggestions and Solution for Memory Optimised Tables.

Notice that the moment I am not publishing a read to production release, but rather the collection of the scripts that will be updated regularly.

If you are looking for a good book on that matter – consider Kalen Delaney “SQL Server Internals: In-Memory OLTP”, I highly recommend it. This book is a 2014 edition, but it will give you a great overview of the internals and principles so you can get started. I have also remember seeing on twitter a couple of days ago that Kalen announced that a new, 2016 edition of her book will be coming out very soon – that will be another reason to get ready for it.

Please try out MOSL – Memory Optimised Scripts Library and let me know if you find it useful or find any bugs!