卸载对 AlwaysOn 可用性组的次要副本的只读工作负荷Offload read-only workload to secondary replica of an Always On availability group

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Always On 可用性组Always On availability groups 活动辅助功能包括支持对一个或多个次要副本的只读访问(可读次要副本 )。The Always On 可用性组Always On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). 可读辅助副本可以处于异步提交可用性模式或同步提交可用性模式。A readable secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode. 可读辅助副本允许对其所有辅助数据库的只读访问。A readable secondary replica allows read-only access to all its secondary databases. 但是,可读辅助数据库并非设置为只读。However, readable secondary databases are not set to read-only. 它们是动态的。They are dynamic. 当对相应主数据库的更改应用到某一给定的辅助数据库时,该辅助数据库将更改。A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. 对于典型的辅助副本,包括持久内存优化表,辅助数据库中的数据接近实时。For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. 此外,全文检索与辅助数据库同步。Furthermore, full-text indexes are synchronized with the secondary databases. 在许多情况下,主数据库和相应的辅助数据库之间的数据滞后时间只有几秒钟。In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

在主数据库中进行的安全设置会对辅助数据库永久保留。Security settings that occur in the primary databases are persisted to the secondary databases. 这包括用户、数据库角色和应用程序角色及其各自的权限;如果对主数据库启用了透明数据加密 (TDE),还将包括 TDE。This includes users, database roles, and applications roles together with their respective permissions and transparent data encryption (TDE), if enabled on the primary database.

备注

尽管您无法将数据写入辅助数据库,但可以在承载辅助副本的服务器实例上写入读写数据库,包括用户数据库和 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.

Always On 可用性组Always On availability groups 还支持对可读辅助副本(只读路由)的读意向连接请求的重新路由。also supports the re-routing of read-intent connection requests to a readable secondary replica (read-only routing). 有关只读路由的详细信息,请参阅 使用侦听程序连接到只读次要副本(只读路由)For information about read-only routing, see Using a Listener to Connect to a Read-Only Secondary Replica (Read-Only Routing).

优势Benefits

指定与可读辅助副本的只读连接具有以下优点:Directing read-only connections to readable secondary replicas provides the following benefits:

  • 从主副本卸下辅助副本只读工作负荷,以便将资源用于关键任务工作负荷。Offloads your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. 如果具有关键任务读工作负荷或不能滞后的工作负荷,应在主副本上运行它。If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.

  • 提高承载可读辅助副本的系统的投资回报率。Improves your return on investment for the systems that host readable secondary replicas.

此外,可读辅助副本对只读操作提供稳定的支持,如下所示:In addition, readable secondaries provide robust support for read-only operations, as follows:

  • 有关可读辅助数据库的自动临时统计信息可优化对基于磁盘的表的只读查询。Automatic temporary statistics on readable secondary database optimize read-only queries on disk-based tables. 对于内存优化表,将自动创建缺少的统计信息。For memory-optimized tables, the missing statistics are created automatically. 但不会自动更新旧统计信息。However, there is no auto-update of stale statistics. 您需要手动更新主副本的统计信息。You will need to manually update the statistics on the primary replica. 有关详细信息,请参阅本主题后面的 只读访问数据库的统计信息For more information, see Statistics for Read-Only Access Databases, later in this topic.

  • 基于磁盘的表的只读工作负荷使用行版本控制来消除辅助数据库上的阻塞争用。Read-only workloads for disk-based tables use row versioning to remove blocking contention on the secondary databases. 对辅助数据库运行的所有查询都自动映射到快照隔离事务级别,即使在显式设置了其他事务隔离级别时也是如此。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. 此外,所有锁定提示都将被忽略。Also, all locking hints are ignored. 这消除了读取器/编写器的争用。This eliminates reader/writer contention.

  • 基于持久的内存优化表的只读工作负荷访问数据的方式与主数据库中的访问方式相同,使用具有相同事务隔离级别限制的本机存储过程或 SQL 互操作性(请参阅 数据库引擎中的隔离级别)。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). 对主副本运行的报表工作负荷或只读查询可以直接用于辅助副本,无需任何更改。Reporting workload or read-only queries running on the primary replica can be run on the secondary replica without requiring any changes. 同样,对辅助副本运行的报表工作负荷或只读查询也可以直接用于主副本,无需任何更改。Similarly, a reporting workload or read-only queries running on a secondary replica can be run on the primary replica without requiring any changes. 与基于磁盘的表相似,对辅助数据库运行的所有查询都自动映射到快照隔离事务级别,即使在显式设置了其他事务隔离级别时也是如此。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.

  • 对于辅助副本上的基于磁盘的表和内存优化表类型,都可以对表变量执行 DML 操作。DML operations are allowed on table variables both for disk-based and memory-optimized table types on the secondary replica.

可用性组先决条件Prerequisites for the Availability Group

  • 可读辅助副本(必需)Readable secondary replicas (required)

    数据库管理员需要配置一个或多个副本,这样,在辅助角色下运行时,可允许所有连接(仅针对只读访问)或只允许读意向连接。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.

    备注

    或者,数据库管理员可以配置任何可用性副本,以便在主角色下运行时排除只读连接。Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.

    有关详细信息,请参阅本主题后面的 关于对可用性副本的客户端连接访问 (SQL Server)之类的系统数据库。For more information, see About Client Connection Access to Availability Replicas (SQL Server).

  • 可用性组侦听器Availability group listener

    为支持只读路由,可用性组必须具备 可用性组侦听程序To support read-only routing, an availability group must possess an availability group listener. 只读客户端必须将其连接请求定向到此侦听器,并且客户端的连接字符串必须将应用程序意向指定为“只读”。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." 也就是说,它们必须是 读意向连接请求That is, they must be read-intent connection requests.

  • 只读路由Read only routing

    “只读路由” 指的是 SQL Server 将定向到可用性组侦听器的传入的读意向连接请求路由到可用的可读辅助副本的能力。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. 只读路由的先决条件如下:The prerequisites for read-only routing are as follows:

    • 为支持只读路由,可读辅助副本需要一个只读路由 URL。To support read-only routing, a readable secondary replica requires a read-only routing URL. 此 URL 仅在本地副本在辅助角色下运行时起作用。This URL takes effect only when the local replica is running under the secondary role. 必须根据需要在逐个副本的基础上指定只读路由 URL。The read-only routing URL must be specified on a replica-by-replica basis, as needed. 每个只读路由 URL 都用于将读意向请求路由到一个特定的可读辅助副本。Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. 通常,向每个可读辅助副本分配一个只读路由 URL。Typically, every readable secondary replica is assigned a read-only routing URL.

    • 要在其作为主副本时支持只读路由的每个可用性副本都要求一个只读路由列表。Each availability replica that is to support read-only routing when it is the primary replica requires a read-only routing list. 一个给定的只读路由列表仅在本地副本在主角色下运行时才起作用。A given read-only routing list takes effect only when the local replica is running under the primary role. 必须根据需要在逐个副本的基础上指定此列表。This list must be specified on a replica-by-replica basis, as needed. 通常,每个只读路由列表中将包含各只读路由 URL,并且在列表的末尾具有本地副本的 URL。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.

      备注

      读意向连接请求可在不同副本中达到负载平衡。Read-intent connection requests can be load-balanced across replicas. 有关详细信息,请参阅 在只读副本间配置负载平衡For more information, see Configure load-balancing across read-only replicas.

    有关详细信息,请参阅 为可用性组配置只读路由 (SQL Server)For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).

备注

有关可用性组侦听程序的信息,以及只读路由的详细信息,请参阅 可用性组侦听程序、客户端连接和应用程序故障转移 (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).

限制和局限Limitations and Restrictions

不完全支持某些操作,如下所示:Some operations are not fully supported, as follows:

  • 当启用可读副本以便读取时,它便开始接收与其辅助数据库的连接。As soon as a readable replica is enabled for read, it can start accepting connections to its secondary databases. 但是,如果在主数据库上有活动事务,行版本将不会在相应的辅助数据库上完全可用。However, if any active transactions exist on a primary database, the row versions will not be fully available on the corresponding secondary database. 必须提交或回滚在配置辅助副本时主副本上存在的所有活动事务。Any active transactions that existed on the primary replica when the secondary replica was configured must commit or roll back. 在此过程完成前,对辅助数据库的事务隔离级别映射将不完整,并且查询被暂时阻塞。Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.

    警告

    运行长时间运行的事务将影响保存的版本控制行数,对于基于磁盘的表和内存优化表都是如此。Running long transactions impacts the number of versioned rows kept, both for disk-based and memory-optimized tables.

  • 在具有内存优化表的辅助数据库上,即使始终对内存优化表生成行版本,也会阻塞查询,除非在启用辅助副本以便读取时主副本上的所有活动事务都已完成。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. 这将确保基于磁盘的表和内存优化表可同时用于报告工作负荷和只读查询。This ensures that both disk-based and memory-optimized tables are available to the reporting workload and read-only queries at the same time.

  • 属于可读辅助副本的辅助数据库不支持更改跟踪和变更数据捕获:Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:

    • 在辅助数据库上显式禁用更改跟踪。Change tracking is explicitly disabled on secondary databases.

    • 不能仅对次要副本数据库启用变更数据捕获。Change Data Capture cannot be enabled only on a secondary replica database. 可以在主要副本数据库上启用变更数据捕获,并且可以从次要副本数据库上使用函数的 CDC 表读取所做的更改。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.

  • 由于读操作会映射到快照隔离事务级别,因此,一个或多个辅助副本上的事务会阻止在主副本上清除虚影记录。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. 当所有辅助副本都不再需要虚影记录时,虚影记录清除任务将自动清除主副本上基于磁盘的表的虚影记录。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. 这类似于您在主副本上运行事务时执行的操作。This is similar to what is done when you run transaction(s) on the primary replica. 在辅助数据库上的极端情况下,需要终止正在阻塞虚影清除的长时间运行的读查询。In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. 请注意,如果辅助副本断开连接或数据移动在辅助数据库上挂起,可能阻塞虚影清除。Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. 此状态还会阻止日志截断,因此,如果此状态持续,则我们建议您从可用性组中删除此辅助数据库。This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group. 内存优化表没有虚影记录清除问题,因为行版本保存在内存中,独立于主副本上的行版本。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.

  • 如果文件包含辅助副本上仍需要的虚影记录,则主副本上对包含基于磁盘的表的文件的 DBCC SHRINKFILE 操作可能失败。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.

  • SQL Server 2014 (12.x)SQL Server 2014 (12.x)中开始,甚至在主副本由于用户操作或失败而脱机时可读辅助副本仍可保持联机状态。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. 但是,只读路由并不在此情况下工作,因为可用性组侦听器也处于脱机状态。However, read-only routing does not work in this situation because the availability group listener is offline as well. 对于只读工作负荷,客户端必须直接连接到只读辅助副本。Clients must connect directly to the read-only secondary replicas for read-only workloads.

备注

如果你在托管可读次要副本的服务器实例上查询 sys.dm_db_index_physical_stats 动态管理视图,则可能会遇到 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. 这是因为此动态管理视图获取指定用户表或视图的 IS 锁,而该锁可能阻止 REDO 线程对该用户表或视图的 X 锁请求。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.

性能注意事项Performance Considerations

此节讨论可读辅助数据库的几个性能注意事项This section discusses several performance considerations for readable secondary databases

本节内容:In This Section:

数据滞后时间Data Latency

如果您的只读工作负荷可以容忍一定程度的数据滞后,则实现对辅助副本的只读访问很有用。Implementing read-only access to secondary replicas is useful if your read-only workloads can tolerate some data latency. 在数据滞后不可接受的情况下,请考虑对主副本运行只读工作负荷。In situations where data latency is unacceptable, consider running read-only workloads against the primary replica.

主副本将主数据库上的更改日志记录发送到辅助副本。The primary replica sends log records of changes on primary database to the secondary replicas. 在每个辅助数据库上,专用重做线程应用这些日志记录。On each secondary database, a dedicated redo thread applies the log records. 在读访问权限的辅助数据库上,给定的数据更改不显示在查询结果中,直到包含更改的日志记录已应用到辅助数据库并且已在主数据库上提交事务。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.

这意味着在主副本和辅助副本之间将会存在一定程度的滞后时间,通常只需几秒钟。This means that there is some latency, usually only a matter of seconds, between the primary and secondary replicas. 但是,在极少数情况下,例如在网络问题降低了网络吞吐量的情况下,滞后时间可能会较长。In unusual cases, however, for example if network issues reduce throughput, latency can become significant. 在存在 I/O 瓶颈和数据移动操作处于挂起状态时,将增加滞后时间。Latency increases when I/O bottlenecks occur and when data movement is suspended. 若要监视挂起的数据移动,可以使用 AlwaysOn 面板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.

具有内存优化表的数据库上的数据延迟Data Latency on databases with memory-optimized tables

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,存在关于活动次要副本上数据延迟的特殊注意事项 - 请参阅SQL Server 2014 (12.x)SQL Server 2014 (12.x)活动次要副本:可读次要副本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. 启动 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 在内存优化表的数据延迟方面没有特殊注意事项。Starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) there are no special considerations around data latency for memory-optimized tables. 内存优化表的预期数据延迟相当于基于磁盘的表的延迟。The expected data latency for memory-optimized tables is comparable to the latency for disk-based tables.

只读工作负荷的影响Read-Only Workload Impact

为辅助副本配置只读访问时,辅助数据库上的只读工作负荷占用来自重做线程的系统资源,如 CPU 和 I/O(对于基于磁盘的表),特别是在基于磁盘的表的只读工作负荷大量占用 I/O 的情况下。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. 访问内存优化表时没有 IO 影响,因为所有行都驻留在内存中。There is no IO impact when accessing memory-optimized tables because all the rows reside in memory.

此外,辅助副本上的只读工作负荷还会阻止通过日志记录应用的数据定义语言 (DDL) 发生更改。Also, read-only workloads on the secondary replicas can block data definition language (DDL) changes that are applied through log records.

  • 虽然读取操作由于行版本控制而不会占用共享锁,但这些操作会占用架构稳定性 (Sch-S) 锁,这些锁可能会阻塞正在应用 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. DDL 操作包括 ALTER/DROP 表和视图,但不包括存储过程的 DROP 或 ALTER。DDL operations include ALTER/DROP tables and Views but not DROP or ALTER of stored procedures. 因此,如果删除基于磁盘的表或内存优化表,请对主副本执行。So for example, if you drop a table disk-based or memory-optimized, on primary. 当 REDO 线程处理日志记录以删除表时,必须获取表的 SCH_M 锁,并且必须可通过运行查询访问表阻塞。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. 对于主副本也是如此,只是删除表是用户会话的一部分,而不是 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.

  • 还有其他阻塞内存优化表。There is additional blocking Memory-Optimized Tables. 如果辅助副本上存在本机存储过程的并发执行,删除本机存储过程可以导致 REDO 线程阻塞。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. 对于主副本也是如此,只是删除存储过程是用户会话的一部分,而不是 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.

应了解与生成查询有关的最佳实践,并且在辅助数据库中应用这些最佳实践。Be aware of best practices around building queries, and exercise those best practices in the secondary databases. 例如,将需要长时间运行的查询(如数据聚合)安排在低活动期间进行。For example, schedule long-running queries such as aggregations of data during times of low activity.

备注

如果 REDO 线程被次要副本上的查询阻塞,将引发 sqlserver.lock_redo_blocked XEvent。If a redo thread is blocked by queries on a secondary replica, the sqlserver.lock_redo_blocked XEvent is raised.

索引Indexing

若要优化可读辅助副本上的只读工作负荷,您可能需要对辅助数据库中的表创建索引。To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. 因为您无法在辅助数据库上进行架构或数据更改,所以应在主数据库中创建索引,并且允许更改通过重做进程传输到辅助数据库。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.

若要监视辅助副本上的索引使用活动,请查询 sys.dm_db_index_usage_stats动态管理视图的 user_seeksuser_scansuser_lookups 列。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.

只读访问数据库的统计信息Statistics for Read-Only Access Databases

表和索引视图的列的统计信息用于优化查询计划。Statistics on columns of tables and indexed views are used to optimize query plans. 对于可用性组,作为应用事务日志记录操作的一部分,在主数据库上创建和维护的统计信息将自动保留在辅助数据库中。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. 但是,辅助数据库上的只读工作负荷需要的统计信息可能与在主数据库上创建的统计信息不同。However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. 但是,因为辅助数据库被限制为只读访问,所以无法在辅助数据库上创建统计信息。However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

为了解决此问题,辅助副本在 tempdb中创建和维护辅助数据库的临时统计信息。To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. 将在临时统计信息名称后追加后缀 The suffix _readonly_database_statistic,以便将临时统计信息与主数据库永久保存的永久统计信息加以区分。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.

只有 SQL ServerSQL Server 可以创建和更新临时统计信息。Only SQL ServerSQL Server can create and update temporary statistics. 但是,您可以借助用于永久统计信息的相同工具来删除临时统计信息和监视其属性:However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

  • 使用 DROP STATISTICSTransact-SQLTransact-SQL 语句删除临时统计信息。Delete temporary statistics using the DROP STATISTICSTransact-SQLTransact-SQL statement.

  • 使用 sys.statssys.stats_columns 目录视图监视统计信息。Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats 包含一个 is_temporary列,用于指示哪些统计信息是永久的,哪些统计信息是临时的。sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

不支持自动更新主副本或辅助副本上内存优化表的统计信息。There is no support for auto-statistics update for memory-optimized tables on the primary or secondary replica. 必须监视辅助副本上的查询性能和计划,在需要时手动更新主副本上的统计信息。You must monitor query performance and plans on the secondary replica and manually update the statistics on the primary replica when needed. 不过,会在主副本和辅助副本上自动创建缺少的统计信息。However, the missing statistics are automatically created both on primary and secondary replica.

有关 SQL Server 统计信息的详细信息,请参阅 统计信息For more information about SQL Server statistics, see Statistics.

本节内容:In This Section:

辅助数据库上陈旧的永久统计信息Stale Permanent Statistics on Secondary Databases

SQL ServerSQL Server 可检测辅助数据库上的永久统计信息的过时时间。detects when permanent statistics on a secondary database are stale. 但是,除了通过主数据库进行更改外,不能对永久统计信息进行更改。But changes cannot be made to the permanent statistics except through changes on the primary database. 为了进行查询优化, SQL ServerSQL Server 在辅助数据库上为基于磁盘的表创建临时统计信息并使用它们来替代过时的永久统计信息。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.

永久统计信息在主数据库上进行更新后,自动将它们永久保存到辅助数据库。When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. 然后, SQL ServerSQL Server 使用更新的永久统计信息,该信息比临时统计信息要新。Then SQL ServerSQL Server uses the updated permanent statistics, which are more current than the temporary statistics.

如果可用性组进行故障转移,则在所有辅助副本上删除临时统计信息。If the availability group fails over, temporary statistics are deleted on all of the secondary replicas.

限制和局限Limitations and Restrictions

  • 因为临时统计信息存储于 tempdb中,所以重新启动 SQL ServerSQL Server 服务将导致所有临时统计信息消失。Because temporary statistics are stored in tempdb, a restart of the SQL ServerSQL Server service causes all temporary statistics to disappear.

  • 后缀 suffix _readonly_database_statistic 是为 SQL ServerSQL Server生成的统计信息预留的。The suffix _readonly_database_statistic is reserved for statistics generated by SQL ServerSQL Server. 在主数据库上创建统计信息时不能使用此后缀。You cannot use this suffix when creating statistics on a primary database. 有关详细信息,请参阅统计信息For more information, see Statistics.

访问辅助副本上的内存优化表Accessing memory-optimized tables on a Secondary Replica

次要副本上的事务隔离级别与主要副本上的事务隔离级别相同,都可以与内存优化表结合使用。The transaction isolation levels that can be used with memory-optimized tables on a secondary replica are the same as on the primary replica. 建议你将会话级的隔离级别设置为 READ COMMITTED,将数据库级选项 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT 设置为 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. 例如: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  
  

容量规划注意事项Capacity Planning Considerations

  • 对于基于磁盘的表,可读次要副本出于以下两个原因需要占用 tempdb 中的空间:In the case of disk-based tables, readable secondary replicas can require space in tempdb for two reasons:

    • 快照隔离级别会将行版本复制到 tempdb中。Snapshot isolation level copies row versions into tempdb.

    • tempdb中创建和维护辅助数据库的临时统计信息。Temporary statistics for secondary databases are created and maintained in tempdb. 临时统计信息会促使略微增大 tempdb的大小。The temporary statistics can cause a slight increase in the size of tempdb. 有关详细信息,请参阅本节后面的 只读访问数据库的统计信息For more information, see Statistics for Read-Only Access Databases, later in this section.

  • 在配置对一个或多个辅助副本的读访问时,主数据库将对已删除、修改或插入的数据行添加 14 个字节的系统开销,以便为基于磁盘的表存储指向辅助数据库上的行版本的指针。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. 这 14 个字节开销将转入辅助数据库。This 14-byte overhead is carried over to the secondary databases. 在向数据行添加 14 个字节的系统开销时,可能会发生页拆分。As the 14-byte overhead is added to data rows, page splits might occur.

    行版本数据不由主数据库生成。The row version data is not generated by the primary databases. 而是辅助数据库生成行版本。Instead, the secondary databases generate the row versions. 但是,行版本控制在主数据库和辅助数据库中都会增加数据存储。However, row versioning increases data storage in both the primary and secondary databases.

    行版本数据的增加依赖于主数据库上的快照隔离或读提交快照隔离 (RCSI) 级别设置。The addition of the row version data depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. 下表介绍可读辅助数据库上基于磁盘的表的不同设置下的版本控制行为。The table below describes the behavior of versioning on a readable secondary database under different settings for disk based tables.

    可读辅助副本?Readable secondary replica? 启用了快照隔离或 RCSI 级别隔离?Snapshot isolation or RCSI level enabled? 主数据库Primary Database 辅助数据库Secondary Database
    No No 无行版本或 14 个字节的系统开销No row versions or 14-byte overhead 无行版本或 14 个字节的系统开销No row versions or 14-byte overhead
    No Yes 行版本和 14 个字节的系统开销Row versions and 14-byte overhead 无行版本但有 14 个字节的系统开销No row versions, but 14-byte overhead
    Yes No 无行版本但有 14 个字节的系统开销No row versions, but 14-byte overhead 行版本和 14 个字节的系统开销Row versions and 14-byte overhead
    Yes Yes 行版本和 14 个字节的系统开销Row versions and 14-byte overhead 行版本和 14 个字节的系统开销Row versions and 14-byte overhead

相关任务Related Tasks

相关内容Related Content

另请参阅See Also

AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
关于对可用性副本的客户端连接访问 (SQL Server) About Client Connection Access to Availability Replicas (SQL Server)
可用性组侦听程序、客户端连接和应用程序故障转移 (SQL Server) Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)
统计信息Statistics