Planejar a adoção de recursos de OLTP in-memory no SQL Server

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Este artigo descreve as formas como a adoção de recursos in-memory no SQL Server afeta outros aspectos do seu sistema empresarial.

Observação

A. Adoção de recursos de OLTP na memória

As subseções a seguir discutem fatores que você deve considerar ao planejar adotar e implementar recursos in-memory.

A.1 Pré-requisitos

Um pré-requisito para usar os recursos in-memory pode envolver a edição ou a camada de serviço do produto SQL. Para esse e outros pré-requisitos, consulte:

A. 2 Prever a quantidade de memória ativa

O sistema tem memória ativa suficiente para dar suporte a uma nova tabela com otimização de memória?

Microsoft SQL Server

Uma tabela com otimização de memória que contém 200 GB de dados requer mais de 200 GB de memória ativa dedicados a seu suporte. Antes de implementar uma tabela com otimização de memória que contém uma grande quantidade de dados, você precisa prever a quantidade de memória ativa adicional que talvez seja necessário adicionar ao computador servidor. Para obter diretrizes para fazer uma estimativa, consulte:

Orientações semelhantes estão disponíveis para a Instância Gerenciada de SQL do Azure:

Banco de Dados SQL do Azure

Para um banco de dados hospedado no serviço de nuvem do Banco de Dados SQL do Azure, a camada de serviço escolhida afeta a quantidade de memória ativa que o banco de dados pode consumir. Você deve se planejar para monitorar o uso de memória do banco de dados usando um alerta. Para obter detalhes, confira:

Variáveis de tabela com otimização de memória

Uma variável de tabela que tenha sido declarada com otimização de memória às vezes é preferível a uma #TempTable tradicional que resida no banco de dados tempdb. As variáveis de tabela podem fornecer ganhos de desempenho sem usar uma quantidade significativa de memória ativa.

A.3 A tabela deve estar offline para ser convertida para uma tabela com otimização de memória

Algumas ALTER TABLE funcionalidades estão disponíveis para tabelas com otimização de memória. Mas você não pode emitir uma instrução ALTER TABLE para converter uma tabela baseada em disco em uma tabela com otimização de memória. Em vez disso, você deve usar um conjunto de etapas mais manuais. A seguir, temos várias maneiras de converter a tabela baseada em disco em uma tabela com otimização de memória.

Criação manual de scripts

Uma maneira de converter a tabela baseada em disco em uma tabela com otimização de memória é codificar você mesmo as etapas necessárias de Transact-SQL.

  1. Suspenda a atividade do aplicativo.

  2. Faça um backup completo.

  3. Renomeie a tabela baseada em disco.

  4. Emita uma CREATE TABLE instrução para criar sua nova tabela com otimização de memória.

  5. INSERT INTO tabela com otimização de memória com uma SUBSELEÇÃO da tabela baseada em disco.

  6. Exclua sua tabela em disco.

  7. Faça outro backup completo.

  8. Retome a atividade do aplicativo.

Orientador de otimização da memória

A ferramenta Orientador de Otimização de Memória pode gerar um script para ajudar a implementar a conversão de uma tabela baseada em disco para uma tabela com otimização de memória. A ferramenta é instalada como parte do SSDT (SQL Server Data Tools).

Arquivo .dacpac

Você pode atualizar seu banco de dados localmente usando um arquivo. dacpac, gerenciado pelo SSDT. No SSDT, você pode especificar alterações no esquema codificado no arquivo .dacpac.

Você trabalha com arquivos .dacpac no contexto de um projeto do Visual Studio do tipo Banco de Dados.

A.4 Diretrizes sobre a adequação dos recursos de OLTP in-memory para seu aplicativo

Para obter diretrizes sobre se os recursos de OLTP in-memory podem melhorar o desempenho de seu aplicativo específico, consulte:

B. Recursos sem suporte

Os recursos que não têm suporte em determinados cenários de OLTP In-Memory são descritos em:

As subseções a seguir destacam alguns dos mais importantes recursos sem suporte.

B.1 INSTANTÂNEO de um banco de dados

Após a primeira vez em que uma tabela ou módulo com otimização de memória é criado em determinado banco de dados, não é mais possível obter nenhum SNAPSHOT do banco de dados. O motivo específico é que:

  • o primeiro item com otimização de memória torna impossível remover o último arquivo do GRUPO DE ARQUIVOS com otimização de memória; e
  • Nenhum banco de dados que tenha um arquivo em um FILEGROUP otimizado para memória pode suportar um SNAPSHOT.

Normalmente, um SNAPSHOT pode ser útil para iterações rápidas de teste.

B.2 Consultas entre bancos de dados

Tabelas com otimização de memória não dão suporte a transações entre bancos de dados . Você não pode acessar outro banco de dados da mesma transação ou na mesma consulta que também acesse uma tabela com otimização de memória.

Variáveis de tabela não são transacionais. Portanto, variáveis de tabela com otimização de memória podem ser usadas em consultas entre bancos de dados.

B.3 Dica de tabela READPAST

Nenhuma consulta pode aplicar a dica de tabela READPAST a nenhuma tabela otimizada para memória.

A diretiva READPAST é útil em cenários em que várias sessões acessam e modificam o mesmo pequeno conjunto de linhas, como no processamento de uma fila.

B.4 RowVersion, sequência

  • Nenhuma coluna pode ser marcada para RowVersion em uma tabela com otimização de memória.

  • Um SEQUENCE não pode ser usado com uma restrição em uma tabela otimizada para memória. Por exemplo, você não pode criar uma DEFAULT restrição com uma cláusula NEXT VALUE FOR. SEQUENCEs podem ser usados com instruções INSERT e UPDATE.

C. Manutenção administrativa

Esta seção descreve diferenças na administração de bancos de dados em que tabelas com otimização de memória são usadas.

C.1 Redefinição de semente de identidade, incremento > 1

DBCC CHECKIDENT para propagar uma coluna IDENTITY, não pode ser usado em uma tabela com otimização de memória.

O valor de incremento é restrito a exatamente 1 para uma IDENTITY coluna em uma tabela com otimização de memória.

C. 2 DBCC CHECKDB não pode validar tabelas com otimização de memória

O comando DBCC CHECKDB não faz nada quando o destino é uma tabela com otimização de memória. As seguintes etapas são uma solução alternativa:

  1. Fazer backup do log de transações.

  2. Faça backup dos arquivos do FILEGROUP otimizado para memória em um dispositivo nulo. O processo de backup executa uma validação de checksum.

    Se for encontrada corrupção, prossiga com as próximas etapas.

  3. Copie os dados das tabelas com otimização de memória para tabelas baseadas em disco, para armazenamento temporário.

  4. Restaure os arquivos do grupo de arquivos otimizado para memória.

  5. INSERT INTO tabelas com otimização de memória os dados armazenados temporariamente nas tabelas baseadas em disco.

  6. DESCARTE as tabelas baseadas em disco que mantinham os dados temporariamente.

D. Desempenho

Esta seção descreve situações em que o desempenho excelente de tabelas com otimização de memória pode ficar abaixo de seu potencial completo.

D.1 Considerações sobre índices

Todos os índices de uma tabela otimizada para memória são criados e gerenciados pelas instruções relacionadas à tabela CREATE TABLE e ALTER TABLE. Você não pode ter como destino uma tabela com otimização de memória com uma instrução CREATE INDEX.

O índice não clusterizado de árvore B tradicional muitas vezes é a opção mais sensata e simples quando você implementa pela primeira vez uma tabela com otimização de memória. Posteriormente, depois de ver o desempenho do seu aplicativo, você pode considerar passar para outro tipo de índice.

Observação

A documentação usa o termo árvore B geralmente em referência a índices. Em índices de rowstore, o Mecanismo de Banco de Dados implementa uma árvore B+. Isso não se aplica a índices columnstore ou a índices em tabelas otimizadas para memória. Para obter mais informações, confira o Guia de arquitetura e design do índice do SQL Server e SQL do Azure.

Dois tipos especiais de índices precisam ser discutidos no contexto de uma tabela otimizada para memória: índices de hash e índices columnstore.

Para ter uma visão geral dos índices em tabelas com otimização de memória, consulte:

Índices de hash

Índices de hash podem ser a forma mais rápida de acessar uma linha específica pelo valor exato de sua chave primária usando o operador "=".

  • Operadores inexatos como ‘! =’, ‘>’ ou ‘BETWEEN’ prejudicariam o desempenho se usados com um índice de hash.

  • Um índice de hash poderá não ser a melhor opção se a taxa de duplicação do valor da chave se tornar muito alta.

  • Evite subestimar a quantidade de buckets de que seu índice de hash pode precisar para evitar longas cadeias dentro de buckets individuais. Para obter detalhes, confira:

Índices columnstore não clusterizados

Tabelas com otimização de memória oferecem alta taxa de transferência de dados de transações comerciais típicos, no paradigma que chamamos de transação online ou OLTP. Índices de columnStore oferecem alta taxa de transferência de agregações e processamentos semelhantes que chamamos de Análise. No passado, a melhor abordagem disponível para atender às necessidades de OLTP e Análise era ter tabelas separadas com grande movimentação de dados e com algum grau de duplicação de dados. Hoje, uma solução híbrida mais simples está disponível: ter um índice de columnstore em uma tabela com otimização de memória.

  • Um índice columnstore pode ser criado em uma tabela baseada em disco, inclusive como índice clusterizado. Mas, em uma tabela com otimização de memória, um índice de columnstore não pode ser clusterizado.

  • Colunas de LOB ou fora da linha para uma tabela com otimização de memória impedem a criação de um índice de columnstore na tabela.

  • Nenhuma instrução ALTER TABLE pode ser executada em uma tabela otimizada para memória enquanto houver um índice columnstore na tabela.

    • A partir de agosto de 2016, a Microsoft tem planos de curto prazo para melhorar o desempenho da recriação do índice de columnstore.

D.2 Colunas de LOB e fora de linha

Objetos grandes (LOBs) são colunas como varchar (max). Ter duas colunas de LOB em uma tabela com otimização de memória provavelmente não causa danos suficientes no desempenho para ter importância. Mas evite ter mais colunas de LOB do que seus dados precisam. O mesmo conselho se aplica a colunas fora de linha. Não defina uma coluna como nvarchar(3072) se varchar(512) for suficiente.

Um pouco mais sobre colunas de LOB e fora de linha está disponível em:

E. Limitações dos procedimentos nativos

Não há suporte para elementos específicos do Transact-SQL em módulos T-SQL compilados nativamente, incluindo procedimentos armazenados. Para obter detalhes sobre quais recursos têm suporte, consulte:

Para considerações ao migrar um módulo Transact-SQL que usa recursos sem suporte para ser compilado nativamente, consulte:

Além das limitações em determinados elementos do Transact-SQL, também há limitações de operadores de consulta com suporte em módulos T-SQL compilados nativamente. Devido a essas limitações, os procedimentos armazenados compilados nativamente não são adequados para consultas analíticas que processam grandes conjuntos de dados.

Não há processamento paralelo em um procedimento nativo

O processamento paralelo não pode fazer parte de nenhum plano de consulta para um procedimento nativo. Procedimentos nativos são sempre do tipo single-threaded.

Tipos de junção

As junções de hash e junções de mesclagem não podem fazer parte de nenhum plano de consulta para um proc nativo. Junções por laços aninhados são usadas.

Sem agregação por hash

Quando o plano de consulta para um processo nativo requer uma fase de agregação, somente a agregação de fluxo está disponível. Não há suporte para agregação de hash em um plano de consulta para um procedimento nativo.

  • A agregação de hash é melhor quando dados de um grande número de linhas precisam ser agregados.

F. Design de aplicativos: transações e lógica de repetição

Uma transação que envolve uma tabela com otimização de memória pode se tornar dependente de outra transação que envolve a mesma tabela. Se a contagem de transações dependentes atingir o máximo permitido, todas as transações dependentes falharão.

No o SQL Server 2016:

  • O máximo permitido é de oito transações dependentes. Oito também é o limite de transações das quais uma determinada transação pode depender.
  • O número do erro é 41839. (No SQL Server 2014, o número do erro é 41301.)

Você pode tornar seus scripts do Transact-SQL mais robustos contra possíveis erros de transação adicionando a lógica de repetição a eles. É mais provável que a lógica de repetição ajude quando as chamadas UPDATE e DELETE forem frequentes ou quando a tabela otimizada para memória for referenciada por uma chave estrangeira em outra tabela. Para obter detalhes, confira:

Conteúdo relacionado

  • Last updated on