Press "Enter" to skip to content

Category: In-Memory OLTP

Indexes for Memory-Optimized Tables

Monica Rathbun takes us through the options available when creating indexes on memory-optimized tables:

Before we dive into this subject it is VERY important to note the biggest differences.

First, ALL memory optimized indexes MUST be created when the table is created or migrated. You cannot add indexes in an existing table without dropping and recreating the table.

Secondly, currently you can only have 8 indexes per table including your primary key. Remember that every table must have a primary key to enforce a secondary copy for a minimum of schema durability This  means you can only really add 7 additional indexes so be sure to understand your workloads and plan indexing accordingly.

There are a few other differences as well, which Monica covers before detailing the specific index options.

Leave a Comment

Contrasting TVPs and Memory-Optimized TVPs

Denis Gobo wants to see what memory-optimized table-valued parameters are good for:

The other day I was thinking about the blog post Faster temp table and table variable by using memory optimization I read a while back. Since you can’t believe anything on the internets (no disrespect to whoever wrote that post) , I decided to take this for a test

In this post I will be creating 2 databases, one is a plain vanilla database and the other, a database that also has a file group that contains memory optimized data

I will also be creating a table type in each database, a plain one and a memory optimized one in the memory optimized database

Read on for Denis’s findings.

Leave a Comment

Memory Optimizer Advisor

Monica Rathbun takes us through the Memory Optimization Advisor in SQL Server Management Studio:

Previously I wrote about In-Memory Optimized Tables, in this blog I am going to discuss how to determine which tables could benefit from being In-Memory by using a tool called Memory Optimization Advisor (MOA). This a is a tool built into SQL Server Management Studio (SSMS) that will inform you of which tables could benefit  using In Memory OLTP capabilities and which may have non supported features. Once identified, MOA will help you to actually migrate that table and data to be optimized. Let’s see how it works by walking through it using a table I use for demonstrations in AdventureWorks2016CTP3. Since this is a smaller table and doesn’t incur a ton writes it is not a good use case, however, for simplicity I am using it for this demo.

This is good for seeing which tables could easily move to memory-optimized and which you shouldn’t even try.

Leave a Comment

Memory-Optimized Tables and Error Log Entries

Shaun J. Stuart points out impoliteness on the part of In-Memory OLTP:

It’s nice that they are labeled with [INFO], so you can be fairly sure they aren’t a major issue, but they still annoyingly fill up the log with information that is of no use to anyone outside of Microsoft. It would be nice if you could disable these messages but, to my knowledge, you cannot.

These are the types of error messages which should, by default, not write to the error log. My real bugbear is “Hey, we successfully backed up the transaction log!” You should not need a trace flag to turn that off; you should need one to turn it on for diagnostic purposes.

Comments closed

Limitations with Memory-Optimized TempDB Metadata

Milos Radivojevic takes us through a few limitations in memory-optimized TempDB metadata tables in SQL Server 2019:

When we are about to enable a new feature, one of the first things we have to check is whether enabling this feature will break the existing code. Enabling this feature could bring two breaking changes: one is related to columnstore indexes, the other to transactions with memory-optimized tables.

I don’t think these limitations are that game-breaking, but if you’re regularly loading large tables in tempdb and using columnstore indexes on them, you might be in for a nasty surprise.

Comments closed

The Future of In-Memory OLTP?

Niko Neugebauer has some speculation regarding In-Memory OLTP:

In SQL Server 2019 without that much deserved fanfare (for me at least), Microsoft has released a couple of improvements that made me think about the current status (was apparently almost dead) and the future of the In-Memory OLTP technology, that was launched in the year 2014, and besides significant improvements in SQL Server 2016 looked pretty much abandoned since. 

This is a perfect example of an excellent idea halfway implemented. There’s a lot of potential here, but enough pieces are missing that it’s hard to recommend using it outside of specialized scenarios. And that’s a shame given the potential.

Comments closed

Memory-Optimized Tempdb Metadata

Niko Neugebauer takes a detailed look at an exciting 2019 feature:

Anyone who has ever seen/done tuning bigger Hardware would instantly be interested, since the CPU is clearly going 100% during the processing, showing APPARENT better focus on the process and hopefully better performance. Since the granularity of the Task Manager for both cases is the same, you can easily notice that overall spent significantly less time churning the same workload and that is very true – we have spent just 11.777 Seconds on the average!

The main benefit to this is an environment where you’re creating and destroying a lot of temp tables concurrently. If you are in that situation, you can realize significant performance improvements. But Niko does have a warning at the end.

Comments closed

SQL Server 2019: Database Snapshots and In-Memory Filegroups

Niko Neugebauer points out a small but interesting addition to SQL Server 2019:

Database Snapshots can serve for so many great purposes, such as Quick Restores and DWH/BI/Reporting reading operations, between others.

In-Memory is still the feature that I love a lot, and while the most active Programming Feature has not seen a lot (or enough) development, because frankly we are back to “Chicken & Egg, Who was first” kind of problem, I see little but important developments and most importantly feature integration. The original implementation in Sql Server 2014 and further improvements in Sql Server 2016 & Sql Server 2017 have improved the programming surface to being useful member of the toolkit, but some of the current limitations are dreading for a number of people and projects.

Well, it seems that Microsoft has been silently working on the improvements and one of them is the support for the Database Snapshots of the In-Memory File Groups in the Sql Server 2019.

I haven’t used database snapshots in a while, but I was fond of them for testing purposes and even wrote an ugly WinForms app to let devs manage them at a prior company.

Click through for Niko’s demonstration as well as a limitation with this.

Comments closed

T-SQL Tuesday 117 Roundup

Steve Jones has the roundup for this month’s T-SQL Tuesday:

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.

Comments closed

Memory-Optimized Table Types

Rob Farley hates spelling “optimized” the best way:

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.

Comments closed