sp_BlitzInMemoryOLTP.sql

USE master;
GO

IF OBJECT_ID('dbo.sp_BlitzInMemoryOLTP', 'P') IS NULL
EXECUTE ('CREATE PROCEDURE dbo.sp_BlitzInMemoryOLTP AS SELECT 1;');
GO

ALTER PROCEDURE dbo.sp_BlitzInMemoryOLTP(
        @instanceLevelOnly BIT            = 0
      , @dbName            NVARCHAR(4000) = N'ALL'
      , @tableName         NVARCHAR(4000) = NULL
      , @debug             BIT            = 0
)
/*
.SYNOPSIS
    Get detailed information about In-Memory SQL Server objects

.DESCRIPTION
    Get detailed information about In-Memory SQL Server objects
    Tested on SQL Server: 2014, 2016, 2017
    NOT tested on Azure SQL Database

.PARAMETER @instanceLevelOnly
    Only check instance In-Memory related information

.PARAMETER @dbName
    Check database In-Memory objects for specified database

.PARAMETER @tableName
    Check database In-Memory objects for specified tablename

.PARAMETER @debug
    Only PRINT dynamic sql statements without executing it

.EXAMPLE
    EXEC sp_BlitzInMemoryOLTP;
    -- Get all In-memory information

.EXAMPLE
    EXEC sp_BlitzInMemoryOLTP @instanceLevelOnly = 1;
    -- Get only instance In-Memory information

.EXAMPLE
    EXEC sp_BlitzInMemoryOLTP @debug = 1;
    -- PRINT dynamic sql statements without executing it

.LICENSE MIT
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

.NOTE
    Author: Ned Otter
    Original link: http://nedotter.com/archive/2017/10/in-memory-oltp-diagnostic-script/
    Version: 1.0

    Modified: 2017-12-06
    Author: Aleksey Nagorskiy
    Version: 1.1

    Modified: 2017-12-13
    Author: Konstantin Taranov
    Version: 1.2

    Modified: 2017-12-14
    Author: Konstantin Taranov
    Version: 1.3

    Modified: 2017-12-14
    Author: Aleksey Nagorskiy
    Version: 1.4

    Modified: 2017-12-18
    Author: Konstantin Taranov
    Version: 1.5

    Modified: 2017-12-19
    Author: Ned Otter
    Version: 1.6

    Modified: 2017-12-23
    Author: Ned Otter
    Version: 1.7

    Modified: 2017-12-25
    Author: Ned Otter
    Version: 1.8

*/
AS 
BEGIN TRY

    SET NOCOUNT ON;

    DECLARE @crlf VARCHAR(10) = CHAR(10);

    DECLARE @VersionString NVARCHAR(MAX) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))
          , @Edition NVARCHAR(MAX) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128))
          , @errorMessage  NVARCHAR(512);

    DECLARE @Version INT = CONVERT(INT, SUBSTRING(@VersionString, 1, CHARINDEX('.', @VersionString) - 1));

    IF @debug = 1 PRINT('--@Version = ' + CAST(@Version AS VARCHAR(30)));

    IF @Version < 12
    BEGIN
        SET @errorMessage = CONCAT('In-Memory OLTP is not supported if SQL Server version is less than 2014. You are running SQL Server version: ', @Version);
        THROW 55000, @errorMessage, 1;
    END;

    /*
    ###################################################
        if we get here, we are running at least SQL 2014, but that version 
        only runs In-Memory if we are using Enterprise Edition
    ###################################################
    */

    IF @Version = 12 AND (@Edition NOT LIKE 'Enterprise%' AND  @Edition NOT LIKE 'Developer%')
    BEGIN
        SET @errorMessage = CONCAT('For SQL 2014, In-Memory OLTP is only suppported on Enterprise Edition. You are running SQL Server edition: ', @Edition);
        THROW 55000, @errorMessage, 1;
    END;

    /*
    ######################################################################################################################
        DATABASE LEVEL
    ######################################################################################################################
    */

    IF OBJECT_ID('tempdb..#inmemDatabases') IS NOT NULL DROP TABLE #inmemDatabases;
    SELECT QUOTENAME(name) AS name
         , database_id
         , ROW_NUMBER() OVER (ORDER BY name ASC) AS rowNumber
    INTO #inmemDatabases
    FROM sys.databases
    WHERE name NOT IN ( 'master', 'model', 'tempdb', 'distribution', 'msdb', 'SSISDB')
        AND (name = @dbName OR @dbName = 'ALL')
        AND state_desc = 'ONLINE';

    IF @debug = 1 SELECT 'All ONLINE user databases' AS AllDatabases, * FROM #inmemDatabases;

    IF @dbName IS NULL AND @instanceLevelOnly = 0
    BEGIN
        SET @errorMessage = '@dbName IS NULL, please specify database name or ALL';
        THROW 55001, @errorMessage, 1;
        RETURN;
    END;

    IF (@dbName IS NOT NULL AND @dbName <> 'ALL') 
         AND (NOT EXISTS (SELECT 1 FROM #inmemDatabases WHERE name = QUOTENAME(@dbName)) AND @instanceLevelOnly = 0)
    BEGIN
        SET @errorMessage = '@dbName not found in sys.databases';
        THROW 55002, @errorMessage, 1;
        RETURN;
    END;

    IF @dbName = 'ALL' AND NOT EXISTS (SELECT 1 FROM #inmemDatabases)
    BEGIN
        SET @errorMessage = 'ALL was specified, but no memory-optimized databases were found';
        THROW 55002, @errorMessage, 1;
        RETURN;
    END;

    IF OBJECT_ID('tempdb..#moduleSplit') IS NOT NULL DROP TABLE #moduleSplit;

    CREATE TABLE #moduleSplit
    (
         rowNumber INT IDENTITY PRIMARY KEY
        ,value NVARCHAR(MAX) NULL
    );

    DECLARE @loadedModules TABLE
    (
         rowNumber INT IDENTITY PRIMARY KEY
        ,name NVARCHAR(MAX) NULL
    );

    INSERT @loadedModules
    (
        name
    )
    SELECT name
    FROM sys.dm_os_loaded_modules AS a
    WHERE description = 'XTP Native DLL'
        AND PATINDEX('%[_]p[_]%', name) > 0;

    DECLARE @maxLoadedModules INT = (SELECT COUNT(*) FROM @loadedModules);
    DECLARE @moduleCounter INT = 1;
    DECLARE @loadedModuleName NVARCHAR(MAX) = '';

    SET @moduleCounter = 1;

    WHILE @moduleCounter <= @maxLoadedModules
    BEGIN

        SELECT @loadedModuleName = name
        FROM @loadedModules
        WHERE rowNumber = @moduleCounter;

        DECLARE @xml XML
              , @delimiter NVARCHAR(10);
        SET @delimiter = '_';
        SET @xml = CAST(('<X>'+REPLACE(@loadedModuleName, @delimiter, '</X><X>')+'</X>') AS XML);

        INSERT #moduleSplit
        (
            value
        )
        SELECT C.value('.', 'NVARCHAR(1000)') AS value FROM @xml.nodes('X') as X(C);
                      
        SELECT @moduleCounter += 1;

    END;

    IF @instanceLevelOnly = 0
    BEGIN

        /*
        ####################################################
            Determine which databases are memory-optimized
        ####################################################
        */
        DECLARE @sql     NVARCHAR(MAX) = ''
              , @counter INT = 1
              , @MaxRows INT = (SELECT COUNT(*) FROM #inmemDatabases);

        WHILE @counter <= @MaxRows
        BEGIN

            IF @debug = 1 PRINT('--@counter = ' + CAST(@counter AS VARCHAR(30)) + ';' + @crlf);

            IF @counter = 1
            BEGIN
                SELECT @sql += ';WITH InMemDatabases AS (';
            END;

            SELECT @sql +=
            CASE 
                WHEN @counter = 1 THEN ''  -- there is exactly 1 database for the entire instance
                ELSE @crlf + ' UNION ALL ' + @crlf
            END;

            SELECT @sql +=
                CONCAT
                (
                        @crlf
                    ,'SELECT DISTINCT '
                    , 'N'''
                    ,  name
                    , ''' AS databaseName,' + @crlf
                    , database_id
                    , ' AS database_id' + @crlf+ ' FROM '
                    , name
                    , '.sys.database_files' + @crlf + ' INNER JOIN '
                    , name
                    , '.sys.filegroups ON database_files.data_space_id = filegroups.data_space_id WHERE filegroups.type = ''FX'''
                )
            FROM #inmemDatabases
            WHERE rowNumber = @counter;

            --IF @debug = 1 PRINT(@sql);

            SELECT @counter += 1;
        END;

        -- post-processing
        SELECT @sql += 
            CONCAT
            (
                    ')'
                ,@crlf
                ,'SELECT InMemDatabases.*, sys.databases.log_reuse_wait_desc'
                ,@crlf
                ,'FROM InMemDatabases '
                ,@crlf
                ,'INNER JOIN sys.databases ON '
                ,'QUOTENAME(sys.databases.name) = InMemDatabases.databaseName;'
            );

        IF @debug = 1
            PRINT('--Determine which databases are memory-optimized' + @crlf + @sql + @crlf);

        DECLARE @RowCount INT = (SELECT COUNT(*) FROM #inmemDatabases);

        IF @RowCount <> 0
        BEGIN

            IF OBJECT_ID('tempdb..#MemoryOptimizedDatabases') IS NOT NULL DROP TABLE #MemoryOptimizedDatabases;

            CREATE TABLE #MemoryOptimizedDatabases
            (
                 rowNumber           INT IDENTITY
               , dbName              NVARCHAR(256) NOT NULL
               , database_id         INT NULL
               , log_reuse_wait_desc NVARCHAR(256)
            );

            INSERT #MemoryOptimizedDatabases
            (
                dbName
               ,database_id
               ,log_reuse_wait_desc
            )
            EXECUTE sp_executesql @sql;

            IF @debug = 1 PRINT(@sql + @crlf);
            ELSE
            BEGIN
                SELECT 'Memory-optimized database(s)' AS databases
                      ,dbName
                      ,database_id
                      ,log_reuse_wait_desc
                FROM #MemoryOptimizedDatabases
                ORDER BY dbName;
            END;
        END;

        IF OBJECT_ID('tempdb..#NativeModules') IS NOT NULL DROP TABLE #NativeModules;

        CREATE TABLE #NativeModules
        (
            moduleKey INT IDENTITY NOT NULL
           ,moduleID INT NOT NULL
           ,moduleName NVARCHAR(256) NOT NULL
           ,collectionStatus BIT NULL
        );

        SELECT @sql = '';
        DECLARE @dbCounter INT = 1;
        SELECT @MaxRows = COUNT(*) FROM #MemoryOptimizedDatabases;
        DECLARE @databaseID INT = 1;


        /*
        ###################################################
            This is the loop that processes each db
        ###################################################
        */

        WHILE @dbCounter <= @MaxRows
        BEGIN
 
            /*
            ###################################################
                List memory-optimized tables in this database
            ###################################################
            */
            SELECT @sql = 
                CONCAT
                (
                    'SELECT TOP 1'
                    ,'''Memory optimized tables'''
                    , ' AS [object],'
                    , ' N'''
                    ,dbName
                    ,''' AS databaseName'
                    ,', b.name AS tableName 
                    , p.rows AS [rowCount]
                    ,durability_desc
                    , temporal_type_desc
                    ,FORMAT(memory_allocated_for_table_kb, ''###,###,###'') AS memoryAllocatedForTableKB
                    ,FORMAT(memory_used_by_table_kb, ''###,###,###'') AS memoryUsedByTableKB
                    ,FORMAT(memory_allocated_for_indexes_kb, ''###,###,###'') AS memoryAllocatedForIndexesKB
                    ,FORMAT(memory_used_by_indexes_kb, ''###,###,###'') AS memoryUsedByIndexesKB
                     FROM '
                    , dbName
                    ,'.sys.dm_db_xtp_table_memory_stats a'
                    ,' INNER JOIN '
                    , dbName
                    ,'.sys.tables b ON b.object_id = a.object_id'
                    ,' INNER JOIN '
                    ,dbName
                    ,'.sys.partitions p'
                    ,' ON p.[object_id] = b.[object_id]'
                    ,' INNER JOIN '
                    ,dbName
                    ,'.sys.schemas s'
                    ,' ON b.[schema_id] = s.[schema_id]'
                    ,' WHERE p.index_id = 2'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @tableName IS NOT NULL
            BEGIN
                SELECT @sql += CONCAT(' AND b.name = ', '''', @tableName, '''');
            END;

            IF @debug = 1
            PRINT('--List memory-optimized tables in this database' + @crlf + @sql + @crlf);
            ELSE 
            BEGIN
                DECLARE @resultsInMemTables TABLE
                (
                     [object] NVARCHAR(MAX)
                    ,databaseName NVARCHAR(MAX)
                    ,tableName NVARCHAR(MAX)
                    ,[rowCount] INT
                    ,durability_desc NVARCHAR(MAX)
                    ,temporal_type_desc NVARCHAR(MAX)
                    ,memoryAllocatedForTableKB NVARCHAR(MAX)
                    ,memoryUsedByTableKB NVARCHAR(MAX)
                    ,memoryAllocatedForIndexesKB NVARCHAR(MAX)
                    ,memoryUsedByIndexesKB NVARCHAR(MAX)
                );

                INSERT @resultsInMemTables
                EXECUTE sp_executesql @sql;

                IF EXISTS(SELECT 1 FROM @resultsInMemTables)
                    SELECT * FROM @resultsInMemTables;

            END;

            /*
            ##############################################################
                List indexes on memory-optimized tables in this database
            ##############################################################
            */
            SELECT @sql = 
                CONCAT
                (
                        'SELECT '
                        ,'''List indexes on memory-optimized tables in this database'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,''''
                        ,' AS databaseName
                        ,t.name AS tableName
                        ,i.name AS indexName
                        ,c.memory_consumer_id
                        ,c.memory_consumer_type_desc AS consumerType
                        ,c.memory_consumer_desc AS description
                        ,c.allocation_count AS allocations
                        ,FORMAT(c.allocated_bytes / 1024.0, ''###,###,###,###'') AS allocatedBytesMB
                        ,FORMAT(c.used_bytes / 1024.00, ''###,###,###,###.###'') AS usedBytesMB
                    FROM '
                    ,dbName 
                    ,'.sys.dm_db_xtp_memory_consumers c
                    INNER JOIN '
                    ,dbName
                    ,'.sys.tables t ON t.object_id = c.object_id'
                    ,CASE WHEN @Version > 12 THEN ' INNER JOIN sys.memory_optimized_tables_internal_attributes a ON a.object_id = c.object_id
                                                                            AND a.xtp_object_id = c.xtp_object_id' ELSE NULL END
                    ,@crlf + ' LEFT JOIN '
                    ,dbName 
                    ,'.sys.indexes i ON c.object_id = i.object_id
                                                    AND c.index_id = i.index_id '
                                                    ,CASE WHEN @Version > 12 THEN 'AND a.minor_id = 0' ELSE NULL END
                    ,@crlf + ' WHERE t.type = '
                    , '''u'''
                    , '   AND t.is_memory_optimized = 1 '
                    ,' AND i.index_id IS NOT NULL'
                                       
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @tableName IS NOT NULL
            BEGIN
                SELECT @sql += CONCAT(' AND t.name = ', '''', @tableName, '''');
            END;

            SELECT @sql += ' ORDER BY tableName, indexName;'

            IF @debug = 1
            PRINT('--List indexes on memory-optimized tables in this database' + @crlf + @sql + @crlf);
            ELSE 
                BEGIN
                    DECLARE @resultsIndexes TABLE
                    (
                        [object] NVARCHAR(MAX)
                       ,databaseName NVARCHAR(MAX)
                       ,tableName NVARCHAR(MAX)
                       ,indexName INT
                       ,memory_consumer_id INT
                       ,consumerType NVARCHAR(MAX)
                       ,description NVARCHAR(MAX)
                       ,allocations INT
                       ,allocatedBytesMB NVARCHAR(MAX)
                       ,usedBytesMB NVARCHAR(MAX)
                    );

                    INSERT @resultsIndexes
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsIndexes)
                        SELECT * FROM @resultsIndexes;

                END;

            /*
            #########################################################
                verify avg_chain_length for HASH indexes

                From BOL:

                Empty buckets:
                    33% is a good target value, but a larger percentage (even 90%) is usually fine.
                    When the bucket count equals the number of distinct key values, approximately 33% of the buckets are empty.
                    A value below 10% is too low.

                Chains within buckets:
                    An average chain length of 1 is ideal in case there are no duplicate index key values. Chain lengths up to 10 are usually acceptable.
                    If the average chain length is greater than 10, and the empty bucket percent is greater than 10%, 
                    the data has so many duplicates that a hash index might not be the most appropriate type.

            #########################################################
            */
        
            SELECT @sql = 
                CONCAT
                (
                       'SELECT '
                       ,'''avg_chain_length for HASH indexes'''
                       ,' AS [object],'''
                       ,dbName
                       ,''''
                       ,' AS databaseName'
                       ,', sch.name AS [Schema] '
                       ,', t.name AS tableName 
                         ,i.name AS [indexName]
                         ,h.total_bucket_count AS totalBucketCount
                         ,h.empty_bucket_count AS emptyBucketCount
                         ,FLOOR((CAST(h.empty_bucket_count AS FLOAT) / h.total_bucket_count) * 100) AS [emptybBucketPercent]
                         ,h.avg_chain_length AS avg_ChainLength
                         ,h.max_chain_length AS maxChainLength
                         ,IIF(FLOOR((CAST(h.empty_bucket_count AS FLOAT) / h.total_bucket_count) * 100) < 33, ''Free buckets % is low!'', '''') AS [Free buckets status]
                         ,IIF(h.avg_chain_length > 10 AND FLOOR((CAST(h.empty_bucket_count AS FLOAT) / h.total_bucket_count) * 100) > 10, ''avg_chain_length has many collisions!'', '''') AS [avg_chain_length status]
                     FROM '
                    ,dbName 
                    ,'.sys.dm_db_xtp_hash_index_stats AS h 
                    INNER JOIN '
                    ,dbName
                    ,'.sys.indexes AS i ON h.object_id = i.object_id AND h.index_id = i.index_id'
                    ,CASE WHEN @Version > 12 THEN
                    CONCAT(' INNER JOIN ', dbName ,'.sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id = ia.xtp_object_id') ELSE NULL END
                    ,' INNER JOIN '
                    ,dbName
                    ,'.sys.tables t ON h.object_id = t.object_id'
                    ,' INNER JOIN '
                    ,dbName
                    ,'.sys.schemas sch ON sch.schema_id = t.schema_id '
                    ,CASE WHEN @Version > 12 THEN 'WHERE ia.type = 1' ELSE NULL END
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @tableName IS NOT NULL
            BEGIN
                SELECT @sql += CONCAT(' AND t.name = ', '''', @tableName, '''');
            END;

            SELECT @sql += ' ORDER BY sch.name
                                     ,t.name
                                     ,i.name;';

            IF @debug = 1
            PRINT('--Verify avg_chain_length for HASH indexes' + @crlf + @sql + @crlf);
            ELSE 
            BEGIN
            
                DECLARE @resultsHashBuckets TABLE
                (
                     [object] NVARCHAR(MAX)
                    ,databaseName NVARCHAR(MAX)
                    ,[Schema] NVARCHAR(MAX)
                    ,TableName NVARCHAR(MAX)
                    ,indexName NVARCHAR(MAX)
                    ,totalBucketCount BIGINT
                    ,emptyBucketCount BIGINT
                    ,emptyBucketPercent INT
                    ,avg_ChainLength INT
                    ,max_ChainLength BIGINT
                    ,[Free buckets status] NVARCHAR(MAX)
                    ,[avg_chain_length status] BIGINT
                );

                INSERT @resultsHashBuckets
                EXECUTE sp_executesql @sql;
;
                IF EXISTS(SELECT 1 FROM @resultsHashBuckets)
                    SELECT * FROM @resultsHashBuckets;

            END;			
            

            /*
            #########################################################
                Count of indexes per table in this database
            #########################################################
            */

            SELECT @sql = 
                CONCAT
                (
                    'SELECT '
                    ,'''Number of indexes per table'' AS [object],'
                    ,' N'''
                    ,dbName
                    ,''''
                    ,' AS databaseName
                    ,t.name AS tableName
                    ,COUNT(DISTINCT i.index_id) AS indexCount
                    FROM '
                    ,dbName 
                    ,'.sys.dm_db_xtp_memory_consumers c
                    INNER JOIN '
                    ,dbName
                    ,'.sys.tables t ON t.object_id = c.object_id'
                    ,CASE WHEN @Version > 12 THEN
                    CONCAT(' INNER JOIN ', dbName ,'.sys.memory_optimized_tables_internal_attributes a ON a.object_id = c.object_id
                                                                        AND a.xtp_object_id = c.xtp_object_id') ELSE NULL END 
                    ,' LEFT JOIN '
                    ,dbName 
                    ,'.sys.indexes i ON c.object_id = i.object_id
                                                    AND c.index_id = i.index_id '
                                                    ,CASE WHEN @Version > 12 THEN ' AND a.minor_id = 0' ELSE NULL END
                    ,' WHERE t.type = '
                    , '''u'''
                    , '   AND t.is_memory_optimized = 1 '
                    ,' AND i.index_id IS NOT NULL'
                    --,' GROUP BY t.name
                    --    ORDER BY t.name'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @tableName IS NOT NULL
            BEGIN
                SELECT @sql += CONCAT(' AND t.name = ', '''', @tableName, '''');
            END;

            SELECT @sql +=
                     ' GROUP BY t.name
                       ORDER BY t.name';

            IF @debug = 1
            PRINT('--Count of indexes per table in this database' + @crlf + @sql + @crlf);
            ELSE 
            BEGIN
                DECLARE @resultsIndexCount TABLE
                (
                     [object] NVARCHAR(MAX)
                    ,databaseName NVARCHAR(MAX)
                    ,tableName NVARCHAR(MAX)
                    ,indexCount INT
                );

                INSERT @resultsIndexCount
                EXECUTE sp_executesql @sql;

                IF EXISTS(SELECT 1 FROM @resultsIndexCount)
                    SELECT * FROM @resultsIndexCount;

            END;


            /*
            #####################################################
                List natively compiled modules in this database
            #####################################################
            */
            /*
          
                FN = SQL scalar function
                IF = SQL inline table-valued function
                TF = SQL table-valued-function
                TR = SQL DML trigger
            */

            IF @tableName IS NULL
            BEGIN

                SELECT @sql = 
                    CONCAT
                    (
                        'SELECT ''Natively compiled modules'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,''''
                        ,' AS databaseName
                         ,A.name
                         ,CASE A.type
                            WHEN ''FN'' THEN ''Function''
                            WHEN ''P'' THEN ''Procedure''
                            WHEN ''TR'' THEN ''Trigger''
                           END AS type
                         ,B.definition AS [definition]
                         FROM '
                        , dbName
                        ,'.sys.all_objects AS A
                         INNER JOIN '
                        ,dbName
                        ,'.sys.sql_modules AS B ON B.object_id = A.object_id
                        WHERE UPPER(B.definition) LIKE ''%NATIVE_COMPILATION%''
                        ORDER BY A.type, A.name'
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @debug = 1
                PRINT('--List natively compiled modules in this database' + @crlf + @sql + @crlf);
                ELSE 
                BEGIN
                    DECLARE @resultsNativeModules TABLE
                    (
                         [object] NVARCHAR(MAX)
                        ,Name NVARCHAR(MAX)
                        ,databaseName NVARCHAR(MAX)
                        ,[type] NVARCHAR(MAX)
                        ,[definition] NVARCHAR(MAX)
                    );

                    INSERT @resultsNativeModules
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsNativeModules)
                        SELECT * FROM @resultsNativeModules;

                END;
            END;

            /*
            #####################################################
                List *loaded* natively compiled modules in this database, i.e. executed at least 1x
            #####################################################
            */

            /*
                the format for checkpoint files changed from SQL 2014 to SQL 2016

                SQL 2014 format:
                database_id = 5
                object_id = 309576141

                H:\SQLDATA\xtp\5\xtp_p_5_309576141.dll

                SQL 2016+ format
                database_id = 9
                object_id = 1600880920

                H:\SQLDATA\xtp\9\xtp_p_9_1600880920_185048689287400_1.dll

                the following code should handle all versions
            */
            IF @tableName IS NULL
            BEGIN
               SELECT @sql =
                    CONCAT
                    (
                        ';WITH nativeModuleObjectID AS
                         (
                            SELECT DISTINCT REPLACE(value, ''.dll'', '''') AS object_id
                            FROM #moduleSplit
                            WHERE rowNumber % ' 
                     ,CASE WHEN @Version = 12 THEN ' 4 = 0'
                           ELSE ' 6 = 4'  -- @Version >= 13 
                      END 
                        ,')'
                    );

                SELECT @sql += 
                    CONCAT
                    (
                        'SELECT ''Loaded natively compiled modules'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,''''
                        ,' AS databaseName
                       ,name AS moduleName
                       ,procedures.object_id
                        FROM '
                        ,dbName
                        ,'.sys.all_sql_modules
                        INNER JOIN '
                        ,dbName
                        ,'.sys.procedures ON procedures.object_id = all_sql_modules.object_id
                        INNER JOIN nativeModuleObjectID ON nativeModuleObjectID.object_id = procedures.object_id'
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @debug = 1
                PRINT('--List loaded natively compiled modules in this database (@Version >= 13)' + @crlf + @sql + @crlf);
                ELSE 
                BEGIN
                    DECLARE @resultsNativeLoaded TABLE
                    (
                        [object] NVARCHAR(MAX)
                       ,databaseName NVARCHAR(MAX)
                       ,moduleName NVARCHAR(MAX)
                       ,object_id INT
                    );

                    INSERT @resultsNativeLoaded
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsNativeLoaded)
                        SELECT * FROM @resultsNativeLoaded;

                END;				
            END;

            /*
            #########################################################
                Count of natively compiled modules in this database
            #########################################################
            */
            IF @tableName IS NULL
            BEGIN
                SELECT @sql = 
                    CONCAT
                    (
                        'SELECT ''Count of natively compiled modules'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,' '''
                        ,' AS databaseName
                        , COUNT(*) AS [Number of modules]
                             
                        FROM '
                        , dbName
                        ,'.sys.all_sql_modules
                         INNER JOIN '
                        ,dbName
                        ,'.sys.procedures ON procedures.object_id = all_sql_modules.object_id
                        WHERE uses_native_compilation = 1
                        ORDER BY 1'
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @debug = 1
                PRINT('--Count of natively compiled modules in this database' + @crlf + @sql + @crlf);
                ELSE 
                BEGIN
                    DECLARE @resultsNativeModuleCount TABLE
                    (
                        [object] NVARCHAR(MAX)
                       ,databaseName NVARCHAR(MAX)
                       ,[Number of modules] INT
                    );

                    INSERT @resultsNativeModuleCount
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsNativeModuleCount WHERE [Number of modules] > 0)
                        SELECT * FROM @resultsNativeModuleCount;

                END;				
            END;
            
            /*
            ############################################################
                Display memory consumption for temporal/internal tables
            ############################################################
            */

            -- temporal is supported in SQL 2016+
            IF @Version >= 13
            BEGIN

                SELECT @sql = 
                    CONCAT
                    (
                        ';WITH InMemoryTemporalTables
                        AS
                        (
                            SELECT '
                            ,'N'''
                            ,dbName
                            ,'''' 
                            ,' AS databaseName'
                            ,',sch.name AS temporalTableSchema
                                  ,T1.OBJECT_ID AS temporalTableObjectId
                                  ,IT.OBJECT_ID AS internalTableObjectId
                                  ,T1.name AS temporalTableName
                                  ,IT.Name AS internalHistoryTableName
                            FROM '
                            ,dbName
                            ,'.sys.internal_tables IT 
                            INNER JOIN '
                            ,dbName
                            ,'.sys.tables T1 ON IT.parent_OBJECT_ID = T1.OBJECT_ID 
                            INNER JOIN '
                            ,dbName
                            ,'.sys.schemas sch ON sch.schema_id = T1.schema_id
                            WHERE T1.is_memory_optimized = 1 
                              AND T1.temporal_type = 2
                        )
                        ,DetailedConsumption
                        AS
                        (
                            SELECT databaseName
                                  ,temporalTableSchema
                                  ,T.temporalTableName
                                  ,T.internalHistoryTableName
                                  ,CASE
                                      WHEN C.object_id = T.temporalTableObjectId
                                      THEN ''Temporal Table''
                                      ELSE ''Internal Table''
                                   END AS ConsumedBy
                                  ,C.allocated_bytes
                                  ,C.used_bytes
                            FROM '
                            ,dbName
                            ,'.sys.dm_db_xtp_memory_consumers C
                            INNER JOIN InMemoryTemporalTables T
                            ON C.object_id = T.temporalTableObjectId OR C.object_id = T.internalTableObjectId
                            WHERE C.allocated_bytes > 0
                              AND C.object_id <> T.temporalTableObjectId
                        )
                        SELECT DISTINCT databaseName
                              ,temporalTableSchema
                              ,temporalTableName
                              ,internalHistoryTableName
                              ,SUM(allocated_bytes) OVER (PARTITION BY temporalTableName ORDER BY temporalTableName) AS allocatedBytesForInternalHistoryTable
                              ,SUM(used_bytes) OVER (PARTITION BY temporalTableName ORDER BY temporalTableName) AS usedBytesForInternalHistoryTable
                        FROM DetailedConsumption'
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @tableName IS NOT NULL
                BEGIN
                    SELECT @sql += CONCAT(' WHERE temporalTableName = ', '''', @tableName, '''');
                END;

                IF @debug = 1
                PRINT('--Display memory consumption for temporal/internal tables' + @crlf + @sql + @crlf);
                ELSE
                BEGIN
                    DECLARE @resultsTemporal TABLE
                    (
                        databaseName NVARCHAR(MAX)
                       ,temporalTableSchema NVARCHAR(MAX)
                       ,temporalTableName NVARCHAR(MAX)
                       ,internalHistoryTableName NVARCHAR(MAX)
                       ,allocatedBytesForInternalHistoryTable BIGINT
                       ,usedBytesForInternalHistoryTable BIGINT
                    );

                    INSERT @resultsTemporal
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsTemporal)
                        SELECT * FROM @resultsTemporal;

                END;
            END; -- display memory consumption for temporal/internal tables

            /*
            #########################################################
                Display memory structures for LOB columns (off-row)
                for SQL 2016+
            #########################################################
            */

            IF @Version >= 13
            BEGIN
    
                SELECT @sql = 
                    CONCAT
                    (
                        'SELECT DISTINCT '
                        ,'''LOB/Off-row data '' AS [object],'
                        ,' N'''
                        ,dbName
                        ,'''' 
                        ,' AS databaseName'
                        ,', OBJECT_NAME(a.object_id) AS tableName
                        ,cols.name AS columnName
                        ,a.type_desc AS typeDescription
                        ,c.memory_consumer_type_desc AS memoryConsumerTypeDescription
                        ,c.memory_consumer_desc AS memoryConsumerDescription
                        ,c.allocated_bytes AS allocatedBytes
                        ,c.used_bytes AS usedBytes
                         FROM '
                        ,dbName
                        ,'.sys.dm_db_xtp_memory_consumers c
                        INNER JOIN '
                        ,dbName
                        ,'.sys.memory_optimized_tables_internal_attributes a ON a.object_id = c.object_id
                                                                                AND a.xtp_object_id = c.xtp_object_id '
                                    ,' INNER JOIN '
                        ,dbName
                        ,'.sys.objects AS b ON b.object_id = a.object_id '
                        ,' INNER JOIN '
                        ,dbName
                        ,'.sys.syscolumns AS cols ON cols.id = b.object_id
                           WHERE a.type_desc = '
                        ,''''
                        ,'INTERNAL OFF-ROW DATA TABLE'
                        ,''''
                        ,' AND c.memory_consumer_desc = ''Table heap'''
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @tableName IS NOT NULL
                BEGIN
                    SELECT @sql += CONCAT(' AND OBJECT_NAME(a.object_id) = ', '''', @tableName, '''');
                END;

                SELECT @sql += ' ORDER BY databaseName, tableName, columnName';

                IF @debug = 1
                PRINT('--Display memory structures for LOB columns (off-row)' + @crlf + @sql + @crlf);
                ELSE 
                BEGIN
                    DECLARE @resultsMemoryConsumerForLOBs TABLE
                    (
                        [object] NVARCHAR(MAX)
                       ,databaseName NVARCHAR(MAX)
                       ,tableName NVARCHAR(MAX)
                       ,columnName NVARCHAR(MAX)
                       ,typeDescription NVARCHAR(MAX)
                       ,memoryConsumerTypeDescription NVARCHAR(MAX)
                       ,memoryConsumerDescription NVARCHAR(MAX)
                       ,allocatedBytes INT
                       ,usedBytes INT
                    );
             
                    INSERT @resultsMemoryConsumerForLOBs
                                                                                         
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsMemoryConsumerForLOBs)
                        SELECT * FROM @resultsMemoryConsumerForLOBs;

                END;

            END;

            /*
            #######################################################
                Display memory-optimized table types
            #######################################################
            */
            IF @tableName IS NULL
            BEGIN 
                SELECT @sql = 
                    CONCAT
                    (
                        'SELECT '
                        ,'''Memory optimized table types'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,''' AS databaseName,' 
                        ,'SCHEMA_NAME(tt.schema_id) AS [Schema]
                              ,tt.name AS [Name]
                        FROM '
                        ,dbName
                        ,'.sys.table_types AS tt
                        INNER JOIN '
                        ,dbName
                        ,'.sys.schemas AS stt ON stt.schema_id = tt.schema_id
                        WHERE tt.is_memory_optimized = 1
                        ORDER BY [Schema], tt.name '
                    )
                FROM #MemoryOptimizedDatabases
                WHERE rowNumber = @dbCounter;

                IF @debug = 1
                PRINT('--Display memory-optimized table types' + @crlf + @sql + @crlf);
                ELSE 
                BEGIN
                    DECLARE @resultsTableTypes TABLE
                    (
                         [object] NVARCHAR(MAX)
                        ,databaseName NVARCHAR(MAX)
                        ,[Schema] NVARCHAR(MAX)
                        ,[Name] NVARCHAR(MAX)
                    );

                    INSERT @resultsTableTypes
                    EXECUTE sp_executesql @sql;

                    IF EXISTS(SELECT 1 FROM @resultsTableTypes)
                        SELECT * FROM @resultsTableTypes;

                END;

            END;

            /*
            ##################################################################
                ALL database files, including container name, size, location
            ##################################################################
            */

            SELECT @sql = 
                CONCAT
                (
                    'SELECT '
                    ,'''Database layout'' AS [object],'
                    ,' N'''
                    ,dbName
                    ,'''' 
                    ,' AS databaseName'
                    ,',filegroups.name AS fileGroupName
                      ,physical_name AS fileName
                      ,database_files.name AS [Name]
                      ,filegroups.type AS fileGroupType
                      ,IsContainer = IIF(filegroups.type = ''FX'', ''Yes'', ''No'')
                      ,filegroups.type_desc AS fileGroupDescription
                      ,database_files.state_desc AS fileGroupState
                      ,FORMAT(database_files.size * CONVERT(BIGINT, 8192) / 1024, ''###,###,###,###'') AS sizeKB
                      ,FORMAT(database_files.size * CONVERT(BIGINT, 8192) / 1048576.0, ''###,###,###,###'') AS sizeMB
                      ,FORMAT(database_files.size * CONVERT(BIGINT, 8192) / 1073741824.0, ''###,###,###,###.##'') AS sizeGB
                      ,FORMAT(SUM(database_files.size / 128.0) OVER(), ''###,###,###,###'') AS totalSizeMB
                    FROM '
                    ,dbName
                    ,'.sys.database_files
                    LEFT JOIN '
                    ,dbName
                    ,'.sys.filegroups ON database_files.data_space_id = filegroups.data_space_id
                    ORDER BY filegroups.type, filegroups.name, database_files.name'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @debug = 1
            PRINT('--ALL database files, including container name, size, location' + @crlf + @sql + @crlf);
            ELSE 
            BEGIN
                DECLARE @resultsDatabaseLayout TABLE
                (
                     [object] NVARCHAR(MAX)
                    ,databaseName NVARCHAR(MAX)
                    ,fileGroupName NVARCHAR(MAX)
                    ,fileName NVARCHAR(MAX)
                    ,[containerName/fileType] NVARCHAR(MAX)
                    ,Name NVARCHAR(MAX)
                    ,IsContainer NVARCHAR(MAX)
                    ,fileGroupDescription NVARCHAR(MAX)
                    ,fileGroupState NVARCHAR(MAX)
                    ,sizeKB NVARCHAR(MAX)
                    ,sizeMB NVARCHAR(MAX)
                    ,sizeGB NVARCHAR(MAX)
                    ,totalSizeMB NVARCHAR(MAX)
                );

                INSERT @resultsDatabaseLayout
                EXECUTE sp_executesql @sql;

                IF EXISTS(SELECT 1 FROM @resultsDatabaseLayout)
                    SELECT * FROM @resultsDatabaseLayout;

            END;


            /*
            ##################################################################
                container name, size, number of files
            ##################################################################
            */

            SELECT @sql = 
                CONCAT
                (
                     ';WITH ContainerDetails AS
                     (
                             SELECT '
                             ,' container_id
                               ,SUM(ISNULL(file_size_in_bytes, 0)) AS sizeinBytes
                               ,COUNT(*) AS fileCount
                               ,MAX(container_guid) AS container_guid
                         FROM ' 
                     ,dbName
                     ,'.sys.dm_db_xtp_checkpoint_files
                         GROUP BY container_id
                     )
                     SELECT 
                          ''Container details by container name'' AS [object],'
                         ,' N'''
                         ,dbName
                         ,'''' 
                         ,' AS databaseName
                         ,database_files.name AS containerName
                         ,ContainerDetails.container_id
                         ,FORMAT(ContainerDetails.sizeinBytes / 1048576., ''###,###,###'') AS sizeMB
                         ,ContainerDetails.fileCount
                     FROM ContainerDetails
                     INNER JOIN '
                     ,dbName
                     ,'.sys.database_files ON ContainerDetails.container_guid = database_files.file_guid'
                 )
             FROM #MemoryOptimizedDatabases
             WHERE rowNumber = @dbCounter;

            IF @debug = 1
            PRINT('--container name, size, number of files' + @crlf + @sql + @crlf);
            ELSE EXECUTE sp_executesql @sql;

            /*
            ##################################################################
                container file summary
            ##################################################################
            */

            SELECT @sql = 
                CONCAT
                (
                    ';WITH ContainerFileSummary AS
                    (
                            SELECT '
                            ,' 
                                   SUM(ISNULL(file_size_in_bytes, 0)) AS sizeinBytes
                                  ,MAX(ISNULL(file_type_desc, '''')) AS fileType
                              ,COUNT(*) AS fileCount
                              ,MAX(state_desc) AS fileState
                              ,MAX(container_guid) AS container_guid
                        FROM ' 
                    ,dbName
                    ,'.sys.dm_db_xtp_checkpoint_files
                        GROUP BY file_type_desc, state_desc
                    )
                    SELECT 
                         ''Container details by fileType and fileState'' AS [object],'
                        ,' N'''
                        ,dbName
                        ,'''' 
                        ,' AS databaseName
                        ,ContainerFileSummary.fileType
                        ,ContainerFileSummary.fileState
                        ,FORMAT(ContainerFileSummary.sizeinBytes, ''###,###,###'') AS sizeBytes
                        ,FORMAT(ContainerFileSummary.sizeinBytes / 1048576., ''###,###,###'') AS sizeMB
                        ,ContainerFileSummary.fileCount
                        ,database_files.state_desc AS fileGroupState
                        FROM ContainerFileSummary
                    INNER JOIN '
                    ,dbName
                    ,'.sys.database_files ON ContainerFileSummary.container_guid = database_files.file_guid'
                    ,' ORDER BY ContainerFileSummary.fileType, ContainerFileSummary.fileState;'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @debug = 1
            PRINT('--container file summary' + @crlf + @sql + @crlf);
            ELSE EXECUTE sp_executesql @sql;

            /*
            ##################################################################
                container details
            ##################################################################
            */

            SELECT @sql = 
                CONCAT
                (
                    ';WITH ContainerFileDetails AS
                    (
                       SELECT 
                       container_id
                      ,SUM(ISNULL(file_size_in_bytes, 0)) AS sizeinBytes
                      ,MAX(ISNULL(file_type_desc, '''')) AS fileType
                      ,COUNT(*) AS fileCount
                      ,MAX(state_desc) AS fileState
                      ,MAX(container_guid) AS container_guid
                    FROM '
                        ,dbName
                    ,'.sys.dm_db_xtp_checkpoint_files
                        GROUP BY container_id, file_type_desc, state_desc
                    )
                    SELECT '
                    ,'''Container file details by container_id, fileType and fileState'' AS [object],'
                    ,' N'''
                    ,dbName
                    ,'''' 
                    ,' AS databaseName
                    ,database_files.name AS containerName
                    ,ContainerFileDetails.container_id
                    ,ContainerFileDetails.fileType
                    ,ContainerFileDetails.fileState
                    ,FORMAT(ContainerFileDetails.sizeinBytes, ''###,###,###'') AS sizeBytes
                    ,FORMAT(ContainerFileDetails.sizeinBytes / 1048576., ''###,###,###'') AS sizeGB
                    ,ContainerFileDetails.fileCount
                    ,database_files.state_desc AS fileGroupState
                    FROM ContainerFileDetails
                    INNER JOIN '
                    ,dbName
                    ,'.sys.database_files ON ContainerFileDetails.container_guid = database_files.file_guid'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            IF @debug = 1
                         
            PRINT('--container details' + @crlf + @sql + @crlf);
            ELSE EXECUTE sp_executesql @sql;

            /*

            ###########################################################
                Report on whether or not execution statistics 
                for natively compiled procedures is enabled
            ###########################################################
            */
            SELECT @sql = 
                CONCAT
                (
                    'INSERT #NativeModules
                    (
                         moduleID
                        ,moduleName
                    )
                    SELECT '
                    ,dbName
                    ,'.sys.all_sql_modules.Object_ID AS ObjectID 
                    ,name AS moduleName
                    FROM '
                    ,dbName
                    ,'.sys.all_sql_modules
                     INNER JOIN '
                    ,dbName
                    ,'.sys.procedures ON procedures.object_id = all_sql_modules.object_id'
                    ,' WHERE uses_native_compilation = 1'
                )
            FROM #MemoryOptimizedDatabases
            WHERE rowNumber = @dbCounter;

            EXECUTE sp_executesql @sql;

            IF EXISTS (SELECT 1 FROM #NativeModules)
            BEGIN
                DECLARE @procCounter INT = 1;
                DECLARE @MaxModules INT = (SELECT COUNT(*) FROM #NativeModules);
                DECLARE @dbID INT = (SELECT database_id FROM #MemoryOptimizedDatabases  WHERE rowNumber = @dbCounter);
                DECLARE @moduleID INT;
                DECLARE @ModuleStatus BIT;
                DECLARE @moduleName NVARCHAR(256);

                /*
                ########################################################
                    This is the loop that processes each native module
                ########################################################
                */
                WHILE @procCounter <= @MaxModules
                BEGIN
    
                    SELECT @moduleID = moduleID
                          ,@moduleName = moduleName
                    FROM #NativeModules
                    WHERE moduleKey = @procCounter;

                    PRINT CONCAT('Verifying collection stats of ', @moduleName);

                    SELECT @ModuleStatus = 0;

                    /*#############################################################################################

                        If the module we are verifying collection status for has not been executed at least once,
                        error 41377 will be returned, and will terminate the WHILE loop (so we trap it in a CATCH block, 
                        in order to determine the correct status for this specific proc).

                        Msg 41377, Level 16, State 2, Procedure sp_xtp_control_query_exec_stats_internal, Line 1 [Batch Start Line 0]
                        The natively compiled module with database ID 27 and object ID 1973582069 has not been executed. 
                        Query execution statistics collection can only be enabled if the module has been executed at least once since creation or last database restart.
                    #############################################################################################
                    */

                BEGIN TRY
                    EXEC sys.sp_xtp_control_query_exec_stats
                        @database_id = @dbID
                       ,@xtp_object_id = @moduleID
                       ,@old_collection_value = @ModuleStatus OUTPUT;
                END TRY
                BEGIN CATCH
                    SELECT
                        @ModuleStatus = 0;
                END CATCH;

                    IF @ModuleStatus = 1
                    BEGIN
                        UPDATE #NativeModules
                        SET collectionStatus = 1
                        WHERE moduleKey = @procCounter;
                    END;

                    SELECT @procCounter += 1;
                END; -- -- This is the loop that processes each native module

                IF EXISTS(SELECT * FROM #NativeModules WHERE collectionStatus = 1)
                    SELECT 'Native execution statistics' AS [object]
                          ,moduleName
                          ,moduleID
                         ,CASE 
                               WHEN collectionStatus = 1 THEN 'YES' 
                               WHEN collectionStatus IS NULL THEN 'NO' 
                               ELSE 'NO' 
                          END AS CollectionStatsEnabled
                    FROM #NativeModules
                    WHERE collectionStatus = 1
                    ORDER BY moduleName;
                ELSE
                BEGIN
                    PRINT '--No modules found that have collection stats enabled';
                END;

            END; --IF EXISTS (SELECT 1 FROM #NativeModules)

            SELECT @dbCounter += 1;

        END; -- This is the loop that processes each database
    END;


    IF OBJECT_ID('#NativeModules', 'U') IS NOT NULL DROP TABLE #NativeModules;

    /*
    ######################################################################################################################
        INSTANCE LEVEL
    ######################################################################################################################
    */


    /*
    ###################################################
        Because SQL 2016/SP1 brings In-Memory OLTP to 
        editions other Enterprise, we must check
        @@version
    ###################################################
    */

    IF @instanceLevelOnly = 1 AND @Version >= 12
    BEGIN

        SELECT @@version AS Version;

        SELECT name
              ,value AS configValue
              ,value_in_use AS runValue
        FROM sys.configurations
        WHERE name like 'max server memory%'
        ORDER BY name OPTION (RECOMPILE);

        -- from Mark Wilkinson
        /*
            If memory is being used it should be in here.

            Memory that is reported as being consumed here for XTP was missing in 
            the other XTP DMVs. We should simply look to see what the highest consumer is.

            SELECT * FROM sys.dm_xtp_system_memory_consumers

            SELECT * FROM sys.dm_db_xtp_memory_consumers 

        */
        SELECT * FROM sys.dm_os_memory_clerks;

        SELECT type AS object_type
              ,SUM(pages_kb) /1024.0 /1024.0 AS pages_mb
        FROM sys.dm_os_memory_clerks
        WHERE type LIKE '%XTP%'
        GROUP BY type;

        SELECT memory_consumer_type_desc AS object_type,
             SUM(allocated_bytes) /1024.0 /1024.0 AS pagesAllocatedMB
            ,SUM(allocated_bytes) /1024.0 /1024.0 AS pagesUsedMB
        FROM sys.dm_xtp_system_memory_consumers
        GROUP BY memory_consumer_type_desc 
        ORDER BY memory_consumer_type_desc;

        SELECT memory_consumer_type_desc AS object_type,
             SUM(allocated_bytes) /1024.0 /1024.0 AS pagesAllocatedMB
            ,SUM(allocated_bytes) /1024.0 /1024.0 AS pagesUsedMB
        FROM sys.dm_db_xtp_memory_consumers
        GROUP BY memory_consumer_type_desc 
        ORDER BY memory_consumer_type_desc;


        SELECT FORMAT(committed_target_kb, '###,###,###,###,###') AS committedTargetKB
              ,FORMAT(committed_target_kb / 1024, '###,###,###,###,###') AS committedTargetMB
              ,FORMAT(committed_target_kb / 1048576, '###,###,###,###,###') AS committedTargetGB
        FROM sys.dm_os_sys_info;

        IF OBJECT_ID('#TraceFlags', 'U') IS NOT NULL DROP TABLE #TraceFlags;

        CREATE TABLE #TraceFlags
        (
             TraceFlag INT NOT NULL
            ,Status TINYINT NOT NULL
            ,Global TINYINT NOT NULL
            ,Session TINYINT NOT NULL
        );

        SET @sql = 'DBCC TRACESTATUS';

        INSERT #TraceFlags    
    
        EXECUTE sp_executesql @sql;

        IF @debug = 1
        PRINT(@crlf + @sql + @crlf);

        DECLARE @msg NVARCHAR(MAX);

        IF EXISTS (SELECT 1 FROM #TraceFlags WHERE TraceFlag = 10316) -- allows custom indexing on hidden staging table for temporal tables
        BEGIN

            SELECT @msg = 'TraceFlag 10316 is enabled';

            SELECT @msg
                  ,TraceFlag
                  ,Status
                  ,Global
                  ,Session
            FROM #TraceFlags
            WHERE TraceFlag = 10316 
            ORDER BY TraceFlag;

        END;

        /*
        #############################################################################################
            Verify if collection statistics are enabled for:
            1. specific native modules
            2. all native modules (instance-wide config)
    
            Having collection statistics enabled can severely impact performance of native modules.
        #############################################################################################
        */

        -- instance level
        DECLARE @InstancecollectionStatus BIT;

        EXEC sys.sp_xtp_control_query_exec_stats
        @old_collection_value = @InstancecollectionStatus OUTPUT;

        SELECT
            CASE 
                WHEN @InstancecollectionStatus = 1 THEN 'YES' 
                ELSE 'NO'
            END AS [instance-level collection of execution statistics for Native Modules enabled];

        /*
        ####################################################################################
            List any databases that are bound to resource pools
    
            NOTE #1: if there are memory optimized databases that do NOT appear
            in this list, they consume memory from the 'default' pool, where 
            all other SQL Server memory is allocated from. 
    
            If the memory-optimized footprint grows, from either addition of rows, 
            or row versions, it can put pressure on the buffer pool, cause it to shrink,
            and affect performance for harddrive-based tables. 

            NOTE #2: if you want to bind a memory-optimized database to resource pool, 
            the database must be taken OFFLINE/ONINE for the binding to take effect. 
            This will cause all durable data to be removed from memory, and re(streamed)
            from checkpoint file pairs.
    
        ####################################################################################
        */

        IF EXISTS (
            SELECT 1
            FROM sys.databases d
            INNER JOIN sys.dm_resource_governor_resource_pools AS Pools ON Pools.pool_id = d.resource_pool_id
        )
        SELECT 'Resource pool' AS [object]
                ,Pools.name AS poolName
                ,d.name AS databaseName
                ,min_memory_percent AS minMemoryPercent
                ,max_memory_percent AS maxMemoryPercent
                ,used_memory_kb / 1024 AS usedMemoryMB
                ,max_memory_kb / 1024 AS maxMemoryMB
                ,FORMAT(((used_memory_kb * 1.0) / (max_memory_kb  * 1.0) * 100), '###.##') AS percentUsed
                ,target_memory_kb / 1024 AS targetMemoryMB
        FROM sys.databases d
        INNER JOIN sys.dm_resource_governor_resource_pools AS Pools ON Pools.pool_id = d.resource_pool_id
        ORDER BY poolName, databaseName;

        /*
        ###########################################################
            Memory breakdown
        ###########################################################
        */

        ;WITH clerksAggregated AS
        (
            SELECT clerks.[type] AS clerkType
                  ,CONVERT(CHAR(20)
                  ,SUM(clerks.pages_kb) / 1024.0) AS clerkTypeUsageMB
            FROM sys.dm_os_memory_clerks AS clerks WITH (NOLOCK)
            WHERE clerks.pages_kb <> 0
            AND clerks.type IN ('MEMORYCLERK_SQLBUFFERPOOL', 'MEMORYCLERK_XTP')
            GROUP BY clerks.[type]
        )
        ,clerksAggregatedString AS
        (
            SELECT clerkType
                  ,clerkTypeUsageMB
                  ,PATINDEX('%.%', clerkTypeUsageMB) AS decimalPoint
            FROM clerksAggregated
        )
        SELECT clerkType
              ,memUsageMB = 
              CASE 
                  WHEN decimalPoint > 1 THEN SUBSTRING(clerkTypeUsageMB, 1, PATINDEX('%.%', clerkTypeUsageMB) -1)
                  ELSE clerkTypeUsageMB
              END 
        FROM clerksAggregatedString;

        -- total memory allocated for in-memory engine
        SELECT type clerk_type
             , name
             , memory_node_id
             , pages_kb/1024 pages_mb 
        FROM sys.dm_os_memory_clerks 
        WHERE type LIKE '%xtp%';


        /*
        #################################################################
            Oldest xtp transactions, they might prevent 
            garbage collection from cleaning up row versions
        #################################################################
        */

        SELECT TOP 10 'Oldest xtp transactions' AS [object]
              ,xtp_transaction_id
              ,transaction_id
              ,session_id
              ,begin_tsn
              ,end_tsn
              ,state_desc
              ,result_desc
        FROM sys.dm_db_xtp_transactions
        ORDER BY begin_tsn DESC;

        /*
        #################################################################
            Is event notification defined at the serverdb level?
            If so, errors will be generated, as EN is not 
            supported for memory-optimized objects, and causes problems
        #################################################################
        */

        IF EXISTS(
            SELECT 1
            FROM sys.event_notifications
        )
        BEGIN 
            SELECT 'Event notifications are listed below';
            SELECT *
            FROM sys.event_notifications;
        END;
    END; -- @instanceLevelOnly = 1 AND @Version >= 12
END TRY

BEGIN CATCH
    THROW 
    PRINT 'Error: '       + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity: '  + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State: '     + CONVERT(varchar(5), ERROR_STATE()) +
          ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
          ', Line: '      + CONVERT(varchar(5), ERROR_LINE()) +
          ', User name: ' + CONVERT(sysname, CURRENT_USER);
    PRINT ERROR_MESSAGE();
END CATCH;
GO