Sql-server – SQL Server agent job dependency

I don't think you can work with that single job to get that exact schedule. You might want to consider having another job that disables/enables the job for the period of time that you don't want it to run. Another solution would be to add another conditional logic to your existing job and do a check on the current day of the week and what time it is, and decide whether or not to execute your job's logic.

A few alternatives here, but I think you'll have to get creative with your relatively complex schedule.

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