Поделиться через

переменные таблицы Memory-Optimized

Помимо таблиц, оптимизированных для памяти (для эффективного доступа к данным) и скомпилированных в собственном коде хранимых процедур (для эффективной обработки запросов и бизнес-логики) In-Memory OLTP представляет третий тип объекта: тип таблицы, оптимизированный для памяти. Переменная таблицы, созданная с помощью типа таблицы, оптимизированной для памяти, — это оптимизированная для памяти переменная таблицы.

Переменные таблицы, оптимизированные для памяти, обеспечивают следующие преимущества по сравнению с переменными таблицы на основе диска:

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

  • При использовании оптимизированных для памяти табличных переменных не используется tempdb. Переменные таблицы не хранятся в tempdb и не используют ресурсы в tempdb.

Типичными сценариями использования для переменных таблицы, оптимизированными для памяти, являются:

  • Хранение промежуточных результатов и создание единых наборов результатов на основе нескольких запросов в нативно скомпилированных хранимых процедурах.

  • Передача параметров табличного типа в нативно скомпилированные хранимые процедуры и интерпретируемые хранимые процедуры.

  • Замена переменных таблиц, хранящихся на диске, и в некоторых случаях временных таблиц (#temp), которые используются локально в хранимой процедуре. Это особенно полезно, если в системе возникает много конкуренции tempdb.

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

Как и оптимизированные для памяти таблицы, SQL Server создает библиотеку DLL для каждого типа таблицы, оптимизированной для памяти. (Компиляция вызывается при создании оптимизированного для памяти типа таблицы, а не при использовании для создания переменных таблицы, оптимизированных для памяти.) Эта библиотека DLL включает функции для доступа к индексам и получения данных из переменных таблицы. Когда переменная таблицы, оптимизированная для памяти, объявлена на основе типа таблицы, экземпляр структуры таблиц и индексов, соответствующих типу таблицы, создается в сеансе пользователя. Затем переменная таблицы может использоваться так же, как и переменные таблицы на основе диска. Вы можете вставлять, обновлять и удалять строки в переменной таблицы, а также использовать переменные в Transact-SQL запросах. Вы также можете передавать переменные в нативно компилируемые и интерпретируемые хранимые процедуры в виде табличного параметра (TVP).

В следующем примере показан тип таблицы, оптимизированной для памяти, из примера In-Memory OLTP на основе AdventureWorks (пример SQL Server 2014 In-Memory OLTP).

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

В примере показано, что синтаксис оптимизированных для памяти типов таблиц аналогичен типам таблиц на основе диска, причем следующие исключения:

  • MEMORY_OPTIMIZED=ON указывает, что тип таблицы оптимизирован для памяти.

  • Тип должен иметь по крайней мере один индекс. Как и в таблицах, оптимизированных для памяти, можно использовать хэш-и некластеризованные индексы.

    Для хэш-индекса число сегментов должно быть примерно в два раза больше, чем число ожидаемых уникальных ключей индекса. Дополнительные сведения см. в разделе "Определение правильного количества сегментов для хэш-индексов".

  • Ограничения типа данных и ограничений для оптимизированных для памяти таблиц также применяются к типам таблиц, оптимизированных для памяти. Например, в SQL Server 2014 поддерживаются ограничения по умолчанию, но ограничения на проверку не поддерживаются.

Как и таблицы, оптимизированные для памяти, переменные таблицы, оптимизированные для памяти,

  • Не поддерживать параллельные планы.

  • Должен помещаться в памяти и не использовать ресурсы диска.

Табличные переменные на основе дисков существуют в tempdb. Переменные таблицы, оптимизированные для памяти, существуют в пользовательской базе данных (но не используют хранилище и не восстанавливаются).

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

параметры Table-Valued

В следующем примере скрипта показано объявление табличной переменной в виде типа Sales.SalesOrderDetailType_inmemтаблицы, оптимизированного для памяти, вставки трех строк в переменную и передачи переменной в виде TVP Sales.usp_InsertSalesOrder_inmem.

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

Типы таблиц, оптимизированные для памяти, можно использовать как тип для параметров табличного значения хранимой процедуры (TVPs), и клиенты могут использовать их точно так же, как типы таблиц на основе дисков и TVPs. Таким образом, вызов хранимых процедур с оптимизированными для памяти телевизорами и скомпилированные хранимые процедуры работают точно так же, как вызов интерпретированных хранимых процедур с помощью дисковых телевизоров.

Замена таблицы #temp

В следующем примере показаны оптимизированные для памяти типы таблиц и переменные таблицы в качестве замены для таблиц #temp, которые являются локальными для хранимой процедуры.

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

Создание одного результирующего набора

В следующем примере показано, как хранить промежуточные результаты и создавать отдельные результирующие наборы на основе нескольких запросов в скомпилированных хранимых процедурах. Пример вычисляет объединение SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2.

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

Потребление памяти для переменных таблицы

Потребление памяти для табличных переменных аналогично таблицам, оптимизированным для памяти, за исключением некластеризованных индексов. Если вставить много строк в переменные таблицы, оптимизированные для памяти, с некластеризованными индексами и если ключи индекса большие, эти переменные таблицы будут использовать непропорциональное количество памяти. Некластеризованные индексы для больших табличных переменных требуют пропорционально больше памяти, чем потребовалось бы для некластеризованного индекса при вставке того же количества строк в обычную таблицу (из-за большего объема памяти, необходимого для страниц индекса).

Память для переменных таблицы поступает из пула ресурсов регулятора ресурсов базы данных.

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

Память учитывается как часть одного потребителя памяти PGPOOL базы данных.

См. также

Поддержка Transact-SQL для выполняющейся в памяти OLTP