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.
Jayraj ChhayaPosted Jan 8, 2024, 9:25 AM
To identify the cause of the time execution mismatch between Job1 and Job2, as well as to check if any other query/job is causing an impact, you can follow these steps:
Review the Execution Plans: Compare the execution plans of the SP step for both Job1 and Job2 in their respective databases (DB1 and DB2). Look for any significant differences in the plan, such as variations in the indexes used, join methods, or data access methods. This can help identify any performance bottlenecks or inefficiencies.
Analyze Query Performance: Use SQL Server Profiler or Extended Events to capture the queries executed during the time when Job1 and Job2 are running. Look for any long-running or resource-intensive queries that may be affecting the execution time of Job1. Pay attention to queries that access the same tables or resources as the SP step in Job1.
Check for Locking and Blocking: Use SQL Server's built-in tools like sp_whoisactive or sys.dm_exec_requests to identify any blocking or locking issues during the execution of Job1. Blocking can occur when multiple queries are trying to access the same resources simultaneously, causing delays in execution.
Monitor Resource Utilization: Check the server's resource utilization during the execution of Job1 and Job2. Monitor CPU usage, memory consumption, and disk I/O to identify any resource contention issues that may be impacting the execution time.
Review Error Logs: Check the SQL Server error logs for any error messages or warnings related to Job1 or the SP step. Look for any indications of performance issues or conflicts with other queries/jobs.
Tuhin PaulPosted Jan 8, 2024, 9:23 AM
If you are using SQL Server 2016 or later, utilize the Query Store feature to track query performance over time. This can help identify changes in execution plans and performance regressions.
Tuhin PaulPosted Jan 8, 2024, 9:23 AM
Investigate if there are any locks or blocking issues during the execution of Job1 on DB1. Use SQL Server Management Studio or system views like sys.dm_tran_locks to identify potential blocking scenarios.
Tuhin PaulPosted Jan 8, 2024, 9:23 AM
Compare the execution plans for the stored procedure in both databases (DB1 and DB2). Use tools like SQL Server Management Studio (SSMS) to analyze the execution plans.
Use SQL Server Profiler or Extended Events to capture and analyze the queries executed during the execution of Job1 and Job2.