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
exec History.ScanServerProgress @projectId
Stored Procedure sql
History.ScanServerProgress
create procedure History.ScanServerProgress (@ProjectID int)
WITH ENCRYPTION
as begin
set nocount on;
declare @msg nvarchar(2000) = N'Procedure name : History.ScanServerProgress' + char(13)
set @msg += N' Returns scanning server overall progress. ' + char(13)
set @msg += N' 1) Servable Targets. ' + char(13)
set @msg += N' 2) Served targets' + char(13)
set @msg += N' 3) Completed percentage' + 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.ScanServerProgress @projectId ' + char(13) + char(13)
set @msg += N' NOTE : Targets which can be served by multiple servers' + char(13)
set @msg += N' 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
declare @projectId int = 1
exec history.ServerThroughput @projectId
history.ServerThroughput
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
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:
