I was looking through some\u00a0in-memory OLTP DMVs\u00a0in SQL Server 2014 and came across these in the DMV sys.dm_xtp_system_memory_consumers:
\nWRITE_SET_LOOKASIDE
\nSCAN_SET_LOOKASIDE
\nREAD_SET_LOOKASIDE<\/p>\n
Thought it might be nice to see how these work, but explain first\u00a0I’ll explain\u00a0what they’re about. By the way, a lookaside is just a cache, sometimes\u00a0of pointers to memory addresses. You’ve probably heard of the L1 and L2 cache built into CPUs.<\/p>\n
These sets and their corresponding lookasides\u00a0have to do with how memory-optimized tables do transactions against multi-version concurrency control (MVCC) tables (memory-optimized tables in SQL Server 2014 use MVCC). The three basic transaction types supported by\u00a0MVCC tables (lowest to highest isolation) are snapshot, repeatable read, and serializable. In all isolation levels, reads are performed as of the beginning of the transaction. So any of these levels will read rows where the begin-time of the transaction is between the begin-timestamp and end-timestamp of the row.<\/p>\n
Snapshot transactions require no additional processing, simply read the correct versions. Repeatable read means that the version you’d read at the end of the transaction must be the same as you’d read at the end. Serializable means repeatable read and in addition, if you’re doing a scan (e.g. select rows where id is between 10 and 20) no new rows in your range were added between beginning and end of your transaction. READ_SET and SCAN_SET have to do with repeatable read and serializable transactions, respectively.<\/p>\n
SQL Server\u00a0MVCC accomplishes the additional checks by **re-reading the rows at commit time**. Sounds expensive. It needs to make this re-reading fast, so instead of doing the\u00a0whole operation again, repeatable read transactions keep a pointer to each row they’ve read in a READ_SET and check the pointer again at commit time to see if they’re the same. You can see this behavior on a system with no other activity, in sys.dm_xtp_system_memory_consumers.<\/p>\n
— execute sys.dm_xtp_system_memory_consumers and check the READ_SET_LOOKASIDE amount
\nselect * from sys.dm_xtp_system_memory_consumers;
\n— now repeatable read transaction using AdventureWorks memory-optimized table sample
\nbegin transaction
\nselect * from Sales.SalesOrderHeader_inmem with (repeatableread);
\ncommit
\n— note READ_SET_LOOKASIDE should be bigger
\nselect * from sys.dm_xtp_system_memory_consumers;<\/p>\n
For a serializable transaction you need to ensure read consistency with the READ_SET and also ensure scans will produce the same rows and\u00a0no additional rows\u00a0with SCAN_SET. Choose a different table if you want to see them both increase, because, if there’s no other changes in Sales.SalesOrderHeader_inmem (from the first experiment)\u00a0the READ_SET_LOOKASIDE will retain the same rows\/same size:<\/p>\n
— execute sys.dm_xtp_system_memory_consumers and check the READ_SET_LOOKASIDE and SCAN_SET_LOOKASIDE amount
\nselect * from sys.dm_xtp_system_memory_consumers;
\n— now repeatable read transaction using AdventureWorks memory-optimzed table sample
\nbegin transaction
\nselect * from Production.Product_inmem with (serializable);
\ncommit
\n— note READ_SET_LOOKASIDE and SCAN_SET_LOOKASIDE should be bigger
\nselect * from sys.dm_xtp_system_memory_consumers;<\/p>\n
So what about the WRITE_SET then? Transactions that update MVCC rows replace the end-timestamp of the rows they update with the transaction ID while the transaction is running. A bit indicates whether the information is an end-timestamp or a transaction ID. The transaction ID also prohibits concurrent updates of the same row (it’s not a lock, transactions that attempt concurrent update will simply fail and roll back the transaction). They’ll also insert new rows with a transaction ID as a begin-timestamp.<\/p>\n
Transaction commit happens like this:
\nAcquire an end-timestamp
\nValidation (see READ_SET and SCAN_SET above)
\nHarden to the transaction log
\nUpdate the timestamps in the updated\/new rows<\/p>\n
The WRITE_SET is used to make updating the timestamps fast. Because this entry is getting a bit long, I’ll leave it to the reader to observe WRITE_SET activity using the DMV. And I’ll also leave it to you to\u00a0figure out how the transaction commit series of events guarantees transactionally consistent data. Hint: edge-cases and additional information\u00a0is\u00a0available in my previous blog entries of the subject.<\/p>\n
Cheers, @bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"
I was looking through some\u00a0in-memory OLTP DMVs\u00a0in SQL Server 2014 and came across these in the DMV sys.dm_xtp_system_memory_consumers: WRITE_SET_LOOKASIDE SCAN_SET_LOOKASIDE READ_SET_LOOKASIDE Thought it might be nice to see how these work, but explain first\u00a0I’ll explain\u00a0what they’re about. By the way, a lookaside is just a cache, sometimes\u00a0of pointers to memory addresses. You’ve probably heard of […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,41],"tags":[],"class_list":["post-1762","post","type-post","status-publish","format-standard","hentry","category-hekaton","category-sql-server-2014"],"yoast_head":"\n