Sometimes it happens that the DV job limit is reached because of heavy server load. Maybe because a complex query is running which consumes a lot of resources or a lot of jobs are scheduled at the same time. When this happens for a longer period of time, scheduled jobs are starting to pile up. A job which is supposed to run at 6am will perhaps start at 6:15am or even later.
It would be very helpful to give scheduled jobs a priority to force their execution at their scheduled time. For example when the 6am job has the highest priority. As soon as it's 6am this job will be get the next free job slot and another lower prioritized job is postponed. Tableau schedules work this way and it is quite a handy feature.
Is it possible to implement something like this in DV?
Comment actionsOfficial comment
thanks for posting this feature request.
This is indeed a handy feature that goes into the direction of a wider topic such as Resource Governor where jobs, schedules, system and user queries can be assigned to different priority queues and processed by the system accordingly to their priorities. Resource Governor will also allow to limit the usage of resources like CPU, memory, etc.
What you described here will be part of our Resource Governor that we have already on our Roadmap after other features like Metadata Management, Clustering, Improvement of Job Scheduling and Control, etc.
As an additional info, we are also finalizing the upcoming major 2.3 that will include new features like Data Lineage, Row-based security and column masking, metadata visibility based on user permissions, Web Business Data Shop.
thank you for your response. As I understood this is a very wide and complex topic with room for many improvements and new features in the future.
But perhaps it is possible to approach my request on a smaller time scale on a more specific level. Maybe it is possible to adjust only how dependent job schedules are executed. At the moment a task scheduled via dependency is queued like any other time scheduled task. So when DV is waiting for 100 jobs to finish a dependant job coming on top will be executed at position 101.
In the scenario I described, it would be helpful if a dependant job would be implicitly prioritized skipping at least all the time scheduled jobs. So it could be ensured that dependant jobs are processed right after each other.
For us this would be a very helpfull improvement.
I understand the change you suggested is a possible fix. However, changes to the job system need to be done very carefully, as they quickly can create other problems, i.e. critical time-based schedules being stuck because of dependent schedules having priority. Making this behavior configurable on a per-job/schedule basis would be possible, but even further complicate the current job system, making it less predictable/traceable when and why a certain job was (or was not) processed. This is why we currently refrain from making changes that seemingly appear to be quick and simple but come at the expense of increased complexity, potentially pushing the system to a point where it is no longer comprehensible.
As Salvatore mentioned, we do plan to address this in the scope of the Resource Governor feature, and also have a separate initiative to simplify the job system as a whole.
Another option with the means available right now is to put code that should be executed without any interruptions into the same job. So instead of having a structure like this:
-- job 1
-- job 2
where there is a dependent schedule between job 1 and 2, merge them into one single job:
This would require moving any sort of logic from the job definitions to views/procedures and only use the jobs for orchestrating view/procedure calls. Otherwise, there would be copy-and-paste duplications and reusability would not be given, if portions of the same code should be run in the context of different jobs.
Please sign in to leave a comment.