dbo.QBM_FTDBQCSOverviewLoad

1create function dbo.QBM_FTDBQCSOverviewLoad (@LastDBQueueChecksum int  , @ExecutionType int    ) returns @erg table (UID_Task varchar(38) collate database_default
2 not null , CountElements int default 0  , HasRecalculate bit default 0  , CountReset int default 0   , PreliminaryPathLength int default 0   , CountProcessing
3 int default 0  primary key nonclustered (UID_Task) ) as begin declare @CurrentDBQueueChecksum int declare @CurrentTaskDefChecksum int declare @CurrentDBQueueCount
4 int declare @ExecutionTypeToHandle int = 0 declare @IsDBSchedulerDisabled bit declare @UID_CutOffTask varchar(38) declare @SingleUserProcess int declare
5 @UpdatePhase int declare @MaxPathlength int declare @Einschluß QBM_YSingleGUID declare @Uebervoll_Lademenge int = 250000 declare @Uebervoll_Nachlademenge
6 int = 75000 declare @UbervollLimit int = @Uebervoll_Lademenge + @Uebervoll_Nachlademenge select top 1 @IsDBSchedulerDisabled = d.IsDBSchedulerDisabled
7 , @SingleUserProcess = d.SingleUserProcess , @UpdatePhase = d.UpdatePhase , @UID_CutOffTask = d.UID_CutOffTask from DialogDatabase d with (readpast) where
8 d.IsMainDatabase = 1 select top 1 @CurrentTaskDefChecksum = abs(abs(CHECKSUM_AGG(x.cs)) - dbo.QBM_FGITableCountAll('QBMDBQueueTaskDepend'))  from ( select
9 checksum(concat(t.UID_Task , str(t.CustomWeight))) as cs from QBMDBQueueTask t with (readpast)  ) as x insert into @erg (UID_Task, CountElements) select
10 'TASKCHECKSUM' , @CurrentTaskDefChecksum if @UID_CutOffTask > ' ' begin insert into @erg (UID_Task, CountElements) select 'CutOffTaskLevel', m.PathLength
11 from QBMDBQueueTaskMetric m with (readpast) where m.UID_Task = @UID_CutOffTask end if  @IsDBSchedulerDisabled = 1 or @SingleUserProcess > 0 begin insert
12 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 @CurrentDBQueueCount
14 = dbo.QBM_FGITableCountAll('DialogDBQueue') select top 1 @CurrentDBQueueChecksum = abs(abs(isnull(CHECKSUM_AGG(x.cs), 0)) - @CurrentDBQueueCount + (datepart
15(ss, getutcdate()) / 5)) from ( select top 500 checksum( concat(q.UID_DialogDBQueue, str(q.Generation) )) as cs from DialogDBQueue q with (readpast) order
16 by q.UID_DialogDBQueue ) as x insert into @erg(UID_Task, CountElements) select 'CountTotal', @CurrentDBQueueCount insert into @erg(UID_Task, CountElements
17) 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   begin
19 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, CountElements
21) select y.UID_Task, count(*) from ( select top (@Uebervoll_Lademenge) q.UID_Task, q.Generation from DialogDBQueue q with (readpast , index (QBM_XC7DialogDBQueue
22) )    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_SingleGuid
23) select top 10 UID_Task from QBMDBQueueTaskMetric m with (readpast) where m.PathLength >= ( select  max(m.PathLength) from QBMDBQueueTaskMetric m with
24 (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 asc
25   if (select sum(me.MaxInstanceEffective) as Summe from @erg e join QBMDBQueueTaskMetric me with (readpast) on e.UID_Task = me.UID_Task ) < dbo.QBM_FGIDBQueueSlotsMax
26() 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.PathLength
28 ) 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 when
31 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_Task
33, sum (AnzahlReset) as AnzahlReset, sum(AnzahlProcessing) as AnzahlProcessing from ( select cu.UID_Task, count(*) as AnzahlReset, 0 as AnzahlProcessing
34 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 AnzahlProcessing
35 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 when
36 matched then update set t.CountReset = s.AnzahlReset , t.CountProcessing = s.AnzahlProcessing when not matched by target then insert (UID_Task , CountElements
37, HasRecalculate, CountReset, CountProcessing ) values (s.UID_Task, 0, 0, s.AnzahlReset, s.AnzahlProcessing) ; end if @UpdatePhase > 0 begin select @MaxPathlength
38 = -1 select top 1 @MaxPathlength = m.PathLength from QBMDBQueueTaskMetric m with (readpast) where m.UID_Task = 'QBM-K-QBMSystemCompiled' if @MaxPathlength
39 > 0 begin update @erg set PreliminaryPathLength = m.PathLength from @erg e join QBMDBQueueTaskMetric m with (readpast) on e.UID_Task = m.UID_Task delete
40 @erg from @erg e where e.PreliminaryPathLength > @MaxPathlength end end  endLabel: return end 
41