Hey folks! Today I'll share another bug we found recently when testing some new SQL Server 2019 instances in our development environment. This one is a bit concerning as it could affect a lot of shops and the bug presents itself during a pretty common use case.

The Setup

All you need to reproduce this bug is:

When this issue first started popping up it was on a SQL Server 2019 VM, but I was able to reproduce it using a fresh container using Docker as well, so you should be able to do the same.

To get our setup ready, we need a database to work with:

CREATE DATABASE [testdatabase];
GO
ALTER DATABASE [testdatabase] ADD FILEGROUP [memopt] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [testdatabase] ADD FILE (
    NAME = N'memopt',
    FILENAME = N'/var/opt/mssql/data/memopt'
) TO FILEGROUP [memopt]
GO

My demo code was run on a Linux-based container, hence the odd file paths. If you don't have access to run containers you will need to change the file path above to a valid path on the instance.

Now let's create a schema-only memory-optimized table:

-- Create memory-optimized table
USE [testdatabase];
GO

CREATE TABLE dbo.[testtable]
(
    [id] [int] NOT NULL,
    INDEX [IX_testtable_id] NONCLUSTERED 
    (
        [id] ASC
    )
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Last but not least we need to enable the Memory-Optimized TempDB Metadata feature:

EXEC sp_configure 'Show Advanced Options',1
RECONFIGURE
EXEC sp_configure 'tempdb metadata memory-optimized',1
RECONFIGURE
GO

After this is enabled you need to reboot the system to apply the change.

The repro

Ok, we've got everything set up, now let's run a query and see what happens:

USE [testdatabase];
GO

SELECT  *
FROM    sys.indexes indexes 
        LEFT OUTER JOIN sys.stats AS stats
            ON indexes.[object_id] = stats.[object_id]
            AND indexes.[index_id] = stats.[stats_id] 
        LEFT OUTER JOIN sys.partitions AS partitions
            ON indexes.[object_id] = partitions.[object_id]
            AND indexes.index_id = partitions.index_id;
GO

If everything broke like it should have you should now see an error message (and one that might be familiar if you work with Memory-Optimized OLTP features):

Msg 41317, Level 16, State 0, Line 3
A user transaction that accesses memory-optimized tables or natively compiled modules cannot access more than
one user database or databases model and msdb, and it cannot write to master.
Commands completed successfully.

If you disable Memory-Optimized TempDB Metadata, reboot the server, and re-run the query, you will no longer see this error.

Conclusion

We found this issue after enabling this new feature on a test instance. The errors were being generated by Ola's Index Maintenance scripts, so this isn't really an extreme edge-case issue like we typically run into. It was easy to find the statement that generated the error but my co-worker Pablo Lozano (T|B) simplified it and found that it specifically occurs when you JOIN to the sys.stats table from another table. This seems like a major issue taking into account the number of people that use Ola's scripts. I currently have a support case in with Microsoft so hopefully this will be resolved soon.