Page Comparison

create function History.fn_ScanServerProgress (@ProjectID int) 
returns @ScanServerProgress table(
     ProjectID          int
    ,ServerID           int
    ,server_name        nvarchar(255)
    ,servable_targets   int
    ,complete_targets   int
    ,complete_pct       numeric(6,2)
    primary key (ProjectID, ServerID)
    )
    WITH ENCRYPTION
as begin
    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]
        )

        insert into @ScanServerProgress
            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
    return
end
go

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'        2) ActiveJobCount (average over 10 mins)'    + char(13) + CHAR(13)
    set @msg += N'    Sample execution:'        + char(13)
    set @msg += N'        declare @proejctId int = 1'   + char(13)
    set @msg += N'        exec history.ServerThroughput @proejctId' + char(13)
    set @msg += N'    '
    raiserror (@msg, 0, 1) with nowait;

    ; with c_Jobs_Served as
        (
        SELECT 
              format(StartDate, 'yyyy-MM-dd HH:00:00')                  as [Time]
            , NTILE(6) over(order by datepart(minute, StartDate))  as [time_ntile]
            , j.JobID
            , ServerID
        FROM jobs.t_job                             j   with(nolock)
        inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on j.JobID = jlr.JobID
        where jlr.ProjectID = @ProjectId
        )

    , c_server_active_jobs as
        (
        select 
             sm.ServerID
            ,NTILE(6) over(order by datepart(minute, sm.[Timestamp])) [time_ntile]
            ,format(sm.[TimeStamp], 'yyyy-MM-dd HH:00:00')            [Timestamp]
            ,sm.ActiveJobCount
        from [config].[t_ServerMetric]  sm
        )
    ,   c_server_active_jobs_summ as
        (
        select 
             aj.ServerID
            ,dateadd(minute, aj.time_ntile * 10, aj.[Timestamp])  [Timestamp]
            --,aj.time_ntile
            ,format(avg(aj.ActiveJobCount * 1.0), 'N2')       [ActiveJobCount]
        from c_server_active_jobs  aj
        group by
             aj.ServerID
            ,dateadd(minute, aj.time_ntile * 10, aj.[Timestamp]) 
        )

        select 
             js.ServerID
            ,dateadd(minute, js.[time_ntile] * 10,  js.[Time]) [Time]
            ,count(js.JobID) [jobs_served]
            ,aj.ActiveJobCount
        from c_Jobs_Served                          js
        left outer join c_server_active_jobs_summ   aj on js.ServerID = aj.ServerID
                                                      and js.[Time]   = aj.[Timestamp]
        group by
             js.ServerID
            ,dateadd(minute, js.[time_ntile] * 10,  js.[Time])
            ,aj.ActiveJobCount
        order by
            [Time]
            ,js.ServerID
end
go