Solution Download
JobCollisionDetector.zip (8.5 KB)
Summary
NAA (NEA) does not currently have the ability for administrators of the application to easily identify which jobs run at the same time.
Issue
NAA allows for the scheduling of a multitude of different jobs to run at various times throughout the week/day and even concurrently if desired. As useful as that functionality is, the increased complexity of multiple running/scheduled jobs and the limited time slots available in a day can potentially lead to conflictions in SQL or competition for system resources. This can leave an NAA admin scratching their head unsure where the problem lies or digging through lengthy historical job run time tables trying to piece together what could have gone wrong when a previous job that normally ran fine all of a sudden runs for 2 days long and causes the NAA server to slow down to unusable levels.
Instructions
The solution is the JobCollisionDetector job which finds all jobs that were running simultaneously with a specified target job provided by the admin while highlighting ones with the highest overlap.
Job Collision Detector job
The JobCollisionDetector job can be ran ad-hoc by an NAA admin to help identify where job confliction issues might lie.
The job requires a target job name and date the target job ran to be configured.
After the JobCollisionDetector job is run, the Concurrent Jobs report can then be viewed to assist with highlighting any jobs that were running at the same time as the target job.
The JobName, JobType, OverlapMinutes, StartTime, EndTime, AnalysisTasks, Aborted, and DurationTime columns are available within the report.
The JobName column identifies the name of the jobs found that were running at the same time as the target job along with the name of the target job that was previously configured.
The JobType column specifies whether the job listed is either the TARGET JOB or a CONCURRENT JOB that ran the same time as the target job.
The OverlapMinutes column calculates how many minutes the concurrent job and the target job overlapped with one another while running. This is a very helpful column for determining if certain jobs might have conflicted with one another if they have a similar lengthy amount of minutes outside the norm.
The StartTime column reports on when the identified job started running.
The EndTime column reports on when the identified job stopped running.
The AnalysisTasks column will show a YES if the job had any analysis tasks that were ran during the captured run or a NO if none were ran. This is helpful for identifying jobs that might be more likely to conflict with others within SQL. If a job had no running analysis tasks, then the issue is unlikely (not guaranteed) to be one of those jobs. This info should be used supplementally when performing investigation.
The Aborted column identifies any jobs that were forcefully aborted before they could complete normally.
The DurationTime column calculates the total run time (in seconds) of the specified job.
Note: If a job has no end time (either currently running or failed to complete properly from a previous run) and started before the target job, then that job will not be shown as a concurrent job on the report. However, if that same job started after the target job, then it will be shown despite not having an end time.
For further details on any of the jobs, copy the job name from the Concurrent Jobs widget at the top of the report, activate the filters in the All Tasks (Recent Runs) widget at the bottom of the report, and paste the job under JobName.
You can then see specific details on which specific types of tasks (Query, Analysis, Report) ran on the job and at which specific times.
No single job in NAA does everything. You need to use the right tools for the right job. This is just another helpful tool to add to your tool belt. Time to get to work!
