Monthly Archives: November 2017

In-Memory OLTP Resources, Part 1: The Foundation

This multi-part blog post will cover various resource conditions that can affect memory-optimized workloads. We’ll first lay the foundation for what types of resources are required for In-Memory OLTP, and why.

The following topics will be covered :

  • causes of OOM (Out of Memory)
  • how files that persist durable memory-optimized data affect backup size
  • how memory is allocated, including resource pools, if running Enterprise Edition
  • potential effect on disk-based workloads (buffer pool pressure)
  • what happens when volumes that store durable memory-optimized data run out of free space
  • what you can and cannot do when a memory-optimized database runs out of resources
  • database restore/recovery
  • garbage collection (GC) for row versions and files (file merge)
  • BPE (buffer pool extension)

Like most everything in the database world, In-Memory OLTP requires the following resources:

  • storage
  • IOPS
  • memory
  • CPU

Let’s take storage first – why would a memory-optimized database require storage, what is it used for, and how much storage is required?

Why and What?

You’ll need more storage than you might expect, to hold the files that persist your durable memory-optimized data, and backups. 

How much storage? 

No one can exactly answer that question, as we’ll explain over the next few blog posts. However, Microsoft’s recommendation is that you have 4x durable memory-optimized data size as a starting point for storage capacity planning.


A memory-optimized database must have a special filegroup designated for memory-optimized data, known as a memory-optimized filegroup. This special filegroup is logically associated with one or more “containers”. What the heck is a “container”? Well, it’s just a fancy word for “folder”, nothing more, nothing less. But what is actually stored in those fancy folders?

Containers hold files known as “checkpoint file pairs”, which are also known as “data and delta files”, and these files persist durable memory-optimized data (in this blog post series, I’ll use the terms CFP and data/delta files interchangeably). You’ll note on the following image that it clearly states in bold red letters, “NO MAXSIZE” and “STREAMING”. “NO MAXSIZE” means that you can’t specify how large these files will grow, nor can you specify how large the container that houses them can grow (unless you set a quota, but you should NOT do that). And there’s also no way at the database level to control the size of anything having to do with In-Memory OLTP storage – you simply must have enough available free space for the data and delta files to grow.

This is the first potential resource issue for In-Memory OLTP: certain types of data modifications are no longer allowed if the volume your container resides upon runs out of free space. I’ll cover workload recovery from resource depletion in a future blog post.

“STREAMING” means that the data stored within these files is different than what’s stored in MDF/LDF/NDF files. Data files for disk-based tables store data rows on 8K pages, a group of which is known as an extent. Data for durable memory-optimized tables is not stored on pages or extents. Instead, memory-optimized data is written in a sequential, streaming fashion, like the FILESTREAM feature (it should be noted that you do not have to enable the FILESTREAM feature in order to use In-Memory OLTP, and that statement has been true since In-Memory OLTP was first released in SQL 2014).   


How do these data/delta files get populated? All that is durable in SQL Server is written to the transaction log, and memory-optimized tables are no exception. After first being written to the transaction log, a process known as “offline checkpoint” harvests changes related to memory-optimized tables, and persists those changes in the data/delta files. In SQL 2014, there was a single offline checkpoint thread, but as of SQL 2016, there are multiple offline checkpoint threads. 


Let’s create a sample database:

After creating the database, the InMemOOMTest folder looks like this:


OOM_DB_inmem1 and OOM_DB_inmem2 are containers (folders), and they’ll be used to hold checkpoint file pairs. You’ll note in the DDL listed above, that under the memory-optimized filegroup, each container has both a name and filename entry. The name is the logical name of the container, while the filename is actually the container name, which represents the folder that gets created on disk. Initially there are no CPFs in the containers, but as soon as you create your first memory-optimized table, CFPs get created in both containers.

If we have a look in one of the containers, we can see files that have GUIDs as names, and are created with different sizes.


This is definitely not human-readable, but luckily, Microsoft has created a DMV to allow us to figure out what these files represent.

Below we can clearly see that there are different types of files, and that files can have different “states”, which is central to the discussion of the storage footprint for memory-optimized databases, and backups of those databases. There are different values for container_id – remember we said that a memory-optimized database can have one or more containers. Next, we should pay attention to the fact that all entries for the “relative_file_path” column begin with “$HKv2\”. This means that in each container, we have a folder with the name “$HKv2”, and all data/delta files for that container are located there.


At this point, it’s time for a discussion of the various file states. I’ll stick to SQL 2016+ (because SQL 2014 had more file states).

The possible file states are:


We’ll discuss the first three now, and save MERGE TARGET and WAITING FOR LOG TRUNCATION for later.

PRECREATED: as a performance optimization technique, the In-Memory engine will “precreate” files. These precreated files have nothing in them – they are completely empty, from a durable data perspective. A file in this state cannot yet be populated.

UNDER CONSTRUCTION: when the engine starts adding data to a file, the state of the file changes from PRECREATED to UNDER CONSTRUCTION. Data and delta files are shared by all durable memory-optimized tables, so it’s entirely possible that the first entry is for TableA, the next entry for TableB, and so on. “UNDER CONSTRUCTION” could be interpreted as “able to be populated”.

ACTIVE: When a file that was previously UNDER CONSTRUCTION gets closed, the state transitions to ACTIVE. That means it has entries in it, but is no longer able to be be populated. What causes a file to be closed? The CHECKPOINT process will close the checkpoint, changing all UNDER CONSTRUCTION files to ACTIVE.

That’s the basic rundown of the file states we need to know about at this point.

In Part 2, we’ll dive deeper into the impact of data/delta file states and the storage footprint for memory-optimized databases.

Technology can change your life

Roy Dorman

When I first met Roy Dorman, he was in is mid-thirties, and completing a degree in philosophy at Fordham University here in New York City. Roy was coupled with a very close friend; I took a liking to him immediately.

It’s a tall order to find a teaching position in philosophy under the best of circumstances, and upon graduating, he struggled to find work (his age was likely not an asset). To meet the financial demands of living in this city, he toiled at whatever jobs came his way: limousine dispatcher, real-estate manager, etc.

After getting to know him for a while, I could see that Roy had the right combination of natural skills that would be perfect for a role in technology, and I told him so. He was extremely responsible, persevering, detail oriented, and enjoyed solving problems.

Remembering my own struggles to wrap my brain around technology (detailed here), I said to Roy – “Hey man, if you ever want to do something in the tech world, just let me know – I’d be glad to teach you.”

Time ticked on by, and then one day the phone rang – Roy was ready to begin his studies.

First ascent

The first hurdle we faced was that Roy had zero disposafble income, so I offered to lend him the money to purchase a computer. Our plan was that he would do self-study from home, with my oversight. However, there was one gigantic problem with this scenario – he had a very young child at home (I don’t know what we were thinking). As the great jazz musician Ahmad Jamal told me years ago – “you cannot serve two masters.”

With no measurable progress after a fair amount of study time, the likelihood of Roy being gainfully employed in the field of technology was hovering around impossible.

We needed another plan.

Second ascent

I could see that the obligations of a husband and father were not easily circumvented. On the other hand, Roy had worked a series of gigs that had zero financial upside, so he was highly motivated – an essential requirement of all heavy lifting.

Our second plan was more structured and required a larger commitment from both of us. Also built in to the plan were some “teeth”.

I asked Roy how much money he needed to survive, and the figure he came up with was $1,000 per week, and I agreed to sustain him financially throughout his studies. But because lending money to a friend can jeopardize the relationship, we approached the financial aspect from a different perspective. I told him that I would not lend him the money he needed – I would give him the money he needed. And should he one day be in a financial position to return the gesture, so be it. We went forward without my having any expectation of seeing the money that flowed to him.

Roy had no idea if he could morph into a SQL Server DBA, and I was keenly aware of the vast amount of trust he placed in me. It was an awesome responsibility, to say the least.

I had recently started a contract for a large migration of 100+ SQL Servers to the latest version, and this put me in a position to help Roy out. I wrote him a letter explaining what was expected of each of us, and what would happen if we did not succeed. With the letter I enclosed a check for $2,000, saying that there would be subsequent payments of $1,000 per week. My estimation was that it would take approximately nine months for Roy to become employable, but our timeline was open-ended.

Tough love

My letter in part said:

“If I was you, I’d be as frugal as humanly possible with this check, for the following reasons:

· I eat before you do

· Anything can happen

· Don’t assume there will be a next check, because I don’t. You’re a consultant now; your income is unpredictable.”

“I will evaluate your performance each month and discuss with you where you stand. If for whatever reason I deem that you are not living up to your end of the bargain – and it does not change – the deal is off, I become yet another line item in your long list of creditors, and our lives resume as before.“

I didn’t like to be that hard on Roy, but I felt it was the only way we could get to the finish line. It was for his own good, and he knew it.

I told him that he had to treat his SQL Server education exactly like a job. He received a set of keys to my studio apartment in Greenwich Village, and had to be there five days a week, eight hours a day.

To the grindstone

In addition to being a SQL Server DBA, I’ve had a life in music. Many people have come to me for music lessons, and almost none of them have ever returned after the initial encounter. Perhaps it’s because I don’t sugar-coat what’s involved with pursuing the subtleties of creative improvised music. Roy stands alone as the only student I’ve ever had that got into the long run with me.

Keep in mind that he had only very basic knowledge of computers when we began, and he knew absolutely nothing about operating systems or database software. I’ve sometimes thought that if Roy knew how much he’d have to learn in such a short span of time, he might have backed out. But ignorance is bliss, as they say.

To be honest, we worked like mad men.

I drilled him.

I grilled him.

I imparted mindful after mindful of technology upon Roy Dorman each and every day. Weeks turned to months, and slowly, the veil of technological ignorance lifted, giving way to comprehension and knowledge. After seven months, I had taught Roy everything I could think of (but I did leave off some critical items, such as how to determine how much memory is installed on a SQL Server – sorry, Roy!).

One day I arrived home to find a package he had left for me. Inside was a collage that he made about our collaboration, and I laughed out loud when I saw it. It had photos of the two of us, plus artwork for Sybase, Oracle and MS SQL – really hysterical. Also included was a very small pair of scissors. I called Roy and asked him what the scissors were for. He said: “To cut the cord!”

It was time for Roy to fly.

Looking for a gig

Roy wanted to work as a consultant, but at that point in time, there seemed to be more opportunities for him as an employee. Also, with a wife and young child, he needed the benefits that came with full time employment.

One day he called and said that he was going to have an interview, and there was something in his voice that sounded kind of funny. His appointment was to be at the same company I was working for, and in the same building where I worked. He asked me: “Are your manager’s initials ‘XYZ’?”

They were indeed.

Unbelievably, Roy would have an interview in my office the following Monday (I wonder how long those odds were).

As luck would have it, when Roy arrived on site, my manager asked me to give him a technical interview, and then he “introduced” me to Roy.

You will not be shocked to learn that I gave Roy the green light. Not because I had trained him, but because he was a good candidate, and would have been an asset to the team. But as it turned out, the company decided not to engage the services of Roy Dorman. I was really disappointed – we would have had a ball working together.

Opportunity knocks

After interviewing for a while, Roy told me that he was being considered for positions at two different companies – there was now third party endorsement of his skills. He had studied hard and done well.

One of the companies had three servers, and the other had fifteen. I told him that he had to take the job at the fifteen-server company, but he was extremely hesitant to do so. I stated in no uncertain terms that the larger company was his only move, and when he asked me why, I told him: “Because you’ll learn more there.”

Roy accepted the position at the fifteen-server company, and it was the beginning of a career that would encompass working at some very large corporations, such as Viacom, SAC Capital and others.

Through our collaboration, an intensely personal bond between us was formed – I looked upon Roy as a brother, and we stayed close throughout the many years that followed.

The Eternal Optimist

Roy called one day, revealing that he had Stage IV colon cancer. One thing I remember him saying to me was that he was looking at it as one of life’s “bumps in the road”. That was Roy’s essence – he was The Eternal Optimist.

For a while he did well with treatment. But after about a year, it was clear that Roy was losing the battle. When I picked him up from the hospital to take him home for the last time, he thanked me for giving him an opportunity to do better. I told him that my role was simply one of guidance (and ass kicker drill sergeant) – he did all the work.

Tragically, Roy passed away not long after that conversation, leaving behind a wife and two beautiful young daughters. For about ten years, he got to live the life he wanted (if you think that working is living, that is).


What can we take away from all of this?

· Back in the day, there was a lot less to learn, in order to become gainfully employed as a SQL Server DBA

· it’s never too late to change your life

· Motivation is more important than raw talent

· Good karma is good

In New York City, for decades the final destination for many who were down on their luck was the infamous Bowery. My mother told me that every time she walked along that famed stretch known as Skid Row, she came home broke – you need not be Sigmund Freud to figure out where my generous side originates.

Some of you reading this post might think that I had ulterior motives for helping Roy – that nobody does something for nothing. I can assure you there were no obligations on Roy’s part, but he did remit every penny of the $29,600 that he received.

Now that I think of it, perhaps I did get more from Roy than what I initially gave him. Every time I think of him, and what we achieved together, a warm smile spreads across my face.

Good DBAs always strive to be better at their tradecraft. How many of us attempt to improve our humanity?

I challenge everyone reading this post to commit to making another person’s life better in some way, large or small – no strings attached.

Thanks for reading –

Ned Otter

New York City, 2013