Exemplo de in-memory no Banco de Dados SQL do Azure

  • Artigo

Aplica-se a: Banco de Dados SQL do Azure

As tecnologias In-Memory no Banco de Dados SQL do Azure que podem melhorar o desempenho do seu aplicativo e reduzir potencialmente o custo do banco de dados. Ao usar tecnologias In-Memory no Banco de Dados SQL do Azure, obtenha melhorias de desempenho com várias cargas de trabalho.

Neste artigo, duas amostras ilustram o uso do OLTP in-memory, bem como os índices columnstore no Banco de Dados SQL do Azure.

Para saber mais, veja:

Para obter uma demonstração introdutória do OLTP in-memory, consulte:

1. Instalar o exemplo de OLTP Na Memória.

Você pode criar o banco de dados de exemplo AdventureWorksLT em algumas etapas no Portal do Azure. Em seguida, use as etapas nesta seção para adicionar objetos OLTP in-memory ao seu banco de dados AdventureWorksLT e demonstrar os benefícios de desempenho.

Etapas de instalação

  1. No portal do Azure, crie um banco de dados Premium (DTU) ou Comercialmente Crítico (vCore) em um servidor lógico. Defina a Origem como o banco de dados de exemplo AdventureWorksLT. Para obter instruções detalhadas, confira Criar seu primeiro banco de dados no Banco de Dados SQL do Azure.

  2. Conecte-se ao banco de dados com o SQL Server Management Studio (SSMS)

  3. Copie o script Transact-SQL do OLTP Na Memória para a área de transferência. O script T-SQL cria os objetos necessários in-memory no banco de dados de exemplo AdventureWorksLT criado na etapa 1.

  4. Cole o script T-SQL no SSMS e execute o script. A cláusula MEMORY_OPTIMIZED = ON nas declarações CREATE TABLE é crucial. Por exemplo:

    CREATE TABLE [SalesLT].[SalesOrderHeader_inmem](
    [SalesOrderID] int IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ...,
    ...
) WITH (MEMORY_OPTIMIZED = ON);

Erro 40536

Se você receber o erro 40536 quando executar o script T-SQL, execute o seguinte script T-SQL para verificar se o banco de dados oferece suporte a objetos in-memory:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Um resultado 0 significa que não há suporte para OLTP in-memory e 1 significa que há suporte. O OLTP in-memory está disponível nas camadas Banco de Dados SQL Premium (DTU) do Azure e Comercialmente Crítico (vCore).

Sobre os itens criados com otimização de memória

Tabelas: o exemplo contém as seguintes tabelas com otimização de memória:

  • SalesLT.Product_inmem
  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
  • Demo.DemoSalesOrderHeaderSeed
  • Demo.DemoSalesOrderDetailSeed

Você pode filtrar para exibir apenas as tabelas com otimização de memória no Pesquisador de Objetos no SSMS. Ao clicar com o botão direito do mouse em Tabelas, navegue até >Filtro>Configurações de filtro>Com otimização de memória. O valor é igual a 1.

Ou você pode consultar as exibições do catálogo, como:

SELECT is_memory_optimized, name, type_desc, durability_desc
    FROM sys.tables
    WHERE is_memory_optimized = 1;

Procedimento armazenado compilado nativamente: você pode inspecionar SalesLT.usp_InsertSalesOrder_inmem por meio de uma consulta de exibição de catálogo:

SELECT uses_native_compilation, OBJECT_NAME(object_id) AS module_name, definition
    FROM sys.sql_modules
    WHERE uses_native_compilation = 1;

2. Executar a carga de trabalho OLTP

A única diferença entre os dois procedimentos armazenados a seguir é que o primeiro procedimento usa tabelas com otimização de memória, enquanto o segundo procedimento usa as tabelas em disco regulares:

  • SalesLT.usp_InsertSalesOrder_inmem
  • SalesLT.usp_InsertSalesOrder_ondisk

Nesta seção, você verá como usar o utilitário ostress.exe para executar os dois procedimentos armazenados. Você pode comparar quanto tempo as duas execuções demoram para serem concluídas.

Instalar utilitários RML e o ostress

É recomendável executar ostress.exe em uma VM (máquina virtual) do Azure. Você criaria uma VM do Azure na mesma região do Azure em que seu banco de dados AdventureWorksLT está. Se for possível se conectar ao seu banco de dados SQL do Azure, também será possível executar ostress.exe em sua máquina local. Contudo, a latência da rede entre sua máquina e o banco de dados no Azure pode reduzir os benefícios de desempenho do OLTP in-memory.

Na VM ou em qualquer host que você escolher, instale os utilitários RML (Replay Markup Language). Os utilitários incluem o ostress.exe.

Para saber mais, veja:

Script para ostress.exe

Esta seção exibe o script T-SQL, que está inserido em nossa linha de comando do ostress.exe. O script usa itens que foram criados pelo script T-SQL instalado anteriormente.

Quando executar o ostress.exe, recomendamos será passar valores de parâmetro projetados para enfatizar a carga de trabalho usando ambas as seguintes estratégias:

  • Execute um grande número de conexões simultâneas usando -n100.
  • Faça com que cada conexão se repita centenas de vezes usando -r500.

No entanto, talvez você queira começar com valores muito menores, como -n10 e -r50 para garantir que tudo esteja funcionando.

O script a seguir insere um pedido de vendas de exemplo com cinco itens de linha nas seguintes tabelascom otimização de memória:

  • SalesLT.SalesOrderHeader_inmem
  • SalesLT.SalesOrderDetail_inmem
DECLARE
    @i int = 0,
    @od SalesLT.SalesOrderDetailType_inmem,
    @SalesOrderID int,
    @DueDate datetime2 = sysdatetime(),
    @CustomerID int = rand() * 8000,
    @BillToAddressID int = rand() * 10000,
    @ShipToAddressID int = rand() * 10000;

INSERT INTO @od
    SELECT OrderQty, ProductID
    FROM Demo.DemoSalesOrderDetailSeed
    WHERE OrderID= cast((rand()*60) as int);

WHILE (@i < 20)
BEGIN;
    EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT,
        @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od;
    SET @i = @i + 1;
END

Para criar a versão _ondisk do script T-SQL anterior para ostress.exe, substitua as duas ocorrências da substring _inmem por _ondisk. Essas substituições afetam os nomes de tabelas e os procedimentos armazenados.

Executar a carga de trabalho de estresse do _inmem primeiro

Você pode usar uma janela RML Cmd Prompt para executar o ostress.exe. Os parâmetros de linha de comando direcionam o ostress para:

  • Execute 100 conexões simultaneamente (-n100).
  • Faça cada conexão executar o script T-SQL 50 vezes (-r50).
ostress.exe -n100 -r50 -S<servername>.database.windows.net -U<login> -P<password> -d<database> -q -Q"DECLARE @i int = 0, @od SalesLT.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand()* 10000; INSERT INTO @od SELECT OrderQty, ProductID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*60) as int); WHILE (@i < 20) begin; EXECUTE SalesLT.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @od; set @i += 1; end"

Para executar a linha de comando do ostress.exe anterior:

  1. Redefina o conteúdo de dados do banco de dados executando o seguinte comando no SSMS para excluir todos os dados inseridos por todas as execuções anteriores:

    EXECUTE Demo.usp_DemoReset;

  2. Copie o texto da linha de comando anterior do ostress.exe para a área de transferência.

  3. Substitua o <placeholders> para os parâmetros -S -U -P -d pelos valores corretos.

  4. Execute a linha de comando editada em uma janela Cmd RML.

O resultado é uma duração

Quando o ostress.exe é concluído, ele grava a duração da execução como sua linha final de saída na janela Cmd RML. Por exemplo, uma execução de teste mais curta dura aproximadamente 1,5 minuto:

11/12/15 00:35:00.873 [0x000030A8] OSTRESS exiting normally, elapsed time: 00:01:31.867

Redefinir, editar _ondisk e executar novamente

Depois de obter o resultado da execução do _inmem, realize as seguintes etapas para a execução de _ondisk:

  1. Redefina o banco de dados executando o seguinte comando no SSMS para excluir todos os dados inseridos pela execução anterior:

    EXECUTE Demo.usp_DemoReset;

  2. Edite a linha de comando do ostress.exe para substituir todos os _inmem por _ondisk.

  3. Execute novamente o ostress.exe pela segunda vez e capture o resultado da duração.

  4. Redefina o banco de dados mais uma vez.

Resultados esperados para a comparação

Os testes do OLTP in-memory mostraram uma melhoria de desempenho de nove vezes para essa carga de trabalho simplista, com o ostress.exe sendo executado em uma VM do Azure na mesma região do Azure que o banco de dados.

3. Instalar o exemplo de análise in-memory

Nesta seção, você vai comparar os resultados de E/S e de estatísticas ao usar um índice columnstore versus um índice b-tree tradicional.

Para fazer uma análise em tempo real em uma carga de trabalho OLTP, quase sempre será melhor usar um índice columnstore não clusterizado. Para ver mais detalhes, confira Índices Columnstore Descritos.

Preparar o teste de análise de columnstore

  1. Use o portal do Azure para criar um novo banco de dados AdventureWorksLT desde o exemplo. Use qualquer objetivo de serviço que dê suporte a índices columnstore.

  2. Copie o sql_in-memory_analytics_sample para sua área de transferência.

    • O script T-SQL cria os objetos necessários no banco de dados de exemplo AdventureWorksLT criado na etapa 1.
    • O script cria tabelas de dimensões e duas tabelas de fatos. As tabelas de fatos são preenchidas com 3,5 milhões de linhas cada.
    • Em objetivos de serviço menores, o script pode levar 15 minutos ou mais para ser concluído.

  3. Cole o script T-SQL no SSMS e execute o script. A palavra-chave COLUMNSTORE na instrução CREATE INDEX é crucial: CREATE NONCLUSTERED COLUMNSTORE INDEX ...;

  4. Configure AdventureWorksLT no nível de compatibilidade mais recente, o SQL Server 2022 (160): ALTER DATABASE AdventureworksLT SET compatibility_level = 160;

Tabelas chave e índices de columnstore

  • dbo.FactResellerSalesXL_CCI é uma tabela com um índice columnstore clusterizado, que tem compactação avançada no nível de dados.

  • dbo.FactResellerSalesXL_PageCompressed é uma tabela com um índice clusterizado regular equivalente, compactado somente no nível de página.

4. Consultas chave para comparar o índice columnstore

Há diversos tipos de consulta T-SQL que podem ser executados para ver as melhorias de desempenho. Na etapa 2 no script T-SQL, preste atenção neste par de consultas. Elas diferem apenas em uma linha:

  • FROM FactResellerSalesXL_PageCompressed AS a
  • FROM FactResellerSalesXL_CCI AS a

Um índice columnstore clusterizado está na tabela FactResellerSalesXL_CCI.

O script T-SQL a seguir imprime a atividade lógica de E/S e as estatísticas de tempo usando SET STATISTICS IO e SET STATISTICS TIME para cada consulta.

/*********************************************************************
Step 2 -- Overview
-- Page compressed BTree table vs Columnstore table performance differences
-- Enable actual query plan in order to see Plan differences when executing.
*/
-- Ensure the database uses the latest compatibility level
ALTER DATABASE AdventureworksLT SET compatibility_level = 160
GO

-- Execute a typical query that joins the fact table with dimension tables.
-- Note this query will run on the page compressed table. Note down the time.
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_PageCompressed AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO


-- This is the same query on a table with a clustered columnstore index (CCI).
-- The comparison numbers are the more pronounced the larger the table is (this is an 11 million row table).
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT c.Year
    ,e.ProductCategoryKey
    ,FirstName + ' ' + LastName AS FullName
    ,COUNT(SalesOrderNumber) AS NumSales
    ,SUM(SalesAmount) AS TotalSalesAmt
    ,AVG(SalesAmount) AS AvgSalesAmt
    ,COUNT(DISTINCT SalesOrderNumber) AS NumOrders
    ,COUNT(DISTINCT a.CustomerKey) AS CountCustomers
FROM FactResellerSalesXL_CCI AS a
INNER JOIN DimProduct AS b ON b.ProductKey = a.ProductKey
INNER JOIN DimCustomer AS d ON d.CustomerKey = a.CustomerKey
INNER JOIN DimProductSubCategory AS e on e.ProductSubcategoryKey = b.ProductSubcategoryKey
INNER JOIN DimDate AS c ON c.DateKey = a.OrderDateKey
GROUP BY e.ProductCategoryKey,c.Year,d.CustomerKey,d.FirstName,d.LastName
GO

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

Em um banco de dados com o objetivo de serviço P2, você pode esperar um ganho de desempenho de cerca de nove vezes para essa consulta usando o índice columnstore clusterizado em comparação com o índice tradicional. Com o objetivo de serviço P15, você pode esperar cerca de 57 vezes o ganho de desempenho ao usar o índice columnstore.

Conteúdo relacionado