Exemplo de In-Memory na Instância Gerenciada de SQL do Azure

  • Artigo

Aplica-se a:Instância Gerenciada de SQL do Azure

As tecnologias In-Memory na Instância Gerenciada de 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 na Instância Gerenciada de SQL do Azure, obtenha melhorias de desempenho com várias cargas de trabalho.

Neste artigo, você verá dois exemplos que ilustram o uso do OLTP In-Memory, bem como dos índices columnstore, na Instância Gerenciadas de SQL do Azure.

Para saber mais, veja:

Para ver uma demonstração de desempenho mais simples, porém, mais visualmente interessante do OLTP in-memory, veja:

1. Restaurar o banco de dados de exemplo de OLTP in-memory

Você pode restaurar o banco de dados de exemplo AdventureWorksLT com algumas etapas T-SQL no SQL Server Management Studio (SSMS). Para obter mais informações sobre como restaurar um banco de dados para sua instância gerenciada de SQL, consulte Guia de início rápido: restaurar um banco de dados para a instância gerenciada de SQL do Azure com SSMS.

Em seguida, as etapas desta seção explicam como você pode aprimorar seu banco de dados AdventureWorksLT com objetos OLTP in-memory e demonstram os benefícios de desempenho.

  1. Abra o SSMS e conecte-se à instância gerenciada de SQL.

    Observação

    As conexões com sua Instância Gerenciada de SQL do Azure a partir de sua estação de trabalho local ou de uma VM do Azure podem ser feitas com segurança, sem abrir o acesso público. Considere Guia de início rápido: configurar uma conexão ponto a site com uma Instância Gerenciada de SQL do Azure do local ou Guia de início rápido: configurar uma VM do Azure para se conectar à Instância Gerenciada do Banco de Dados SQL do Azure.

  2. No Pesquisador de Objetos, clique com o botão direito do mouse na instância gerenciada e selecione Nova Consulta para abrir uma nova janela de consulta.

  3. Execute o script T-SQL a seguir, que usa um contêiner de armazenamento pré-configurado publicamente disponível e uma chave de assinatura de acesso compartilhado para criar uma credencial na instância gerenciada de SQL. Com o armazenamento disponível publicamente, nenhuma assinatura SAS é necessária.

    CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/examples/]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE';

  4. Execute a instrução a seguir para restaurar o banco de dados de exemplo AdventureWorksLT.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak';

  5. Execute o script a seguir para acompanhar o status do processo de restauração.

    SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete
   , dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

  6. Quando o processo de restauração for concluído, veja o banco de dados AdventureWorksLT no Pesquisador de Objetos. Verifique se o banco de dados AdventureWorksLT foi restaurado usando a exibição sys.dm_operation_status.

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 então, você pode consultar as exibições do catálogo, tal 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), 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 versões com otimização de memória das tabelas, 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 em níveis estressantes. Você pode comparar quanto tempo as duas execuções demoram para serem concluídas.

Instalar utilitários RML e o ostress

O ideal é você planejar executar o ostress.exe em uma VM (máquina virtual) do Azure. Você criaria uma VM do Azure na mesma região do Azure que sua instância gerenciada de SQL do Azure. Mas você pode executar o ostress.exe em sua estação de trabalho local, desde que possa se conectar à sua instância gerenciada de SQL do Azure.

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

Para obter mais informações, consulte:

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 subcadeia de caracteres _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 Prompt Cmd RML para executar nossa linha de comando do 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 reais 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 novamente o banco de dados (para exclusão responsável do que pode ser uma grande quantidade de dados de teste).

Resultados esperados para a comparação

Os testes In-Memory mostraram uma melhoria de desempenho de nove vezes para essa carga de trabalho simplista, com o ostress 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. Restaure um novo banco de dados AdventureWorksLT para sua instância gerenciada de SQL, substituindo o banco de dados existente instalado anteriormente, usando o WITH REPLACE.

    RESTORE DATABASE [AdventureWorksLT] 
FROM URL = 'https://mitutorials.blob.core.windows.net/examples/AdventureWorksLT2022.bak'
WITH REPLACE;

  2. Copie o sql_in-memory_analytics_sample para sua á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.
    • 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.
    • O script pode levar 15 minutos 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 v/s Columnstore table performance differences
-- Enable actual Query Plan in order to see Plan differences when Executing
*/
-- Ensure Database is in 130 compatibility mode
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 Prior query on a table with a clustered columnstore index CCI
-- The comparison numbers are even more dramatic the larger the table is (this is an 11 million row table only)
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

Dependendo da configuração da instância gerenciada de SQL, você pode esperar ganhos significativos de desempenho para essa consulta usando o índice columnstore clusterizado em comparação com o índice tradicional.

Conteúdo relacionado