Page Comparison

...

Job Progress per Scan Server:

Code Block

language	sql
title	sqlHistory.ScanServerProgress

declare @projectID int = 1
select * fromexec History.fn_ScanServerProgress ( @projectId )

Function Stored Procedure sql

Expand

title	Function sql codeHistory.ScanServerProgress

Code Block

language	sql

create functionprocedure History.fn_ScanServerProgress (@ProjectID int) 
returns @ScanServerProgress table(  WITH ENCRYPTION
as begin
ProjectID    set nocount on;
   int declare @msg nvarchar(2000) = ,ServerIDN'Procedure name : History.ScanServerProgress' + char(13)
    set int@msg += N'   ,server_name Returns scanning server overall progress. ' + nvarcharchar(25513)
    ,servable_targetsset @msg += intN'     ,complete_targets   int1) Servable Targets.  ' ,complete_pct     +  numeric(6,2char(13)
    primaryset key (ProjectID, ServerID)@msg += N'     )   2) Served WITHtargets' ENCRYPTION as begin + char(13) 
 with c_serv as set @msg += N'     (   3) Completed percentage'    select+ char(13) + CHAR(13)
    set @msg += N'    srv.Hostname
Sample execution:'        + char(13)
  ,srv.ServerID  set @msg += N'        ,ips.LocationIDdeclare @proejctId int = 1'         ,sum(ips.Target_count)+ char(13)
    set @msg += N' [servable_targets]       exec History.ScanServerProgress  @projectId '  ,[served].cnt  + char(13) + char(13)
    set @msg += N'    NOTE : Targets [complete_targets]which can be served by multiple servers'   from config.fn_IpRangeSummary(@ProjectID, default+ char(13)
  ips  set @msg += N'    -- ProjectID :-> 1
        inner join config.t__Location_Server     will simply be allocated evenly across the each' + char(13)
    set @msg += N'           server to give the servable_target figure. ' + char(13)
    set @msg += N'           This may not be how they get served in practice.' + char(13)
    raiserror (@msg, 0, 1) with nowait;
    with c_serv as
        (
        select 
             srv.Hostname
            ,srv.ServerID
            ,ips.LocationID
            ,sum(ips.Target_count)        [servable_targets]
            ,[served].cnt                 [complete_targets]
        from config.fn_IpRangeSummary(@ProjectID, default)   ips         -- ProjectID :-> 1
        inner join config.t__Location_Server        ls  with ( nolock ) on ips.LocationID = ls.LocationID
        inner join config.t_Server                  srv with ( nolock ) on ls.ServerID    = srv.ServerID
        left outer join 
            (
            SELECT 
                 j.ServerID
                ,Count(j.JobID) cnt
            FROM jobs.t_job     j with(nolock)
            GROUP BY ServerID
            )
            [served]                   on ls.ServerID = [served].ServerID
        group by 
             srv.Hostname
            ,srv.ServerID
            ,ips.LocationID
            ,[served].cnt
        )

        -- Locations with multiple Servers assigned
        -- Simply divide targets by Server count.
    ,   c_mult_server_per_loc as
        (
        select 
             s.LocationID
            ,count(s.ServerID)  [servers_for_location]
        from c_serv     s
        group by s.LocationID
        having count(s.ServerID) > 1
        )

    ,   c_res as
        (
        select 
             s.Hostname
            ,s.ServerID
            ,sum(s.[servable_targets] / coalesce(sloc.servers_for_location, 1)) [servable_targets]
            ,s.[complete_targets]
        from c_serv                             s
        left outer join c_mult_server_per_loc   sloc on s.LocationID = sloc.LocationID
        group by
             s.Hostname
            ,s.ServerID
            ,s.[complete_targets]
        )

        select 
                @ProjectID                      [ProjectID]
            ,rank() over (order by r.ServerID)  [ServerID]
            ,r.Hostname                         [server_name]
            ,r.servable_targets
            ,r.complete_targets
            ,cast(r.complete_targets * 100.00 / r.servable_targets as numeric ( 6, 2)) [complete_pct]
        from c_res      r
end
go

Server Throughput vs ActiveJobCount

Code Block

	sql
	sql

declare @projectId int = 1
exec history.ServerThroughput @projectId

Image Added

Expand

title	history.ServerThroughput

Code Block

	sql
	sql

create procedure history.ServerThroughput
    @ProjectId int
with encryption
as
begin
    set nocount on;
    declare @msg nvarchar(2000) = N'Procedure name : history.ServerThroughput' + char(13)
    set @msg += N'    Returns scanning server activity per 10 minute interval.' + char(13)
    set @msg += N'        1) Jobs Served.   (total over 10 mins)'      + char(13)
    set @msg += N'       ls 2) withActiveJobCount ( nolock ) on ips.LocationID = ls.LocationID
        inner join config.t_Serveraverage over 10 mins)'    + char(13) + CHAR(13)
    set @msg += N'    Sample execution:'        + char(13)
    set @msg += N' srv with ( nolock ) on ls.ServerID declare @proejctId int = srv.ServerID1'   + char(13)
    leftset outer join@msg += N'        exec history.ServerThroughput @proejctId' + char(13)
    set @msg += N'    '
SELECT    raiserror (@msg, 0, 1) with nowait;

    ; with c_Jobs_Served as
j.ServerID        (
        ,Count(j.JobID) cnt
 SELECT 
         FROM jobs.t_job     j with(nolockformat(StartDate, 'yyyy-MM-dd HH:00:00')             GROUP BY ServerID   as [Time]
        )    , NTILE(6) over(order by datepart(minute, StartDate))   as [servedtime_ntile]
                , j.JobID
 on ls.ServerID = [served].ServerID         , ServerID
 group by      FROM jobs.t_job        srv.Hostname             ,srv.ServerID        j   with(nolock)
 ,ips.LocationID       inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on ,[served].cnt
j.JobID = jlr.JobID
       ) where jlr.ProjectID = @ProjectId
     -- Locations with multiple)
Servers
assigned    , c_server_active_jobs as
  -- Simply divide targets by Server count.(
    ,   c_mult_server_per_loc as select 
       (      sm.ServerID
  select          ,NTILE(6) over(order by datepart(minute,  s.LocationIDsm.[Timestamp])) [time_ntile]
            ,countformat(ssm.ServerID)  [servers_for_location]
[TimeStamp], 'yyyy-MM-dd HH:00:00')        from c_serv   [Timestamp]
 s         group by s,sm.LocationIDActiveJobCount
        having count(s.ServerID) > 1from [config].[t_ServerMetric]  sm
        )

    ,   c_res_server_active_jobs_summ as
        (
        select 
             aj.ServerID
            ,dateadd(minute, aj.time_ntile * 10, s.Hostnameaj.[Timestamp])  [Timestamp]
            --,saj.ServerIDtime_ntile
            ,sumformat(avg(s.[servable_targets] / coalesce(sloc.servers_for_location, 1)) [servable_targets]
     aj.ActiveJobCount * 1.0), 'N2')       ,s.[complete_targets[ActiveJobCount]
        from c_serv _server_active_jobs  aj
        group by
             aj.ServerID
   s         left outer join c_mult_server_per_loc   sloc on s.LocationID = sloc.LocationID,dateadd(minute, aj.time_ntile * 10, aj.[Timestamp]) 
        )

   group by    select 
        s.Hostname     js.ServerID
       ,s.ServerID     ,dateadd(minute, js.[time_ntile] * 10,     ,sjs.[complete_targetsTime]) [Time]
       )     ,count(js.JobID) [jobs_served]
   insert into @ScanServerProgress       ,aj.ActiveJobCount
     select   from  c_Jobs_Served              @ProjectID            js
        left outer join  [ProjectID]
    c_server_active_jobs_summ   aj on js.ServerID = aj.ServerID
           ,rank() over (order by r.ServerID)  [ServerID]                 ,r.Hostname                    and js.[Time]   = aj.[server_name]Timestamp]
        group by
         ,r.servable_targets    js.ServerID
            ,r.complete_targets
   ,dateadd(minute, js.[time_ntile] * 10,  js.[Time])
            ,cast(r.complete_targets * 100.00 / r.servable_targets as numeric ( 6, 2)) [complete_pct]aj.ActiveJobCount
        order by
            [Time]
from c_res      r     return,js.ServerID
end
go

Job Data Summary:

Displays server info, current summary of the state of the jobs, and shows active jobs.

...

Versions Compared

Old Version 15

New Version Current

Key

Job Progress per Scan Server:

Server Throughput vs ActiveJobCount

Job Data Summary: