Same select query is running long on Primary node but quickly finishes on secondary node

Yashwant Vishwakarma 116 Reputation points
2021-08-17T14:00:43.917+00:00

We have two node Always On Cluster configured SQL Server ( Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64) Apr 28 2021 10:32:18 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 )

Same select query is running long on Primary node utilizing high cpu but quickly finishes on secondary, not sure why this is the situation when both nodes are in sync.

Any thoughts and inputs to find RCA and resolution....

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,977 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-17T21:36:24.487+00:00

    As Dan says, start with looking at the execution plans. One possible explanation that comes to mind is that the plans are sniffed for different parameter values.

    0 comments No comments

  2. Seeya Xi-MSFT 16,471 Reputation points
    2021-08-18T07:01:56.773+00:00

    Hi @Yashwant Vishwakarma ,

    Here is a similar thread.
    You can check some the performance metrics (CPU%, Memory, buffers) on two nodes.

    Best regards,
    Seeya


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.