Partilhar via

Banco de dados de exemplo para OLTP na memória

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

Visão geral

Este exemplo mostra o recurso OLTP na memória. Ele mostra tabelas com otimização de memória e procedimentos armazenados compilados nativamente, e pode ser usado para demonstrar os benefícios de desempenho do OLTP na memória.

Observação

Para ler este artigo para o SQL Server 2014 (12.x), consulte Extensões para AdventureWorks para demonstrar In-Memory OLTP.

O exemplo migra cinco tabelas no banco de dados AdventureWorks2025 para memória otimizada e inclui uma carga de trabalho de demonstração para processamento de ordens de venda. Você pode usar essa carga de trabalho de demonstração para ver o benefício de desempenho do uso de OLTP na memória em seu servidor.

Na descrição do exemplo, discutimos as compensações que foram feitas na migração das tabelas para OLTP na memória para levar em conta os recursos que (ainda) não são suportados para tabelas otimizadas para memória.

A documentação deste exemplo está estruturada da seguinte forma:

Pré-requisitos

  • SQL Server 2016 (13.x)

  • Para testes de desempenho, um servidor com especificações semelhantes ao seu ambiente de produção. Para este exemplo específico, você deve ter pelo menos 16 GB de memória disponível para o SQL Server. Para obter diretrizes gerais sobre hardware para OLTP na memória, consulte a seguinte postagem no blog: Considerações de hardware para In-Memory OLTP no SQL Server

Instalar o exemplo OLTP em memória baseado no AdventureWorks

Siga estas etapas para instalar o exemplo:

  1. Transfira AdventureWorks2016_EXT.bak e SQLServer2016Samples.zip de: https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks para uma pasta local, por exemplo C:\Temp.

  2. Restaure o backup do banco de dados usando o Transact-SQL ou o SQL Server Management Studio:

    1. Identifique a pasta de destino e o nome do arquivo de dados, por exemplo:

      H:\DATA\AdventureWorks2022_Data.mdf

    2. Identifique a pasta de destino e o nome do arquivo de log, por exemplo:

      I:\DATA\AdventureWorks2022_log.ldf
      1. O arquivo de log deve ser colocado em uma unidade diferente do arquivo de dados, idealmente uma unidade de baixa latência, como um armazenamento SSD ou PCIe, para obter o máximo desempenho.

    Exemplo de script T-SQL:

    RESTORE DATABASE [AdventureWorks2022]
  FROM DISK = N'C:\temp\AdventureWorks2022.bak'
    WITH FILE = 1,
  MOVE N'AdventureWorks2022_Data' TO N'h:\DATA\AdventureWorks2022_Data.mdf',
  MOVE N'AdventureWorks2022_Log' TO N'i:\DATA\AdventureWorks2022_log.ldf',
  MOVE N'AdventureWorks2022_mod' TO N'h:\data\AdventureWorks2022_mod'
 GO

  3. Para exibir os scripts de exemplo e a carga de trabalho, descompacte o arquivo SQLServer2016Samples.zip em uma pasta local. Consulte o arquivo In-Memory OLTP\readme.txt para obter instruções sobre como executar a carga de trabalho.

Descrição dos exemplos de tabelas e procedimentos

O exemplo cria novas tabelas para produtos e ordens de venda, com base em tabelas existentes em AdventureWorks2025. O esquema das novas tabelas é semelhante às tabelas existentes, com algumas diferenças, conforme explicado mais adiante nesta seção.

As novas tabelas com otimização de memória carregam o sufixo _inmem. O exemplo também inclui tabelas correspondentes com o sufixo _ondisk - essas tabelas podem ser usadas para fazer uma comparação um-para-um entre o desempenho de tabelas com otimização de memória e tabelas baseadas em disco em seu sistema.

As tabelas com otimização de memória usadas na carga de trabalho para comparação de desempenho são totalmente duráveis e totalmente registradas. Eles não sacrificam a durabilidade ou a confiabilidade para alcançar o ganho de desempenho.

A carga de trabalho alvo para este exemplo é o processamento de ordens de venda, onde consideramos também informações sobre produtos e descontos. Para este fim, usamos as tabelas SalesOrderHeader, SalesOrderDetail, Product, SpecialOffere SpecialOfferProduct.

Dois novos procedimentos armazenados, Sales.usp_InsertSalesOrder_inmem e Sales.usp_UpdateSalesOrderShipInfo_inmem, são usados para inserir ordens de venda e atualizar as informações de envio de uma determinada ordem de venda.

O novo esquema Demo contém tabelas auxiliares e procedimentos armazenados para executar uma carga de trabalho de demonstração.

Concretamente, o exemplo de OLTP In-Memory adiciona os seguintes objetos a AdventureWorks2025:

Tabelas adicionadas pela amostra

As novas mesas

Sales.SalesOrderHeader_inmem

  • Informações de cabeçalho sobre ordens de venda. Cada ordem de venda tem uma linha nesta tabela.

Sales.SalesOrderDetail_inmem

  • Detalhes das ordens de venda. Cada item de linha de uma ordem de venda tem uma linha nesta tabela.

Sales.SpecialOffer_inmem

  • Informações sobre ofertas especiais, incluindo a percentagem de desconto associada a cada oferta especial.

Sales.SpecialOfferProduct_inmem

  • Tabela de referência entre ofertas especiais e produtos. Cada oferta especial pode apresentar zero ou mais produtos, e cada produto pode ser apresentado em zero ou mais ofertas especiais.

Production.Product_inmem

  • Informações sobre produtos, incluindo o seu preço de tabela.

Demo.DemoSalesOrderDetailSeed

  • Usado na carga de trabalho de demonstração para construir exemplos de ordens de venda.

Variações das tabelas baseadas em disco

  • Sales.SalesOrderHeader_ondisk

  • Sales.SalesOrderDetail_ondisk

  • Sales.SpecialOffer_ondisk

  • Sales.SpecialOfferProduct_ondisk

  • Production.Product_ondisk

Diferenças entre as tabelas originais baseadas em disco e as novas tabelas com otimização de memória

Normalmente, as novas tabelas introduzidas por este exemplo usam as mesmas colunas e os mesmos tipos de dados que as tabelas originais. No entanto, existem algumas diferenças. Listamos as diferenças nesta seção, juntamente com uma justificativa para as alterações.

Sales.SalesOrderHeader_inmem

  • As restrições padrão são suportadas para tabelas com otimização de memória, e a maioria das restrições padrão foi migrada como está. No entanto, a tabela original Sales.SalesOrderHeader contém duas restrições padrão que recuperam a data atual, para as colunas OrderDate e ModifiedDate. Em uma carga de trabalho de processamento de pedidos com alta taxa de transferência e muita concorrência, qualquer recurso global pode se tornar um ponto de contenda. O tempo do sistema é um recurso global, e observamos que ele pode se tornar um gargalo ao executar uma carga de trabalho In-Memory OLTP que insere pedidos de venda, especialmente se o tempo do sistema precisar ser recuperado para várias colunas no cabeçalho do pedido de venda e nos detalhes do pedido de venda. O problema é resolvido neste exemplo ao recuperar a hora do sistema apenas uma vez para cada ordem de venda inserida, e usa-se esse valor para as colunas datetime em SalesOrderHeader_inmem e SalesOrderDetail_inmemno procedimento armazenado Sales.usp_InsertSalesOrder_inmem.

  • Alias tipos de dados definidos pelo usuário (UDTs) - A tabela original usa dois alias UDTs dbo.OrderNumber e dbo.AccountNumber, para as colunas PurchaseOrderNumber e AccountNumber, respectivamente. O SQL Server 2016 (13.x) não oferece suporte a UDT de alias para tabelas com otimização de memória, portanto, as novas tabelas usam os tipos de dados do sistema nvarchar(25) e nvarchar(15), respectivamente.

  • Colunas anuláveis em chaves de índice - Na tabela original, a coluna SalesPersonID é anulável, enquanto nas novas tabelas a coluna não é anulável e tem uma restrição padrão com valor (-1). Essa circunstância ocorre porque os índices em tabelas com otimização de memória não podem ter colunas anuláveis na chave de índice; -1 é um substituto para NULL neste caso.

  • Colunas computadas - As colunas computadas, indicadas por SalesOrderNumber e TotalDue, são omitidas, uma vez que o SQL Server 2016 (13.x) não oferece suporte para colunas computadas em tabelas otimizadas para memória. A nova vista Sales.vSalesOrderHeader_extended_inmem reflete as colunas SalesOrderNumber e TotalDue. Portanto, você pode usar esse modo de exibição se essas colunas forem necessárias.

    • Aplica-se a: SQL Server 2017 (14.x). A partir do SQL Server 2017 (14.x), há suporte para colunas computadas em tabelas e índices com otimização de memória.

  • Restrições de chave estrangeira são suportadas para tabelas com otimização de memória no SQL Server 2016 (13.x), mas somente se as tabelas referenciadas também forem otimizadas para memória. As chaves estrangeiras que fazem referência a tabelas que também são migradas para memória otimizada são mantidas nas tabelas migradas, enquanto outras chaves estrangeiras são omitidas. Além disso, SalesOrderHeader_inmem é uma tabela ativa na carga de trabalho de exemplo, e as restrições de chaves estrangeiras exigem processamento extra para todas as operações DML, pois requer pesquisas em todas as outras tabelas referenciadas nessas restrições. Portanto, a suposição é que o aplicativo garante a integridade referencial para a tabela Sales.SalesOrderHeader_inmem, e a integridade referencial não é validada quando as linhas são inseridas.

  • Rowguid - A coluna rowguid é omitida. Embora uniqueidentifier tenha suporte para tabelas com otimização de memória, a opção ROWGUIDCOL não é suportada no SQL Server 2016 (13.x). Colunas deste tipo são normalmente usadas para replicação por fusão ou tabelas com colunas filestream. Este exemplo não inclui nenhum dos dois.

Vendas.DetalhePedidoVenda

  • Restrições padrão - semelhante a SalesOrderHeader, a restrição padrão que exige a data/hora do sistema não é migrada. Em vez disso, o procedimento armazenado que insere ordens de venda cuida de inserir a data/hora atual do sistema na primeira inserção.

  • Colunas computadas - a coluna LineTotal computada não foi migrada, pois as colunas computadas não são suportadas com tabelas com otimização de memória no SQL Server 2016 (13.x). Para acessar esta coluna, use o Sales.vSalesOrderDetail_extended_inmem modo de exibição.

  • Rowguid - A coluna rowguid é omitida. Para obter detalhes, consulte a descrição da tabela SalesOrderHeader.

Produção.Produto

  • Alias UDTs - a tabela original usa o tipo de dados definido pelo usuário dbo.Flag, que é equivalente ao bit do tipo de dados do sistema. Em vez disso, a tabela migrada usa o tipo de dados 'bit'.

  • Rowguid - A coluna rowguid é omitida. Para obter detalhes, consulte a descrição da tabela SalesOrderHeader.

Vendas.OfertaEspecial

  • Rowguid - A coluna rowguid é omitida. Para obter detalhes, consulte a descrição da tabela SalesOrderHeader.

Sales.SpecialOfferProduto

  • Rowguid - A coluna rowguid é omitida. Para obter detalhes, consulte a descrição da tabela SalesOrderHeader.

Considerações para índices em tabelas com otimização de memória

O índice de linha de base para tabelas otimizadas para memória é o índice NONCLUSTERED, que suporta pesquisas de pontos (busca de índice no predicado de igualdade), varreduras de intervalo (busca de índice no predicado de desigualdade), varreduras de índice completas e varreduras ordenadas. Além disso, os índices NONCLUSTERED suportam a pesquisa nas colunas principais da chave de índice. Na verdade, os índices NONCLUSTERED otimizados para memória suportam todas as operações suportadas por índices NONCLUSTERED baseados em disco, com a única exceção sendo as varreduras regressivas. Portanto, usar índices NONCLUSTERED é uma escolha segura para seus índices.

Os índices HASH podem ser usados para otimizar ainda mais a carga de trabalho. Eles são otimizados para pesquisas por ponto e inserções de linhas. No entanto, deve-se considerar que eles não suportam varreduras de intervalo, varreduras ordenadas ou pesquisa em colunas de chave de índice principais. Portanto, é preciso ter cuidado ao usar esses índices. Além disso, é necessário especificar o bucket_count no momento da criação. Normalmente, deve ser definido entre uma e duas vezes o número de valores de chave de índice, mas a superestimação geralmente não é um problema.

Para mais informações:

Os índices nas tabelas migradas foram ajustados para a carga de trabalho de processamento de ordens de venda de demonstração. A carga de trabalho depende de inserções e pesquisas de pontos nas tabelas Sales.SalesOrderHeader_inmem e Sales.SalesOrderDetail_inmeme também depende de pesquisas pontuais nas colunas de chave primária nas tabelas Production.Product_inmem e Sales.SpecialOffer_inmem.

Sales.SalesOrderHeader_inmem tem três índices, que são todos índices HASH por motivos de desempenho e porque nenhuma verificação ordenada ou de intervalo é necessária para a carga de trabalho.

  • Índice HASH em (SalesOrderID): bucket_count é dimensionado em 10 milhões (arredondado para 16 milhões), porque o número esperado de ordens de venda é de 10 milhões

  • Índice HASH em (SalesPersonID): bucket_count é de 1 milhão. O conjunto de dados fornecido não tem muitos vendedores. Mas este elevado bucket_count permite crescimento futuro. Além disso, você não paga uma penalidade de desempenho por pesquisas de pontos se o bucket_count for superdimensionado.

  • Índice HASH em (CustomerID): bucket_count é de 1 milhão. O conjunto de dados fornecido não tem muitos clientes, mas isso permite o crescimento futuro.

Sales.SalesOrderDetail_inmem tem três índices, que são todos índices HASH por motivos de desempenho e porque nenhuma verificação ordenada ou de intervalo é necessária para a carga de trabalho.

  • Índice HASH em (SalesOrderID, SalesOrderDetailID): este é o índice de chave primária e, embora as pesquisas em (SalesOrderID, SalesOrderDetailID) sejam pouco frequentes, o uso de um índice de hash para a chave acelera as inserções de linha. O bucket_count é dimensionado em 50 milhões (arredondado para 67 milhões): o número esperado de pedidos de vendas é de 10 milhões, e este é dimensionado para ter uma média de cinco itens por pedido

  • Índice HASH em (SalesOrderID): pesquisas por ordem de venda são frequentes: você deseja encontrar todos os itens de linha correspondentes a uma única ordem. bucket_count é dimensionado em 10 milhões (arredondado para 16 milhões), porque o número esperado de pedidos de vendas é de 10 milhões

  • Índice HASH em (ProductID): bucket_count é de 1 milhão. O conjunto de dados fornecido não tem muitos produtos, mas isso permite o crescimento futuro.

Production.Product_inmem tem três índices

  • HASH index on (ProductID): pesquisas em ProductID estão no caminho crítico para a carga de trabalho de demonstração, portanto, este é um índice de hash

  • Índice NÃO AGRUPADO em (Name): isto permite varreduras ordenadas de nomes de produtos

  • Índice NONCLUSTERED em (ProductNumber): facilita varreduras ordenadas dos números de produtos

Sales.SpecialOffer_inmem tem um índice HASH em (SpecialOfferID): consultas pontuais de ofertas especiais são um elemento crucial da carga de trabalho de demonstração. O bucket_count é dimensionado em 1 milhão para permitir o crescimento futuro.

Sales.SpecialOfferProduct_inmem não é referenciado na carga de trabalho de demonstração e, portanto, não há necessidade aparente de usar índices de hash nesta tabela para otimizar a carga de trabalho - os índices em (SpecialOfferID, ProductID) e (ProductID) são NONCLUSTERED.

No exemplo anterior, algumas das contagens de bucket são superdimensionadas, mas não as contagens de bucket para os índices em SalesOrderHeader_inmem e SalesOrderDetail_inmem: eles são dimensionados para apenas 10 milhões de ordens de venda. Isso foi feito para permitir a instalação do exemplo em sistemas com baixa disponibilidade de memória, embora nesses casos a carga de trabalho de demonstração falhe com um erro de falta de memória. Se você quiser escalar muito além de 10 milhões de pedidos de vendas, sinta-se à vontade para aumentar as contagens de balde de acordo.

Considerações sobre a utilização da memória

A utilização de memória no banco de dados de exemplo, antes e depois de executar a carga de trabalho de demonstração, é discutida na Seção Utilização de memória para as tabelas com otimização de memória.

Procedimentos armazenados incluídos no exemplo

Os dois principais procedimentos armazenados para inserir a ordem do cliente e atualizar os detalhes de envio são os seguintes:

  • Sales.usp_InsertSalesOrder_inmem

    • Insere uma nova ordem de venda no banco de dados e gera o SalesOrderID dessa ordem de venda. Como parâmetros de entrada, são utilizados detalhes para o cabeçalho do pedido de venda e os itens de linha do pedido.

    • Parâmetro de saída:

      • @SalesOrderID int - o SalesOrderID para a ordem de venda que acabou de ser inserida

    • Parâmetros de entrada (obrigatório):

      • @DueDatedatetime2
      • @CustomerIDint
      • @BillToAddressIDint
      • @ShipToAddressIDint
      • @ShipMethodIDint
      • @SalesOrderDetailsSales.SalesOrderDetailType_inmem - parâmetro de valor de tabela (PVT) que contém as linhas de itens da encomenda

    • Parâmetros de entrada (opcional):

      • @Statustinyint
      • @OnlineOrderFlagbit
      • @PurchaseOrderNumbernvarchar(25)
      • @AccountNumberNvarchar(15)
      • @SalesPersonIDint
      • @TerritoryIDint
      • @CreditCardIDint
      • @CreditCardApprovalCodeVarchar(15)
      • @CurrencyRateIDint
      • @CommentNvarchar(128)

  • Sales.usp_UpdateSalesOrderShipInfo_inmem

    • Atualize as informações de envio de uma determinada ordem de venda. Isso também atualiza as informações de envio para todos os itens de linha da ordem do cliente.

    • Este é um procedimento de encapsulamento para os procedimentos armazenados compilados nativamente Sales.usp_UpdateSalesOrderShipInfo_native com lógica de repetição para lidar com potenciais conflitos inesperados com transações simultâneas atualizando o mesmo pedido. Para obter mais informações, consulte lógica de repetição.

  • Sales.usp_UpdateSalesOrderShipInfo_native

    • Este é o procedimento armazenado compilado nativamente que realmente processa a atualização das informações de envio. Destina-se a ser chamado a partir do procedimento armazenado do 'wrapper' Sales.usp_UpdateSalesOrderShipInfo_inmem. Se o cliente puder lidar com falhas e implementar lógica de tentativas, poderá chamar este procedimento diretamente, em vez de usar o procedimento armazenado pela função wrapper.

O procedimento armazenado a seguir é usado para a carga de trabalho de demonstração.

  • Demo.usp_DemoReset

    • Redefine a demonstração esvaziando e resemeando as tabelas SalesOrderHeader e SalesOrderDetail.

Os procedimentos armazenados a seguir são usados para inserir e excluir tabelas com otimização de memória, garantindo a integridade referencial e de domínio.

  • Production.usp_InsertProduct_inmem
  • Production.usp_DeleteProduct_inmem
  • Sales.usp_InsertSpecialOffer_inmem
  • Sales.usp_DeleteSpecialOffer_inmem
  • Sales.usp_InsertSpecialOfferProduct_inmem

Finalmente, o seguinte procedimento armazenado é usado para verificar a integridade de domínio e referencial.

  1. dbo.usp_ValidateIntegrity

    • Parâmetro opcional: @object_id - ID do objeto para validar a integridade para

    • Este procedimento depende das tabelas dbo.DomainIntegrity, dbo.ReferentialIntegritye dbo.UniqueIntegrity para as regras de integridade que precisam ser verificadas - o exemplo preenche essas tabelas com base na verificação, chave estrangeira e restrições exclusivas que existem para as tabelas originais no banco de dados AdventureWorks2025.

    • Ele depende dos procedimentos auxiliares dbo.usp_GenerateCKCheck, dbo.usp_GenerateFKChecke dbo.GenerateUQCheck para gerar o T-SQL necessário para executar as verificações de integridade.

Medições de desempenho usando a carga de trabalho de demonstração

ostress é uma ferramenta de linha de comando que foi desenvolvida pela equipe de suporte do Microsoft CSS SQL Server. Esta ferramenta pode ser usada para executar consultas ou executar procedimentos armazenados em paralelo. Você pode configurar o número de threads para executar uma determinada instrução T-SQL em paralelo e pode especificar quantas vezes a instrução deve ser executada nesse thread; ostress gira os threads e executa a instrução em todos os threads em paralelo. Após a conclusão da execução de todos os threads, ostress relata o tempo necessário para todos os threads concluírem a execução.

Instalar ostress

ostress é instalado como parte dos utilitários RML (Report Markup Language); Não existe uma instalação autónoma para o OSTRESS.

Etapas de instalação:

  1. Transfira e execute o pacote de instalação x64 para os utilitários RML a partir da seguinte página: Baixar RML para SQL Server

  2. Se houver uma caixa de diálogo dizendo que certos arquivos estão em uso, selecione 'Continuar'

Correr ostress

Ostress é executado a partir do prompt da linha de comandos. É mais conveniente executar a ferramenta a partir da Linha de Comando RML, que é instalada como parte dos RML Utilities.

Para abrir o prompt RML Cmd, siga estas instruções:

No Windows, abra o menu Iniciar selecionando a tecla Windows e digite rml. Selecione RML Cmd Prompt, que está na lista de resultados da pesquisa.

Verifique se o prompt de comando está localizado na pasta de instalação do RML Utilities.

As opções de linha de comando para ostress podem ser vistas quando simplesmente são executadas ostress.exe sem opções de linha de comando. As principais opções a considerar para a execução de ostress com esta amostra são as seguintes:

Opção Description
-S Nome da instância do SQL Server à qual se conectar.
-E Use a autenticação do Windows para se conectar (padrão); se você usar a autenticação do SQL Server, use as opções -U e -P para especificar o nome de usuário e a senha, respectivamente.
-d Nome do banco de dados, para este exemplo AdventureWorks2025.
-Q A instrução T-SQL a ser executada.
-n Número de conexões que processam cada arquivo/consulta de entrada.
-r O número de iterações para cada conexão para executar cada arquivo/consulta de entrada.

Carga de trabalho de demonstração

O principal procedimento armazenado usado na carga de trabalho de demonstração é Sales.usp_InsertSalesOrder_inmem/ondisk. O script no exemplo a seguir constrói um parâmetro com valor de tabela (TVP) com dados de exemplo e chama o procedimento para inserir um pedido de venda com cinco linhas de item.

A ferramenta ostress é utilizada para executar em paralelo as chamadas de procedimentos armazenados, a fim de simular clientes a inserir pedidos de venda concomitantemente.

Redefina a demonstração após cada teste de esforço executando Demo.usp_DemoReset. Este procedimento exclui as linhas nas tabelas com otimização de memória, trunca as tabelas baseadas em disco e executa um ponto de verificação do banco de dados.

O script a seguir é executado simultaneamente para simular uma carga de trabalho de processamento de ordem de venda:

DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1;
INSERT INTO @od
SELECT OrderQty,
       ProductID,
       SpecialOfferID
FROM Demo.DemoSalesOrderDetailSeed
WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT);
WHILE (@i < 20)
    BEGIN
        EXECUTE Sales.usp_InsertSalesOrder_inmem
            @SalesOrderID OUTPUT,
            @DueDate,
            @CustomerID,
            @BillToAddressID,
            @ShipToAddressID,
            @ShipMethodID,
            @od;
        SET @i + = 1;
    END

Com esse script, cada ordem de amostra construída é inserida 20 vezes, através de 20 procedimentos armazenados executados em um loop WHILE. O loop é usado para explicar o fato de que o banco de dados é usado para construir a ordem de amostra. Em ambientes de produção típicos, o aplicativo intermediário constrói a ordem de venda a ser inserida.

O script anterior insere ordens de venda em tabelas com otimização de memória. O script para inserir ordens de venda em tabelas baseadas em disco é derivado substituindo as duas ocorrências de _inmem por _ondisk.

Usamos a ferramenta ostress para executar os scripts usando várias conexões simultâneas. Usamos o parâmetro -n para controlar o número de conexões e o parâmetro r para controlar quantas vezes o script é executado em cada conexão.

Executar a carga de trabalho

Para testar em escala, inserimos 10 milhões de ordens de venda, usando 100 conexões. Este teste tem um desempenho razoável em um servidor modesto (por exemplo, 8 físicos, 16 núcleos lógicos) e armazenamento SSD básico para o log. Se o teste não tiver um bom desempenho no hardware, consulte a seção Solucionar problemas de testes de execução lenta. Se você quiser reduzir o nível de estresse para este teste, diminua o número de conexões alterando o parâmetro -n. Por exemplo, para reduzir a contagem de conexões para 40, altere o parâmetro -n100 para -n40.

Como medida de desempenho para a carga de trabalho, usamos o tempo decorrido conforme relatado por ostress.exe após a execução da carga de trabalho.

As instruções e medições a seguir utilizam uma carga de trabalho que insere 10 milhões de pedidos de venda. Para obter instruções sobre como executar uma carga de trabalho reduzida inserindo 1 milhão de ordens de venda, consulte as instruções que fazem parte do arquivo In-Memory OLTP\readme.txtSQLServer2016Samples.zip.

Tabelas com otimização de memória

Começamos executando a carga de trabalho em tabelas com otimização de memória. O comando a seguir abre 100 threads, cada uma executando 5.000 iterações. Cada iteração insere 20 ordens de venda em transações separadas. Há 20 inserções por iteração para compensar o fato de que o banco de dados é usado para gerar os dados a serem inseridos. Isso gera um total de 20 * 5.000 * 100 = 10.000.000 inserções de ordem de venda.

Abra o prompt RML Cmd e execute o seguinte comando:

Selecione o botão Copiar para copiar o comando e cole-o no prompt de comando das Utilidades RML.

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_inmem, @SalesOrderID AS INT, @DueDate AS DATETIME2 = SYSDATETIME(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT); WHILE (@i < 20) BEGIN EXECUTE Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; SET @i + = 1; END"

Em um servidor de teste com um número total de 8 núcleos físicos (16 lógicos), isso levou 2 minutos e 5 segundos. Em um segundo servidor de teste com 24 núcleos físicos (48 lógicos), isso levou 1 minuto e 0 segundos.

Observe a utilização da CPU enquanto a carga de trabalho está em execução, por exemplo, usando o gerenciador de tarefas. Você vê que a utilização da CPU está próxima de 100%. Se esse não for o caso, você tem um afunilamento de E/S de log, consulte também Solucionar problemas de testes de execução lenta.

Tabelas baseadas em disco

O comando a seguir executa a carga de trabalho em tabelas baseadas em disco. Essa carga de trabalho pode demorar um pouco para ser executada, o que se deve em grande parte à contenção de travamento no sistema. As tabelas com otimização de memória são livres de travamento e, portanto, não sofrem com esse problema.

Abra o prompt RML Cmd e execute o seguinte comando:

Selecione o botão Copiar para copiar o comando e cole-o no prompt de comando das Utilidades RML.

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2022 -q -Q"DECLARE @i AS INT = 0, @od AS Sales.SalesOrderDetailType_ondisk, @SalesOrderID AS INT, @DueDate AS DATETIME2 = sysdatetime(), @CustomerID AS INT = RAND() * 8000, @BillToAddressID AS INT = RAND() * 10000, @ShipToAddressID AS INT = RAND() * 10000, @ShipMethodID AS INT = (RAND() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID = CAST ((RAND() * 106) + 1 AS INT); WHILE (@i < 20) BEGIN EXECUTE Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; SET @i + = 1; END"

Em um servidor de teste com um número total de 8 núcleos físicos (16 lógicos), isso levou 41 minutos e 25 segundos. Em um segundo servidor de teste com 24 núcleos físicos (48 lógicos), isso levou 52 minutos e 16 segundos.

O principal fator na diferença de desempenho entre tabelas com otimização de memória e tabelas baseadas em disco neste teste é que, ao usar tabelas baseadas em disco, o SQL Server não pode utilizar totalmente a CPU. O motivo é a contenção de fechadura: transações simultâneas estão tentando gravar na mesma página de dados; as fechaduras são usadas para garantir que apenas uma transação de cada vez possa gravar em uma página. O mecanismo OLTP In-Memory é livre de travamento e as linhas de dados não são organizadas em páginas. Assim, as transações simultâneas não bloqueiam as inserções umas das outras, permitindo que o SQL Server utilize totalmente a CPU.

Você pode observar a utilização da CPU enquanto a carga de trabalho está em execução, por exemplo, usando o gerenciador de tarefas. Você vê que com tabelas baseadas em disco a utilização da CPU está longe de 100%. Em uma configuração de teste com 16 processadores lógicos, a utilização ficaria em torno de 24%.

Opcionalmente, você pode visualizar o número de esperas de trava por segundo usando o Monitor de Desempenho, com o contador de desempenho \SQL Server:Latches\Latch Waits/sec.

Redefinir a demonstração

Para redefinir a demonstração, abra o prompt RML Cmd e execute o seguinte comando:

ostress.exe -S. -E -dAdventureWorks2022 -Q"EXEC Demo.usp_DemoReset"

Dependendo do hardware, isso pode levar alguns minutos para ser executado.

Recomendamos uma redefinição após cada execução de demonstração. Como esta carga de trabalho é de apenas inserção, cada execução consome mais memória e, assim, é necessário um restabelecimento para evitar a falta de memória. A quantidade de memória consumida após uma execução é discutida na Seção Utilização de memória após a execução da carga de trabalho.

Solucionar problemas de testes de execução lenta

Os resultados do teste normalmente variam com o hardware e também com o nível de simultaneidade usado na execução do teste. Algumas coisas a procurar se os resultados não forem os esperados:

  • Número de transações simultâneas: ao executar a carga de trabalho em um único thread, o ganho de desempenho com In-Memory OLTP é provavelmente inferior a 2X. A contenção de trava só é um problema significativo se houver um alto nível de simultaneidade.

  • Baixo número de núcleos disponíveis para o SQL Server: Isso significa que há um baixo nível de simultaneidade no sistema, pois só pode haver tantas transações executadas simultaneamente quanto os núcleos disponíveis para SQL.

    • Sintoma: se a utilização da CPU for alta ao executar a carga de trabalho em tabelas baseadas em disco, isso significa que não há muita contenção, apontando para a falta de simultaneidade.

  • Velocidade da unidade de log: se a unidade de log não puder acompanhar a taxa de transferência de transações no sistema, a carga de trabalho ficará limitada na E/S de log. Embora o registro em log seja mais eficiente com In-Memory OLTP, se a E/S de log for um gargalo, o ganho potencial de desempenho será limitado.

    • Sintoma: se a utilização da CPU não estiver perto de 100% ou for muito irregular durante a execução da carga de trabalho em tabelas otimizadas para memória, é possível que haja um gargalo de E/S de log. Isso pode ser confirmado abrindo o Monitor de Recursos e verificando o comprimento da fila para a unidade de log.

Utilização de memória e espaço em disco no exemplo

No exemplo a seguir, descrevemos o que esperar em termos de utilização de memória e espaço em disco para o banco de dados de exemplo. Também mostramos os resultados de um servidor de teste com 16 núcleos lógicos.

Utilização de memória para as tabelas com otimização de memória

Utilização geral da base de dados

A consulta a seguir pode ser usada para obter a utilização total de memória para In-Memory OLTP no sistema.

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

Instantâneo após o banco de dados ter acabado de ser criado:

tipo Nome pages_MB
MEMORYCLERK_XTP Padrão 94
MEMORYCLERK_XTP DB_ID_5 877
MEMORYCLERK_XTP Padrão 0
MEMORYCLERK_XTP Padrão 0

Os agentes de memória padrão contêm estruturas de memória a nível do sistema e são relativamente pequenos. O gestor de memória para o banco de dados do usuário, neste caso, o banco de dados com ID 5 (o database_id pode diferir na sua instância), ocupa cerca de 900 MB.

Utilização de memória por tabela

A consulta a seguir pode ser usada para detalhar a utilização da memória das tabelas individuais e seus índices:

SELECT object_name(t.object_id) AS [Table name],
       memory_allocated_for_table_kb,
       memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

A tabela a seguir exibe os resultados dessa consulta para uma nova instalação do exemplo:

Nome da tabela memory_allocated_for_table_kb memory_allocated_for_indexes_kb
SpecialOfferProduct_inmem 64 3840
DemoSalesOrderHeaderSeed 1984 5504
SalesOrderDetail_inmem 15316 663552
DemoSalesOrderDetailSeed 64 10432
SpecialOffer_inmem 3 8192
SalesOrderHeader_inmem 7168 147456
Product_inmem 124 12352

Como você pode ver, as tabelas são bastante pequenas: SalesOrderHeader_inmem é de cerca de 7 MB, e SalesOrderDetail_inmem tem cerca de 15 MB de tamanho.

O que chama a atenção aqui é o tamanho da memória alocada para índices, em comparação com o tamanho dos dados da tabela. Isso ocorre porque os índices de hash no exemplo são pré-dimensionados para um tamanho de dados maior. Os índices de hash têm um tamanho fixo e, portanto, seu tamanho não cresce com o tamanho dos dados na tabela.

Utilização da memória após a execução da carga de trabalho

Depois de inserir 10 milhões de ordens de venda, a utilização da memória total é semelhante à seguinte consulta:

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

Aqui está o conjunto de resultados.

type name pages_MB
MEMORYCLERK_XTP Padrão 146
MEMORYCLERK_XTP DB_ID_5 7374
MEMORYCLERK_XTP Padrão 0
MEMORYCLERK_XTP Padrão 0

Como você pode ver, o SQL Server está usando um pouco menos de 8 GB para as tabelas e índices com otimização de memória no banco de dados de exemplo.

Observando o uso detalhado de memória por tabela após uma execução de exemplo:

SELECT object_name(t.object_id) AS [Table name],
       memory_allocated_for_table_kb,
       memory_allocated_for_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats AS dms
     INNER JOIN sys.tables AS t
         ON dms.object_id = t.object_id
WHERE t.type = 'U';

Aqui está o conjunto de resultados.

Table name memory_allocated_for_table_kb memory_allocated_for_indexes_kb
SalesOrderDetail_inmem 5113761 663552
DetalheSementeOrdemDeVendaDemo 64 10368
OfertaEspecial_inmem 2 8192
SalesOrderHeader_inmem 1575679 147456
Product_inmem 111 12032
SpecialOfferProduct_inmem 64 3712
DemoSalesOrderHeaderSeed 1984 5504

Podemos ver um total de cerca de 6,5 GB de dados. O tamanho dos índices na tabela SalesOrderHeader_inmem e SalesOrderDetail_inmem é o mesmo que o tamanho dos índices antes de inserir as ordens de venda. O tamanho do índice não foi alterado porque ambas as tabelas estão usando índices de hash e os índices de hash são estáticos.

Após reinicialização da demonstração

O procedimento armazenado Demo.usp_DemoReset pode ser usado para reiniciar a demonstração. Ele exclui os dados nas tabelas SalesOrderHeader_inmem e SalesOrderDetail_inmem, e repopula os dados a partir das tabelas originais SalesOrderHeader e SalesOrderDetail.

Agora, mesmo que as linhas nas tabelas tenham sido excluídas, isso não significa que a memória seja recuperada imediatamente. O SQL Server recupera memória de linhas excluídas em tabelas com otimização de memória em segundo plano, conforme necessário. Você vê que imediatamente após a redefinição da demonstração, sem carga de trabalho transacional no sistema, a memória das linhas excluídas ainda não foi recuperada:

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

Aqui está o conjunto de resultados.

type name pages_MB
MEMORYCLERK_XTP Padrão 2261
MEMORYCLERK_XTP DB_ID_5 7396
MEMORYCLERK_XTP Padrão 0
MEMORYCLERK_XTP Padrão 0

Isso é esperado: a memória é recuperada quando a carga de trabalho transacional está em execução.

Se você iniciar uma segunda execução da carga de trabalho de demonstração, verá a utilização da memória diminuir inicialmente, à medida que as linhas excluídas anteriormente são limpas. Em algum momento, o tamanho da memória aumenta novamente até que a carga de trabalho termine. Depois de inserir 10 milhões de linhas após o reinício da demonstração, a utilização da memória é muito semelhante àquela observada após a primeira execução. Por exemplo:

SELECT type,
       name,
       pages_kb / 1024 AS pages_MB
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%xtp%';

Aqui está o conjunto de resultados.

type name pages_MB
MEMORYCLERK_XTP Padrão 1863
MEMORYCLERK_XTP DB_ID_5 7390
MEMORYCLERK_XTP Padrão 0
MEMORYCLERK_XTP Padrão 0

Utilização do disco para tabelas com otimização de memória

O tamanho total em disco para os ficheiros de ponto de verificação de uma base de dados num determinado momento pode ser encontrado usando a consulta:

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Estado inicial

Quando o grupo de arquivos de exemplo e as tabelas otimizadas para memória de exemplo são criados inicialmente, vários arquivos de ponto de verificação são pré-criados e o sistema começa a preencher os arquivos - o número de arquivos de ponto de verificação pré-criados depende do número de processadores lógicos no sistema. Como o exemplo é inicialmente muito pequeno, os arquivos pré-criados ficam praticamente vazios após a criação inicial.

O código a seguir mostra o tamanho inicial no disco para o exemplo em uma máquina com 16 processadores lógicos:

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Aqui está o conjunto de resultados.

Tamanho no disco em MB
2312

Como você pode ver, há uma grande discrepância entre o tamanho no disco dos arquivos de ponto de verificação, que é de 2,3 GB, e o tamanho real dos dados, que é mais próximo de 30 MB.

Observando mais de perto de onde vem a utilização do espaço em disco, você pode usar a consulta a seguir. O tamanho no disco retornado por esta consulta é aproximado para arquivos com estado em 5 (NECESSÁRIO PARA BACKUP/HA), 6 (EM TRANSIÇÃO PARA LÁPIDE) ou 7 (LÁPIDE).

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

Para o estado inicial do exemplo, o resultado se parece com a tabela a seguir para um servidor com 16 processadores lógicos:

descrição_do_estado descrição_do_tipo_de_ficheiro contagem Tamanho no disco em MB
pré-criado DADOS 16 2048
pré-criado DELTA 16 128
EM CONSTRUÇÃO DADOS 1 128
EM CONSTRUÇÃO DELTA 1 8

Como você pode ver, a maior parte do espaço é usada por dados pré-criados e arquivos delta. O SQL Server pré-criou um par de arquivos (dados, delta) por processador lógico. Além disso, os arquivos de dados são pré-dimensionados em 128 MB, e arquivos delta em 8 MB, a fim de tornar a inserção de dados nesses arquivos mais eficiente.

Os dados reais nas tabelas com otimização de memória estão no único arquivo de dados.

Depois de executar a carga de trabalho

Após uma única execução de teste que insere 10 milhões de ordens de venda, o tamanho geral no disco fica assim (para um servidor de teste de 16 núcleos):

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Aqui está o conjunto de resultados.

Tamanho no disco em MB
8828

O tamanho no disco é próximo de 9 GB, o que se aproxima do tamanho na memória dos dados.

Observando mais de perto os tamanhos dos arquivos de ponto de verificação nos vários estados:

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
            ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

Aqui está o conjunto de resultados.

state_desc file_type_desc count on-disk size MB
pré-criado DADOS 16 2048
pré-criado DELTA 16 128
EM CONSTRUÇÃO DADOS 1 128
EM CONSTRUÇÃO DELTA 1 8

Ainda temos 16 pares de ficheiros pré-criados, prontos a utilizar à medida que os pontos de verificação são fechados.

Existe um par em construção, que é utilizado até que o ponto de verificação atual seja fechado. Junto com os arquivos de ponto de verificação ativos, isso dá cerca de 6,5 GB de utilização de disco para 6,5 GB de dados na memória. Lembre-se de que os índices não são persistentes no disco e, portanto, o tamanho geral no disco é menor do que o tamanho na memória neste caso.

Após reinicialização da demonstração

Após a redefinição da demonstração, o espaço em disco não é recuperado imediatamente se não houver nenhuma carga de trabalho transacional no sistema nem pontos de verificação do banco de dados. Para que os arquivos de ponto de verificação passem por várias etapas e eventualmente sejam descartados, é necessário que ocorram vários pontos de verificação e eventos de truncamento de log, para iniciar a mesclagem dos arquivos de ponto de verificação, assim como para iniciar a recolha do lixo. Isso acontece automaticamente se você tiver uma carga de trabalho transacional no sistema (e fizer backups de log regulares, caso esteja usando o modelo de recuperação FULL), mas não quando o sistema estiver ocioso, como em um cenário de demonstração.

No exemplo, após o reinício da demonstração, poderás ver algo como:

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]
FROM sys.filegroups AS f
     INNER JOIN sys.database_files AS df
         ON f.data_space_id = df.data_space_id
WHERE f.type = N'FX';

Aqui está o conjunto de resultados.

Tamanho no disco em MB
11839

Com quase 12 GB, isso é significativamente mais do que os 9 GB que tínhamos antes da reposição da demo. Isso ocorre porque algumas mesclagens de arquivos de ponto de verificação foram iniciadas, mas alguns dos destinos de mesclagem ainda não foram instalados e alguns dos arquivos de origem de mesclagem ainda não foram limpos, como pode ser visto no exemplo a seguir:

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

Aqui está o conjunto de resultados.

state_desc file_type_desc count on-disk size MB
pré-criado DADOS 16 2048
pré-criado DELTA 16 128
ATIVO DADOS 38 5152
ATIVO DELTA 38 1331
DESTINO DE MESCLAGEM DADOS 7 896
DESTINO DE MESCLAGEM DELTA 7 56
FONTE MESCLADA DADOS 13 1772
FONTE MESCLADA DELTA 13 455

Os objetivos de mesclagem são instalados e as fontes mescladas são limpas à medida que a atividade transacional acontece no sistema.

Após uma segunda execução da carga de trabalho de demonstração, inserindo 10 milhões de ordens de venda após a redefinição da demonstração, verifica-se que os arquivos construídos durante a primeira execução da carga de trabalho foram limpos. Se você executar a consulta anterior várias vezes enquanto a carga de trabalho está em execução, poderá ver os arquivos de ponto de verificação passarem pelos vários estágios.

Após a segunda execução da carga de trabalho, quando são inseridas 10 milhões de ordens de venda, observa-se que a utilização do disco é muito semelhante, embora não necessariamente igual à depois da primeira execução, uma vez que o sistema é dinâmico por natureza. Por exemplo:

SELECT state_desc,
       file_type_desc,
       COUNT(*) AS [count],
       SUM(CASE WHEN state = 5 AND file_type = 0 THEN 128 * 1024 * 1024
                WHEN state = 5 AND file_type = 1 THEN 8 * 1024 * 1024
                WHEN state IN (6, 7) THEN 68 * 1024 * 1024
           ELSE file_size_in_bytes END) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;

Aqui está o conjunto de resultados.

state_desc file_type_desc count on-disk size MB
pré-criado DADOS 16 2048
pré-criado DELTA 16 128
EM CONSTRUÇÃO DADOS 2 268
EM CONSTRUÇÃO DELTA 2 16
ATIVO DADOS 41 5608
ATIVO DELTA 41 328

Nesse caso, há dois pares de arquivos de ponto de verificação no estado UNDER CONSTRUCTION, o que significa que vários pares de arquivos foram movidos para o estado UNDER CONSTRUCTION, provavelmente devido ao alto nível de concorrência na carga de trabalho. Vários threads simultâneos exigiam um novo par de arquivos ao mesmo tempo e, portanto, moviam um par de PRECREATED para UNDER CONSTRUCTION.

Conteúdo relacionado

  • Last updated on