Troubleshooting Time Discrepancies in Parallel Jobs

Rohit 231 Reputation points
2024-01-08T08:42:29.23+00:00

Hello All,
Good Day!

I have 2 databases, DB1 & DB2. There are 2 set of identical jobs Job1 & Job2. Job1 is used to populate data to DB1 and Job2 is used to populate data to DB2.
All the jobs (including Job1 & Job2) have a step in which it executes a Stored Procedure (SP) which changes the schema of the table mentioned in the parameter from XYZ to ABC.
Job1 & Job2 are triggered on the same time. At a certain time, other jobs are also executing parallelly which also executes the same step of the SP mentioned above on both the databases (but for different tables)
Now the Job1 is executing parallelly along with other jobs the same SP step (in DB1) executes for more than 1 hour where as Job2 executing parallelly along with other jobs is executing the same SP step in DB2 for less than 10 seconds.

How do I identify the cause of this time execution mismatch also how do I check the logs to identify if any other query/job is also causing an impact on this ? As Ideally job execution time should be similar across both the databases as the data & other job execution time is also same. 

Thanks for your help in advance.

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Oury Ba-MSFT 20,186 Reputation points Microsoft Employee
    2024-01-09T17:48:57.29+00:00

    @Rohit

    I was able to get the more information about your ask.

    Best place to look at job executions/related details are in the jobs.job_executions  view in your Jobs DB for the agent - jobs.job_executions (Azure Elastic Jobs) (Transact-SQL) - SQL Server | Microsoft Learn. lifecycle and last_message columns in that view may give some clues if there was any error coming from your target database(s).

    It is possible that your SP executions at the target database are waiting/deadlocking on other activities on that table. Job Agent just kicks off your script executions at the target databases and waits for them to come back. So, the delay/any blockage should be coming from the target database.

    I hope that helps

    Regards,

    Oury

    0 comments No comments

  2. Erland Sommarskog 116.5K Reputation points MVP
    2024-01-09T23:02:10.3933333+00:00

    Are you running on-prem from SQL Server Agent on in Azure SQL Database? (You have labeled your question Azure SQL Database, so I guess this is where you are running the job, but I want to make sure.)

    If I understand you correctly, the SP in question is running

    ALTER SCHEMA ABC TRANSFER XYZ.sometable
    

    The only reason that is taking an hour is that there is a lock of some sort on the table. To change the schema, you need a Sch-M lock on the table, which is incompatible with all other locks. This includes Sch-S (schema-stability) which is taken by SELECT queries if they are in READ COMMITTED SNAPSHOT or are using NOLOCK.

    When the job is running, you can use sp_who2 to see if there is any blocking. Check the Blk column. If there is a value in that column, this means that the spid on this row is blocked by the spid in the Blk column.


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.