Database Reference
In-Depth Information
SQL Server maintains another unique timestamp,
TransactionId
, and it increments and assigns it to the
transaction when it starts.
TransactionId
is used as a temporary value for
BeginTs
and
EndTs
timestamps in the rows
created or deleted by uncommitted transactions. We will discuss this process in detail later in the chapter.
Every statement in a transaction has a unique 4-byte
StmtId
value. The third element in a row header is the
StmtId
of the statement that is inserted a row. It works as a
Halloween protection
technique, similar to
Table Spools
in
on-disk tables, and it allows the statement to skip rows it inserted. You can think about the
INSERT INTO T SELECT
FROM T
statement as the classic example of such a situation.
■
We discussed the Halloween Protection technique and table Spools in Chapter 25, “Query Optimization
and execution.”
Note
In contrast to on-disk tables, where nonclustered indexes are separate data structures, all indexes in
memory-optimized tables reference actual data rows. Each new index that is defined on a table adds a pointer to a
data row. For example, if a table has two indexes defined, every data row in a table would have two 8-byte pointers
that reference the next data rows in the index chains. This, in a nutshell, makes every index in memory-optimizing
tables covering; that is, when SQL Server locates a row through an index, it finds the actual data row rather than the
separate index row structure.
The next element in the header, 2-byte
IdxLinkCount
, indicates how many indexes (pointers) reference the row.
SQL Server uses it to detect rows that can be deallocated by the garbage collection process.
An array of 8-byte index pointers is the last element of the row header. As you can guess, every memory-optimized
table should have at least one index to link data rows together. At most, you can define eight indexes per memory-optimized
table, including the primary key.
The actual row data is stored in the Payload section of the row. As already mentioned, the Payload format may
vary depending on the table schema. SQL Server works with Payload through a DLL that is generated and compiled at
the time of table creation.
A key principle of in-memory OLTP is that Payload data is never updated. When a table row needs to be updated,
Hekaton sets the
EndTs
attribute of original row to the
Global Transaction Timestamp
of the transaction and inserts the
new version of the data row with the new
BeginTs
and
EndTs
values of
Infinity
. We will see how this works in detail shortly.
Hash Indexes
Hash indexes
are one of two index types supported by in-memory OLTP. They consist of an array of hash buckets, each
of which contains a pointer to a data row. SQL Server applies a hash function to the index key columns, and the result
of the function determines to which bucket a row belongs. All rows that have the same hash value and belong to the
same bucket are linked together through a chain of index pointers in the data rows.
Figure
32-3
illustrates an example of a memory-optimized table with two hash indexes defined on the
Name
and
City
columns. Solid arrows represent pointers in the index on the
Name
column. Dotted arrows represent pointers in
the index on the
City
column. For simplicity sake, let's assume that the hash function generates a hash value based on
the first letter of the string.
