The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic.
Either everyone is on vacation or not many people think about them. Or maybe they aren’t interesting. In any case, here’s the roundup.
I’d like to say it’s because everyone’s on vacation, but I think In-Memory OLTP is an underutilized technology. Granted, there are reasons why it’s not used as much as it should be—early versions were too limiting and could have weird consequences on your servers—but if you’re on SQL Server 2017, it’s worth another look.
Let me start by saying that if you really want to get the most out of this feature, you will dive deep into questions like durability and natively-compiled stored procedures, which can really make your database fly if the conditions are right. Arguably, any process you’re doing (such as ETL) where the data doesn’t have to survive a system restart should be considered for Memory-Optimized Tables with durability set to SCHEMA_ONLY (I say ‘considered’ because the answer isn’t always obvious – at the moment inserting into memory-optimised tables won’t run in parallel, and this could be a show-stopper for you).
But today I’m going to mention one of the quick-wins available: Table Variables that use User-defined Table Types
This can absolutely help you out, especially in versions of SQL Server prior to 2019 where temporary object metadata contention is a real issue on busy servers.
I have SQL Server 2019 CTP 3.2 installed on my virtual machine, which has 8GB of memory (max server memory set to 6 GB) and 4 vCPUs. I created four (4) tempdb data files, each sized to 1GB.
I restored a copy of WideWorldImporters and then created three stored procedures (definitions below). Each stored procedure accepts a date input, and pushes all rows from Sales.Order and Sales.OrderLines for that date into the temporary object. In Sales.usp_OrderInfoTV the object is a table variable, in Sales.usp_OrderInfoTT the object is a temporary table defined via SELECT … INTO with a nonclustered added afterwards, and in Sales.usp_OrderInfoTTALT the object is a pre-defined temporary table which is then altered to have an additional column. After the data is added to the temporary object, there is a SELECT statement against the object that joins to the Sales.Customers table.
This isn’t a be-all, end-all performance test, but it does give us an idea of what can work better with memory-optimized tempdb metadata.
Like other changes to TempDB, in order for the new memory-optimization to take effect a restart of the SQL Server service is required. Once the service is restarted, system tables in TempDB are now memory-optimized (it should be that way for RTM, but in CTP 3.0, it could be the case that not all system tables have been converted to Hekaton). You can reverse this setting with the following command, and again restarting the SQL Server service:
There are a couple of important notes that Ned gives us around accessing these metadata tables in scripts.
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. I’m told it will be “opt in”, so you can use this enhancement if you want to, but you can also back out of it, which would require a restart of the SQL Server service.
But there’s at least one other enhancement that’s not been announced, although the details of its implementation are not yet known.
Read on to learn about this not-yet-announced update.
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 with storage for In-Memory OLTP.
Unfortunately, that’s not correct: through SSMS or TSQL, you can in fact set a max size for a container.
But you should not ever do that…..
Because if you do, and your checkpoint files exceed the max size of the container, your database can go into the In Recovery, Suspect, or OFFLINE state.
Read on for a repro that you should not try in production. Or anywhere, really.
Instance level evaluates the following:
the version/edition of SQL server
SQL Server ‘max memory’ setting
XTP memory consumers, aggregated
XTP memory consumers, detailed
the value of the committed_target_kb column from sys.dm_os_sys_info
whether or not instance-level collection of execution statistics has been enabled for all natively compiled stored procedures (because this can kill their performance….)
when running Enterprise, if there are any resource groups defined, and which memory-optimized databases are bound to them
XTP and buffer pool memory allocations, because In-Memory OLTP can affect on-disk workloads
summary of memory used by XTP
There’s a lot of useful information you can get out of this procedure. Click through for the full documentation.
We just added new database-scoped configuration options that will help with monitoring performance of natively compiled stored procedures. The new options XTP_PROCEDURE_EXECUTION_STATISTICS and XTP_QUERY_EXECUTION_STATISTICS are available now in Azure SQL Database, and will be available in the next major release of SQL Server. These options will improve your monitoring and troubleshooting experience for databases leveraging In-Memory OLTP with natively compiled stored procedures.
After enabling these options, you can monitor the performance of natively compiled stored procedures using Query Store, as well as the DMVs sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. Note that there is a performance impact to enabling execution statistics collection, thus we recommend to disable stats collection when not needed.
That last sentence is important: there’s an observer effect which slows down execution of natively compiled stored procedures, and considering that you’re implementing them specifically for the speed, that’s fairly unwelcome.
This is a rather simple blog post that is dedicated to the theme of the In-Memory Columnstore Indexes location. This has been a constant topic of discussion over a long period of time, even during the public events – and there is a need to clear out this topic.
I have assumed that the In-Memory Columnstore structures (Segments, Dictionaries, …) are located in the In-Memory, but there have been voices that I greatly respect, pointing that actually the Columnstore Object Pool is the exact location of any Columnstore structures, and there is nothing better than to take this feature for a ride and see what the SQL Server engine is actually doing.
Niko shows off a couple of useful DMVs along the way, too.
What can cause a memory-optimized database to run out of memory? It could be that resource consumption (memory) exceeded:
- the relevant percentage of committed_target_kb from the sys.dm_os_sys_info DMV (explained in a moment)
- MAX_MEMORY_PERCENT value of a Resource Pool that the database is bound to (if running Enterprise Edition and using Resource Governor)
- garbage collection is not operational (the purpose of GC is to reclaim memory consumed by stale row versions)
- updates to memory-optimized table variables caused row versions to be created, and because GC does not operate on table variables, you ran out of memory (for table variables that have a very large amount of rows)
The only thing that can prevent GC from working is a long running transaction.
This is the final post of Ned’s resource issues miniseries and it’s been a good one.