Gathering Information On Memory-Optimized Objects

Ned Otter has a diagnostic script for learning about how your servers are using memory-optimized objects:

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

  • 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

Ned provides the script on his blog, so click through to get that.  This looks great if you’re trying to build up some basic information on how developers in your environment use memory-optimized objects.

Related Posts

What’s New With In-Memory OLTP In SQL Server 2019

Ned Otter gives us two things to look forward to with SQL Server 2019: So far, there’s been only one publicly announced enhancement for In-Memory OLTP in SQL 2019: system tables in TempDB will be “Hekatonized”. This will forever solve the issue of system table contention in TempDB, which is a fantastic use of Hekaton. […]

Read More

Don’t Set Max Size For Containers In In-Memory OLTP

Ned Otter recommends you not mess with the maximum container size when creating a memory-optimized filegroup: I recently saw a thread on twitter, where the OP talked about setting the max size for an In-Memory OLTP container. I responded as I always do: it’s not possible to set a limit on anything having to do […]

Read More


October 2017
« Sep Nov »