THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Geek City: SQL Server 2014 In-Memory OLTP (“Hekaton”) Whitepaper

Tech Ed isn’t even over yet, and my new Hekaton Whitepaper is already generating questions I can’t answer yet!

Here are the two questions that were in my inbox this morning:

1. The whitepaper says “There are other wait types, such as waiting for a log write to complete.”
Does this mean that writing to the transaction log  will be the main bottleneck of the system?

Hey folks, the product isn’t even publicly available. How can I, or anyone, know what your main bottleneck will be? You do know that there is ALWAYS a bottleneck, right? Something has to be the limiting factor? But I can’t even begin to make a guess yet as to what kind of limiting factors will be in Hekaton, now called SQL Server In-Memory OLTP. The paper is about the Internals, and that’s what I researched. I did not do any performance testing. But, as I did mention in the paper, log writing is much more efficient for Hekaton tables than for the disk-based tables. So even if it IS the limiting factor, it will not be as limiting as it could potentially be for non-Hekaton tables.

 

2.  The whitepaper says “Note that none of the LOB data types are allowed.” 
We have some 75 columns  which are still ntext, so  it seems we are dead in the water even if we convert to nvarchar(max)?

As you read in the paper, rows are stored completely differently, and not on the disk-based 8K pages. So there is no mechanism for special pages for LOB or row-overflow data. Also, you’re aware that there is extra overhead for managing and accessing the LOB data, and to make In-Memory OLTP as efficient as possible, those extra-large rows needing extra work are avoided.  And like in my previous answer, without seeing your complete design (no, please don’t send it to me Smile ) there is no way I can tell just how ‘dead in the water’ you might be. I tend to feel there would be a way to redesign your tables, and separate the LOB types (which should be converted to something other than the deprecated ntext soon, anyway) into their own tables. As you read in the paper,  In-Memory OLTP tables can be joined with disk-based tables.

Please feel free to send more questions, but I most likely will NOT be able to answer every one in a blog post. There will be a update to the paper, with more internals details, for CTP2, and some of the questions will be answered there. Finally, the ultimate plan is a book, like my SQL Server Concurrency book, all about Hekaton… oops, I mean In-Memory OLTP.  Hopefully, the book will contain best practices suggestions gathered from actual research with In-Memory OLTP implementations, plus a big Q&A section to answer questions that weren’t answered elsewhere in the book.

Thanks!

~Kalen

Published Wednesday, June 05, 2013 10:42 AM by Kalen Delaney

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

 

Aaron said:

Can you fix my database for me, it's slow. kthx bye.

Thanks for the whitepaper, I shall have a read now :)

June 5, 2013 5:58 PM
 

Aaron said:

Nice one.  Any hints about what sku's will include this ? hopefully standard gets it.

Let me know asap, so I can start removing my foreign keys and identity columns ;)

June 5, 2013 6:54 PM
 

Greg Linwood said:

Great article Kalen, very well written as usual and I really like where Microsoft is going with this, particularly as it integrates into the existing RDBMS & allows gradual migration.

Whilst there are some limitations to the implementation they seem reasonable & will still allow fairly wide spread use of this feature.

I have struggled to find compelling reasons to encourage customers to upgrade to SQL 2012 but this will be a significant reason to upgrade to SQL 2014, particulaly for customers with custom built solutions who can leverage the in-memory table feature relatively quickly.

June 5, 2013 10:02 PM
 

tobi said:

So many limitations. I had hoped for a feature that you could just enable by default and leave on auto-pilot.

This is targeted at the big clients who can expend as many man hours as they please. Management overhead is not a big concern for them.

June 6, 2013 7:27 AM
 

Wim said:

Nice whitepapaer!

2 Questions:

1. Why is the IdxLinkCount in the row header two bytes. As far I can see only 8 indexes may reference data in the data payload?

2. Figure 4, Insert of Greg, Beijing; I think the starting timestamp should be 100 in stead of 200?

June 6, 2013 10:30 AM
 

Wim said:

Please forget my first question... IdxLinkCount is a number of references not the number of indexes. Clear te me now.

June 6, 2013 10:41 AM
 

Luan Moreno said:

Kalen, thank you so much for this whitepaper i'm your fan

June 6, 2013 12:32 PM
 

Steve W said:

Could you possibly post the full URL to the WP? Tinyurls are blocked in my office.

Oh yeah, can you fix my database? It's slow...

THANKS!

June 6, 2013 4:48 PM
 

Steve W said:

Please cancel that last request. I used http://longurl.org to get it.

Thanks.

June 6, 2013 4:52 PM
 

Kalen Delaney said:

You can also go to Microsoft's SQL Server 2014 page and link to the paper from the lower-right corner.

http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx?WT.mc_id=OO_Technet_Home_General_EN_US  

~Kalen

June 6, 2013 5:53 PM
 

Chad Miller said:

The whitepaper mentions replication isn't support. Does that just mean using Hekaton tables as a replication source? I would think as a replication target--this would still be supported.

June 8, 2013 5:10 PM
 

Kalen Delaney said:

Hi Chad... I'll try to get details into the next whitepaper. I haven't tested replication yet, but my guess would be that being a subscriber isn't supported either due to the restrictions on the datatypes allowed. It would not be easy to ascertain in advance if the rows from the source would be allowable, so they just don't allow it at all. That's my guess for now....

~Kalen

June 8, 2013 6:28 PM
 

JRStern said:

Thanks for the paper, this sounds like big news.  I guess this is the ace in the hole that the SQL Server group has had for a while, as an excuse not to worry too much about some basic engine issues.

The number of limitations for the in-memory tables is offputting, but maybe they make sense in context - like who needs truncate table, if you don't have FKs and delete runs super-fast?

But not having FKs ... have to think about that, and the LOB/8K business too.  And - no identity columns?  That seems a pretty large shock to the system of any and all SQL Server practitioners!!!

June 17, 2013 2:06 PM
 

Kalen Delaney said:

Hi JRStern..

Since this is a completely new way of accessing and processing data, every operation has to be completely rewritten. MS decided they would prefer to get a system with basic operations out quickly, rather than wait for every feature to be implemented before releasing. More features will be added in every release!

~Kalen

June 19, 2013 6:16 PM
 

AlKessler said:

Seems like we'll be designing for a moveing target for several releases.  Switching between SQL-Classic and New-SQL (IMOLTP, aka Hekaton) sounds like a long term gradual process.  Any suggestions on db design ideas to take advantage of the new speedy features without breaking ?  I plan on buying asprin in bulk!  

June 21, 2013 9:46 AM
 

Luca Ferrari said:

thank's !!!!!! Great Whitepaper

June 25, 2013 5:54 PM
 

Anil Maharjan said:

It's really great to see such whitepapers and to know about the project 'Hekaton' aka In-Memory OLTP.

Thanks,

Anil

June 26, 2013 8:54 AM
 

Robert Young said:

The WP says that FK *constraints* aren't (yet?) supported.  But are FK access paths supported?  IOW, a FK can be defined for the purposes of generating joins, but FK violations are ignored on UPDATEs?  Or do we just use ANSI join syntax, and Hekaton uses single table access path for each table?

June 30, 2013 1:13 PM
 

dugi said:

Thanks for sharing!

July 3, 2013 5:43 PM
 

Kalen Delaney said:

AlKessler

I hope to get hands on with a couple of Hekaton implementations in the next month or two, so hopefully I'll be able to get some suggestions for db design issues.

~Kalen

July 8, 2013 1:47 PM
 

Kalen Delaney said:

Hi Robert

I'm really not sure what you're asking in your question about FK access paths. Can you clarify?

The JOIN syntax you use in a query should not affect how the query is processed internally.

~Kalen

July 8, 2013 1:50 PM
 

SQL Server 2014 – New age of performance | ???????? TechNet ?????????? said:

May 13, 2014 4:03 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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