Always On 可用性グループのセカンダリ レプリカに読み取り専用の負荷を移すOffload read-only workload to secondary replica of an Always On availability group

適用対象: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Always On 可用性グループAlways On availability groups のアクティブなセカンダリ機能では、1 つ以上のセカンダリ レプリカ (読み取り可能なセカンダリ レプリカ) への読み取り専用アクセスをサポートしています。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) も含まれます (プライマリ データベースで有効な場合)。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).


読み取り可能なセカンダリ レプリカに読み取り専用接続を割り当てることには、次の利点があります。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)

    データベース管理者は、セカンダリ ロールで実行しているときに、すべての接続 (読み取り専用アクセスのみ) または読み取りを目的とした接続のみを許可するように 1 つ以上のレプリカを構成する必要があります。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.

  • 読み取り操作はスナップショット分離トランザクション レベルにマップされるため、プライマリ レプリカでのゴースト レコードのクリーンアップが 1 つ以上のセカンダリ レプリカのトランザクションによってブロックされることがあります。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 動的管理ビューに対してクエリを実行すると、再実行のブロックという問題が発生する可能性があります。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 ロックを獲得することが原因です。IS ロックは、そのユーザー テーブルまたはビューの X ロックに関して REDO スレッドの要求をブロックする可能性があります。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:

Read-Only Workload Impact

Statistics for Read-Only Access Databases

データ待機時間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. データ移動の中断を監視するには、 Always On ダッシュボード または 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 there were special considerations around data latency on active secondaries - see 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
セカンダリ レプリカを読み取り専用アクセス用に構成した場合、セカンダリ データベースに対する読み取り専用ワークロードによって (ディスク ベース テーブルに対する読み取り専用ワークロードで大量の I/O が発生する場合には特に)、再実行スレッドの CPU や (ディスク ベース テーブルに対する) 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. 再実行スレッドは、テーブルを削除するためにログ レコードを処理する場合、テーブルに対する 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. この動作は、プライマリ レプリカでも同じです。ただし、テーブルの削除は再実行スレッドではなくユーザー セッションの一部として実行されます。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. セカンダリ レプリカで同時実行されているネイティブ ストアド プロシージャがある場合に、ネイティブ ストアド プロシージャを削除すると、再実行スレッドのブロックが発生する可能性があります。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. この動作は、プライマリ レプリカでも同じです。ただし、ストアド プロシージャの削除は再実行スレッドではなくユーザー セッションの一部として実行されます。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.


セカンダリ レプリカに対するクエリによって再実行スレッドがブロックされると、 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.


読み取り可能なセカンダリ レプリカでの読み取り専用ワークロードを最適化するには、セカンダリ データベースのテーブルにインデックスを作成できます。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_seeks列、 user_scans 列、および user_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. 一時的な統計と、(プライマリ データベースで作成されてセカンダリ データベースに保存される) 永続的な統計とを区別するために、サフィックス _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.stats カタログ ビューと sys.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.

  • サフィックス _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:

SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  

キャパシティ プランニングの注意点Capacity planning considerations

  • ディスク ベース テーブルの場合、読み取り可能なセカンダリ レプリカでは、2 つの理由から 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.

  • 1 つまたは複数のセカンダリ レプリカに対して読み取りアクセスを構成した場合、ディスク ベース テーブルについてセカンダリ データベースに行バージョンのポインターを格納するために、プライマリ データベースでデータ行の削除、変更、または挿入ごとに 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.

    行バージョン データの追加の有無は、プライマリ データベースのスナップショット分離または READ COMMITTED スナップショット分離 (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

View Availability Replica Properties (SQL Server)


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