Controlando Tabelas In-Memory OLTP – “Hekaton” com o Resource Governor

Posted on março 20, 2014

1


Introdução

Uma pergunta frequente que respondo é como o SQL Server controla a memória do servidor quando o mesmo possui tabelas In-Memory, realmente não é fácil de abstrair de primeira que tabelas In-Memory são totalmente diferentes de tabelas On-Disk, ambas residem na memória do servidor porém são gerenciadas de formas diferentes.

Tabelas On-Disk são gerenciadas pelo Buffer Pool e Data Cache enquanto tabelas In-Memory são gerenciadas diretamente em memória. De tempos em tempos os arquivos de Chekpoints – Data File e Delta File – https://luanmorenodba.wordpress.com/2013/07/01/in-memory-a-k-a-oltp-hekaton-deep-dives-1/ se encarregam de capturar mudanças realizadas nas tabelas. Para saber mais sobre a estrutura interna do Hekaton – https://luanmorenodba.wordpress.com/?s=hekaton

Uma outra questão muito comum é saber como se controla tabelas In-Memory,  sua alocação de espaço, gerenciamento e crescimento. O “Hekaton” não deve ser colocado em todos os servidores, há melhores práticas, cenários de aplicação e usabilidade, mas falaremos disto depois, aqui irei mostrar como controlar suas tabelas In-Memory para que elas não ocupem toda a memória do servidor podendo assim ocorrer um OOM – Out Of Memory.

Resource Governor

O Resource Governor é um recurso que possibilita o controle de Memória, CPU e no SQL Server 2014 o contole de IOPs. Os grandes benefícios do RG (Resource Governor) são:

    • Controle de Gerenciamento de Cargas de Trabalho (Workloads)
    • Priorização de Carga
    • Prevênção de consultas AD-HOC’s indesejadas
    • Controle de Tabelas In-Memory

Segue Alguns Cenários de Utilização:

  1. Um backup do banco de dados mais utilizado da empresa é executado diariamente as 14:00 Hs e o mesmo faz com que o servidor de banco de dados consuma 80% de CPU e que utilize quase toda memória. Utilizando o RG você pode limitar a quantidade de memória e CPU que esse mesmo backup será executado ou seja o mesmo será realizado porém não fará com que seu o servidor de banco de dados seja afetado.
  2. Usuários que realizam consultas que fazem com que o servidor de banco de dados fique com pressão na memória e faça com que os sistemas fiquem lentos e com alta taxa de espera. Utilizando o RG você pode vincular um grupo de usuários que podem entrar em um Workload diferente para consumir por exemplo 15% de CPU e 500MB de RAM, as consultas serão executadas porém não irá carregar seu ambiente.
  3. Dentro do seu ambiente há tabelas In-Memory “Hekaton” e com isso se um RG não for configurado assim como o MIN Server Memory e MAX Server Memory pode fazer com que tabelas In-Memory consumam toda sua memória causando um OOM – Out of Memory. Utilizando O RG, você consegue ter um maior controle dos bancos e dados das tabelas In-Memory sem causar problemas de contenção no seu servidor de banco de dados.

 

In-Memory + Resource Governor

Sendo assim podemos utilizar o Resource Governor no SQL Server 2014 em conjunto com o Hekaton. O que irei mostrar é como podemos nos beneficiar desse recurso para controlarmos as tabelas que estão In-Memory em diversos bancos de dados criando assim um POOL aonde as mesmas serão controladas.O SQL Server utiliza o Resource Pool Padrão que são todos os recursos disponíveis e para sua proteção referente as Optimized Tables o recomendado é que criemos um Resource Pool separado para gerenciar o consumo dos banco de dados que possuem tabelas Optimized Tables.

Criando um Banco de Dados In-Memory – Criação de um FileGroup especial para a criação de tabelas In-Memory

USE [master]

GO

 

 

CREATE DATABASE [HktDB]

ON PRIMARY

(

       NAME = N’HktDB’,

       FILENAME = N’C:\BaseDados\inmemorydb\HktDB.mdf’

),

FILEGROUP [InMemoryDB] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT

(

       NAME = N’HktDBDados’,

       FILENAME = N’C:\BaseDados\inmemorydb\HktDB’ ,

       MAXSIZE = UNLIMITED

)

 LOG ON

(

       NAME = N’HktDB_log’,

       FILENAME = N’C:\BaseDados\inmemorydb\HktDB_log.ldf’ ,

       FILEGROWTH = 10%

)

GO

Criando uma tabela no Banco de Dados

USE HktDB

go

 

CREATE TABLE inmem_VendasProdutos

(

       [IDSEQUENCE] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),

       [SalesOrderID] [int] NOT NULL INDEX idxNCL_inmem_VendasProdutos_SalesOrderID NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),

       [CarrierTrackingNumber] [nvarchar](25) NULL,

       [OrderQty] [smallint] NOT NULL,

       [ProductID] [int] NOT NULL,

       [Name] [nvarchar](50) NOT NULL,

       [ProductNumber] [nvarchar](25) NOT NULL,

       [Color] [nvarchar](15) NULL,

       [RowGUIDProduct] [uniqueidentifier] NOT NULL,

       [UnitPrice] [money] NOT NULL,

       [UnitPriceDiscount] [money] NOT NULL,

       [LineTotal] [numeric](38, 6) NOT NULL,

       [RowGUIDOrderDetail] [uniqueidentifier] NOT NULL,

       [ModifiedDate] [datetime] NOT NULL INDEX idxNCL_inmem_VendasProdutos_ModifiedDate NONCLUSTERED

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

Para encotrarmos todas as tabelas que estão em memória em um banco de dados podemos consultar…

 

SELECT name, object_id, is_memory_optimized, durability, durability_desc

FROM sys.tables

WHERE type = ‘u’

       AND name = ‘inmem_VendasProdutos’

image

(Figura 1 – Script para busca de tabelas In-Memory dentro de um banco de dados.)

image

(Figura 2 – Tabela In-Memory.)

No banco de dados há um relatório chamado – Memory Usage By Memory Optimized Objects aonde conseguimos medir de forma rápida e fácil o crescimento das tabelas

image

(Figura 3 – Tabela criada sem inserção de dados.)

Agora iremos realizar a inserção de registros dentro da tabela e após isso verificar o relatório novamente.

–Insert – inmem_VendasProdutos

WITH DadosInsert AS

(

SELECT ROW_NUMBER() OVER(ORDER BY CarrierTrackingNumber) AS ID, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,

          RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate

FROM dbo.ondsk_VendasProdutos

–121.317

)

INSERT INTO inmem_VendasProdutos (IDSEQUENCE, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,

                                                         RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate)

SELECT *

FROM DadosInsert

WHERE ID BETWEEN 1 AND 10000

 

 

image

(Figura 4 – Inserção de 10 Mil registros inseridos na tabela, verificando as alterações no relatório, agora temos o Table Used Memory  = 2.09 MB.)

Saimos de um tabela de 16.25 MB para 21.29 MB. Com isso se pararmos para pensar as tabelas irão consumir memória até não possuir mais disponível, sabendo disso iremos agora criar um Pool particular para Tabelas In-Memory

Criando o Resource Pool In-Memory

CREATE RESOURCE POOL PoolHekaton

   WITH

        (

                                  MIN_MEMORY_PERCENT = 3,

          MAX_MEMORY_PERCENT = 3

                                );

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

SELECT pool_id

     , Name

     , min_memory_percent

     , max_memory_percent

     , max_memory_kb/1024 AS max_memory_mb

     , used_memory_kb/1024 AS used_memory_mb

     , target_memory_kb/1024 AS target_memory_mb

FROM sys.dm_resource_governor_resource_pools

 

image

(Figura 5 – PoolHekaton – Consumindo um Total de 81MB da Memória Total configurada no SQL Server.)

Na criação do Resource Pool foi configurado o MIN_MEMORY_PERCENT = 3 e o MAX_MEMORY_PERCENT = 3, porém somente para exemplo, no BOL (Books Online) temos um cálculo para mensurar o tamanho do POOL das Tabelas In-Memory – http://msdn.microsoft.com/en-us/library/dn465873(v=sql.120).aspx 

Vemos que para o meu Resource Pool possuímos um total de 81 MB disponíveis para as tabelas In-Memory. Após a configuração do Resource Pool nosso o próximo passo a ser realizado é realizar a configuração do BIND Database. O BIND nada mais é do que criar o vínculo do Banco de Dados In-Memory com o Resource Pool criado para o Hekaton. O lado positivo desse recurso é que para Optimizes Tables podemos possuir diversos bancos de dados em BIND fazendo com que todas as tabelas de todos os bancos compartilhem o mesmo Resource Pool. Para que a aplicação seja efetivada é necessário colocar o banco de dados OFFLINE e depois ONLINE

EXEC sp_xtp_bind_db_resource_pool ‘HktDB’, ‘PoolHekaton’

GO

USE master

GO

 

ALTER DATABASE HktDB SET OFFLINE

GO

ALTER DATABASE HktDB SET ONLINE

GO

 

USE HktDB

GO

 

SELECT d.database_id, d.name, d.resource_pool_id

FROM sys.databases d

WHERE resource_pool_id IS NOT NULL

GO

image

(Figura 6 – Banco de Dados vínculado ao Resource Pool de Nome – PoolHekaton)

Como o Resource Pool está vinculado com o Banco de Dados – HkTDB agora não precisamos nos preocupar com problemas de OOM – Out Of Memory, que são problemas referentes a falta de memória física no servidor, por isso que o RG para Banco de Dados In-Memory é importante, porque irá realizar a prevenção, por isso se inserirmos uma quantidade de registros maior do que a quantidade do que o Pool aceita teremos o seguinte resultado.

;WITH DadosInsert AS

(

SELECT ROW_NUMBER() OVER(ORDER BY CarrierTrackingNumber) AS ID, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,

                   RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate

FROM dbo.ondsk_VendasProdutos

–121.317

)

INSERT INTO inmem_VendasProdutos (IDSEQUENCE, SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, Name, ProductNumber , Color,

                                                                                                                                  RowGUIDProduct, UnitPrice, UnitPriceDiscount, LineTotal, RowGUIDOrderDetail, ModifiedDate)

SELECT *

FROM DadosInsert

WHERE ID BETWEEN 30001 AND 60000

 

The statement has been terminated.
Msg 701, Level 17, State 103, Line 175
There is insufficient system memory in resource pool ‘PoolHekaton’ to run this query.

Verificando o Tamanho Disponível do Pool do Hekaton

SELECT pool_id

     , Name

     , min_memory_percent

     , max_memory_percent

     , max_memory_kb/1024 AS max_memory_mb

     , used_memory_kb/1024 AS used_memory_mb

     , target_memory_kb/1024 AS target_memory_mb

FROM sys.dm_resource_governor_resource_pools

image

(Figura 7 – Quantidade de USED_Memory_MB está no limite fazendo com que a transação de inserção não fosse concluída.)

O Resource Governor controlou a quantidade de memória, se desejar alterar esse valor, podemos alterar o Pool instantaneamente.

ALTER RESOURCE POOL PoolHekaton

WITH

     (

                   MIN_MEMORY_PERCENT = 80,

       MAX_MEMORY_PERCENT = 100

                 )

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

image

(Figura 8 – A alteração do RG fez com que a quantidade de TARGET_MEMORY_MB fosse para 2.6 GB aumentando assim o Pool.)

Conclusões

 

 

É de extrema importância que para todos os cenários aonde serão utilizados o Hekaton seja configurado o Resource Governor de acordo com as configurações do Servidor isso evitará diversos tipos de problemas. No próximo post irei mostar os problemas de OOM – Out of Memory com o Hekaton.