Sql-server – SQL server suddenly backups are slow and async_io_completion shows suspended for long time

1. Stick with UPDATE command. CURSOR will be slower for what you are trying to do.
2. Drop/disable all indexes, including those for indexed views. If you have foreign key on A.X, drop it.
3. Create index which will contain only A.B_ID, and another one for B.ID.
4. Even though you are using Simple Recovery model, the last transaction will be always in transaction log before it is flushed to disk. That's why you need to pre-grow your transaction log and set it to grow for some larger amount (eg. 100 MB).
5. Also, set data file growth to some larger amount.
6. Make sure you have enough disk space for further growth of log and data files.
7. When update finishes recreate/enable indexes which you dropped/disabled in step 2.
8. If you don't need them anymore, drop indexes created in step 3.

Edit: Since I can't comment on your original post, I'll answer here your question from Edit 4. You have 7 indexes on A.X. Index is a B-tree, and each update to that field causes B-tree to rebalance. It's faster to rebuild those indexes from scratch than to rebalance it each time.

Given all the detail disclosed…

1. From the comments for what you’ve tried, etc.

   • confirmed no blocking
   • no issues with SELECT statements from VIEWS
   • updated stats
   • rebuilt indexes
   • executed proc with runtime WITH RECOMPILE
   • no other parts of the query being an issue when you tested (other than 2. below)
   • no issues with server level resources such as disk IO, CPU, memory, etc.

2. you specifically narrowing this down to just the one statement in the SP with the logic of:

   SELECT DISTINCT CustomerZipCode, CustomerCountry INTO #temp FROM [dbo].SAP_ONE_SAP
   

3. you confirming that you’ve taken a look at the query plan and see no issues at this level

So for your question. . .

Any ideas why that might be the case? Nothing has changed with the code in the sproc and everything else seems normal. The load on the server seems to be fine as well.

Due to the fact that you have a configuration of AlwaysOn Availability Groups in an Availability Mode of Synchronous-commit mode, I can only assume this is the ROOT cause of the issue (see notes and resource link below).

There could be latency issues with committed transactions in this Availability Mode configuration due to the primary replica waiting for acknowledgement from the secondary replica that it's hardened its transaction logs before it commits its transaction on the primary.

So, not knowing all the business and infrastructure detail on your side, you may want to consider or perhaps test changing the Availability Mode to Asynchronous-commit mode since it commits its transactions without waiting for acknowledgment from the secondary replica that it has hardened its transaction logs.

You may want to check and confirm if there are issues with transaction log hardening on the secondary replica server when this occurs. If you're going over a slower WAN or MAN link perhaps, confirm no issues at network level hops, or any general server issues, etc.

If there is an issue found at one of these other levels, then fixing that should fix your original issue I would think since the primary could acknowledge quicker that the secondary replica hardened its logs and then it'd commit its transaction.

Synchronous-commit mode

An availability replica that uses this availability mode is known as a synchronous-commit replica. Under synchronous-commit mode, before committing transactions, a synchronous-commit primary replica waits for a synchronous-commit secondary replica to acknowledge that it has finished hardening the log. Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

Asynchronous-commit mode

An availability replica that uses this availability mode is known as an asynchronous-commit replica. Under asynchronous-commit mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

RESOURCE: https://msdn.microsoft.com/en-us/library/ff877884(v=sql.110).aspx