Planejar a adoção de recursos de OLTP in-memory no SQL ServerPlan your adoption of In-Memory OLTP Features in SQL Server

APLICA-SE A: SimSQL Server SimBanco de Dados SQL do Azure NãoAzure Synapse Analytics (SQL DW) NãoParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

Este artigo descreve as formas como a adoção de recursos in-memory afeta outros aspectos do seu sistema empresarial.This article describes the ways in which the adoption of In-Memory features affects other aspects of your business system.

a.A. Adoção de recursos do OLTP in-memoryAdoption of In-Memory OLTP features

As subseções a seguir discutem fatores que você deve considerar ao planejar adotar e implementar recursos in-memory.The following subsections discuss factors you must consider when you plan to adopt and implement In-Memory features. Muitas informações explicativas estão disponíveis em:A lot of explanatory information is available at:

A.1 Pré-requisitosA.1 Prerequisites

Um pré-requisito para usar os recursos in-memory pode envolver a edição ou a camada de serviço do produto SQL.One prerequisite for using the In-Memory features can involve the edition or service tier of the SQL product. Para esse e outros pré-requisitos, consulte:For this and other prerequisites, see:

A. 2 Prever a quantidade de memória ativaA.2 Forecast the amount of active memory

O sistema tem memória ativa suficiente para dar suporte a uma nova tabela com otimização de memória?Does your system have enough active memory to support a new memory-optimized table?

Microsoft SQL ServerMicrosoft 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.A memory-optimized table which contains 200 GB of data requires more than 200 GB of active memory be dedicated to its support. 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.Before you implement a memory-optimized table containing a large amount of data, you must forecast the amount of additional active memory you might need to add to your server computer. Para obter diretrizes para fazer uma estimativa, consulte:For estimation guidance, see:

Banco de Dados SQL do AzureAzure SQL Database

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.For a database hosted in the Azure SQL Database cloud service, your chosen service tier affects the amount of active memory your database is allowed to consume. Você deve se planejar para monitorar o uso de memória do banco de dados usando um alerta.You should plan to monitor the memory usage of your database by using an alert. Para obter detalhes, confira:For details, see:

Variáveis de tabela com otimização de memóriaMemory-optimized table variables

Uma variável de tabela declarada como com otimização de memória às vezes é preferível a uma #TempTable tradicional que reside no banco de dados tempdb .A table variable which is declared to be memory-optimzed is sometimes a preferable to a traditional #TempTable that resides in the tempdb database. Essas variáveis de tabela podem fornecer ganhos de desempenho significativos sem usar uma quantidade significativa de memória ativa.Such table variables can provide significant performance gains without using significant amounts of active memory.

A.3 A tabela deve estar offline para ser convertida para uma tabela com otimização de memóriaA.3 Table must be offline to convert to memory-optimized

Algumas funcionalidades de ALTER TABLE estão disponíveis para tabelas com otimização de memória.Some ALTER TABLE functionality is available for memory-optimized tables. 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.But you cannot issue an ALTER TABLE statement to convert a disk-based table into a memory-optimized table. Em vez disso, você deve usar um conjunto de etapas mais manuais.Instead you must use a more manual set of steps. A seguir, temos várias maneiras de converter a tabela baseada em disco em uma tabela com otimização de memória.What follows are various ways you can convert your disk-based table to be memory-optimized.

Script manualManual scripting

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.One way to convert your disk-based table to a memory-optimized table is to code the necessary Transact-SQL steps yourself.

  1. Suspenda a atividade do aplicativo.Suspend application activity.

  2. Faça um backup completo.Take a full backup.

  3. Renomeie a tabela baseada em disco.Rename your disk-based table.

  4. Emita uma instrução CREATE TABLE para criar a nova tabela com otimização de memória.Issue a CREATE TABLE statement to create your new memory-optimized table.

  5. INSIRA na tabela com otimização de memória com uma SUBSELEÇÃO da tabela baseada em disco.INSERT INTO your memory-optimized table with a sub-SELECT from the disk-based table.

  6. DESCARTE a tabela baseada em disco.DROP your disk-based table.

  7. Faça outro backup completo.Take another full backup.

  8. Retome a atividade do aplicativo.Resume application activity.

Orientador de otimização da memóriaMemory Optimization Advisor

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.The Memory Optimization Advisor tool can generate a script to help implement the conversion of a disk-based table to a memory-optimized table. A ferramenta é instalada como parte do SSDT (SQL Server Data Tools).The tool is installed as part of SQL Server Data Tools (SSDT).

Arquivo .dacpac.dacpac file

Você pode atualizar seu banco de dados localmente usando um arquivo. dacpac, gerenciado pelo SSDT.You can update your database in-place by using a .dacpac file, managed by SSDT. No SSDT, você pode especificar alterações no esquema codificado no arquivo .dacpac.In SSDT you can specify changes to the schema that is encoded in the .dacpac file.

Você trabalha com arquivos .dacpac no contexto de um projeto do Visual Studio do tipo Banco de Dados.You work with .dacpac files in the context of a Visual Studio project of type Database.

A.4 Diretrizes sobre a adequação dos recursos de OLTP in-memory para seu aplicativoA.4 Guidance for whether In-Memory OLTP features are right for your application

Para obter diretrizes sobre se os recursos de OLTP in-memory podem melhorar o desempenho de seu aplicativo específico, consulte:For guidance on whether In-Memory OLTP features can improve the performance of your particular application, see:

B.B. Recursos sem suporteUnsupported features

Os recursos não compatíveis com certos cenários de OLTP in-memory são descritos em:Features which are not supported in certain In-Memory OLTP scenarios are described at:

As subseções a seguir destacam alguns dos mais importantes recursos sem suporte.The following subsections highlight some of the more important unsupported features.

B.1 INSTANTÂNEO de um banco de dadosB.1 SNAPSHOT of a database

Após a primeira vez em que qualquer tabela ou módulo com otimização de memória é criado em um banco de dados, nenhum INSTANTÂNEO do banco de dados pode ser feito.After the first time that any memory-optimized table or module is created in a given database, no SNAPSHOT of the database can ever be taken. O motivo específico é que:The specific reason is that:

  • 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; eThe first memory-optimized item makes it impossible to ever drop the last file from the memory-optimized FILEGROUP; and
  • Nenhum banco de dados que tenha um arquivo em um GRUPO DE ARQUIVOS com otimização de memória pode dar suporte a um INSTANTÂNEO.No database that has a file in a memory-optimized FILEGROUP can support a SNAPSHOT.

Normalmente, um INSTANTÂNEO pode ser útil para iterações de testes rápidas.Normally a SNAPSHOT can be handy for quick testing iterations.

B.2 Consultas entre bancos de dadosB.2 Cross-database queries

Tabelas com otimização de memória não dão suporte a transações entre bancos de dados .Memory-optimized tables do not support cross-database transactions. 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.You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table.

Variáveis de tabela não são transacionais.Table variables are not transactional. Portanto, variáveis de tabela com otimização de memória podem ser usadas em consultas entre bancos de dados.Therefore, memory-optimized table variables can be used in cross-database queries.

B.3 Dica de tabela READPASTB.3 READPAST table hint

Nenhuma consulta pode aplicar a dica de tabela READPAST a qualquer tabela com otimização de memória.No query can apply the READPAST table hint to any memory-optimized table.

A dica READPAST é útil em cenários em que várias sessões estão acessando e modificando o mesmo conjunto pequeno de linhas, como ao processar uma fila.The READPAST hint is helpful in scenarios where several sessions are each accessing and modifying the same small set of rows, such as in processing a queue.

B.4 RowVersion, sequênciaB.4 RowVersion, Sequence

  • Nenhuma coluna pode ser marcada para RowVersion em uma tabela com otimização de memória.No column can be tagged for RowVersion on a memory-optimized table.

  • Não é possível usar SEQUENCE com uma restrição em uma tabela com otimização de memória.A SEQUENCE cannot be used with a constraint in a memory-optimized table. Por exemplo, não é possível criar uma restrição DEFAULT com uma cláusula NEXT VALUE FOR.For example, you cannot create a DEFAULT constraint with a NEXT VALUE FOR clause. SEQUENCEs podem ser usados com instruções INSERT e UPDATE.SEQUENCEs can be used with INSERT and UPDATE statements.

C.C. Manutenção administrativaAdministrative maintenance

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.This section describes differences in database administration where memory-optimized tables are used.

C. 1 Redefinição de semente de identidade, incremento > 1C.1 Identity seed reset, increment > 1

DBCC CHECKIDENT, para propagar uma coluna IDENTITY, não pode ser usado em uma tabela com otimização de memória.DBCC CHECKIDENT, to reseed an IDENTITY column, cannot be used on a memory-optimized table.

O valor do incremento é restrito a exatamente 1 para uma coluna IDENTITY em uma tabela com otimização de memória.The increment value is restricted to exactly 1 for an IDENTITY column on a memory-optimized table.

C. 2 DBCC CHECKDB não pode validar tabelas com otimização de memóriaC.2 DBCC CHECKDB cannot validate memory-optimized tables

O comando DBCC CHECKDB não faz nada quando o destino é uma tabela com otimização de memória.The DBCC CHECKDB command does nothing when its target is a memory-optimized table. As seguintes etapas são uma solução alternativa:The following steps are a work-around:

  1. Fazer backup do log de transações.Back up the transaction log.

  2. Faça backup dos arquivos no GRUPO DE ARQUIVOS com otimização de memória para um dispositivo nulo.Back up the files in the memory-optimized FILEGROUP to a null device. O processo de backup invoca uma validação de soma de verificação.The backup process invokes a checksum validation.

    Se forem encontrados danos, continue com as próximas etapas.If corruption is found, proceed with the next steps.

  3. Copie os dados das tabelas com otimização de memória para tabelas baseadas em disco, para armazenamento temporário.Copy data from your memory-optimized tables into disk-based tables, for temporary storage.

  4. Restaure os arquivos do GRUPO DE ARQUIVOS com otimização de memória.Restore the files of the memory-optimized FILEGROUP.

  5. INSIRA nas tabelas com otimização de memória os dados armazenados temporariamente nas tabelas baseadas em disco.INSERT INTO the memory-optimized tables the data you temporarily stored in the disk-based tables.

  6. DESCARTE as tabelas baseadas em disco que mantinham os dados temporariamente.DROP the disk-based tables which temporarily held the data.

D.D. DesempenhoPerformance

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.This section describes situations where the excellent performance of memory-optimized tables can be held below full potential.

D.1 Considerações sobre índicesD.1 Index considerations

Todos os índices em uma tabela com otimização de memória são criados e gerenciados pelas instruções CREATE TABLE e ALTER TABLE relacionadas à tabela.All indexes on a memory-optimized table are created and managed by the table-related statements CREATE TABLE and ALTER TABLE. Você não pode ter uma tabela com otimização de memória como alvo de uma instrução CREATE INDEX.You cannot target a memory-optimized table with a CREATE INDEX statement.

O índice não clusterizado de árvore B tradicional normalmente é a opção mais sensata e simples quando você implementa pela primeira vez uma tabela com otimização de memória.The traditional b-tree nonclustered index is often the sensible and simple choice when you first implement a memory-optimized table. Posteriormente, depois de ver o desempenho do seu aplicativo, você pode considerar passar para outro tipo de índice.Later, after you see how your application performs, you can consider swapping another index type.

Dois tipos especiais de índices precisam de discussão no contexto de uma tabela com otimização de memória: índices columnstore e índices de hash.Two special types of indexes need discussion in the context of a memory-optimized table: Hash indexes, and Columnstore indexes.

Para ter uma visão geral dos índices em tabelas com otimização de memória, consulte:For an overview of indexes on memory-optimized tables, see:

Índices de hashHash indexes

Í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 " = ".Hash indexes can be the fastest format for accessing one specific row by its exact primary key value by using the '=' operator.

  • Operadores inexatos como ‘ ! = ’, ‘ > ’ ou ‘BETWEEN’ prejudicariam o desempenho se usados com um índice de hash.Inexact operators such as '!=', '>', or 'BETWEEN' would harm performance if used with a hash index.

  • 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.A hash index might not be the best choice if the rate of key value duplication becomes too high.

  • Evite subestimar a quantidade de buckets de que seu índice de hash pode precisar para evitar longas cadeias dentro de buckets individuais.Guard against underestimating how many buckets your hash index might need, to avoid long chains within individual buckets. Para obter detalhes, confira:For details, see:

Índices columnstore não clusterizadosNonclustered columnstore indexes

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.Memory-optimized tables deliver high throughput of typical business transactional data, in the paradigm we call online transaction processing or OLTP. Índices de columnStore oferecem alta taxa de transferência de agregações e processamentos semelhantes que chamamos de Análise.Columnstore indexes deliver high throughput of aggregations and similar processing we call Analytics. 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.In years past the best approach available for satisfying the needs of both OLTP and Analytics was to have separate tables with heavy movement of data, and with some degree of data duplication. 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.Today a simpler hybrid solution is available: have a columnstore index on a memory-optimized table.

  • Um índice de columnstore pode ser interno a uma tabela baseada em disco, até mesmo como o índice clusterizado.A columnstore index can be built on a disk-based table, even as the clustered index. Mas, em uma tabela com otimização de memória, um índice de columnstore não pode ser clusterizado.But on a memory-optimized table a columnstore index cannot be clustered.

  • 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.LOB or off-row columns for a memory-optimized table prevent the creation of a columnstore index on the table.

  • Nenhuma instrução ALTER TABLE pode ser executada em uma tabela com otimização de memória enquanto um índice de columnstore existir na tabela.No ALTER TABLE statement can be executed against a memory-optimized table while a columnstore index exists on the table.

    • A partir de agosto de 2016, a Microsoft tem planos de curto prazo para melhorar o desempenho da recriação do índice de columnstore.As of August 2016, Microsoft has near-term plans to improve the performance of re-creating the columnstore index.

D.2 Colunas de LOB e fora de linhaD.2 LOB and off-row columns

Objetos grandes (LOBs) são colunas como varchar (max).Large objects (LOBs) are columns of such types as 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.Having a couple of LOB columns on a memory-optimized table probably does not harm performance enough to matter. Mas evite ter mais colunas de LOB do que seus dados precisam.But do avoid having more LOB columns than your data needs. O mesmo conselho se aplica a colunas fora de linha.The same advice applies to off-row columns. Não defina uma coluna como nvarchar(3072) se varchar(512) for suficiente.Do not define a column as nvarchar(3072) if varchar(512) will suffice.

Um pouco mais sobre colunas de LOB e fora de linha está disponível em:A bit more about LOB and off-row columns is available at:

E.E. Limitações dos procedimentos nativosLimitations of native procs

Não há suporte para elementos específicos do Transact-SQL em módulos T-SQL compilados nativamente, incluindo procedimentos armazenados.Particular elements of Transact-SQL are not supported in natively compiled T-SQL modules, including stored procedures. Para obter detalhes sobre quais recursos têm suporte, consulte:For details about which features are supported, see:

Para obter considerações ao migrar um módulo Transact-SQL que usa recursos sem suporte para procedimentos compilados nativamente, consulte:For considerations when migrating a Transact-SQL modules that uses unsupported features to natively compiled, see:

Além das limitações em determinados elementos do Transact-SQL, também há limitação de operadores de consulta com suporte em módulos T-SQL compilados nativamente.Besides limitations on certain elements of Transact-SQL, there are also limitation on query operators supported in natively compiled T-SQL modules. 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.Because of these limitations, natively compiled stored procedures are not suitable for analytical queries that process large data sets.

Não há processamento paralelo em um procedimento nativoNo parallel processing in a native proc

O processamento paralelo não pode fazer parte de nenhum plano de consulta para um procedimento nativo.Parallel processing cannot be a part of any query plan for a native proc. Procedimentos nativos são sempre do tipo single-threaded.Native procs are always single-threaded.

Tipos de junçãoJoin types

Nem uma junção de hash nem uma junção de mesclagem podem fazer parte de qualquer plano de consulta para um procedimento nativo.Neither a hash join nor a merge join can be a part of any query plan for a native proc. Junções de loops aninhados são usadas.Nested loop joins are used.

Não há agregação de hashNo hash aggregation

Quando o plano de consulta para um processo nativo requer uma fase de agregação, somente a agregação de fluxo está disponível.When the query plan for a native proc requires an aggregation phase, only stream aggregation is available. Não há suporte para agregação de hash em um plano de consulta para um procedimento nativo.Hash aggregation is not supported in a query plan for a native proc.

  • A agregação de hash é melhor quando dados de um grande número de linhas precisam ser agregados.Hash aggregation is better when data from a large number of rows must aggregated.

F.F. Design de aplicativos: transações e lógica de repetiçãoApplication design: Transactions and retry logic

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.A transaction involving a memory-optimized table can become dependent on another transaction which involves the same table. Se a contagem de transações dependentes ultrapassar o máximo permitido, todas as transações dependentes falharão.If the count of dependent transactions reaches exceeds the allowed maximum, all the dependent transactions fail.

No o SQL Server 2016:In SQL Server 2016:

  • O máximo permitido é de 8 transações dependentes.The allowed maximum is 8 dependent transactions. 8 também é o limite de transações de que qualquer transação pode ser dependente.8 is also the limit of transactions that any given transaction can be dependent on.
  • O número do erro é 41839.The error number is 41839. (No SQL Server 2014, o número do erro é 41301.)(In SQL Server 2014 the error number is 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.You can make your Transact-SQL scripts more robust against a possible transaction error by adding retry logic to your scripts. A lógica de repetição tem mais chances de ajudar quando as chamadas UPDATE e DELETE são frequentes ou se a tabela com otimização de memória for referenciada por uma chave estrangeira em outra tabela.Retry logic is more likely to help when UPDATE and DELETE calls are frequent, or if the memory-optimized table is referenced by a foreign key in another table. Para obter detalhes, confira:For details, see: