Descarregar a carga de trabalho somente leitura na réplica secundária de um grupo de disponibilidade Always OnOffload read-only workload to secondary replica of an Always On availability group

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Os recursos secundários ativos do Grupos de disponibilidade AlwaysOnAlways On availability groups incluem suporte para acesso somente leitura para uma ou mais réplicas secundárias (réplicas secundárias legíveis).The Grupos de disponibilidade AlwaysOnAlways On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). Uma réplica secundária legível pode estar no modo de disponibilidade de confirmação síncrona ou no modo de disponibilidade de confirmação assíncrona.A readable secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode. Uma réplica secundária legível permite acesso somente leitura a todos os seus bancos de dados secundários.A readable secondary replica allows read-only access to all its secondary databases. No entanto, os bancos de dados secundários legíveis não são definidos como somente leitura.However, readable secondary databases are not set to read-only. Eles são dinâmicos.They are dynamic. Um banco de dados secundário determinado muda conforme as alterações nos dados do banco de dados primário correspondente são aplicadas ao banco de dados secundário.A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. Para uma réplica secundária típica, os dados, incluindo tabelas com otimização de memória durável, nos bancos de dados secundários estão quase em tempo real.For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. Além disso, os índices de texto completo são sincronizados com os bancos de dados secundários.Furthermore, full-text indexes are synchronized with the secondary databases. Em muitas circunstâncias, a latência de dados entre um banco de dados primário e o banco de dados secundário correspondente é de somente alguns segundos.In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

Configurações de segurança que ocorrem nos bancos de dados primários para os bancos de dados secundários são permitidas.Security settings that occur in the primary databases are persisted to the secondary databases. Isso inclui os usuários, as funções de banco de dados e de aplicativos junto com suas permissões respectivas e a TDE (criptografia de dados transparente), se habilitada no banco de dados primário.This includes users, database roles, and applications roles together with their respective permissions and transparent data encryption (TDE), if enabled on the primary database.

Observação

Embora não seja possível gravar dados em bancos de dados secundários, é possível gravar em bancos de dados de leitura e gravação na instância de servidor que hospeda a réplica secundária, incluindo bancos de dados de usuário e do sistema, como tempdb.Though you cannot write data to secondary databases, you can write to read-write databases on the server instance that hosts the secondary replica, including user databases and system databases such as tempdb.

Grupos de disponibilidade AlwaysOnAlways On availability groups também dá suporte ao reencaminhamento de solicitações de conexão de intenção de leitura para uma réplica secundária legível (roteamento somente leitura).also supports the re-routing of read-intent connection requests to a readable secondary replica (read-only routing). Para obter informações sobre roteamento somente leitura, veja Usando um ouvinte para de conectar a uma réplica secundária somente leitura (roteamento somente leitura).For information about read-only routing, see Using a Listener to Connect to a Read-Only Secondary Replica (Read-Only Routing).

BenefíciosBenefits

Direcionar conexões somente leitura a réplicas secundárias legíveis tem os seguintes benefícios:Directing read-only connections to readable secondary replicas provides the following benefits:

  • Descarrega suas cargas de trabalho somente leitura secundárias de sua réplica primária, o que conserva seus recursos para cargas de trabalho críticas.Offloads your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. Se você tiver missão carga de trabalho de leitura crítica ou que não possa tolerar latência, execute-a na primária.If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.

  • Melhora seu retorno de investimento para os sistemas que hospedam réplicas secundárias legíveis.Improves your return on investment for the systems that host readable secondary replicas.

Além disso, secundárias legíveis fornecem suporte robusto para operações somente leitura, como segue:In addition, readable secondaries provide robust support for read-only operations, as follows:

  • Estatísticas temporárias automáticas sobre o banco de dados secundário legível otimizam as consultas somente leitura em tabelas baseadas em disco.Automatic temporary statistics on readable secondary database optimize read-only queries on disk-based tables. Para tabelas com otimização de memória, as estatísticas ausentes são criadas automaticamente.For memory-optimized tables, the missing statistics are created automatically. Porém, não há nenhuma atualização automática de estatísticas obsoletas.However, there is no auto-update of stale statistics. Você precisará atualizar manualmente as estatísticas na réplica primária.You will need to manually update the statistics on the primary replica. Para obter informações, veja Estatísticas para bancos de dados somente leitura, mais adiante neste tópico.For more information, see Statistics for Read-Only Access Databases, later in this topic.

  • As cargas de trabalho somente leitura para tabelas baseadas em disco usam o controle de versão de linha para remover a contenção de bloqueio nos bancos de dados secundários.Read-only workloads for disk-based tables use row versioning to remove blocking contention on the secondary databases. Todas as consultas executadas nos bancos de dados secundários são mapeadas automaticamente para o nível de transação de isolamento de instantâneo, até mesmo quando outros níveis de isolamento de transação são definidos explicitamente.All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Além disso, todas as dicas de bloqueio são ignoradas.Also, all locking hints are ignored. Isso elimina a contenção do leitor/gravador.This eliminates reader/writer contention.

  • As cargas de trabalho somente leitura para tabelas duráveis com otimização de memória acessam os dados exatamente da mesma forma que são acessadas no banco de dados primário, usando procedimentos armazenados nativos ou interoperabilidade de SQL com as mesmas restrições de nível de isolamento da transação (veja Níveis de isolamento no mecanismo de banco de dados).Read-only workloads for memory-optimized durable tables access the data in exactly the same way it is accessed on the primary database, using native stored procedures or SQL Interoperability with the same transaction isolation level limitations (See Isolation Levels in the Database Engine). A carga de trabalho de relatório ou as consultas somente leitura que são executadas na réplica primária podem ser executadas na réplica secundária sem exigir nenhuma alteração.Reporting workload or read-only queries running on the primary replica can be run on the secondary replica without requiring any changes. Da mesma maneira, uma carga de trabalho de relatório ou as consultas somente leitura que são executadas em uma réplica secundária podem ser executadas na réplica primária sem exigir nenhuma alteração.Similarly, a reporting workload or read-only queries running on a secondary replica can be run on the primary replica without requiring any changes. De maneira semelhante a tabelas baseadas em disco, todas as consultas executadas nos bancos de dados secundários são mapeadas automaticamente para o nível de transação de isolamento de instantâneo, até mesmo quando outros níveis de isolamento de transação são definidos explicitamente.Similar to disk-based tables, all queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set.

  • As operações DML são permitidas em variáveis de tabela para os tipos baseados em disco e em tabela com otimização de memória na réplica secundária.DML operations are allowed on table variables both for disk-based and memory-optimized table types on the secondary replica.

Pré-requisitos para o grupo de disponibilidadePrerequisites for the Availability Group

  • Réplicas secundárias legíveis (obrigatório)Readable secondary replicas (required)

    O administrador de banco de dados precisa configurar uma ou mais réplicas de forma que, ao ser executado sob a função secundária, eles permitem todas as conexões (apenas para acesso somente leitura) ou apenas conexões de intenção de leitura.The database administrator needs to configure one or more replicas so that, when running under the secondary role, they allow either all connections (just for read-only access) or only read-intent connections.

    Observação

    Como opção, o administrador de banco de dados pode configurar qualquer réplica de disponibilidade para excluir conexões somente leitura ao executar sob a função primária.Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.

    Para obter informações, veja Sobre o acesso de conexão de cliente a réplicas de disponibilidade (SQL Server).For more information, see About Client Connection Access to Availability Replicas (SQL Server).

  • Ouvinte de grupo de disponibilidadeAvailability group listener

    Para dar suporte ao roteamento somente leitura, um grupo de disponibilidade deve ter um ouvinte do grupo de disponibilidade.To support read-only routing, an availability group must possess an availability group listener. O cliente somente leitura deve direcionar suas solicitações de conexão para este ouvinte e a cadeia de conexão do cliente deve especificar a intenção do aplicativo como "somente leitura."The read-only client must direct its connection requests to this listener, and the client's connection string must specify the application intent as "read-only." Ou seja, elas devem ser solicitações de conexão de intenção de leitura.That is, they must be read-intent connection requests.

  • Roteamento somente leituraRead only routing

    Roteamento somente leitura refere-se à capacidade de o SQL Server encaminhar solicitações de conexão de intenção de leitura, que são direcionadas para um ouvinte do grupo de disponibilidade, para uma réplica secundária legível disponível.Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, that are directed to an availability group listener, to an available readable secondary replica. Os pré-requisitos para roteamento somente leitura são os seguintes:The prerequisites for read-only routing are as follows:

    • Para dar suporte a roteamento somente leitura, uma réplica secundária legível exigirá uma URL de roteamento somente leitura.To support read-only routing, a readable secondary replica requires a read-only routing URL. Esta URL só entra em vigor quando a réplica local estiver sendo executada sob a função secundária.This URL takes effect only when the local replica is running under the secondary role. A URL do roteamento somente leitura deve ser especificada réplica por réplica, quando necessário.The read-only routing URL must be specified on a replica-by-replica basis, as needed. Cada URL de roteamento somente leitura é usada para solicitações de conexão de intenção de leitura para uma réplica secundária legível específica.Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. Normalmente, toda réplica secundária legível é atribuída uma URL de roteamento somente leitura.Typically, every readable secondary replica is assigned a read-only routing URL.

    • Cada réplica de disponibilidade que deve dar suporte a roteamento somente leitura quando é a réplica primária exige uma lista de roteamento somente leitura.Each availability replica that is to support read-only routing when it is the primary replica requires a read-only routing list. Uma determinada lista de roteamento somente leitura só entra em vigor quando a réplica local estiver sendo executada em uma função primária.A given read-only routing list takes effect only when the local replica is running under the primary role. Essa lista deve ser especificada réplica por réplica, quando necessário.This list must be specified on a replica-by-replica basis, as needed. Normalmente, cada lista de roteamento somente leitura deveria conter todas as URLs de roteamento somente leitura, com a URL da réplica local no final da lista.Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

      Observação

      Solicitações de conexão de intenção de leitura podem ter balanceamento de carga entre réplicas.Read-intent connection requests can be load-balanced across replicas. Para obter mais informações, veja Configurar o balanceamento de carga entre réplicas somente leitura.For more information, see Configure load-balancing across read-only replicas.

    Para obter informações, veja Configurar o roteamento somente leitura para um grupo de disponibilidade (SQL Server).For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).

Observação

Para obter informações sobre ouvintes do grupo de disponibilidade e mais informações sobre roteamento somente leitura, veja Ouvintes do grupo de disponibilidade, conectividade de cliente e failover de aplicativos (SQL Server).For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

Limitações e restriçõesLimitations and Restrictions

Algumas operações não têm suporte total, como segue:Some operations are not fully supported, as follows:

  • Assim que uma réplica legível é habilitada para leitura, pode começar a aceitar conexões para seus bancos de dados secundários.As soon as a readable replica is enabled for read, it can start accepting connections to its secondary databases. Porém, se houver transações ativas em um banco de dados primário, as versões de linha não estarão completamente disponíveis no banco de dados secundário correspondente.However, if any active transactions exist on a primary database, the row versions will not be fully available on the corresponding secondary database. As transações ativas que existiam na réplica primária quando a réplica secundária foi configurada devem ser confirmadas ou revertidas.Any active transactions that existed on the primary replica when the secondary replica was configured must commit or roll back. Até que esse processo termine, o mapeamento do nível de isolamento da transação no banco de dados secundário ficará incompleto e as consultas serão bloqueadas temporariamente.Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.

    Aviso

    Executar transações demoradas tem um impacto no número de linhas com versão mantidas para tabelas baseadas em disco e com otimização de memória.Running long transactions impacts the number of versioned rows kept, both for disk-based and memory-optimized tables.

  • Em um banco de dados secundário com tabelas com otimização de memória, embora as versões de linha sempre sejam geradas para tabelas com otimização de memória, as consultas serão bloqueadas até que todas as transações ativas que existiam na réplica primária quando a réplica secundária foi habilitada para leitura sejam concluídas.On a secondary database with memory-optimized tables, even though row versions are always generated for memory-optimized tables, queries are blocked until all active transactions that existed in the primary replica when the secondary replica was enabled for read complete. Isso garante que as tabelas baseadas em disco e com otimização de memória estejam disponíveis para a carga de trabalho do relatório e as consultas somente leitura ao mesmo tempo.This ensures that both disk-based and memory-optimized tables are available to the reporting workload and read-only queries at the same time.

  • Não há suporte para o controle de alterações e a captura de dados de alterações em bancos de dados secundários que pertencem a uma réplica secundária legível:Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:

    • O controle de alterações é desabilitado explicitamente em bancos de dados secundários.Change tracking is explicitly disabled on secondary databases.

    • A Captura de Dados de Alterações não pode ser habilitado apenas em um banco de dados de réplica secundária.Change Data Capture cannot be enabled only on a secondary replica database. A Captura de Dados de Alterações pode ser habilitada no banco de dados de réplica primária e as alterações podem ser lidas em tabelas de CDC usando as funções do banco de dados de réplica secundária.Change Data Capture can be enabled on the primary replica database and the changes can be read from the CDC tables using the functions on the secondary replica database.

  • Como as operações de leitura são mapeadas para nível de transação de isolamento de instantâneo, a limpeza de registros fantasmas na réplica primária pode ser bloqueada por transações em uma ou mais réplicas secundárias.Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. A tarefa de limpeza de registro fantasma limpará automaticamente os registros fantasmas para tabelas baseadas em disco na réplica primária quando eles não forem mais necessários nas réplicas secundárias.The ghost record cleanup task will automatically clean up the ghost records for disk-based tables on the primary replica when they are no longer needed by any secondary replica. Isso é similar ao que acontece quando você executa transações na réplica primária.This is similar to what is done when you run transaction(s) on the primary replica. Em caso extremo no banco de dados secundário, você precisará eliminar consultas de leitura de longa execução que bloqueiam a limpeza fantasma.In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Observe que a limpeza fantasma pode ser bloqueada se a réplica secundária for desconectada ou quando a movimentação de dados for suspensa no banco de dados secundário.Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. Este estado também impede truncamento de logs. Portanto, se este estado persistir, recomendaremos que você remova este banco de dados secundário do grupo de disponibilidade.This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group. Não há nenhum problema de limpeza de registro fantasma com tabelas com otimização de memória porque as versões de linha são mantidas na memória e são independentes das versões de linha na réplica primária.There is no ghost record cleanup issue with memory-optimized tables because the row versions are kept in memory and are independent of the row versions on the primary replica.

  • A operação DBCC SHRINKFILE em arquivos que contêm tabelas baseadas em disco poderá falhar na réplica primária se o arquivo contiver registros fantasmas que ainda são necessários em uma réplica secundária.The DBCC SHRINKFILE operation on files containing disk-based tables might fail on the primary replica if the file contains ghost records that are still needed on a secondary replica.

  • A partir do SQL Server 2014 (12.x)SQL Server 2014 (12.x), as réplicas secundárias legíveis podem permanecer online mesmo quando a réplica primária estiver offline devido à ação do usuário ou a uma falha.Beginning in SQL Server 2014 (12.x)SQL Server 2014 (12.x), readable secondary replicas can remain online even when the primary replica is offline due to user action or a failure. No entanto, o roteamento somente leitura não funciona nessa situação porque o ouvinte do grupo de disponibilidade está offline também.However, read-only routing does not work in this situation because the availability group listener is offline as well. Os clientes devem se conectar diretamente às réplicas secundárias somente leitura para cargas de trabalho somente leitura.Clients must connect directly to the read-only secondary replicas for read-only workloads.

Observação

Se você consultar a exibição de gerenciamento dinâmico sys.dm_db_index_physical_stats em uma instância de servidor que está hospedando uma réplica secundária legível, poderá encontrar um problema de bloqueio REDO.If you query the sys.dm_db_index_physical_stats dynamic management view on a server instance that is hosting a readable secondary replica, you might encounter a REDO blocking issue. Isso ocorre porque essa exibição de gerenciamento dinâmico adquire um bloqueio IS na exibição ou tabela de usuário especificada, que pode bloquear solicitações por um thread REDO para um bloqueio X na exibição ou tabela de usuário.This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

Considerações sobre desempenhoPerformance Considerations

Esta seção aborda várias considerações sobre desempenho de bancos de dados secundários legíveisThis section discusses several performance considerations for readable secondary databases

Nesta seção:In This Section:

Latência de dadosData Latency

A implementação do acesso somente leitura a réplicas secundárias será útil se as suas cargas de trabalho somente leitura puderem tolerar certa latência de dados.Implementing read-only access to secondary replicas is useful if your read-only workloads can tolerate some data latency. Nas situações em que a latência de dados é inaceitável, considere executar cargas de trabalho somente leitura na réplica primária.In situations where data latency is unacceptable, consider running read-only workloads against the primary replica.

A réplica primária envia registros de log de alterações no banco de dados primário para as réplicas secundárias.The primary replica sends log records of changes on primary database to the secondary replicas. Em cada banco de dados secundário, um thread de restauração dedicado aplica os registros de log.On each secondary database, a dedicated redo thread applies the log records. Em um banco de dados secundário de acesso de leitura, uma determinada alteração de dados não aparece nos resultados da consulta até que o registro de log que contém a alteração seja aplicado ao banco de dados secundário e a transação seja confirmada no banco de dados primário.On a read-access secondary database, a given data change does not appear in query results until the log record that contains the change has been applied to the secondary database and the transaction has been committed on primary database.

Isso significa que há alguma latência, normalmente apenas uma questão de segundos, entre as réplicas primárias e secundárias.This means that there is some latency, usually only a matter of seconds, between the primary and secondary replicas. No entanto, em casos incomuns, como quando problemas de rede reduzem a taxa de transferência, a latência pode se tornar significativa.In unusual cases, however, for example if network issues reduce throughput, latency can become significant. A latência aumenta quando ocorrem gargalos de E/S e quando a movimentação de dados é suspensa.Latency increases when I/O bottlenecks occur and when data movement is suspended. Para monitorar a movimentação de dados suspensa, você pode usar o Painel AlwaysOn ou a exibição de gerenciamento dinâmico sys.dm_hadr_database_replica_states .To monitor suspended data movement, you can use the Always On Dashboard or the sys.dm_hadr_database_replica_states dynamic management view.

Latência de dados em banco de dados com tabelas com otimização de memóriaData Latency on databases with memory-optimized tables

No SQL Server 2014 (12.x)SQL Server 2014 (12.x), havia considerações especiais relativas à latência de dados em secundárias ativas – confira SQL Server 2014 (12.x)SQL Server 2014 (12.x) Secundárias ativas: Réplicas secundárias legíveis.In SQL Server 2014 (12.x)SQL Server 2014 (12.x) there were special considerations around data latency on active secondaries - see SQL Server 2014 (12.x)SQL Server 2014 (12.x) Active Secondaries: Readable Secondary Replicas. A partir do SQL Server 2016 (13.x)SQL Server 2016 (13.x) , não há considerações especiais em relação à latência de dados em tabelas com otimização de memória.Starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) there are no special considerations around data latency for memory-optimized tables. A latência de dados esperada para tabelas com otimização de memória é comparável à latência para tabelas baseadas em disco.The expected data latency for memory-optimized tables is comparable to the latency for disk-based tables.

Impacto da carga de trabalho somente leituraRead-Only Workload Impact

Ao configurar uma réplica secundária para acesso somente leitura, suas cargas de trabalho somente leitura nos bancos de dados secundários consumirão recursos do sistema, como CPU e E/S (para tabelas baseadas em disco) de threads de restauração, especialmente se as cargas de trabalho somente leitura (para tabelas baseadas em disco) consumirem muitos recursos de E/S.When you configure a secondary replica for read-only access, your read-only workloads on the secondary databases consume system resources, such as CPU and I/O (for disk-based tables) from redo threads, especially if the read-only workloads on disk-based tables are highly I/O-intensive. Não há nenhum impacto de E/S ao acessar tabelas com otimização de memória porque todas as linhas residem na memória.There is no IO impact when accessing memory-optimized tables because all the rows reside in memory.

Além disso, as cargas de trabalho somente leitura nas réplicas secundárias podem bloquear alterações de DDL (linguagem de definição de dados) aplicadas por meio de registros de log.Also, read-only workloads on the secondary replicas can block data definition language (DDL) changes that are applied through log records.

  • Mesmo que as operações de leitura não usem bloqueios compartilhados devido ao controle de versão de linha, essas operações usam bloqueios de estabilidade de esquema (Sch-S), o que pode bloquear operações de refazer que estejam aplicando alterações DDL.Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes. As operações de DDL incluem tabelas ALTER/DROP e exibições, mas não DROP ou ALTER de procedimentos armazenados.DDL operations include ALTER/DROP tables and Views but not DROP or ALTER of stored procedures. Portanto, por exemplo, se você descartar uma tabela com base em disco ou com otimização de memória, no primário.So for example, if you drop a table disk-based or memory-optimized, on primary. Quando o thread REDO processa o registro de log para descartar a tabela, ele deve adquirir um bloqueio de SCH_M na tabela e pode ser bloqueado por uma consulta em execução que acessa a tabela.When REDO thread processes the log record to drop the table, it must acquire a SCH_M lock on the table and can get blocked by a running query accessing table. Esse é o mesmo comportamento na réplica primária exceto que o descarte da tabela é feito como parte de uma sessão de usuário e não para o thread REDO.This is the same behavior on primary replica except that the drop of the table is done as part of a user session and not REDO thread.

  • Há um bloqueio adicional de tabelas com otimização de memória.There is additional blocking Memory-Optimized Tables. Um descarte do procedimento armazenado nativo pode fazer o thread REDO ser bloqueado se houver uma execução simultânea do procedimento armazenado nativo na réplica secundária.A drop of native stored procedure can cause REDO thread to block if there is a concurrent execution of the native stored procedure on the secondary replica. Esse é o mesmo comportamento na réplica primária exceto que o descarte do procedimento armazenado é feito como parte de uma sessão de usuário e não para o thread REDO.This is the same behavior on the primary replica except that the drop of the stored procedure is done as part of a user session and not REDO thread.

Fique atento às práticas recomendadas referentes à criação de consultas e use-as nos bancos de dados secundários.Be aware of best practices around building queries, and exercise those best practices in the secondary databases. Por exemplo, agende consultas de longa execução, como agregações de dados durante horários de baixa atividade.For example, schedule long-running queries such as aggregations of data during times of low activity.

Observação

Se um thread de restauração estiver bloqueado por consultas em uma réplica secundária, o XEvent sqlserver.lock_redo_blocked será gerado.If a redo thread is blocked by queries on a secondary replica, the sqlserver.lock_redo_blocked XEvent is raised.

IndexaçãoIndexing

Para otimizar cargas de trabalho somente leitura nas réplicas secundárias legíveis, talvez você queira criar índices nas tabelas nos bancos de dados secundários.To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. Como você não pode fazer alterações de esquema ou de dados nos bancos de dados secundários, crie índices nos bancos de dados primários e permita que as alterações sejam transferidas para o banco de dados secundário por meio do processo de refazer.Because you cannot make schema or data changes on the secondary databases, create indexes in the primary databases and allow the changes to transfer to the secondary database through the redo process.

Para monitorar a atividade de uso de índice em uma réplica secundária, consulte as colunas user_seeks, user_scanse user_lookups da exibição de gerenciamento dinâmico sys.dm_db_index_usage_stats .To monitor index usage activity on a secondary replica, query the user_seeks, user_scans, and user_lookups columns of the sys.dm_db_index_usage_stats dynamic management view.

Estatísticas para bancos de dados somente leituraStatistics for Read-Only Access Databases

As estatísticas em colunas de tabelas e exibições indexadas são usadas para otimizar os planos da consulta.Statistics on columns of tables and indexed views are used to optimize query plans. Para grupos de disponibilidade, as estatísticas que são criadas e mantidas nos bancos de dados primários são automaticamente persistidas nos bancos de dados secundários como parte da aplicação dos registros dos logs de transação.For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. No entanto, a carga de trabalho somente leitura nos bancos de dados secundários talvez precise de estatísticas diferentes daquelas criadas nos bancos de dados primários.However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. No entanto, como os bancos de dados secundários são restritos ao acesso somente leitura, não é possível criar estatísticas nos bancos de dados secundários.However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

Para resolver esse problema, a réplica secundária cria e mantém estatísticas temporárias para bancos de dados secundários em tempdb.To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. O sufixo _readonly_database_statistic é anexado ao nome das estatísticas temporárias para diferenciá-las das permanentes que persistem do banco de dados primário.The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database.

Somente o SQL ServerSQL Server pode criar e atualizar estatísticas temporárias.Only SQL ServerSQL Server can create and update temporary statistics. No entanto, você pode excluir estatísticas temporárias e monitorar suas propriedades usando as mesmas ferramentas que você utiliza para estatísticas permanentes:However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

  • Exclua estatísticas temporárias usando a instrução DROP STATISTICSTransact-SQLTransact-SQL .Delete temporary statistics using the DROP STATISTICSTransact-SQLTransact-SQL statement.

  • Para monitorar as estatísticas, use as exibições de catálogo sys.stats e sys.stats_columns .Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats inclui uma coluna, is_temporary, para indicar quais estatísticas são permanentes e quais são temporárias.sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

Não há suporte para a atualização automática de estatísticas para tabelas com otimização de memória na réplica primária ou secundária.There is no support for auto-statistics update for memory-optimized tables on the primary or secondary replica. Você deve monitorar o desempenho de consulta e os planos na réplica secundária e atualizar manualmente as estatísticas na réplica primária quando necessário.You must monitor query performance and plans on the secondary replica and manually update the statistics on the primary replica when needed. No entanto, as estatísticas ausentes são criadas automaticamente na réplica primária e secundária.However, the missing statistics are automatically created both on primary and secondary replica.

Para obter mais informações sobre estatísticas do SQL Server, veja Estatísticas.For more information about SQL Server statistics, see Statistics.

Nesta seção:In This Section:

Estatísticas permanentes obsoletas em bancos de dados secundáriosStale Permanent Statistics on Secondary Databases

SQL ServerSQL Server detecta quando as estatísticas permanentes em um banco de dados secundário estão obsoletas.detects when permanent statistics on a secondary database are stale. Mas alterações não podem ser feitas nas estatísticas permanentes, exceto por alterações no banco de dados primário.But changes cannot be made to the permanent statistics except through changes on the primary database. Para a otimização das consultas, o SQL ServerSQL Server cria estatísticas temporárias para tabelas baseadas em disco no banco de dados secundário e usa essas estatísticas em vez das estatísticas permanentes obsoletas.For query optimization, SQL ServerSQL Server creates temporary statistics for disk-based tables on the secondary database and uses these statistics instead of the stale permanent statistics.

Quando as estatísticas permanentes são atualizadas no banco de dados primário, elas persistem automaticamente no banco de dados secundário.When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. Em seguida, o SQL ServerSQL Server usa as estatísticas permanentes atualizadas, que são mais atuais que as estatísticas temporárias.Then SQL ServerSQL Server uses the updated permanent statistics, which are more current than the temporary statistics.

Se o grupo de disponibilidade passar por failover, as estatísticas temporárias serão excluídas em todas as réplicas secundárias.If the availability group fails over, temporary statistics are deleted on all of the secondary replicas.

Limitações e restriçõesLimitations and Restrictions

  • Como as estatísticas temporárias são armazenadas em tempdb, uma reinicialização do serviço SQL ServerSQL Server faz com que todas as estatísticas temporárias desapareçam.Because temporary statistics are stored in tempdb, a restart of the SQL ServerSQL Server service causes all temporary statistics to disappear.

  • O sufixo _readonly_database_statistic fica reservado para estatísticas geradas pelo SQL ServerSQL Server.The suffix _readonly_database_statistic is reserved for statistics generated by SQL ServerSQL Server. Você não pode usar esse sufixo na criação de estatísticas em um banco de dados primário.You cannot use this suffix when creating statistics on a primary database. Para obter mais informações, consulte Statistics.For more information, see Statistics.

Acessando tabelas com otimização de memória em uma réplica secundáriaAccessing memory-optimized tables on a Secondary Replica

Os níveis de isolamento de transação que podem ser usados com tabelas com otimização de memória em uma réplica secundária são os mesmos que na réplica primária.The transaction isolation levels that can be used with memory-optimized tables on a secondary replica are the same as on the primary replica. A recomendação é definir o nível de isolamento no nível de sessão como READ COMMITTED e definir a opção no nível de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT como ON.The recommendation is to set the session-level isolation level to READ COMMITTED and set the database-level option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON. Por exemplo:For example:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Considerações sobre o planejamento de capacidadeCapacity Planning Considerations

  • No caso de tabelas baseadas em disco, as réplicas secundárias legíveis podem exigir espaço no tempdb por duas razões:In the case of disk-based tables, readable secondary replicas can require space in tempdb for two reasons:

    • O nível de isolamento do instantâneo copia as versões de linha no tempdb.Snapshot isolation level copies row versions into tempdb.

    • As estatísticas temporárias para bancos de dados secundários são criadas e mantidas no tempdb.Temporary statistics for secondary databases are created and maintained in tempdb. As estatísticas temporárias podem causar um leve aumento no tamanho do tempdb.The temporary statistics can cause a slight increase in the size of tempdb. Para obter informações, veja Estatísticas para bancos de dados somente leitura, mais adiante nesta seção.For more information, see Statistics for Read-Only Access Databases, later in this section.

  • Quando você configura o acesso de leitura para uma ou mais réplicas secundárias, os bancos de dados primários adicionam 14 bytes de sobrecarga nas linhas de dados excluídas, modificadas ou inseridas para armazenar ponteiros para versões de linha nos bancos de dados secundários para tabelas baseadas em disco.When you configure read-access for one or more secondary replicas, the primary databases add 14 bytes of overhead on deleted, modified, or inserted data rows to store pointers to row versions on the secondary databases for disk-based tables. Essa sobrecarga de 14 bytes transferida aos bancos de dados secundários.This 14-byte overhead is carried over to the secondary databases. Como a sobrecarga de 14 bytes é adicionada a linhas de dados, podem ocorrer divisões de página.As the 14-byte overhead is added to data rows, page splits might occur.

    Os dados de versão de linha não são gerados pelos bancos de dados primários.The row version data is not generated by the primary databases. Em vez disso, os bancos de dados secundários geram as versões de linha.Instead, the secondary databases generate the row versions. No entanto, o controle de versão de linha aumenta o armazenamento de dados nos bancos de dados primários e secundários.However, row versioning increases data storage in both the primary and secondary databases.

    A adição dos dados de versão de linha depende do isolamento de instantâneo ou da configuração de nível de isolamento do instantâneo (RCSI) confirmada por leitura no banco de dados primário.The addition of the row version data depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. A tabela abaixo descreve o comportamento do controle de versão em um banco de dados secundário legível em configurações diferentes para tabelas baseadas em disco.The table below describes the behavior of versioning on a readable secondary database under different settings for disk based tables.

    Réplica secundária legível?Readable secondary replica? O isolamento do instantâneo ou nível RCSI está habilitado?Snapshot isolation or RCSI level enabled? Banco de dados primárioPrimary Database Banco de dados secundárioSecondary Database
    NãoNo NãoNo Nenhuma versão de linha ou sobrecarga de 14 bytesNo row versions or 14-byte overhead Nenhuma versão de linha ou sobrecarga de 14 bytesNo row versions or 14-byte overhead
    NãoNo SimYes Versões de linha e sobrecarga de 14 bytesRow versions and 14-byte overhead Nenhuma versão de linha, mas sobrecarga de 14 bytesNo row versions, but 14-byte overhead
    SimYes NãoNo Nenhuma versão de linha, mas sobrecarga de 14 bytesNo row versions, but 14-byte overhead Versões de linha e sobrecarga de 14 bytesRow versions and 14-byte overhead
    SimYes SimYes Versões de linha e sobrecarga de 14 bytesRow versions and 14-byte overhead Versões de linha e sobrecarga de 14 bytesRow versions and 14-byte overhead

Tarefas relacionadasRelated Tasks

Conteúdo relacionadoRelated Content

Consulte TambémSee Also

Visão geral dos grupos de disponibilidade AlwaysOn (SQL Server) Overview of Always On Availability Groups (SQL Server)
Sobre o acesso de conexão de cliente a réplicas de disponibilidade (SQL Server) About Client Connection Access to Availability Replicas (SQL Server)
Ouvintes do grupo de disponibilidade, conectividade de cliente e failover de aplicativos (SQL Server) Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)
EstatísticasStatistics