memory-optimized tempdb metadata bugestimated read time: 3-4 minutes
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.
All you need to reproduce this bug is:
- SQL Server 2019 instance with
Memory-Optimized TempDB Metadataenabled
- database with a memory-optimized filegroup
- A memory-optimized table (doesn't have to be durable) with a non-clustered index on it
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.
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.
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.