sprockit.Process

[ v1.3 ] [ v1.4 ] [ v1.5 ] [ Back to Sprockit home page ]

Each row in this table represents a process managed by Sprockit.

A process is either something that is executed (like a stored procedure or an SSIS package), or a stored query (like a view or a user-defined function). For Sprockit to run a stored procedure or package it must appear in this table. When making an entry in the table, you must provide values for at least the three attributes [DbName], [SchemaName], and [ProcessName].

Configuring views and functions as processes ensures that dependent processes don't attempt to access them before their underlying base tables are ready.

Approaches to process configuration are described in Configuring processes in Sprockit.

Column name1) Column type Description Notes
PK ProcessId INT Unique identifier for the process. IDENTITY
DbName NVARCHAR(128) The name of the database in which the process object is found. For SSIS packages, this is the name of the catalog folder.
SchemaName NVARCHAR(128) The name of the database schema in which the process object is found. For SSIS packages, this is the name of the SSIS project.
ProcessName NVARCHAR(128) The schema-scoped name of the process object. For SSIS packages, this is the name of the SSIS package, including the .dtsx extension.
FqProcessName NVARCHAR(392) The fully-qualified (three-part) process name. COMPUTED
RL1 LatestExecutionId INT Identifies the most recent execution of this process. Updated by Sprockit.
Status VARCHAR(20) The current status of the process. Updated by Sprockit. A process cycles through a series of status values during an ETL run. Initially it is Not ready, then when all its inputs are available it becomes Ready. When selected by a handler it is briefly Reserved before it starts Running. In execution it may complete normally (Done) or fail (Errored); in the latter case any downstream processes not yet ready now become Blocked.
LastStatusUpdate DATETIME The date and time at which the process's status was last changed. Updated by Sprockit.
AvgDuration INT The average duration of the process over recent successful executions. Updated by Sprockit. The number of executions included in the rolling average is controlled by Sprockit's RollingAverageBatchCount property.
BranchWeight INT A process's branch weight is the sum of its average duration and that of all downstream processes. Updated by Sprockit. [BranchWeight] is used by a process handler when choosing between multiple Ready processes.
IsEnabled BIT Indicates whether this process is to be executed or not. When a handler encounters a disabled process ([IsEnabled] = 0), it update's the process's status to 'Done'. Set the field's value to 1 if the process is to be executed, otherwise 0.
Priority TINYINT The process's priority. [Priority] is used by a process handler when choosing between multiple Ready processes. By default a process has a priority of 100 but this can optionally be increased (value < 100) or decreased (value > 100). Note that allocating different priority values to processes overrides [BranchWeight] selection and may cause end-to-end processing time to increase.

Relationships with integrity enforced by a foreign key constraint have identifiers prefixed with FK; others are prefixed RL.

Click here to view the data model diagram.

Attributes
(in this table)
Referenced table Referenced attributes
(in referenced table)
RL1 LatestExecutionId sprockit.Execution ExecutionId
Relationship Attributes
(in referencing table)
Referenced attributes
(in this table)
sprockit.Error.RL1 SourceDb / SourceSchema / SourceObject DbName / SchemaName / ProcessName
sprockit.Event.RL1 SourceDb / SourceSchema / SourceObject DbName / SchemaName / ProcessName
sprockit.Execution.RL1 ProcessId ProcessId
sprockit.Reservation.RL1 ProcessId ProcessId
sprockit.Resource.RL1 ProcessId ProcessId
sprockit.uvw_Process.RL1 ProcessId ProcessId
sprockit.uvw_ProcessDependency.RL1 PredecessorId ProcessId
sprockit.uvw_ProcessDependency.RL2 SuccessorId ProcessId

Process handlers select processes for execution using SP sprockit.usp_ReserveProcess – this SP identifies a Ready process from this table for the handler to execute.

When multiple Ready processes are available, the SP chooses between them as follows:

  • Stored queries (e.g. views or functions) are selected first (on the basis that they take no time to process but may open up other process selection options).
  • Processes with lower [Priority] values are selected in preference to those with higher [Priority] values. This allows you to override [BranchWeight] if you wish to do so, but may cause end-to-end ETL processing time to increase.
  • Processes with higher [BranchWeight] values are selected in preference to those with lower [BranchWeight] values.

A process's branch weight is the sum of its [AvgDuration] and that of every downstream process. The dependency graph below is annotated with [AvgDuration] (grey) and [BranchWeight] (black) values – you can see that (for example) process C's [AvgDuration] is 5 seconds, but its branch weight is 33 = 5(C) + 4(G) + 20 (H) + 4(J):

Branch weight provides an indication of how much work is behind each process, and preferring processes with high [BranchWeight] values allows handlers to get started on that work as quickly as possible. Overall this has the effect of reducing the end-to-end processing time for an ETL run.

[BranchWeight] is recalculated at the start of each run using fresh [AvgDuration] values. Because [AvgDuration] is a rolling average, process selection adapts to changes in the ETL landscape as processes are added, removed, or their execution time changes.


Page generated from database metadata by DbScout.


1)
Columns with names in bold are non-nullable; columns with underlined names are participants in the table's primary key.