Running DML on a Primary AG Instance

• SteveOC

  Ten Centuries

  Points: 1259

  More actions

  April 9, 2025 at 5:31 pm

  #4576131

  Kind of a DML question - but specific to an AG instance running on SQL Server 2019 - so starting here.

  We have 3 AGs and 3 SQL Server 2019 Instances.

  I cannot so easily remember the AG listener names so I often use the Primary Instance name instead - thinking that other than in a failover situation it made no difference........

  I connect to the Instance name and run a piece of DML - it does not do what I expect so I think it is broken.

  I strip the DML down to this :

  Select Count(*) from Files  - (my Table is called Files).

  What is happening is that it is pulling nearly 1 Million rows back to the screen - as if I do a Select * - and then it is doing the count. it takes 22 seconds and I get 2 result sets - one with the rows and one with the count.

  If I run the same DML of the secondaries it works as expected - runs sub-second and only returns 1 result set (Count).

  If I then connect to the Primary AG Listener name and run the same DML it works as expected - 1 result set.

  So this behaviour is very specific to running the DML on the Primary Instance.

  Yes I know I should use the AG Listener Name - and will make an effort to - but I cannot find an explanation for this behaviour via Google (or can't construct the correct wording for the search).

  Can anybody point me to any resources to better understand this as I am ramping up to move more DBs into AGs.

  Thanks.

  Steve O.

• Site Owners

  SSC Guru

  Points: 80356

  More actions

  April 10, 2025 at 6:10 pm

  #4576907

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.