Task Scheduling - OptimalBI/optimal-data-engine-mssql GitHub Wiki

ODE provides a mechanism for ordering and executing groups of tasks in parallel.

Without the Scheduler, all tasks are either run in series or they need to be scheduled by a 3rd Party application, which caters for parallel processing of tasks. In most cases, using a different application means hand crafting the schedules to suit. ODE has a rich collection of Configuration Data, which is leveraged to assist in managing tasks.

The Scheduler makes use of two aspects of Configuration Data:

Source Table Hierarchy

• Each task in ODE relates to a staged "Source Table", which may come from a Source System or from a Vault.
• Tasks sourced from a Vault create dependencies on prior tasks.
• By registering these dependencies as "Source Table Hierarchy" items, the scheduler is able to calculate what needs to be processed in which order.
• In addition, this Hierarchy provides a means of tracking data lineage at a table level.

• In ODE, a Schedule is a list of "Source Tables" which are grouped to run together.
• Schedules can be combined at run time. For example, if there are some tasks which run weekly on a Sunday, then the "Daily" and "Sunday" schedules can be merged into a single run, rather that having two interdependent schedules.

The first step in building up a Schedule, is to ensure that the "Source Table Hierarchy" is captured and correct.

This step is important as the Hierarchy will be used by all Schedules to determine run sequence.

This is done using [dv_scheduler].[dv_source_table_hierarchy_insert] providing the fully qualified names of the Source Table and the Prior Source Table.

For example:

EXECUTE [dv_scheduler].[dv_source_table_hierarchy_insert]
@source_unique_name         = 'link_SalesDiscount'
,@prior_source_unique_name  = 'Sales'
,@release_number            = 150927

Records the fact that the "Source Table" for the Customer Load in the Business Vault is dependant on the Raw Adventureworks Customer Load.

The number (150927) at the end is a Release Number, used for promoting changes to Production.

Each Schedule needs a Name.

This is captured using [dv_scheduler].[dv_schedule_insert], providing a unique Name and some descriptive information.

For Example:

EXECUTE [dv_scheduler].[dv_schedule_insert] 
@schedule_name         = 'Sunday'
,@schedule_description = 'Runs all Loads which only need to be refreshed weekly'
,@schedule_frequency   = 'Weekly on Sunday'
,@release_number       = 150927

Creates a named schedule for the Sunday Run.

The next step is to add "Source Tables" to the Schedule using [dv_scheduler].[dv_schedule_source_table_insert].

For Example:

EXECUTE [dv_scheduler].[dv_schedule_source_table_insert] 
@schedule_name           = 'Sunday'
,@source_system_name     = 'AdventureWorks'
,@source_table_schema    = 'Sales'
,@source_table_name      = 'Customer'
,@source_table_load_type = 'Full'
,@priority               = 'High'
,@queue                  = 'Agent001'
,@release_number         = 150927

EXECUTE [dv_scheduler].[dv_schedule_source_table_insert] 
@schedule_name           = 'Sunday'
,@source_system_name     = 'ODE_Business_Vault'
,@source_table_schema    = 'Stage'
,@source_table_name      = 'Customer'
,@source_table_load_type = 'Full'
,@priority               = 'High'
,@queue                  = 'Agent001'
,@release_number         = 150927

Includes the Adventureworks Customer Table and the Business Vault Customer Load in the Sunday Schedule.

The highly flexible nature of the Hierarchy means that it is possible to build a Schedule with circular references.

In the above example, it is quite possible to create a Hierarchy which makes the Adventureworks load dependant on the Business Vault load. This would not be allowed to execute as it creates a never ending loop.

To check for Circular References, use SELECT BreadCrumb FROM [dv_scheduler].[fn_check_schedule_for_circular_reference] ('Sunday')

There should be no output. If you do have a problem, the BreadCrumb will contain something like "6 > 1 > 6". This example shows that "Source Table" "6" is both a  Prerequisite and a Dependency for "Source Table" "1".

SELECT *
FROM [dbo].[dv_source_table] st
INNER JOIN [dbo].[dv_source_system] ss
ON ss.[source_system_key] = st.[system_key]
WHERE st.[source_table_key] IN (1, 6)

will show the details of the Source Tables.

Once the schedule is Valid, run EXECUTE [dv_scheduler].[dv_list_schedule_hierarchy] 'Sunday'

which will return a list like:

[AdventureWorks].[Sales].[Customer]
[AdventureWorks].[Sales].[Customer] >>> [ODE_Business_Vault].[Stage].[Customer]

Now that you have a valid Schedule, it can be executed. EXECUTE [dv_scheduler].[dv_process_schedule] 'Sunday'

This will:

1. Create a Manifest
2. Find all tasks in the Manifest, for which all Prerequisites have been completed (or for which there are no Prerequisites), and Queue them for execution.
3. As tasks are executed, check to see if any more tasks are ready to be queued (because their Prerequisites have been successfully processed).
4. If all tasks have been completed, end the run with a status of "Completed"
5. If a task has failed, the Scheduler will continue to process all tasks which are still valid. Once there are no more tasks to run, it will end the run with a Status of "Failed".

Once the run has started, you can follow its progress by running:

SELECT  * FROM [dv_scheduler].[dv_run] WHERE run_start_datetime > getdate() - 1 

which will show recent runs, with their run_key's.

SELECT * FROM [dv_scheduler].[vw_manifest_status] WHERE run_key = 123 

shows the detail about run 123, including the Run Status and the Status of each Task.

The Tasks themselves are executed under control of Service Broker, and are treated as Background Processes.

The SQL Server Activity Monitor will not show these tasks.

SELECT * FROM [dv_scheduler].[vw_running_processes] 

will show a list of all running tasks on the server (provided you have the necessary level of permissions on the server), including Background Processes.

If you would like to see what is happening on the Queues themselves,

SELECT * FROM [dv_scheduler].[vw_queue_status] 

will provide a summary of each of the queues.

To see what tasks are in the queues, either:

• right click on the Queue in SQL Server Management Studio > Your ODE Config Database > Service Broker > Queues > Your Queue and choose _"SELECT TOP 1000 rows"_ or
• run:

SELECT TOP 1000 *
, casted_message_body =
CASE message_type_name WHEN 'X'
THEN CAST(message_body AS NVARCHAR(MAX))
ELSE message_body
END
FROM [dbo].[dv_scheduler_q001] WITH (NOLOCK)

To Change the number of processes, which each Queue can run concurrently:

• right click on the Queue in SQL Server Management Studio > Your ODE Config Database > Service Broker > Queues > Your Queue and choose "Modify"
• This will generate an ALTER Queue Script, which you can use to change the Queue parameters.
• Alternatively, run:

  ALTER QUEUE [dbo].[dv_scheduler_q001] WITH ACTIVATION(MAX_QUEUE_READERS = 3)

  to change Queue 001 to run up to 3 Processes.

SELECT * FROM [dv_scheduler].[vw_manifest_status] WHERE run_key = 123 

will output a report on the state of Run 123.

If there has been a failure, the Task or Tasks, which have failed, will have a "run_manifest_status" of "Failed".

The Manifest may still be running as it will only stop after it has processed everything which is not dependent on the failed Task(s).

SELECT TOP 100 * FROM [log4].[Exception] ORDER BY 1 DESC

will show recent errors.

This can be refined by using the session_id from [dv_scheduler].[vw_manifest_status] in a where clause. Further, run times can be used to home in on the error.

Once the problem has been identified and fixed:

If the run is still processing, you can update the Status of the Task to "Scheduled" and the run will pick it up and try to run it.

• Query

  SELECT * FROM [dv_scheduler].[dv_run_manifest] WHERE run_key = 123 AND run_status = 'Failed'

  will provide the "run_manifest_key" for the failed task.

• Query

  UPDATE [dv_scheduler].[dv_run_manifest] 
  SET run_status = 'Scheduled' WHERE run_key = 123 AND run_status = 'Failed' 

  will make the task available to the scheduler to run.

• Query

  EXECUTE [dv_scheduler].[dv_restart_manifest] @vault_run_key = 123, @restart_failed_items = 1 

  will restart processing.

If you decide to halt a schedule, set the Run  to a status of "Cancelled". The following script will cancel all currently active Runs:

UPDATE [dv_scheduler].[dv_run]
SET [run_status] = 'Cancelled'
WHERE [run_status] in('Started', 'Scheduled')

If you wish to stop a specific Active Run,

Obtain the "Run Key" from the run which you wish to stop:

• Query

  SELECT  * FROM [dv_scheduler].[dv_run] WHERE run_start_datetime > getdate() - 1 

  will show recent runs, with their run_key's.

• Then run the following code, substituting with the specific Run Key that you with to stop:

  UPDATE [dv_scheduler].[dv_run]
  SET [run_status] = 'Cancelled'
  WHERE [run_status] in('Started', 'Scheduled')
  AND [run_key] = <nnn>

To find the correct "Spids" to Kill:

• Query

  SELECT * FROM [dv_scheduler].[vw_running_processes] 

  will show a list of all running tasks on the server including the "Spid".

• Query

  SELECT * FROM [dv_scheduler].[vw_manifest_status] 
  WHERE run_manifest_status = 'Processing' AND run_key = <nnn>

  will give details of the Tasks which are expected to be running. The sesion_id column will match the Spid from the above query.

All of the requisite Configuration Items have a "release_key" which can be set to attach the item to a Release.

Release Management does not delete Items in the destination.

Since Schedules are likely to be altered on a regular basis, the related configuration items have been provided with an "is_cancelled" indicator, which will be promoted by Release Management.

Anything with a status of "is_cancelled = 1" will not be included in a run.

Use this in preference to physically deleting Schedule Data.

Terminology:

1. Source Table Hierarchy
   • All tasks in ODE begin with a "Source Table". This may be a Staged Table, coming from a Source System or the result of further processing of data already in a Vault.
   • The processing of Vault data creates dependencies, which ODE records, so that it knows the correct order in which to process the "Source Tables".
2. Schedule
   • Each Named Schedule consists of a list of tasks ("Source Tables"), which are run together.
3. Manifest
   • A Manifest is an Instance of a Schedule, or a combination of Schedules.
   • At execution time, the Scheduler compiles a manifest from the Schedule(s), which is then used to manage the Run.
4. Queue
   • The Scheduler makes use of a Queuing mechanism to execute the individual tasks in parallel.
   • Currently, SQL Server Service Broker is used to manage the Queues.