Diagnostic Scripts for use in Customer Environment

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)

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

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