Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 15 Next »

The scripts listed on this page are to be used in Customer environments to allow us to start collating output counts and begin mapping trends across various iQsonar set-ups.  Additional sections will be added to this page once results from customers are available.  The goal is to help with customer fine tuning and also will assist both Support and Development with designing best practice.  

Timings Test for Targets Serve:

  • Use to check how long the Targets Serve stored proc runs each step (in ms)

Script:

 Targets_Serve_test.sql

Sample Output:

Timings test for QueueStep2

  • Use to see how long each step of the Queue_Step2 Stored Proc takes to complete (in ms)

Script:

 QStep2_Test.sql

Sample Output:

Job Stats query:

  • Overview of jobs showing the counts for each stage (Awaiting Confirmation, Queued, Served, etc.)

Script: Job Stats.sql 

Sample Output:

Queued Jobs Analysis:

  • Tracks items which reach a queued state and gives some indication as to
    • how quickly jobs are served
    • if items are staying queued and blocking spots in the queue
    • the average time between being queued and served
    • the longest un-served item

Instructions:  Run the script for 5 minutes, then stop.  Scroll to the bottom of the script and execute the Trending Results section.  Execute the drop temp tables section after capturing the output.

Script: Queued Jobs Analysis.sql

Sample Output:


Jobs Per Server Per Hour

  • Script that will display a breakdown of jobs per her per scan engine.
  • The script as provided will work for customers with 6 scan engines in their database, there may need to be some interpretation of the results.
  • Null values may belong to servers that are not scanning or are used for ui purposes.

Script: JobsPerServerPerHour.sql

Below is what it looks like with 4 scan engines. If there are more scan engines you can add the numbers required to the script.

Jobs Per Server Per Hour (dynamic)

Picks up Scan Engine Server names automatically.

declare @projectID int = 1
exec History.ScanServerProgressHourly @projectId

Stored Procedure sql

 History.ScanServerProgressHourly
/*
-- Sample execution
    declare @projectID int = 1
    exec History.ScanServerProgressHourly @projectId
*/

alter procedure History.ScanServerProgressHourly (@projectID int) 
WITH ENCRYPTION
as begin
    declare 
         @sqlString1 nvarchar(max) = N'Select [Time], '
        ,@sqlString2 nvarchar(max) = '
        from(
    SELECT 
          dateadd(hour, datediff(hour, 0, StartDate), 0) as [Time]
        , Count(*) cnt
        , ServerID
    FROM jobs.t_job                             j   with(nolock)
    inner join jobs.t_JobLocationProjectIPRange jlr with(nolock) on j.JobID = jlr.JobID
    where jlr.ProjectID = ' + cast (@ProjectID as nvarchar(5)) + 
    'GROUP BY dateadd(hour, datediff(hour, 0, StartDate), 0), ServerID
    ) p
    pivot
    (
        max(cnt)
        for serverid in('

        ,@sqlString3 nvarchar(max) = ')
    )piv
    Order by Time desc'

    ;with c_server as 
        (
        select distinct
             '[' + cast (s.ServerID as nvarchar(5)) + '] ' [ServerID]
            ,'''' + s.Hostname + '''' [Hostname]
        from config.t_Server    s with(nolock)
        inner join jobs.t_job   j with(nolock) on s.ServerID = j.JobID
        )
    
        select distinct
            @sqlString1 += 
         stuff((select ', ' + ServerID + 'AS ' +  Hostname 
        from c_server
        for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,'') --as ServerNames
        + @sqlString2 +
    
        --+   char(13) + 
        stuff((select ', ' + ServerID 
        from c_server
        for xml path(''), type)
        .value('.','NVARCHAR(MAX)'),1,2,'') --as ServerID
    
        + @sqlString3
        from c_server s

    print @sqlString1
    exec (@sqlString1)
end
go


Job Progress per Scan Server:

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

Function sql

 Function sql code
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


Job Data Summary:

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

Script: Job Data Summary.sql

Sample Output:



Average Scan and Save times grouped per Month/Day/Hour

  • Displays the average scan, save and total number of targets per hour

Script: Average Scan Times.sql

Sample Output:

Job Execution Time Query:

  • Displays the elapsed Time in hours for In Progress Jobs

Script: Job_Execution_Time_Query.sql

Sample Output:


Job Serving Analysis:

  • Displays number of jobs queued, capacity, number of jobs waiting and average time.

Script: Job Serving Analysis.sql

Sample Output:






  • No labels