In-Memory OLTP diagnostic script

For a while now, I’ve been working on a script to evaluate a SQL Server environment for anything related to In-Memory OLTP.

I now offer it to the world, with the usual caveat: use at your own risk. It’s is a work in progress, so check back here now and then to see if anything has been added.

The script reports on two categories: instance level and database level.

Instance level evaluates the following:

  • which databases are memory-optimized
  • if running Enterprise, if there are any resource groups defined, and which databases are bound to them
  • version/edition of SQL server
  • ‘max memory’ setting
  • whether or not instance-level collection of execution statistics has been enabled for all natively compiled stored procedures
  • memory clerks for the buffer pool and In-Memory OLTP
  • the value of the committed_target_kb column from sys.dm_os_sys_info
  • display any event notifications (because they conflict with deploying In-Memory OLTP

Database level evaluates the following:

For each memory-optimized database:

  • database files, including container names, size, and location
  • memory-optimized tables
  • indexes on all memory-optimized tables
  • count of indexes per memory-optimized table
  • natively compiled stored procedures
  • which native modules are loaded (stored procedures only)
  • whether or not the collection of execution statistics is enabled for any natively compiled procedures
  • count of natively compiled procedures
  • if using the temporal feature for memory-optimized tables, the amount of memory consumed by hidden temporal internal tables
  • memory structures for LOB columns (off-row)
  • average chain length for HASH indexes
  • memory-optimized table types

In the comments, please let me know other things about the in-memory environment or databases you’d like to see included in the script.

12 thoughts on “In-Memory OLTP diagnostic script

  1. Pingback: Gathering Information On Memory-Optimized Objects – Curated SQL

  2. Ned Otter Post author

    Irving,

    Line 713 as written is 100% valid.

    Perhaps your issue is that you don’t have ‘show advanced options’ enabled for sp_configure.

    Reply
  3. Ned Otter Post author

    Hi Konstantin,

    Thanks very much for your cdmment, and apologies for the case ‘insensitivity’ of my script. Will fix that in the near future.

    Reply
      1. Ned Otter Post author

        This script is so new, I think it needs to stabilize a bit before it’s ready for prime time. So let’s be in touch about that possibility in the near future. We can connect on twitter and DM, or you can fill out the contact form on this site with your contact info, and I’ll be in touch.

        Also, there is a survey that I published today about issues blocking wider adoption of In-Memory OLTP. Link here:

        bit.ly/2zUIgAI

        By the way, are you using In-Memory OLTP in production?

        Thanks again for your interest in my script.

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *