Press "Enter" to skip to content

Category: In-Memory OLTP

Durability and Hekaton

Published 2024-03-12 by Kevin Feasel

Rob Farley ponders a pair of potential performance improvements and their effects on durability:

Durability in SQL is handled by making sure that data changes are written to disk before the calling application is informed that the transaction is complete. We don’t walk out of a shop with our goods before the cashier has confirmed that the credit card payment has worked. If we did, and the payment bounced, the cashier would be calling us back pretty quickly. In SQL this is about confirming that the transaction log entry has been written, and it’s why you shouldn’t use disks with write-cache for databases

And yet, in-memory features of SQL, commonly called “Hekaton” handles transactions without writing to disk. The durability is delayed. This month, Todd Kleinhans invites us to write about Hekaton.

In-Memory OLTP is one of those features that I wish worked better for most use cases or didn’t have as many limitations around only working within the context of a single database. In practice, instead of using In-Memory OLTP for most tables, you’re usually better off just jamming more RAM on the box and limiting how many scans of large tables flush your buffer pool.

Comments closed

In-Memory OLTP and Memory Allocation

Published 2023-10-09 by Kevin Feasel

Tanayankar Chakraborty explains an error:

We recently encountered a support case where a customer using In-memory tables in an Azure SQL DB, receives an error message while trying to insert data into the table that also has a clustered columnstore index. The customer then deleted the entire data from the In-memory Tables (With the clustered columnstore index), however it appeared that the Index Unused memory was still not released. Here’s the memory allocation the customer could see:

Error

In addition to the error above- here is the error text:

Msg 41823, Level 16, State 109, Line 1

Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation. See ‘http://go.microsoft.com/fwlink/?LinkID=623028‘ for more information

In this case, the error ends up being a “didn’t read the manual” type of error.

Comments closed

Startup Pains with Large Memory-Optimized Tables

Published 2023-03-17 by Kevin Feasel

Brent Ozar takes us through a problem:

Well, here it is in 2023, and recently I’ve talked to a couple of architects who wish they could go back in time and watch that video. In both cases, they suffered from the same issue.

The short story is that the more data you put into durable In-Memory OLTP tables – and even just 5GB of data can hit this issue – the more your startups, failovers, and restores turn into long stories, to the point where other databases on your SQL Server are practically unusable.

Click through for the scenario. In-Memory OLTP is one of those features which frustrates me to no end. It had the potential to be outstanding, but due to the difficulty of further development (e.g., getting cross-database queries to work when you have a mix of memory-optimized and non-optimized databases and tables) and the limitations of what it actually made faster (mostly inserts, not selects), the actual number of great use cases for the product is a lot lower than I think it could have been.

Comments closed

Storage Snapshots and In-Memory OLTP

Published 2022-10-27 by Kevin Feasel

Andy Yun answers a question:

Can I still take storage-array snapshots and if yes, will I lose data in my memory-optimized tables? What about data inside my non-durable tables?

Thankfully, the question was not in the headline. Therefore, Betteridge’s Law of Headlines does not apply and the answer may be either ‘yes’ or ‘no’ depending on the facts. Speaking of which, to find that answer, click through and read Andy’s post.

Comments closed

Dealing with Corruption around In-Memory OLTP

Published 2021-08-25 by Kevin Feasel

Chris Taylor has a tale of woe:

Late last week (20th) we had an emergency call from a company that had a production database go into Suspect mode and needed help. Now this isn’t a great situation to be in so when they then go on to tell us that the last valid backup they had was from the 12th and the backup job had been failing since then – even less of a great situation to be in

Read the whole thing.

Comments closed

In-Memory OLTP and HammerDB Setup Error

Published 2020-11-06 by Kevin Feasel

Erik Darling tracks down an error:

This a short post about a sort of quirky error message I got while trying to run the TPC-C load test against SQL Server’s in-memory tables.

Error in Virtual User1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The integer value XXXXXXXXXXXX is out of range.

Click through for the solution. Also, read down to the bottom of the post for a huge discount on Erik’s recorded training content. Erik is extremely knowledgeable and has a great way of explaining things, so take advantage of that knowledge.

Comments closed

Memory-Optimized Table Types to Avoid tempdb Contention

Published 2020-10-26 by Kevin Feasel

Michael J. Swart uses In-Memory OLTP:

At D2L, we’re the perfect candidate customer for In Memory OLTP features, but we’ve held off adopting those features for years. Our servers handle tons of super quick but super frequent queries and so we find ourselves trying to address the same scaling challenges we read about in Microsoft’s customer case studies.

But there’s only one In Memory feature in particular that I care about. It’s the Memory Optimized Table Types. Specifically, I’ve always wanted to use that feature to avoid tempdb object allocation contention. Recently I finally got my chance with a lot of success. So even though I could say I’m happy with In Memory features, I think it’s more accurate to say that I feel relieved at having finally squashed my tempdb issues.

We’ve used memory-optimized table types for a couple of years to solve exactly this problem and the plan was pretty much the same as what Michael put into action.

Comments closed

Columnstore and Memory-Optimized tempdb

Published 2020-05-08 by Kevin Feasel

Erik Darling has a bucket of cold water for us:

In SQL Server 2019:

– Exciting stuff: In memory tempdb!
– Exciting stuff: sp_estimate_data_compression_savings can evaluate columnstore compression!
– Disappointing stuff: If you use in memory tempdb, you can’t have any columnstore anything in tempdb

That means if you’re using sneaky tricks like clustered columnstore indexes on temp tables to induce batch mode, you’re gonna get a lot of errors.

Likewise, you won’t be able to evaluate if columnstore will help your tables.

Click through to understand the extent of this limitation. Hopefully this is something we see addressed in vNext and a CU for 2019.

Comments closed

Indexes for Memory-Optimized Tables

Published 2020-01-23 by Kevin Feasel

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.

Comments closed

Contrasting TVPs and Memory-Optimized TVPs

Published 2020-01-22 by Kevin Feasel

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.

Comments closed