In-Memory OLTP - SQL Server 2014
-
Thanks for the feed back all appreciated.
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
according to http://msdn.microsoft.com/en-us/library/dn511014(v=sql.120).aspx
Memory-optimized tables are fully durable by default, . . .
A second copy of the table data is maintained on disk, but only for durability purposes.
-
Awesome. This was very interesting. Thank you for the post. 🙂
(please post more questions like this.... like very new and amazing concepts..)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
paul s-306273 (3/10/2014)
If 'could be' is an option, it's often right.Should have gone for that.
🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Nice interesting question.
The "it depends" answer of course conceals an additional complication - full durability versus delayed durability. It's interesting to think about which side of the question "delayed durability" falls on. It counts as "SCHEMA_AND_DATA" durability but doesn't actually guarantee data durability (it violates the write log first rule of the standard durable transaction model). So presumably the same question restricted to tables with "SCHEMA_AND_DATA" durability would have the same right answer.
Tom
-
Interesting question, thanks!
-
TomThomson (3/11/2014)
It counts as "SCHEMA_AND_DATA" durability but doesn't actually guarantee data durability (it violates the write log first rule of the standard durable transaction model)Huh? That's the first time I have heard that - and I have heard quite a lot about the In-Memory OLTP aka Memory-Optimized Tables aka Hekaton project.
Can you quote a source for that claim?
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
I'm with Grasshopper, whilst I'm aware that you can configure a Memory Optimized Table to be Not-Durable, as it is just an optional setting, it is NOT a consequence of (does not follow that) it is Not-Durable (when so configured) because it is memory resident.
-
Interesting question and interesting, enlightening discussion. Thanks, Ford!
-
Hugo Kornelis (3/11/2014)
TomThomson (3/11/2014)
It counts as "SCHEMA_AND_DATA" durability but doesn't actually guarantee data durability (it violates the write log first rule of the standard durable transaction model)Huh? That's the first time I have heard that - and I have heard quite a lot about the In-Memory OLTP aka Memory-Optimized Tables aka Hekaton project.
Can you quote a source for that claim?
Yes. BOL is a good source: the relevant page is Control Transaction Durability, which states (for delayed transaction durability)
Delayed transaction durabilityDelayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are kept in a buffer and hardened to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction durability reduces both latency and contention within the system because:
The transaction commit processing does not wait for log IO to finish and return control to the client.
Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.
Note Note
You may still have log I/O contention if there is a high degree of concurrency, particularly if you fill up the log buffer faster than you flush it.
When to use delayed transaction durability
Some of the cases in which you could benefit from using delayed transaction durability are:
You can tolerate some data loss.
If you can tolerate some data loss, for example, where individual records are not critical as long as you have most of the data, then delayed durability may be worth considering. If you cannot tolerate any data loss, do not use delayed transaction durability.
Of course the other thing which counts as SCHEMA_AND_DATA durability is full transaction durability, which conforms to the standard write-ahead model and does guarantee data durability (to the same extent that it's guaranteed for disc based data - setting off enough big enough bombs in all the right places will still tend to render data non-durable).
Tom
-
TomThomson (3/14/2014)
Hugo Kornelis (3/11/2014)
TomThomson (3/11/2014)
It counts as "SCHEMA_AND_DATA" durability but doesn't actually guarantee data durability (it violates the write log first rule of the standard durable transaction model)Huh? That's the first time I have heard that - and I have heard quite a lot about the In-Memory OLTP aka Memory-Optimized Tables aka Hekaton project.
Can you quote a source for that claim?
Yes. BOL is a good source: the relevant page is Control Transaction Durability, which states (for delayed transaction durability)
(quote snipped)
That page describes the feature "delayed durability", which is added as a new, optional feature in SQL Server 2014.
This feature is not specifically tied in to in-memory OLTP. Delayed durability affects ALL tables, because it changes the hardening of the log when transactions are committed from synchronous to asynchronous.
The biggest property that Hekaton and "delayed durability" have in common is being released in SQL Server 2014.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (3/14/2014)
That page describes the feature "delayed durability", which is added as a new, optional feature in SQL Server 2014.This feature is not specifically tied in to in-memory OLTP. Delayed durability affects ALL tables, because it changes the hardening of the log when transactions are committed from synchronous to asynchronous.
The biggest property that Hekaton and "delayed durability" have in common is being released in SQL Server 2014.
As you asked for documentation for the statement that delayed transaction durability when combined with Hekaton's SCHEMA_AND_DATA durability I don't understand why you are suggesting the reference I provided isn't relevant. Delayed transactional durability does as I stated violate the standard log-write-ahead consistency model, and is as I stated one of the models applicable to the case Hekaton documentation calls "SCHEMA_AND_DATA durability" (the other is of course the standard model). If you already knew about delayed transaction durability, why did you ask for a reference to its violation of the standard durability model?
Tom
-
TomThomson (3/14/2014)
Hugo Kornelis (3/14/2014)
That page describes the feature "delayed durability", which is added as a new, optional feature in SQL Server 2014.This feature is not specifically tied in to in-memory OLTP. Delayed durability affects ALL tables, because it changes the hardening of the log when transactions are committed from synchronous to asynchronous.
The biggest property that Hekaton and "delayed durability" have in common is being released in SQL Server 2014.
As you asked for documentation for the statement that delayed transaction durability when combined with Hekaton's SCHEMA_AND_DATA durability I don't understand why you are suggesting the reference I provided isn't relevant. Delayed transactional durability does as I stated violate the standard log-write-ahead consistency model, and is as I stated one of the models applicable to the case Hekaton documentation calls "SCHEMA_AND_DATA durability" (the other is of course the standard model). If you already knew about delayed transaction durability, why did you ask for a reference to its violation of the standard durability model?
The post you first made about this suggests (at least to me) that delayed durability is a side effect of using in-memory OLTP. Especially because you bring it up in a discussion on the effect on durability of in-memory OLTP. That's why I, upon reading that comment, didn't even make the mental connection to the new "delayed durability" feature. I honestly thought you were commenting on the in-memory feature.
So to summarize:
Delayed durability (not the subject of this QotD) affects the durability of all tables and all data, both memory-optimzed and "standard" - except for schema and data in temporary tables, and data in hekaton tables with "durability=schema_only".
In-memory OLTP offers no durability for data in tables with "durability=schema_only"; data in tables with "durability=schema_and_data" and schema of all tables is always exactly as durable as data in permanent "standard" tables (and hence subject to the new delayed-durability database option).
If that is what you were tryiing to say, then please accept my apologies for misunderstanding.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (3/14/2014)
The post you first made about this suggests (at least to me) that delayed durability is a side effect of using in-memory OLTP.I certainly didn't intend that.
I had two things in mind:
first it's rather obvious that "it depends" when both tables with SCHEMA_AND_DATA durability and tables with SCHEMA_ONLY durability are considered, a bit less obvious when only tables with SCHEMA_AND_DATA tables are considered (unless one knows about delayed and full transactional durability) so that adjustment might have made a more interesting question than the actual question;
second it's at least questionable whether the term "SCHEMA_AND_DATA durability" might mislead people into thinking that that provides the same level of data durability that was provided for disc-based tables in earlier versions of SQL Server, so that had the question been phrased to cover only tables with that durability it would perhaps have helped some people to avoid a mistake by introducing them to the "delayed durability" concept, and as it wasn't so phrased and given that so many people often say they learned as much or more from the discussion as from the question, I might be doing something useful by bringing that concept up in the discussion as it wasn't mentioned in the question or in the explanation - and the explanation does appear to suggest the the option for in-store durability is all or nothing, since is states that if data durability is selected then no data will be lost at a crash, which is not correct: it depends on the full or delayed durability option.
Perhaps instead I should have submitted a pair of questions - one the modified version of this question and one the same qustion about tables held on disc.
Especially because you bring it up in a discussion on the effect on durability of in-memory OLTP. That's why I, upon reading that comment, didn't even make the mental connection to the new "delayed durability" feature. I honestly thought you were commenting on the in-memory feature.
So to summarize:
Delayed durability (not the subject of this QotD) affects the durability of all tables and all data, both memory-optimzed and "standard" - except for schema and data in temporary tables, and data in hekaton tables with "durability=schema_only".
In-memory OLTP offers no durability for data in tables with "durability=schema_only"; data in tables with "durability=schema_and_data" and schema of all tables is always exactly as durable as data in permanent "standard" tables (and hence subject to the new delayed-durability database option).
That looks like a decent summary of how it all fits together.
If that is what you were tryiing to say, then please accept my apologies for misunderstanding.
All I was trying to say was "look out, there's another feature that impacts on durability". I guess I said it pretty badly - too verboosely to start with. No nedd for an apology for misunderstanding, it's clear from your response that I misunderstood your comment on my response as well.
Tom
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply