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.