大网数据广告活动
数据库与应用安全

Query performance degradation on secondary replicas

· 52CE 我爱测

This article helps you resolve the problem where you experience query performance issues on read-only secondary replicas.

This article helps you resolve the problem where you experience query performance issues on read-only secondary replicas.

Original product version:   SQL Server
Original KB number:   4040549

Symptoms

Assume that you have a Microsoft SQL Server Always On Availability Group member database that contains one or more large tables that have a clustered row-store index. A query of one or more of the large tables completes faster on the primary replica than on a secondary replica.

Notes

  • The query causes a clustered index scan of a large part of the table.
  • The query uses the NOLOCK hint.
  • The execution plan operators and operator order are identical for both fast and slow executions.
  • Querying sys.dm_db_index_physical_stats reveals significant fragmentation of the clustered index.
  • Unjoining the database from the Always On Availability Group improves performance on the same (former) secondary replica instance, so that it is similar to performance on the primary replica.

Cause

When Snapshot Isolation is enforced, NOLOCK hints are ignored. The discrepancy in execution duration between the primary and secondary replicas occurs because the NOLOCK hint is ignored on the read-only secondary replica where Snapshot Isolation is enforced but not on the primary replica where Snapshot Isolation is not enforced by default. This causes the scan of the clustered index to have key order enforced on the secondary replica. On the primary replica, the NOLOCK hint takes precedence and does affect behavior. When the clustered index is highly fragmented, the enforcement of key order for the scan on the read-only secondary replica causes SQL Server to issue single-page reads. But on the primary replica, SQL Server does an allocation unit scan to read multiple pages per IO request.

Resolution

To fix this issue, rebuild the index on the primary replica. This operation then propagates to secondary replicas. For more information, see Recommendations for Index Maintenance with Always On Availability Groups.

More information

The SET STATISTICS IO and execution plan Actual I/O Statistics information may not help in diagnosis when this problem occurs. These give you information about the number of pages that are read but not the number of IO operations to read the pages.

Instead, first look for fragmentation of the clustered index. Additionally, you might collect the Performance Monitor IO Read Operations/sec and IO Read Bytes/sec Process Counters two times-once when you run the query with the database in the Availability Group, and again from the same instance when the database is removed from the Availability Group and brought online. If index fragmentation is causing single-page reads on the secondary replica but not on the primary replica, you would expect to see a larger number of read IO/sec and a smaller number of read bytes/sec when the database is in the Availability Group as compared to when it's not.

Additionally, this behavior may occur but not noticeably manifest in all environments. For example, an IO subsystem that can handle the increased level of IO/sec with minimal latency and similar throughput could allow for this issue to go unnoticed.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/25/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。