The jobs/events that are being frequently created and/ queued at a rate of every 5 or 10 seconds are actually set to execute every minute or 10 - 15 minutes for the periodic and automatic execution. We believe that this causing issues such as deadlocking on the underlined resources and failing the jobs.
Any advise would help. Please cc Glenn Murray and Dallas Monday.
Thanks very much.
Hello Kuldip, I have CCd Glenn and Dallas per your request. Also, please provide us with your job queue table:
SELECT TOP 1000 *
ORDER BY JobID DESC
Thank you, Tracy
Please find the attached as requested.
Thanks very much.
Hello Kuldip,I'm wondering if there is a time difference between your app server and DB server. Please check to see if there is a difference in time. Also may you provide an example of issue(page) so that I can reference from your table.
I think Tracy needs a bit more information to understand what we are seeing and questioning here.
I asked the guys to put in a ticket for this because there are a few things that we found during a little training session we had this morning that raised a few questions.
We have a SQL Server Maintenance job that runs daily at 1:30am to rebuild indexes in all user databases. This is just good DBA practice to ensure optimum indexing performance. The job only runs for 5 to 6 mins, but during this time we may experience a few deadlocks with our DSP jobs. This is not an issue if the DSP job is scheduled to run every minute, 5 mins, hour, etc. However, if this is a job that runs once a day, week, or month and it fails, then we must address it. We can work around this by making sure that no daily, weekly, monthly jobs are scheduled during this 1:30 - ~1:36am timeframe.
So, there no problems here but when I was walking the team through everything that’s involved here; Services, Queues, Jobs and where to find what, we found a couple interesting items.
1. Our Service Page Queue is setup with an End (1am) and Start (2am) time. I can only guess that this was to prevent Service Page jobs from running between the hours of 1 and 2 am when we run our index rebuild. We can adjust this, but what we found interesting was that jobs are constantly being inserted into the job queue during this blackout time. Why would we insert multiple jobs during this timeframe? What happens for a job that runs every minute, is when the timeframe expires we then have 60 jobs in the queue to execute. Example, Review Role Audit Importer runs every minute so 60 jobs are inserted between 1 and 2 am and at 2 am there are 60 of these jobs in the queue.
2. We also noticed that even some of these jobs are being shown as Type “Service Page.” We misinterpreted this as being the job queue and questioned why the Schedule Post Monitor was not waiting until the blackout period expired. I think it would be helpful if we displayed the queue.
3. We also noticed that from the Service Pages page that we display the number of Failed jobs for the Service Page, but if the Failure has been acknowledged when you click the button there are no results.
I hope this helps.
In this case they are both on the same server. I added a couple screenshots to a previous thread from Tracy.
Paul - I would ask Dallas (or Kuldip) to go to Admin>Resources>Queues and click on the Log button for the Service Pages queue. This will show the Recent Service Activity which will be specific to that queue. What he sent us in interesting but many of those service pages look they are running in a different queue (Background Events) so may not be related to the reported issue.Hope this is helpful,Kevin
Hey Dallas,Please go to Admin>Resources>Queues and click on the Log button for the Service Pages queue. This will show the Recent Service Activity which will be specific to that queue. What you sent is interesting but many of those service pages look like they are running in a different queue (Background Events) so may not be related to the reported issue.
Thanks Paul, that is a good page to view jobs by queue.
These jobs are running in a different queue but it is not apparent that they are from the All Jobs page. We should add the queueid to the All Jobs page. The Recent Service Activity page verifies nothing runs in this queue during the blackout period, but even here I think there should be some consistency with the All Jobs Page, i.e. why not show JobID, WebApp, and Added On, we should never display a guid (Service ID) and we show Job Type in the Recent Service Activity and Type on the All Jobs page.
I know this may seem nitpicky, but if we are going to troubleshoot and investigate issues from the front end rather than via the database (which we should), we need proper visibility and consistency and it needs to be intuitive.
I still question why we add multiple jobs of the same process to a queue during a blackout period. My primary concern here is if we give the ability to specify a blackout period for a service and we continue to add jobs to the queue during that period then we can create a backlog of jobs that could give users the impression that the system is not working or create contention causing deadlocks and job failures.
Dallas,I agree that the All Jobs page can definitely show more visibility with added fields to know the different jobs. I would recommend putting in a feature request for this. As to the job queue, this probably could be another feature request, a job is not added if another instance of it is in the JobQueue in a Ready status. I have seen other performance issues caused by just that.
Paul - where is this ticket at? Do we need to do anything, or should Tracy summarize and move to close?
Tracy Could you please summarize and move to close. I have given Dallas the only option to go with at this point and he has not responded so assuming he is content I think we can get this closed.
Hi Kuldip and Dallas,Adding those fields to the report is a fantastic idea!! I do hope that you decide to put in a feature request for that to be considered. Is there anything else that we can assist you with or do you have any further questions? If not, I will be closing this ticket on Friday if we do not hear from you sooner.Thank you, Tracy
The queue name now exists on the All Jobs page to provide a further level of clarity.