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.
So, I was trying to get an in-memory database moved from one server to another (long story, involving IMOLTP melting down on me and resulting in a 2 terabyte log file).
I thought it’d be simple: copy the files over, along with the Filestream folders, and attach. Nope! Various errors, including “5(Access is denied.)”.
Click through for the steps involved.
In my lab, I’m running Windows Server 2012. Let’s use Powershell to install the File System Resource Manager, which will allow us to create a quota for the relevant folder:
add-windowsfeature –name fs-resource-manager –includemanagementtools
After installing the Windows feature we can set the quota for the folder, but we shouldn’t enable it just yet, because first we have to verify the current size of the folder.
On my server, I created a quota of 1.5GB, and then enabled it.
Now let’s INSERT rows into the table, in batches of 1000, until we reach the limit (the INSERT script is listed in Part 2, I’m trying to keep this post from getting too long).
Click through to see what happens. It’s not exactly a swath of carnage, but it’s also something you really don’t want to happen.
It’s clear that before we inserted any data, we had 20 files that were in the PRECREATED state. After inserting 10 rows, we now have 18 PRECREATED files, and 2 UNDER CONSTRUCTION files, which means the In-Memory engine is populating these files, that they are “open” in terms of their CHECKPOINT status. If you don’t understand what these terms mean, please read Part 1.
But there’s one thing that doesn’t look right here: we’ve inserted data into the table, but sizeBytesUsed is still zero for the UNDER CONSTRUCTION files. Why is that?
Ned does assume that you’ve read part 1 of the series already.