In-Memory OLTP: A Case Study

Published on: 2018-04-17

Watch this week’s episode on YouTube.

When In-Memory OLTP was first released in SQL Server 2014, I was excited to start using it.  All I could think was “my queries are going to run so FAST!

Well, I never got around to implementing In-Memory OLTP.  Besides having an incompatible version of SQL Server at the time, the in-memory features had too many limitations for my specific use-cases.

Fast forward a few years, and I’ve done nothing with In-Memory OLTP.  Nothing that is until I saw Erin Stellato present at our Northern Ohio SQL Server User Group a few weeks ago – her presentation inspired me to take a look at In-Memory OLTP again to see if I could use it.

Use case: Improving ETL staging loads

After being refreshed on the ins and outs of in-memory SQL Server, I wanted to see if I could apply some of the techniques to one of my etls.

The ETL consists of two major steps:

  1. Shred documents into row/column data and then dump that data into a staging table.
  2. Delete some of the documents from the staging table.

In the real world, there’s a step 1.5 that does some processing of the data, but it’s not relevant to these in-memory OLTP demos.

So step one was to create my staging tables.  The memory optimized table is called “NewStage1” and the traditional disked based tabled is called “OldStage1”:

Few things to keep in mind:

  • The tables have the same columns and datatypes, with the only difference being that the NewStage1 table is memory optimized.
  • My database is using simple recovery so I am able to perform minimal logging/bulk operations on my disk-based table.
  • Additionally, I’m using  the SCHEMA_ONLY durability setting.  This gives me outstanding performance because there is no writing to the transaction log!  However, this means if I lose my in-memory data for any reason (crash, restart, corruption, etc…) I am completely out of luck.  This is fine for my staging data scenario since I can easily recreate the data if necessary.

Inserting and deleting data

Next I’m going to create procedures for inserting and deleting my data into both my new and old staging tables:

Few more things to note:

  • My new procedures are natively compiled: SQL Server compiles them up front so at run time it can just execute without any extra steps.  The procedures that target my old disk-based tables will have to compile every time.
  • In the old delete procedure, I am deleting data in chunks so my transaction log doesn’t get full.  In the new version of the procedure, I don’t have to worry about this because, as I mentioned earlier, my memory optimized table doesn’t have to use the transaction log.

Let’s simulate a load

It’s time to see if all of this fancy in-memory stuff is actually worth all of the restrictions.

In my load, I’m going to mimic loading three documents with around 3 million rows each.  Then, I’m going to delete the second document from each table:

The in-memory version should have a significant advantage because:

  1. The natively compiled procedure is precompiled (shouldn’t be a huge deal here since we are doing everything in a single INSERT INTO…SELECT).
  2. The in-memory table inserts/deletes don’t have to write to the transaction log (this should be huge!)

Results

Disk-based In-Memory
INSERT 3 documents 65 sec 6 sec
DELETE 1 document 46 sec 0 sec
Total time 111 sec 6 sec
Difference -95% slower 1750% faster

The results speak for themselves.  In this particular example, in-memory destroys the disk-based solution out of the water.

Obviously there are downsides to in-memory (like consuming a lot of memory) but if you are going for pure speed, there’s nothing faster.

Warning! I am not you.

And you are not me.

While in-memory works great for my ETL scenario, there are many requirements and limitations.  It’s not going to work in every scenario.  Be sure you understand the in-memory durability options to prevent any potential data loss and try it out for yourself!  You might be surprised by the performance gains you’ll see.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

AT TIME ZONE: The easy way to deal with time zones and daylight savings time

Published on: 2018-03-27

Many people think daylight savings time was created to help align the hours that the sun is up with our waking hours so farmers and school children didn’t have to be outside in the dark.  Or something like that.

Well let me fill you in on a little secret – daylight savings time was actually created by a government works project to ensure that programmers could forever write tedious conditional logic in their date-based queries to handle the date time conversions surrounding time zones and daylight savings time.

While these types of datetime conversions have historically been a pain, SQL Server 2016 introduced the AT TIME ZONE feature makes these types of conversions a breeze.

Defining Time Zone

Let’s say you have a datetime value that you know is encoded in UTC (if you don’t know what timezone your data was originally encoded in you’re out of luck):

Besides naming convention, there’s nothing that tells us that our datetime is in UTC.  But if we know that to be the case, we can use AT TIME ZONE to add an UTC offset to the timestamp to make it official:

See that +00:00  at the end of our value?  That’s our time zone offset – it’s basically telling us how many hours and minutes away from UTC our date is stored in.  With this offset in place, our UTC datetime isn’t encoded only by the variable name – it’s actually encoded in the data itself.

Converting Time Zones

Now that we have a well-defined UTC datetime, we can use AT TIME ZONE to easily convert our data to different time zones.

For example, if we want to convert our UTC datetime to Eastern Standard Time, we can tack on  AT TIME ZONE 'Eastern Standard Time' :

The time portion of our datetime is now showing 7am with the offset indicating we are 4 hours behind UTC.  Easy time zone conversions, yes!

But What About Our Farmer Friends?

So AT TIME ZONE makes it easy to convert between time zones – but how does it handle daylight savings time conversions?

Well, on March 11, 2018 I lost an hour of my life when daylight savings time kicked in at 2am.  Does AT TIME ZONE recognize this theft?

YES! Say so long to conditional conditions that subtract an amount of time based on the date; AT TIME ZONE handles the switch to DST with ease as noted by the offset change from -05:00  to -04:00 .

And just like that, programming for time zone and day light savings time logic just became a little easier.

But I Don’t Live In The Eastern Standard Time Zone!

No problem, in addition to AT TIME ZONE, SQL Server 2016 added this nifty table that will give you the names of all of the time zones it supports:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Full Automatic Tuning: SQL Server 2026’s Most Killer Feature

Published on: 2018-03-13

This post is a response to this month’s T-SQL Tuesday #100 prompt by the creator of T-SQL Tuesday himself, Adam Machanic.   T-SQL Tuesday is a way for SQL Server bloggers to share ideas about a different database or professional topic every month.

This month I’m going down the science fiction route and pretending that I’m writing about a new SQL Server feature in 2026.


Fully Automatic Tuning

I was really excited when automatic tuning capabilities were first introduced in SQL Server 2017.  I couldn’t wait to say so-long to the days where I had to spend time fixing basic, repetitive query tuning problems.

And while those first versions of automatic plan choice corrections were fine, there was a lot left to be desired…

Fortunately, Microsoft has fully leveraged its built-in R and Python services to allow for advanced automatic tuning to make the life of SQL Server DBAs and developers that much easier.

On By Default

Perhaps the coolest part of these new automatic tuning capabilities is that they are on by default.  What this means is that databases will seem to perform better right out of the box without any kind of intervention.

I think the fact that Microsoft is confident enough to enable this by default in the on-premise version of SQL Server shows how confident they are in the capabilities of these features.

Optimize For Memory and Data Skew

While the first iterations of automatic query tuning involved swapping out query plans when SQL Server found a regression in CPU performance, the new automatic plan correction is able to factor in many more elements.

For example, instead of optimizing for CPU usage, setting the new flag  OPTIMIZE_FOR_MEMORY = ON  allows SQL server to minimize memory usage instead.

Also, with the addition of the new  “Optimized” cardinality estimator (so now we have “Legacy”, “New”, and “Optimized” –  who’s in charge of naming these things???) SQL Server is able to swap out different estimators at the query level in order to generate better execution plans!

What time is it?

Another new addition to automatic plan corrections is SQL Server’s ability to choose an appropriate execution plan based on historical time-of-day server usage.

So if a query is executing during a lull period on the server, SQL Server is intelligent enough to realize this and choose a plan that is more resource intensive.  This means faster query executions at the cost of a more intensive plan – but that’s OK since the server isn’t being fully utilized during those times anyway.

Making use of hardware sensors

As the world continues to include more data collecting sensors everywhere, SQL Server makes good use of these data points in 2026.

Tying into the server’s CPU and motherboard temperature sensors, SQL Server is able to negotiate with the OS and hardware to allow for dynamic CPU overclocking based on server demands.

While this option is not turned on by default, enabling dynamic overclocking allows for SQL Server to give itself a CPU processing boost when necessary, and then dial back down to more stable levels once finished.

This obviously won’t be a feature used by everybody, but users who are willing to trade off some stability for additional analytical processing performance will love this feature.

How I Stopped Worrying And Learned To Love Automatic Tuning

At the end of the day, we are our own worst enemies.  Even with the latest and greatest AI technology, we are capable of writing queries so terrible that even the smartest machine learning algorithms can’t grasp.

While SQL Server’s automatic tuning features work wonderfully on the boring and mundane performance problems, there are still plenty of performance problems that it leaves for us to troubleshoot.

And I love that.  Let the software optimize itself and maintain a “good enough” baseline while letting me play with the really fun performance problems.

I’m sure these features will continue to evolve – but so will we, working on new problems and facing new data challenges.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!