SSIS Package slow only on one of AG Instance

Riaz 0 Reputation points
2023-05-05T05:35:42.59+00:00

Dear All,

We have 2 node availability group. We have one SSIS Package that always takes less than an hour to execute on Node1 (wheneever it is primary replica) but always takes more than 4 hours on the other node (say Node2, (wheneever it is primary replica). The resources (CPU, Memory) all are same across the nodes. Any idea what is going on?

SQL Server 2016 (Build 13.0.7024.30)

Windows 2016

Regards,

Riaz

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2023-05-05T08:40:44.54+00:00

    Hi @Riaz,

    Here is a known issue that when you process a read query on a secondary replica, the performance might be much slower than the primary replica due to frequent DIRTY_PAGE_TABLE_LOCK waits.

    You may take a carefully reference to below links to see if it could solve the issue.

    https://support.microsoft.com/en-us/topic/kb4163087-fix-performance-is-slow-for-an-always-on-ag-when-you-process-a-read-query-in-sql-server-98f384d5-b41c-8603-8bea-e29d08d8b6d0

    https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-20162017-availability-group-secondary-replica-redo-model-and-performance

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.