В первой части мы кратко рассмотрели основные возможности SQL Server по in-memory обработке применительно к аналитическим и транзакционным и приложениям, сконцентрировавшись на последних, поскольку in-memory OLTP (Hekaton) является наиболее существенным нововведением в SQL Server 2014. В данной статье мы продолжим обзор функциональности Гекатона на примере ранее созданной БД.

Коль скоро мы говорим о транзакционной обработке в памяти, нелишним будет вспомнить акроним ACID: атомарность, целостность, изолированность и остаточность (эффекта), конкретно — литеру I. Поскольку добиться полной изоляции накладно, в СУБД поддерживаются уровни изоляции, допускающие ту или иную степень компромисса. Это классика жанра. По мере приближения к идеалу в SQL Server поддерживаются: грязное чтение (допускаются нарушения вида: первая транзакция изменяет данные, вторая читает изменения, первая откатывается, получается, что вторая прочла несуществующее), read committed (возможно неповторяющееся чтение: первая транзакция может изменить данные в масштабе чтения второй, и во второй раз вторая транзакция прочтет уже измененные данные), repeatable read (первая транзакция не может изменять записи в масштабе второй, но может вставлять новые — фантомы), serializable (самый строгий – фантомы не проходят). Когда-то давно, когда страницы в SQL Server были 2-килобайтные, в нем поддерживались только страничные блокировки, поэтому два последних уровня были синонимы. Полноценная поддержка блокировки записи появилась в 7.0 (1998). В 2005-м к перечисленным уровням добавился snapshot Isolation, что, строго говоря, уровнем изоляции не является, а превращает SQL Server из блокировочника в версионник. Как раз snapshot является основным для Гекатона, потому что optimistic concurrency выступает наиболее естественным подходом при работе с объектами в памяти. Имеется в виду — по своей сути, т.к. версии записей в данном случае не хранятся в tempdb. Каждая операция записи имеет непрерывно возрастающий номер транзакции, использующийся для последующих чтений. Незафиксированные записи хранятся в памяти, но до коммита не видны, поэтому грязных чтений не бывает. Старые записи удаляются в процессе сборки мусора и освобождают память.
Также в Гекатоне поддерживается Repeatable Read, которое (в отличие от дискового варианта) никого не блокирует. Когда где-нибудь в конце транзакции происходит повторное чтение, то, если записи изменились, транзакция отменяется с ошибкой 41305 “The current transaction failed to commit due to a repeatable read validation failure on table [name]”. Аналогично работает Serializable — в случае возникновения фантомов транзакция отменяется.
Уровень изоляции задается в блоке ATOMIC нативно скомпилированной хранимой процедуры (посмотрим ниже) или в табличном хинте Т-SQL запроса. Как вариант — с помощью датабазной опции MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, которая при обращении к таблицам в памяти поднимает Read UnCommitted/ Committed до Snapshot. Если привычно написать

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
  UPDATE dbo.UserSession SET ShoppingCartId=3 WHERE SessionId=4

будет ошибка Msg 41333, Level 16, State 1, Line 3
The following transactions must access memory optimized tables and natively compiled stored procedures under snapshot isolation: RepeatableRead transactions, Serializable transactions, and transactions that access tables that are not memory optimized in RepeatableRead or Serializable isolation.

В Т-SQL скрипте уровень изоляции задается хинтом:

BEGIN TRAN 
  UPDATE dbo.UserSession WITH (SNAPSHOT) SET ShoppingCartId=3 WHERE SessionId=4 
  UPDATE dbo.ShoppingCart WITH (SNAPSHOT) SET TotalPrice=100.00 WHERE ShoppingCartId=3 
COMMIT 
(1 row(s) affected)
(1 row(s) affected)

Теоретически Read Committed для memory optimized таблиц тоже поддерживается, но только в случае autocommit (single statement) транзакций.
Затраты на блокировки при этом минимальны, ради чего, собственно, все и затевалось.

BEGIN TRAN
INSERT dbo.ShoppingCart VALUES (5,500,GETUTCDATE(),50.5) 
SELECT resource_type, resource_description, object_name(resource_associated_entity_id), request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID

Обратите внимание, что Гекатон «вешает» блокировку только Schema Stability на таблицу. Никаких Х и IX нет в помине:

Рис.1

Давайте для сравнения вспомним, что происходит в случае традиционных таблиц. Создадим аналогичную дисковую таблицу, вставим тот же набор строк в качестве предварительного наполнения и повторим транзакцию:

CREATE TABLE dbo.ShoppingCart_Disk ( 
   ShoppingCartId int not null 
primary key, 
   UserId int not null, 
   CreatedDate datetime2 not null, 
   TotalPrice money,
   INDEX ixUserid nonclustered (UserId)) 
...
BEGIN TRAN
INSERT dbo.ShoppingCart_Disk VALUES (5,500,GETUTCDATE(),50.5) 
SELECT resource_type, resource_description, case resource_type when 'object' then object_name(resource_associated_entity_id) else cast(resource_associated_entity_id as sysname) end, resource_associated_entity_id, request_mode, request_type, request_status FROM sys.dm_tran_locks WHERE request_session_id = @@SPID

Что называется, почувствуйте разницу:

Рис.2

Минимальные блокировки — одно из явных преимуществ Гекатона. Приложение выиграет от переноса в память, если оно выполнено в стиле OLTP, т.е. характеризуется относительно короткими транзакциями с высоким процентом конкуренции от многочисленных соединений, и проблема состоит в том, что подолгу висят блокировки или физические латчи и не отпускают ресурсы. Портация будет проще, если используются хранимые процедуры, и проблемы с производительностью удается выделить в относительно небольшое подмножество таблиц/ хранимых процедур. Разумеется, не все сценарии выигрывают. Предельный случай — одна таблица из одной записи, которую все обновляют новым значением. Стремление во что бы то ни стало запихнуть все и вся в память приведет к тому, что она тупо кончится. Кстати, довольно часто задают вопрос, что будет, если Гекатон сожрет всю выделенную ему память? В общем, понятно, что ничего хорошего: Msg 701, Level 17, State 103. There is insufficient system memory in resource pool 'default' to run this query. Что в этом случае делать — как правило, ничего. SQL Server довольно шустро чистит память, и на релизе мне его еще не удалось загнать в ступор, который лечится только рестартом (в отличие от СТР). Сборка мусора спроектирована как неблокирующая и кооперативная, что означает, что несмотря на специальный поток для этих целей, бОльшую часть работы выполняют пользовательские процессы, которые время от времени проверяют и помечают записи для последующего удаления, а затем и удаляют. Специализированный поток включается, когда пользовательская активность совсем мала
Как избежать переполнения в процессе работы? Очевидно, правильно оценивать действительность при переносе таблиц в память, тем более, что sp_spaceused никто не отменял.
Можно использовать Memory Optimization Advisor, который проводит статический анализ схемы, индексов и пр. на предмет потенциальных проблем, и более интеллектуальный AMR (Analysis, Migrate and Report) для динамического анализа по характеру использования (берет статистику из
Performance Data Warehouse). Можно превентивно ограничить Гекатон с помощью регулятора ресурсов:

CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = сколько не жалко)
EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool‘

Влияние Resource Governor относится только к памяти, выделяемой In-Memory OLTP аллокаторами, т.е. при отсутствии в БД объектов, оптимизированных для памяти, из пула ресурсов память не изымается. In-memory OLTP engine интегрирован в общий SQL Server Memory Manager, в чем вы можете убедиться, запустив

select type, name, memory_node_id, pages_kb/1024 as pages_MB from sys.dm_os_memory_clerks where type like '%xtp%‘

Первый товарищ будет память, аллоцированная системой, последний (memory_node=64) – выделенное административное соединение, и между ними — пользовательские БД с MEMORY_OPTIMIZED_DATA.

Как отмечалось в первой части, совершенно необязательно засовывать целиком всю базу в память. Возможны комбинированные запросы между таблицами в памяти и привычными таблицами, например,

select m1.ShoppingCartId, m2.UserId, d.CreatedDate, d.TotalPrice from ShoppingCart m1 
join ShoppingCart_Disk d on m1.ShoppingCartId = d.ShoppingCartId 
join UserSession m2 on d.ShoppingCartId = m2.ShoppingCartId

delete from ShoppingCart
insert ShoppingCart select * from ShoppingCart_Disk

update d set TotalPrice = m.TotalPrice from ShoppingCart_Disk d join ShoppingCart m on d.ShoppingCartId = m.ShoppingCartId where m.UserID <= 100

Скрипт 1

Кроме таблиц, в памяти могут размещаться хранимые процедуры. Такие процедуры могут работать только с in-memory таблицами. Процедура обязательно создается с опцией SCHEMABINDING, что означает, что таблицы, на которые ссылается процедура, не могу быть модифицированы или удалены прежде нее. Традиционные процедуры по умолчанию выполняются с опцией EXECUTE AS CALLER. Для процедур в памяти она не поддерживается. Возможными вариантами являются: EXECUTE AS OWNER, EXECUTE AS 'user_name‘ или EXECUTE AS SELF (создатель). Существуют и другие ограничения – см.BOL, «Скомпилированные в собственном коде хранимые процедуры».
Тело процедуры заключается в блок BEGIN ATOMIC из стандарта ANSI SQL, и на данный момент это его единственное применение в T-SQL. Он отличается от BEGIN TRAN тем, что автоматически откатывается при ошибке в то время, как в транзакции необходимо использовать TRY / CATCH и ROLLBACK, т.к. по поводу SET XACT_ABORT ON имеются нюансы. Опциями конструкции служат упоминавшийся выше TRANSACTION ISOLATION LEVEL = SNAPSHOT | REPEATABLEREAD | SERIALIZABLE и LANGUAGE, в качестве которого можно использовать любой язык из sys.languages. Он определяет формат date|time и язык системных сообщений.

CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCount int 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS 
BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
  IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart
  DECLARE @i int = 0
  WHILE @i < @InsertCount 
  BEGIN 
    INSERT INTO dbo.ShoppingCart VALUES (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) 
    SET @i += 1 
  END
END

Скрипт 2

Как и в случае таблиц, для процедур происходит нативная компиляция, которая превращает интерпретируемые команды T-SQL в код на С и далее — в машинный код, поэтому если мы сейчас повторим запрос Скрипт 4 из предыдущей части, то увидим, что добавилась 3-я dll, соответствующая свежесозданной процедуре.

Ключевым моментом использования объектов в памяти выступает производительность. Для сравнения создадим аналогичную хранимую процедуру традиционного способа хранения, которая ровно так же будет вставлять заданное число записей, но уже в дисковую таблицу:

CREATE PROCEDURE dbo.usp_InsertSampleCarts_Disk @StartId int = null, @InsertCount int 
AS BEGIN  
  IF @StartId is null SELECT @StartId = isnull(MAX(ShoppingCartId), 0) + 1 FROM dbo.ShoppingCart
  DECLARE @i int = 0
  WHILE @i < @InsertCount 
  BEGIN 
    INSERT INTO dbo.ShoppingCart_Disk VALUES 
         (@StartId + @i, RAND() * 1000, GETUTCDATE(), NULL) 
    SET @i += 1 
  END
END

Скрипт 3

И вставим туда и туда по миллиону записей:

SET NOCOUNT ON; DBCC FREEPROCCACHE WITH NO_INFOMSGS; DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

exec dbo.usp_InsertSampleCarts null, 1000000
exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 1000000

Как говорится, разница налицо:

Рис.3

Из особенностей следует отметить, что SQL Server 2014 не поддерживает автоматическую перекомпиляцию нативных хранимых процедур. План фиксирован. Parameter sniffing не используется, все параметры полагаются unknown. Вместо рекомпиляции, возможно, придется удалить и заново создать процедуру при изменении данных (для генерации нового плана). Возможно, хватит рекомпиляции, которая происходит при старте сервера, переезде на резервный узел и т.д. Понятно, что чем свежее статистика по таблицам, тем корректнее будет построен план, а статистика, как мы помним из первой части, автоматически не обновляется. Необходимо делать вручную с помощью UPDATE STATISTICS или sp_updatestats. Последняя всегда обновляет статистику для оптимизированных по памяти таблиц (для обычных — по мере надобности).

Из наглядных вещей давайте посмотрим еще разницу в журналировании. В классическом варианте, если мы пишем миллион записей в таблицу с некластерным индексом (и обновления задевают какую-нибудь колонку этого индекса), мы получим два миллиона записей в журнал, что, естественно, сказывается на производительности. К тому же (write-ahead logging), все это помещается в буфер лога немедленно и в нагруженных системах приводит к высокой конкуренции за буфер. В случае Гекатона незафиксированные транзакции вообще не сбрасываются на диск, поэтому не нужно хранить undo-информацию или агрессивно вставлять в буфер лога. Операции над индексами не журналируются — они не хранятся между рестартами. Формируется только одна консолидированная запись журнала при фиксации, которая содержит необходимую информацию о всех деталях изменений, для всех записей, затронутых транзакцией. Смотрим.

declare @lsn nvarchar(46) = (select max([Current LSN]) from sys.fn_dblog(null, null)) 
exec dbo.usp_InsertSampleCarts_Disk @InsertCount = 100
select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc 
select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn

set @lsn = (select max([Current LSN]) from sys.fn_dblog(null, null)) 
exec dbo.usp_InsertSampleCarts null, 100
select * from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn order by [Current LSN] desc 
select count(1), sum([Log Record Length]) from sys.fn_dblog(null, NULL) where [Current LSN] > @lsn

Рис.4

И видим, что вставка 100 записей в случае in-memory таблицы журналирована в виде всего двух записей в журнал типа LOP_HK (Log Operation Hekaton). Как уже отмечалось, эти записи консолидированные. Посмотреть, во что они на самом деле раскрываются, можно с помощью новой недокументированной функции, которую я позаимствовал у Kalen Delaney. В качестве Current LSN ставим значения из соответствующей колонки третьего резалтсета на Рис.4:

SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName 
FROM sys.fn_dblog_xtp(null, null) 
WHERE [Current LSN] = '00000027:000001dd:0002'

SELECT [current lsn], [transaction id], operation, operation_desc, tx_end_timestamp, total_size, object_name(table_id) AS TableName 
FROM sys.fn_dblog_xtp(null, null) 
WHERE [Current LSN] = '00000027:000001dd:0001'

Рис.5

Первая запись, как мы видим, состоит из 102-х записей: begin tx, 100 вставок, commit. Несмотря на это их суммарный объем (см. второй и четвертый результат Рис.4) в >10 раз компактней, чем в случае дисковой таблицы. Если взять non-durable table (DURABILITY=SCHEMA_ONLY), журналирования вообще не будет.

In-Memory OLTP в SQL Server 2014. Часть II