Back to OIM Explorer

dbo.QBM_FTDBQCSOverviewLoad

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 6.249 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Verified DBQueue Overview Function Notes

  • Source catalog entry dbo.QBM_FTDBQCSOverviewLoad is a non-generated SQL table-valued function from sandbox-db sys.sql_modules.
  • The source definition references MaxInstanceEffective from QBMDBQueueTaskMetric and compares the sum against dbo.QBM_FGIDBQueueSlotsMax().
  • The source definition reads DialogDBQueue for queued task rows while building the returned overview data.

Operational meaning: This function is read/overview logic for DBQueue slot capacity. It is a verified lookup point for `MaxInstance`-related queue-capacity display logic, not an enqueue routine.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGIDBQueueSlotsMax source text reference
  • references source dbo.QBM_FGITableCountAll source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL287 lines
1CREATE FUNCTION dbo.QBM_FTDBQCSOverviewLoad(2  @LastDBQueueChecksum int,3  @ExecutionType int4) RETURNS @erg TABLE(UID_Task varchar(38) collate database_default NOT NULL,5CountElements int DEFAULT 0,6HasRecalculate BIT DEFAULT 0,7CountReset int DEFAULT 0,8PreliminaryPathLength int DEFAULT 0,9CountProcessing int DEFAULT 0 primary key nonclustered(UID_Task)10)11AS12BEGIN13  DECLARE @CurrentDBQueueChecksum int14  DECLARE @CurrentTaskDefChecksum int15  DECLARE @CurrentDBQueueCount int16  DECLARE @ExecutionTypeToHandle int = 017  DECLARE @IsDBSchedulerDisabled BIT18  DECLARE @UID_CutOffTask varchar(38)19  DECLARE @SingleUserProcess int20  DECLARE @UpdatePhase int21  DECLARE @MaxPathlength int22  DECLARE @Einschluß QBM_YSingleGUID23  DECLARE @Uebervoll_Lademenge int = 25000024  DECLARE @Uebervoll_Nachlademenge int = 7500025  DECLARE @UbervollLimit int = @Uebervoll_Lademenge + @Uebervoll_Nachlademenge26  SELECT27    TOP 1 @IsDBSchedulerDisabled = d.IsDBSchedulerDisabled,28    @SingleUserProcess = d.SingleUserProcess,29    @UpdatePhase = d.UpdatePhase,30    @UID_CutOffTask = d.UID_CutOffTask31  FROM DialogDatabase d32    WITH(readpast)33  WHERE34    d.IsMainDatabase = 135  SELECT36    TOP 1 @CurrentTaskDefChecksum = abs(abs(CHECKSUM_AGG(x.cs)) - dbo.QBM_FGITableCountAll('QBMDBQueueTaskDepend'))37  FROM(38  SELECT39    checksum(CONCAT(t.UID_Task, str(t.CustomWeight))) AS cs40  FROM QBMDBQueueTask t41    WITH(readpast)) AS x42  INSERT INTO @erg(UID_Task,43  CountElements)44  SELECT45    'TASKCHECKSUM',46    @CurrentTaskDefChecksum47  IF @UID_CutOffTask > ' '48  BEGIN49    INSERT INTO @erg(UID_Task,50    CountElements)51    SELECT52      'CutOffTaskLevel',53      m.PathLength54    FROM QBMDBQueueTaskMetric m55      WITH(readpast)56    WHERE57      m.UID_Task = @UID_CutOffTask58  END59  IF @IsDBSchedulerDisabled = 1 OR @SingleUserProcess > 060  BEGIN61    INSERT INTO @erg(UID_Task,62    CountElements)63    SELECT64      'EmergencyOff',65      166    IF @ExecutionType >= 067    BEGIN68      INSERT INTO @erg(UID_Task,69      CountElements)70      VALUES('CountTotal',71      0),72      ('CurrentChecksum',73      111),74      ('NoChange',75      1)76      GOTO endLabel77    END78    ELSE79    BEGIN80      SELECT @ExecutionTypeToHandle = 081    END82  END83  SELECT @CurrentDBQueueCount = dbo.QBM_FGITableCountAll('DialogDBQueue')84  SELECT85    TOP 1 @CurrentDBQueueChecksum = abs(abs(isnull(CHECKSUM_AGG(x.cs), 0)) - @CurrentDBQueueCount +(datepart(ss,86    getutcdate()) / 5))87  FROM(88  SELECT89    TOP 500 checksum(CONCAT(q.UID_DialogDBQueue, str(q.Generation))) AS cs90  FROM DialogDBQueue q91    WITH(readpast)92  ORDER BY q.UID_DialogDBQueue) AS x93  INSERT INTO @erg(UID_Task,94  CountElements)95  SELECT96    'CountTotal',97    @CurrentDBQueueCount98  INSERT INTO @erg(UID_Task,99  CountElements)100  SELECT101    'CurrentChecksum',102    @CurrentDBQueueChecksum103  IF @CurrentDBQueueChecksum = @LastDBQueueChecksum104  BEGIN105    INSERT INTO @erg(UID_Task,106    CountElements)107    SELECT108      'NoChange',109      0110      GOTO endLabel111  END112  IF @ExecutionTypeToHandle = 0113  BEGIN114    IF @CurrentDBQueueCount < @UbervollLimit AND @CurrentDBQueueCount >= 0115    BEGIN116      INSERT INTO @erg(UID_Task,117      CountElements)118      SELECT119        q.UID_Task,120        q.CountRecords121      FROM(122      SELECT123        dq.UID_Task,124        count(*) CountRecords125      FROM DialogDBQueue dq126        WITH(readpast, index(QBM_XC5DialogDBQueue))127      WHERE128        dq.Generation >= 0129      GROUP BY dq.UID_Task) q130    END131    ELSE132    BEGIN133      INSERT INTO @erg(UID_Task,134      CountElements)135      SELECT136        y.UID_Task,137        count(*)138      FROM(139      SELECT140        TOP(@Uebervoll_Lademenge) q.UID_Task,141        q.Generation142      FROM DialogDBQueue q143        WITH(readpast, index(QBM_XC7DialogDBQueue))144      WHERE145        q.Generation >= 0146      ORDER BY PathLength) AS y147      GROUP BY y.UID_Task option(maxdop 1)148      IF @@ROWCOUNT <= 3149      BEGIN150        INSERT INTO @Einschluß(UID_SingleGuid)151        SELECT TOP 10 UID_Task152        FROM QBMDBQueueTaskMetric m153          WITH(readpast)154        WHERE155          m.PathLength >=(156        SELECT max(m.PathLength)157        FROM QBMDBQueueTaskMetric m158          WITH(readpast)159        JOIN @erg e160          ON m.UID_Task = e.UID_Task) AND NOT EXISTS(161        SELECT TOP 1 1162        FROM @erg e163        WHERE164          e.UID_Task = m.UID_Task)165        ORDER BY m.PathLength ASC166        IF(167          SELECT sum(me.MaxInstanceEffective) AS Summe168        FROM @erg e169        JOIN QBMDBQueueTaskMetric me170          WITH(readpast)171          ON e.UID_Task = me.UID_Task) < dbo.QBM_FGIDBQueueSlotsMax()172        BEGIN173          INSERT INTO @erg(UID_Task,174          CountElements)175          SELECT176            y.UID_Task,177            count(*)178          FROM(179          SELECT180            TOP(@Uebervoll_Nachlademenge) q.UID_Task,181            q.Generation182          FROM DialogDBQueue q183            WITH(readpast, index(QBM_XC5DialogDBQueue), ForceSeek)184          JOIN @Einschluß e185            ON e.UID_SingleGuid = q.UID_Task186          ORDER BY q.PathLength) AS y187          WHERE188            y.Generation >= 0189          GROUP BY y.UID_Task option(maxdop 1)190        END191      END192    END193  END194  IF EXISTS(195    SELECT TOP 1 1196    FROM @erg e197    WHERE198      e.UID_Task IN('QBM-K-CommonReCalculate'))199  BEGIN200    DELETE @erg201    WHERE202      UID_Task IN('QBM-K-CommonReCalculate') merge INTO @erg AS t using(203    SELECT q.Object AS UID_Task204    FROM DialogDBQueue q205      WITH(readpast)206    WHERE207      q.UID_Task IN('QBM-K-CommonReCalculate') AND q.Generation >= 0208    GROUP BY q.Object) AS s209      ON t.UID_Task = s.UID_Task WHEN matched THEN210    UPDATE211    SET t.HasRecalculate = 1 WHEN NOT matched BY target THEN212    INSERT(UID_Task,213    CountElements,214    HasRecalculate)215    VALUES(s.UID_Task,216    1,217    1);218  END219  IF EXISTS(220    SELECT TOP 1 1221    FROM QBMDBQueueCurrent cu222    WITH(readpast)223  WHERE224    cu.SlotNumber < 0)225  BEGIN226    merge INTO @erg AS t using(227    SELECT228      z.UID_Task,229      sum(AnzahlReset) AS AnzahlReset,230      sum(AnzahlProcessing) AS AnzahlProcessing231    FROM(232    SELECT233      cu.UID_Task, count(*) AS AnzahlReset, 0 AS AnzahlProcessing234    FROM QBMDBQueueCurrent cu235      WITH(readpast)236    WHERE237      cu.SlotNumber < 0238    GROUP BY cu.UID_Task239    UNION all240    SELECT241      cu.UID_Task, 0 AS AnzahlReset, count(*) AS AnzahlProcessing242    FROM QBMDBQueueCurrent cu243      WITH(readpast)244    WHERE245      cu.SlotNumber > 0246    GROUP BY cu.UID_Task) AS z247    GROUP BY z.UID_Task) AS s248      ON t.UID_Task = s.UID_Task WHEN matched THEN249    UPDATE250    SET t.CountReset = s.AnzahlReset,251    t.CountProcessing = s.AnzahlProcessing WHEN NOT matched BY target THEN252    INSERT(UID_Task,253    CountElements,254    HasRecalculate,255    CountReset,256    CountProcessing)257    VALUES(s.UID_Task,258    0,259    0,260    s.AnzahlReset,261    s.AnzahlProcessing);262  END263  IF @UpdatePhase > 0264  BEGIN265    SELECT @MaxPathlength = -1266    SELECT TOP 1 @MaxPathlength = m.PathLength267    FROM QBMDBQueueTaskMetric m268      WITH(readpast)269    WHERE270      m.UID_Task = 'QBM-K-QBMSystemCompiled'271    IF @MaxPathlength > 0272    BEGIN273      UPDATE @erg274      SET PreliminaryPathLength = m.PathLength275      FROM @erg e276      JOIN QBMDBQueueTaskMetric m277        WITH(readpast)278        ON e.UID_Task = m.UID_Task279      DELETE @erg280      FROM @erg e281      WHERE282        e.PreliminaryPathLength > @MaxPathlength283    END284  END285  endLabel:286  RETURN287END
Open raw exported source
SQL · Raw41 lines
1create function dbo.QBM_FTDBQCSOverviewLoad (@LastDBQueueChecksum int  , @ExecutionType int    ) returns @erg table (UID_Task varchar(38) collate database_default2 not null , CountElements int default 0  , HasRecalculate bit default 0  , CountReset int default 0   , PreliminaryPathLength int default 0   , CountProcessing3 int default 0  primary key nonclustered (UID_Task) ) as begin declare @CurrentDBQueueChecksum int declare @CurrentTaskDefChecksum int declare @CurrentDBQueueCount4 int declare @ExecutionTypeToHandle int = 0 declare @IsDBSchedulerDisabled bit declare @UID_CutOffTask varchar(38) declare @SingleUserProcess int declare5 @UpdatePhase int declare @MaxPathlength int declare @Einschluß QBM_YSingleGUID declare @Uebervoll_Lademenge int = 250000 declare @Uebervoll_Nachlademenge6 int = 75000 declare @UbervollLimit int = @Uebervoll_Lademenge + @Uebervoll_Nachlademenge select top 1 @IsDBSchedulerDisabled = d.IsDBSchedulerDisabled7 , @SingleUserProcess = d.SingleUserProcess , @UpdatePhase = d.UpdatePhase , @UID_CutOffTask = d.UID_CutOffTask from DialogDatabase d with (readpast) where8 d.IsMainDatabase = 1 select top 1 @CurrentTaskDefChecksum = abs(abs(CHECKSUM_AGG(x.cs)) - dbo.QBM_FGITableCountAll('QBMDBQueueTaskDepend'))  from ( select9 checksum(concat(t.UID_Task , str(t.CustomWeight))) as cs from QBMDBQueueTask t with (readpast)  ) as x insert into @erg (UID_Task, CountElements) select10 'TASKCHECKSUM' , @CurrentTaskDefChecksum if @UID_CutOffTask > ' ' begin insert into @erg (UID_Task, CountElements) select 'CutOffTaskLevel', m.PathLength11 from QBMDBQueueTaskMetric m with (readpast) where m.UID_Task = @UID_CutOffTask end if  @IsDBSchedulerDisabled = 1 or @SingleUserProcess > 0 begin insert12 into @erg (UID_Task, CountElements) select 'EmergencyOff', 1 if @ExecutionType >= 0  begin insert into @erg (UID_Task, CountElements) values ('CountTotal'13, 0) , ('CurrentChecksum' , 111) , ('NoChange' , 1) goto endLabel end else begin  select @ExecutionTypeToHandle = 0  end end  select @CurrentDBQueueCount14 = dbo.QBM_FGITableCountAll('DialogDBQueue') select top 1 @CurrentDBQueueChecksum = abs(abs(isnull(CHECKSUM_AGG(x.cs), 0)) - @CurrentDBQueueCount + (datepart15(ss, getutcdate()) / 5)) from ( select top 500 checksum( concat(q.UID_DialogDBQueue, str(q.Generation) )) as cs from DialogDBQueue q with (readpast) order16 by q.UID_DialogDBQueue ) as x insert into @erg(UID_Task, CountElements) select 'CountTotal', @CurrentDBQueueCount insert into @erg(UID_Task, CountElements17) select 'CurrentChecksum', @CurrentDBQueueChecksum   if @CurrentDBQueueChecksum = @LastDBQueueChecksum begin insert into @erg(UID_Task, CountElements)18 select 'NoChange', 0 goto endLabel end if @ExecutionTypeToHandle = 0 begin if @CurrentDBQueueCount < @UbervollLimit and @CurrentDBQueueCount >= 0   begin19 insert into @erg (UID_Task , CountElements) select q.UID_Task, q.CountRecords from ( select dq.UID_Task , count(*) CountRecords from DialogDBQueue dq 20with (readpast, index (QBM_XC5DialogDBQueue)) where dq.Generation >= 0 group by dq.UID_Task ) q  end else begin   insert into @erg (UID_Task, CountElements21) select y.UID_Task, count(*) from ( select top (@Uebervoll_Lademenge) q.UID_Task, q.Generation from DialogDBQueue q with (readpast , index (QBM_XC7DialogDBQueue22) )    where q.Generation >= 0 order by PathLength  ) as y   group by y.UID_Task option (maxdop 1)   if @@ROWCOUNT <= 3  begin  insert into @Einschluß(UID_SingleGuid23) select top 10 UID_Task from QBMDBQueueTaskMetric m with (readpast) where m.PathLength >= ( select  max(m.PathLength) from QBMDBQueueTaskMetric m with24 (readpast) join @erg e on m.UID_Task = e.UID_Task ) and not exists (select top 1 1 from @erg e where e.UID_Task = m.UID_Task ) order by m.PathLength asc25   if (select sum(me.MaxInstanceEffective) as Summe from @erg e join QBMDBQueueTaskMetric me with (readpast) on e.UID_Task = me.UID_Task ) < dbo.QBM_FGIDBQueueSlotsMax26() begin  insert into @erg (UID_Task, CountElements) select y.UID_Task, count(*) from ( select top (@Uebervoll_Nachlademenge) q.UID_Task, q.Generation 27from DialogDBQueue q with (readpast , index (QBM_XC5DialogDBQueue), ForceSeek) join @Einschluß e on e.UID_SingleGuid = q.UID_Task order by q.PathLength28 ) as y where y.Generation >= 0 group by y.UID_Task option (maxdop 1) end end end  end  if exists (select top 1 1 from @erg e where e.UID_Task in ('QBM-K-CommonReCalculate'29  ) ) begin  delete @erg where UID_Task in ('QBM-K-CommonReCalculate'  ) merge into @erg as t using ( select q.Object as UID_Task from DialogDBQueue q 30with(readpast)  where q.UID_Task in ('QBM-K-CommonReCalculate'  ) and q.Generation >= 0        group by q.Object ) as s on t.UID_Task = s.UID_Task when31 matched then update set t.HasRecalculate = 1 when not matched by target then insert (UID_Task , CountElements, HasRecalculate ) values (s.UID_Task, 1,32 1) ; end if exists (select top 1 1 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber < 0 ) begin merge into @erg as t using ( select z.UID_Task33, sum (AnzahlReset) as AnzahlReset, sum(AnzahlProcessing) as AnzahlProcessing from ( select cu.UID_Task, count(*) as AnzahlReset, 0 as AnzahlProcessing34 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber < 0 group by cu.UID_Task union all select cu.UID_Task, 0 as AnzahlReset, count(*) as AnzahlProcessing35 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber > 0 group by cu.UID_Task ) as z group by z.UID_Task ) as s on t.UID_Task = s.UID_Task when36 matched then update set t.CountReset = s.AnzahlReset , t.CountProcessing = s.AnzahlProcessing when not matched by target then insert (UID_Task , CountElements37, HasRecalculate, CountReset, CountProcessing ) values (s.UID_Task, 0, 0, s.AnzahlReset, s.AnzahlProcessing) ; end if @UpdatePhase > 0 begin select @MaxPathlength38 = -1 select top 1 @MaxPathlength = m.PathLength from QBMDBQueueTaskMetric m with (readpast) where m.UID_Task = 'QBM-K-QBMSystemCompiled' if @MaxPathlength39 > 0 begin update @erg set PreliminaryPathLength = m.PathLength from @erg e join QBMDBQueueTaskMetric m with (readpast) on e.UID_Task = m.UID_Task delete40 @erg from @erg e where e.PreliminaryPathLength > @MaxPathlength end end  endLabel: return end 41