In-Memory OLTP in SQL Server 2014. Part I
The In-Memory OLTP functionality (Hekaton project) is designed to speed up the processing of typical OLTP operations in SQL Server. As you know, the load on the database server, whether it is Microsoft SQL Server or Oracle / MySQL or SAP / Sybase or IBM DB2, etc., can be divided into two classes: data collection and analysis of what was collected, because what was it otherwise to collect? The first class of tasks is called OLTP (On-Line Transactional Processing). It is characterized by short update transactions affecting a relatively small number of tables in the database. Examples of such applications are a banking day, a telecom billing, etc. The second class of tasks is called OLAP (On-Line Analytical Processing) and is characterized by massive long-term reading, covering a significant number of tables and collecting, as a rule, maximum records, replete with binding, sorting, grouping predicates, aggregate functions, etc. As a rule, data structures for different classes of tasks are divided so that they do not interfere with each other, and if the first database structure is built taking into account numerous Codd rules, the second one, on the contrary, is denormalized and is executed according to the star (snowflake) scheme.
The DBMS memory striving manifested itself at the beginning of the new millennium, when it turned out that, despite Moore’s law, the clock frequency and speed of processors do not grow exponentially, but on the contrary, they go to a flat direct saturation despite ILP and other tricks. At the same time, the prices for RAM, which was once indecently expensive, are catastrophically decreasing and, compared to the 90s, have fallen thousands of times. Yeah, the database server makers told themselves. In 2005, Oracle bought TimesTen in-memory, IBM in 2007, Solid, and in this case, Microsoft didn’t take anything from the outside, because they decided to train them in their team.
Started with in-memory OLAP. The first fruits of education were embodied in the VertiPaq ColumnStore engine with the release of SQL Server 2008 R2 in the form of PowerPivot for Excel and SharePoint. As the name implies, the column index is designed in such a way that each page contains a piece of a column in a highly compressed form (~ 2 times better than with the compression that has appeared in SQL Server 2008 page) in order to tamp more data into memory. Traditionally, OLAP benefits from column indexes, because, as we remember, this is a massive read. As a rule, it is required to read along the rows (or range) all columns in which directories (dimension tables, star rays) are connected with foreign key columns in the fact table (hub) in order to build between them (or semi-join). In SQL Server 2012, two useful things happened in this regard. First, xVelocity (formerly VertiPaq) appeared in Analysis Services in the form of so-called tabular models, alternative to the traditional multidimensional, existing, give Gd memory, with SQL Server 7.0. Secondly, the column index ceased to be a thing in itself, and it became possible to build it explicitly as needed — the CREATE [NONCLUSTERED] COLUMNSTORE INDEX command appeared in T-SQL. A cloud of restrictions was immediately imposed on the column indexes, the most cruel of which was, of course, this one - it was not updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before the statement, and then rebuilding the columnstore index after UPDATE has completed. In SQL Server 2014, this was tackled with an updated cluster column index. Well, as a column ... To make the column index updateable, a delta store and delete bitmap were hung on it. When a record is deleted, it does not physically disappear, and the flag in delete bitmap is cocked onto it. When a record is inserted, it enters the delta store. Both are ordinary B-Tree (rowstore) with all its pluses and minuses. There is a Tuple Mover background process that crawls across the delta store and converts the added entries into columnstore segments, but generally speaking, reading the column index means reading not only the columnstore, but also these two overhead friends, because you want to filter the deleted entries and union added. However, two weeks after its release, SQL Sever 2014 showed record results in independent TPC-H analytical tests, taking the first places in the tournament tables by weight categories 1, 3 and 10 TB of database volume in a non-cluster (standalone) standings. Thus, we will assume that with in-memory OLAP everything is fine and move on to in-memory OLTP.
As already mentioned, Hekaton is not the code name of the next version of SQL Server, as were Denali, Katmai, Yukon, etc., but, actually, a project for developing an in-memory engine, i.e. part of the product. This component is the most striking innovation, not only in the current version, but also, perhaps, on the scale of the entire product line, starting with 16-bit Ashton-Tate / Microsoft SQL Server 1.0, which was released 25 years ago. Hecaton is a Greek word and means one hundred or one-hundred = a hundred times that the kagbe hints that this is not steeper than the tenth one. I would like to immediately warn against the misconception that Hecaton is some kind of extended version of dbcc pintable, because with a table attached to the memory, work happens as with a regular disk drive, including execution plans, ensuring transaction integrity with locks, etc. Hecaton is a compact independent core integrated into the SQL Server executive mechanism, characterized by the lack of interpretable execution plans, locks as a means of ensuring logical data integrity and latches for physical integrity compared to the traditional database engine. Let me remind you that the latches (I don’t know how ideologically they are correctly translated into Russian, probably, latches) are lightweight locks that are superimposed on data pages, index pages, some service structures directly at the moment of their reading or change in memory as opposed to from locks that can be valid for the entire transaction. Therefore, without plunging into details, it can be considered that there is no dedlatchey. There are nuances, but not so painful. Another difference is that locks can be controlled (using hints, isolation level). Latchi are under the authority of SQL Server. I will not go into their internal structure in detail, those who wish can refer to BOL or to the page of Yevgeny Khabarov . I won’t bother with theory at all; let's move on to examples.
Let's create a database and in it a file group for in-memory OLTP.
')
Script 1
This is a file-like file group, data from memory will persist into it, and during recovery. accordingly, read backwards. Like clustered columnstore, it consists of data files in which the records inserted in the insert or update sections and delta files in which identifiers of deleted records are stored are sequentially written. At first, changes, as usual, are reflected in memory, and at checkpoint, 256K blocks (in the case of data) and 4K (delta) are thrown onto the disk, which is noted in the transaction log. The merging of data-delta pairs occurs automatically upon reaching a certain size and depending on the size of the RAM, and can also be done manually by the sys.sp_xtp_merge_checkpoint_files procedure. Read more about this process here .
Thus, the placement of tables in memory does not mean that if the server is cut off, all that is acquired by overwork, all will be lost. In-Memory OLTP is a fully transactional technology and supports fault tolerance, including AlwaysOn.
In the newly created database we will create a table optimized for working in memory.
Script 2
The last option just means that the table will be placed in memory. Each MEMORY_OPTIMIZED table must have at least one index. The total number must not exceed 8. The fields in the index must not be nullable. For the fields n (var) char included in the indices, the BIN2 collation should be used. Clustered indices, by definition, no. In its structure of classic b-tree too. For tables in memory, indexes are HASH (better suited for point searches) and RANGE (as the name implies, better suited for scans by range).
The main component of the hash index is the so-called mapping table, in one column which stores the results of applying the hash function to the concatenation of the fields that make up the index key, and in the other - pointers to records. Since the hash function can produce the same results (collisions arise) for completely different values of the argument (for close ones, they must differ), in reality, these will be pointers to the memory areas where the overflow chains lie. Each such chain is a bidirectional list. The main parameter when creating a hash index is bucket_count. This is the number of slots in the match table. The smaller they are, the higher the probability of a collision, the longer the chains of overflows grow from each hash. Accordingly, it is obvious that it should not be less than the number of unique values in the index key. In fact, it is estimated as the number of unique values, rounded up to the next degree 2, and explains in detail why.
The second type of index, available in-memory, is called range (range) and is very similar to the classic cluster. Its nodes form an ordered structure, effective for scanning across ranges. Instead of B-Tree, its modification Bw-Tree is used , the most striking difference of which, perhaps, is that it does not store pointers to duplicate values. If the table has a million identical values, the classic tree will stupidly keep a million pointers (to data) in the leaves. In this case, Bw costs one, which allows dramatic (English-language authors love this word very much) to save space when storing this economy in memory. The only thing is, in this case, overflow chains arise - we do not store pointers to all records, how to get to the next one with the same key value? There is a feeling that there is no saving, just bytes on ptr were transferred from the leaves to the bouquets. But no, read why this is not so, here , and we will move on and create another table.
Script 3
Note the last option highlighted in bold color. It means that the data of this table will not be saved between server restarts (the structure itself will remain). Above, I said that there is a misconception about Hekaton that everything that is in memory is lost when restarted. So for these tables, this is true, but you create them quite consciously to reduce overhead, in particular, for journaling where it is not needed. It is a kind of temporary table. By the way, table variables in Hecaton are also supported. They are declared through the preliminary creation of the table type CREATE TYPE ... AS TABLE ... Unlike regular table variables, they are stored, of course, not in disk tempdb, but refer to the database where they were declared.
Tables in memory do not support automatic statistics update, in particular, ALTER DATABASE ... SET AUTO_UPDATE_STATISTICS ON. Also does not work
exec sp_autostats @tblname = 'ShoppingCart'
Index Name AUTOSTATS Last Updated
[ix_UserId] OFF NULL
[PK__Shopping__7A789AE57302F83B] OFF NULL
exec sp_autostats @tblname = 'ShoppingCart', @flagc = 'ON'
- For example, renaming, are not supported with memory optimized tables.
However, statistics can be updated manually: UPDATE STATISTICS dbo. ShoppingCart WITH FULLSCAN, NORECOMPUTE.
In general, tables in memory have a cloud of limitations. Most tabular hints are not supported: No TABLOCK, XLOCK, PAGLOCK, ... NOLOCK does not swear, but does not react as if it were not. Dynamic and keyset cursors are silently converted to static. TRUNCATE TABLE and MERGE statements are not supported (when the table in memory serves as an assignment). There are restrictions on the types of fields used. You can read about them in detail here , we, in order not to be upset, let's see what we have done.
Script 4
In the installation directory of SQL Server C: \ Program Files \ Microsoft SQL Server \ ... \ DATA \ xtp \ 11 \, two dll appeared, which are called xtp_t_11_ <9 digits> .dll. These are our tables ShoppingCart and UserSession.
The built-in compiler converts T-SQL definitions of tables and stored procedures into C code (can be viewed in the same directory), from which the machine is obtained. The corresponding dynamic libraries are loaded into memory and linked into the SQL Server process. When restarting SQL Server, libraries are compiled and reloaded based on catalog information from metadata.
In the next part, we plan to consider the native compilation of stored procedures, isolation levels of transactions in Hekaton, locks, logging, and overall performance compared to traditional disk objects.
The DBMS memory striving manifested itself at the beginning of the new millennium, when it turned out that, despite Moore’s law, the clock frequency and speed of processors do not grow exponentially, but on the contrary, they go to a flat direct saturation despite ILP and other tricks. At the same time, the prices for RAM, which was once indecently expensive, are catastrophically decreasing and, compared to the 90s, have fallen thousands of times. Yeah, the database server makers told themselves. In 2005, Oracle bought TimesTen in-memory, IBM in 2007, Solid, and in this case, Microsoft didn’t take anything from the outside, because they decided to train them in their team.
Started with in-memory OLAP. The first fruits of education were embodied in the VertiPaq ColumnStore engine with the release of SQL Server 2008 R2 in the form of PowerPivot for Excel and SharePoint. As the name implies, the column index is designed in such a way that each page contains a piece of a column in a highly compressed form (~ 2 times better than with the compression that has appeared in SQL Server 2008 page) in order to tamp more data into memory. Traditionally, OLAP benefits from column indexes, because, as we remember, this is a massive read. As a rule, it is required to read along the rows (or range) all columns in which directories (dimension tables, star rays) are connected with foreign key columns in the fact table (hub) in order to build between them (or semi-join). In SQL Server 2012, two useful things happened in this regard. First, xVelocity (formerly VertiPaq) appeared in Analysis Services in the form of so-called tabular models, alternative to the traditional multidimensional, existing, give Gd memory, with SQL Server 7.0. Secondly, the column index ceased to be a thing in itself, and it became possible to build it explicitly as needed — the CREATE [NONCLUSTERED] COLUMNSTORE INDEX command appeared in T-SQL. A cloud of restrictions was immediately imposed on the column indexes, the most cruel of which was, of course, this one - it was not updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before the statement, and then rebuilding the columnstore index after UPDATE has completed. In SQL Server 2014, this was tackled with an updated cluster column index. Well, as a column ... To make the column index updateable, a delta store and delete bitmap were hung on it. When a record is deleted, it does not physically disappear, and the flag in delete bitmap is cocked onto it. When a record is inserted, it enters the delta store. Both are ordinary B-Tree (rowstore) with all its pluses and minuses. There is a Tuple Mover background process that crawls across the delta store and converts the added entries into columnstore segments, but generally speaking, reading the column index means reading not only the columnstore, but also these two overhead friends, because you want to filter the deleted entries and union added. However, two weeks after its release, SQL Sever 2014 showed record results in independent TPC-H analytical tests, taking the first places in the tournament tables by weight categories 1, 3 and 10 TB of database volume in a non-cluster (standalone) standings. Thus, we will assume that with in-memory OLAP everything is fine and move on to in-memory OLTP.
As already mentioned, Hekaton is not the code name of the next version of SQL Server, as were Denali, Katmai, Yukon, etc., but, actually, a project for developing an in-memory engine, i.e. part of the product. This component is the most striking innovation, not only in the current version, but also, perhaps, on the scale of the entire product line, starting with 16-bit Ashton-Tate / Microsoft SQL Server 1.0, which was released 25 years ago. Hecaton is a Greek word and means one hundred or one-hundred = a hundred times that the kagbe hints that this is not steeper than the tenth one. I would like to immediately warn against the misconception that Hecaton is some kind of extended version of dbcc pintable, because with a table attached to the memory, work happens as with a regular disk drive, including execution plans, ensuring transaction integrity with locks, etc. Hecaton is a compact independent core integrated into the SQL Server executive mechanism, characterized by the lack of interpretable execution plans, locks as a means of ensuring logical data integrity and latches for physical integrity compared to the traditional database engine. Let me remind you that the latches (I don’t know how ideologically they are correctly translated into Russian, probably, latches) are lightweight locks that are superimposed on data pages, index pages, some service structures directly at the moment of their reading or change in memory as opposed to from locks that can be valid for the entire transaction. Therefore, without plunging into details, it can be considered that there is no dedlatchey. There are nuances, but not so painful. Another difference is that locks can be controlled (using hints, isolation level). Latchi are under the authority of SQL Server. I will not go into their internal structure in detail, those who wish can refer to BOL or to the page of Yevgeny Khabarov . I won’t bother with theory at all; let's move on to examples.
Let's create a database and in it a file group for in-memory OLTP.
')
CREATE DATABASE hekaton_test ALTER DATABASE hekaton_test ADD FILEGROUP fg_hekaton_test CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE hekaton_test ADD FILE (name='fg_hekaton_test1', filename='c:\Temp\fg_hekaton_test1') TO FILEGROUP fg_hekaton_test Script 1
This is a file-like file group, data from memory will persist into it, and during recovery. accordingly, read backwards. Like clustered columnstore, it consists of data files in which the records inserted in the insert or update sections and delta files in which identifiers of deleted records are stored are sequentially written. At first, changes, as usual, are reflected in memory, and at checkpoint, 256K blocks (in the case of data) and 4K (delta) are thrown onto the disk, which is noted in the transaction log. The merging of data-delta pairs occurs automatically upon reaching a certain size and depending on the size of the RAM, and can also be done manually by the sys.sp_xtp_merge_checkpoint_files procedure. Read more about this process here .
Thus, the placement of tables in memory does not mean that if the server is cut off, all that is acquired by overwork, all will be lost. In-Memory OLTP is a fully transactional technology and supports fault tolerance, including AlwaysOn.
In the newly created database we will create a table optimized for working in memory.
use hekaton_test CREATE TABLE dbo.ShoppingCart ( ShoppingCartId int not null primary key nonclustered hash with (bucket_count=2000000), UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000), CreatedDate datetime2 not null, TotalPrice money ) WITH (MEMORY_OPTIMIZED=ON) Script 2
The last option just means that the table will be placed in memory. Each MEMORY_OPTIMIZED table must have at least one index. The total number must not exceed 8. The fields in the index must not be nullable. For the fields n (var) char included in the indices, the BIN2 collation should be used. Clustered indices, by definition, no. In its structure of classic b-tree too. For tables in memory, indexes are HASH (better suited for point searches) and RANGE (as the name implies, better suited for scans by range).
The main component of the hash index is the so-called mapping table, in one column which stores the results of applying the hash function to the concatenation of the fields that make up the index key, and in the other - pointers to records. Since the hash function can produce the same results (collisions arise) for completely different values of the argument (for close ones, they must differ), in reality, these will be pointers to the memory areas where the overflow chains lie. Each such chain is a bidirectional list. The main parameter when creating a hash index is bucket_count. This is the number of slots in the match table. The smaller they are, the higher the probability of a collision, the longer the chains of overflows grow from each hash. Accordingly, it is obvious that it should not be less than the number of unique values in the index key. In fact, it is estimated as the number of unique values, rounded up to the next degree 2, and explains in detail why.
The second type of index, available in-memory, is called range (range) and is very similar to the classic cluster. Its nodes form an ordered structure, effective for scanning across ranges. Instead of B-Tree, its modification Bw-Tree is used , the most striking difference of which, perhaps, is that it does not store pointers to duplicate values. If the table has a million identical values, the classic tree will stupidly keep a million pointers (to data) in the leaves. In this case, Bw costs one, which allows dramatic (English-language authors love this word very much) to save space when storing this economy in memory. The only thing is, in this case, overflow chains arise - we do not store pointers to all records, how to get to the next one with the same key value? There is a feeling that there is no saving, just bytes on ptr were transferred from the leaves to the bouquets. But no, read why this is not so, here , and we will move on and create another table.
CREATE TABLE dbo.UserSession ( SessionId int not null primary key nonclustered hash with (bucket_count=400000), UserId int not null, CreatedDate datetime2 not null, ShoppingCartId int, index ix_UserId nonclustered hash (UserId) with (bucket_count=400000) ) WITH (MEMORY_OPTIMIZED=ON, <b>DURABILITY=SCHEMA_ONLY</b>) Script 3
Note the last option highlighted in bold color. It means that the data of this table will not be saved between server restarts (the structure itself will remain). Above, I said that there is a misconception about Hekaton that everything that is in memory is lost when restarted. So for these tables, this is true, but you create them quite consciously to reduce overhead, in particular, for journaling where it is not needed. It is a kind of temporary table. By the way, table variables in Hecaton are also supported. They are declared through the preliminary creation of the table type CREATE TYPE ... AS TABLE ... Unlike regular table variables, they are stored, of course, not in disk tempdb, but refer to the database where they were declared.
Tables in memory do not support automatic statistics update, in particular, ALTER DATABASE ... SET AUTO_UPDATE_STATISTICS ON. Also does not work
exec sp_autostats @tblname = 'ShoppingCart'
Index Name AUTOSTATS Last Updated
[ix_UserId] OFF NULL
[PK__Shopping__7A789AE57302F83B] OFF NULL
exec sp_autostats @tblname = 'ShoppingCart', @flagc = 'ON'
- For example, renaming, are not supported with memory optimized tables.
However, statistics can be updated manually: UPDATE STATISTICS dbo. ShoppingCart WITH FULLSCAN, NORECOMPUTE.
In general, tables in memory have a cloud of limitations. Most tabular hints are not supported: No TABLOCK, XLOCK, PAGLOCK, ... NOLOCK does not swear, but does not react as if it were not. Dynamic and keyset cursors are silently converted to static. TRUNCATE TABLE and MERGE statements are not supported (when the table in memory serves as an assignment). There are restrictions on the types of fields used. You can read about them in detail here , we, in order not to be upset, let's see what we have done.
SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL' Script 4
In the installation directory of SQL Server C: \ Program Files \ Microsoft SQL Server \ ... \ DATA \ xtp \ 11 \, two dll appeared, which are called xtp_t_11_ <9 digits> .dll. These are our tables ShoppingCart and UserSession.
The built-in compiler converts T-SQL definitions of tables and stored procedures into C code (can be viewed in the same directory), from which the machine is obtained. The corresponding dynamic libraries are loaded into memory and linked into the SQL Server process. When restarting SQL Server, libraries are compiled and reloaded based on catalog information from metadata.
In the next part, we plan to consider the native compilation of stored procedures, isolation levels of transactions in Hekaton, locks, logging, and overall performance compared to traditional disk objects.
Source: https://habr.com/ru/post/225167/
All Articles