Compartilhar via

Memory-Optimized Variáveis de Tabela

Além de tabelas com otimização de memória (para acesso eficiente a dados) e procedimentos armazenados compilados nativamente (para processamento de consulta eficiente e execução de lógica de negócios) In-Memory OLTP introduz um terceiro tipo de objeto: o tipo de tabela com otimização de memória. Uma variável de tabela criada usando um tipo de tabela com otimização de memória é uma variável de tabela com otimização de memória.

As variáveis de tabela com otimização de memória oferecem as seguintes vantagens quando comparadas com variáveis de tabela baseadas em disco:

  • As variáveis são armazenadas apenas na memória. O acesso a dados é mais eficiente porque o tipo de tabela com otimização de memória usa o mesmo algoritmo com otimização de memória e estruturas de dados usadas para tabelas com otimização de memória, especialmente quando as variáveis são usadas em procedimentos armazenados compilados nativamente.

  • Com variáveis de tabela com otimização de memória, não há utilização de tempdb. As variáveis de tabela não são armazenadas no tempdb e não usam nenhum recurso no tempdb.

Os cenários de uso típicos para variáveis de tabela com otimização de memória são:

  • Armazenando resultados intermediários e criando conjuntos de resultados únicos com base em várias consultas em procedimentos armazenados compilados nativamente.

  • Passando parâmetros do tipo tabela para procedimentos de armazenamento compilados nativamente e procedimentos de armazenamento interpretados.

  • Substituir variáveis de tabela baseadas em disco e, em alguns casos, tabelas temporárias que são locais de um procedimento armazenado. Isso é particularmente útil se houver muita contenção de tempdb no sistema.

  • As variáveis de tabela podem ser usadas para simular cursores em procedimentos armazenados compilados nativamente, o que pode ajudá-lo a contornar as limitações da área de superfície em procedimentos armazenados compilados nativamente.

Assim como tabelas com otimização de memória, o SQL Server gera uma DLL para cada tipo de tabela com otimização de memória. (A compilação é invocada quando o tipo de tabela com otimização de memória é criado e não quando usado para criar variáveis de tabela com otimização de memória.) Essa DLL inclui as funções para acessar índices e recuperar dados das variáveis de tabela. Quando uma variável de tabela com otimização de memória é declarada com base no tipo de tabela, uma instância da tabela e das estruturas de índice correspondentes ao tipo de tabela são criadas na sessão do usuário. Em seguida, a variável de tabela pode ser usada da mesma forma que as variáveis de tabela baseadas em disco. Você pode inserir, atualizar e excluir linhas na variável de tabela e pode usar as variáveis em consultas Transact-SQL. Você também pode passar as variáveis para procedimentos armazenados compilados e interpretados nativamente, como TVP (parâmetros com valor de tabela).

O exemplo a seguir mostra um tipo de tabela com otimização de memória da amostra OLTP In-Memory baseada em AdventureWorks (amostra OLTP do SQL Server 2014 In-Memory).

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 );

O exemplo mostra que a sintaxe de tipos de tabela com otimização de memória é semelhante aos tipos de tabela baseados em disco, com as seguintes exceções:

  • MEMORY_OPTIMIZED=ON indica que o tipo de tabela é otimizado para memória.

  • O tipo deve ter pelo menos um índice. Assim como acontece com tabelas com otimização de memória, você pode usar índices hash e não clusterizados.

    Para um índice de hash, a quantidade de buckets deve ser cerca de uma a duas vezes o número esperado de chaves de índice exclusivas. Para obter mais informações, consulte Determining the Correct Bucket Count for Hash Indexes.

  • O tipo de dados e as restrições em tabelas otimizadas para memória também se aplicam a tipos de tabela otimizados para memória. Por exemplo, no SQL Server 2014, as restrições padrão são suportadas, mas as restrições de verificação não são.

Como tabelas com otimização de memória, variáveis de tabela com otimização de memória,

  • Não dê suporte a planos paralelos.

  • Deve caber na memória e não usar recursos de disco.

As variáveis de tabela baseadas em disco existem no tempdb. As variáveis de tabela com otimização de memória existem no banco de dados do usuário (mas elas não consomem armazenamento e não são recuperadas).

Você não pode criar uma variável de tabela com otimização de memória usando a sintaxe em linha. Ao contrário das variáveis de tabela baseadas em disco, você deve criar um tipo primeiro.

Parâmetros com valor de tabela

O script de exemplo a seguir mostra a declaração de uma variável de tabela como o tipo Sales.SalesOrderDetailType_inmemde tabela com otimização de memória, a inserção de três linhas na variável e a passagem da variável como um TVP para 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

Os tipos de tabela com otimização de memória podem ser usados como tipo para parâmetros com valor de tabela (TVPs) de procedimentos armazenados e podem ser referenciados por clientes da mesma forma que os tipos de tabela e TVPs baseados em disco. Portanto, a invocação de procedimentos armazenados com TVPs com otimização de memória e procedimentos armazenados compilados nativamente funciona exatamente da mesma forma que a invocação de procedimentos armazenados interpretados com TVPs baseados em disco.

Substituição da tabela temporária #temp

O exemplo a seguir mostra tipos de tabelas otimizadas para memória e variáveis de tabela como uma substituição para as tabelas temporárias #temp que são locais de um procedimento armazenado.

-- 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

Criando um único conjunto de resultados

O exemplo a seguir mostra como armazenar resultados intermediários e criar conjuntos de resultados únicos com base em várias consultas em procedimentos armazenados compilados nativamente. O exemplo está computando a união 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

Consumo de memória para variáveis de tabela

O consumo de memória para variáveis de tabela é semelhante a tabelas com otimização de memória, com exceção de índices não clusterizados. Se você inserir muitas linhas em variáveis de tabela com otimização de memória com índices não clusterizados e se as chaves de índice forem grandes, essas variáveis de tabela usarão uma quantidade desproporcional de memória. Índices não clusterizados em variáveis de tabela grandes exigem proporcionalmente mais memória do que um índice não clusterizado exigiria para o mesmo número de linhas inseridas em uma tabela (mais memória nas páginas de índice).

A memória para variáveis de tabela vem do pool de recursos do Administrador de Recursos do banco de dados.

Ao contrário das tabelas com otimização de memória, a memória consumida (incluindo linhas excluídas) por variáveis de tabela é liberada quando a variável de tabela sai do escopo.

A memória é contabilizada como parte do único consumidor de memória PGPOOL do banco de dados.

Consulte Também

SuporteTransact-SQL para o OLTP In-Memory

  • Last updated on