THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

Testing Hekaton: some results.

In the last months I’ve been playing with Hekaton a while, since a customer of ours is evaluating its usage for one of its flagship products.

Here’s I’d like to share with you what I’ve found doing this test. I think it is really interesting for two main aspect

  1. they show that Hekaton is not magic and still good best practices and good hardware must be in place
  2. Hekaton can be really fast, competing – in performances – with some NoSQL databases (but I’ll discuss about this in another post)

For the test I’ve used a VM running on VMWare Workstation, with the guest having 2 vCPU and 4GB of Ram. Disk is pre-allocated vDisks stored on a Samsung 840 Pro SSD.

Surely it’s not a server, but we wanted to try Hekaton  on (somehow) cheap machines in order to understand which kind of performance we can have also on (somehow) cheap hardware.

The test was a simple insert of 10,000 entities with the following structure

customerId: Int
attribute1: varchar(100)
attribute2: varchar(100)
attribute3: varchar(100)

Here’s the results:

image

SO = Schema_Only
SAD = Schema_And_Data
NC = Native Compilation

We didn’t test the usage of Bulk Insert ‘cause we were interested in understanding how fast we can be on singleton inserts. Insert was done calling a stored procedure made of only one INSERT command.

As visible the usage of Hekaton at its full potential (SO+NC) improved the performance of near three times. Not really bad! (This kind of performance are fast as some NoSQL DB in my tests!)

Dropping the Native Compilation increased the elapsed time a little bit, but that’s the price you have to pay if you want, for example, to use SEQUENCES or other features not actually supported by Native Compiled procedures. (Using SQL Server 2014 CTP1 right now)

The real surprise was the fact that using the Schema_And_Data option for an Hekaton table basically removed all performance gains, making the difference between Hekaton tables and “classic” tables almost invisible.

I really wanted to understand why since I was not really expecting this.

Analyzing wait stats turned out that we where hitting a bottleneck at the transaction log level:

Here’s the wait stats using SO:

image

and here’s the SAD (by all means!) wait stats

image

Now, everything it’s much more clear. Putting Log and Data on the same disk is STILL not a good idea. So what are the thing we’ve learned in these tests?

Well, first of all, Hekaton can really change the game as it’s performance are really impressive and can really compete with some of the fastest NoSQL databases (CouchBase for example).As said before I’ll do a post on that in future, but before I want to do additional tests and wait for Hekaton to RTM status.

As a second point, it shows that there is no magic going around here and one can’t simple hope to solve it’s performance problems simply enabling Hekaton. A good system architecture is still needed and even good understanding of the engine is a must (as always) in order to find the bottleneck and fix the problem.

Unfortunately I wasn’t able to do additional test to show that the bottleneck can be removed due to time and hardware restrictions but I hope to be able to them in the near future so that I can update this post. In the meantime if someone wants to try to execute the same test, just drop me an email and I’ll share the very simple tool I created with you.

Published Friday, August 16, 2013 5:16 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Arvind Shyamsundar said:

Hi Davide

You've actually picked one of the workloads which will NOT scale well with HK. Small, 'chatty' transactions like the INSERT-only workload you picked will not see as much improvement as slightly heavier logic. Remember HK is not a 'cure-all' but is intended to be like a 'surgical knife' which you should use on the right bottleneck. Use the AMR tool to identify those bottlenecks and operate on those, then you will see the multi-fold improvement even with S+D durability.

HTH.

Arvind Shyamsundar (http://blogs.msdn.com/b/arvindsh)

August 16, 2013 11:03 AM
 

Davide Mauri said:

Thanks Arvind.

So that means that even putting the t-log in a dedicated and fast I/O unit, we will observe the same problem?

For now we only tested INSERT but of course we'll be doing a mix of SELECT / INSERT. Having read the BWin case study it looked to me as very similar to our case so I was expecting to be in a perfect case for Hekaton.

August 18, 2013 11:27 AM
 

Hennie said:

I don't understand the  different options. Isn't schema only just the schema and no data? Or are these options  of hekaton? Gr hennie

August 21, 2013 2:34 AM
 

Davide Mauri said:

Hennie: yes, "SCHEMA_ONLY" is just schema and no data. The options a related to in-memory optimized tables, that are managed by the new in-memory engine, named "Hekaton".

August 23, 2013 9:24 AM
 

Pablo Doval said:

Hi Davide,

  Thanks for the reat post! However, I do also partially share the opinión of Arvind.

  Your Solution is very different to the BWin case study; you only have one 'big' transaction, whereas in BWin they have a huge number or tiny transactions. The problema here is that you are hitting a bottleneck in the TLog well before the Hekaton can really make a difference.

  Change that insert statement of 10000 rows with 10000 inserts of one row, and I'm quite certain that you will first remove that TLog bottlenet and then you will be able to compare Hekaton in fair terms.

  The key here is that you cannot evaluate a feature until the problem this feature tries to solve is the bottleneck :)

Best!

Pablo Doval

August 26, 2013 3:04 AM
 

Davide Mauri said:

Hi Pablo! Thanks for your feedback, but actually we also have a huge number of tiny transactions. Each insert of the 10000 insert is a transaction on is own. And this is exactly the situation that make the problem arise. So it cannot obviously be the solution :)

August 26, 2013 8:39 AM
 

Pablo Doval said:

Hi again Davide! I don't know the details of your testing scenario, so probably you are absolutely right :)

However, just for completeness, I'd like to point that 'tiny transactions' inside a 'big' one are handled differently - and don't get me wrong, I know you abslutely understand this!

Just as a conrete example, a couple of years ago a very good friend of mine and I set up a challenge: the goal was to insert an specific workload in the shortest amount of time. He was using MongoDb and I, how not, SQL Server. We detailed all the steps in our respective blogs (sorry, in Spanish only), using all the tricks we had: parallellism, partitioning to avoid PFS contention, processor affinity using soft-NUMA, etc. And for all those tricks, probably one of the most effective was avoiding TLog contention by batching. I can show you the wait stats on that step alone and the difference was huge.

That's why I would, at least, give it a try and check the wait stats afterwards.

Once again, thanks fro sharing your findings!!!

NOTE: We also banned the BULK INSERT for our Little challenge as a 'weapon of mass destruction' :)

August 26, 2013 12:00 PM
 

Roberto Alessi said:

Hello, i was at SQL Saturday 260. I know that log and db should be on different disks, but how to obtain this in a SAN ? i' dont' have a heavy knowledge on this, can you suggest some good practice for a db on a vm in a SAN ?

October 10, 2013 6:02 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement