Skip to content

System Requirements#

What does it do?#

The Process Service (Proc Server) component handles processing bulk operations requested as a dedicated service, allowing users to efficiently offload processing operations and continue working without handling the work locally.

Shared SQL/App/Proc Server Enviroments

For low load scenarios this may work without issue. However, if expecting heavier usage, please be sure to read FAQ Can I install the server components (App,Proc,SQL,SSRS) on the same machine? for performance warnings.

Detailed Sizing of Process Server#

Bulk requests are handling by the process server. When performing recalculations the primary impact will be CPU load. The time to process a task will vary based on the size, complexity, and other aspects of the data.

In addition to planning for high CPU load, the Process Server has two general type of activities that generate different load pattersn. Calc related operations result in heavy CPU/memory usage with a heavy IO load on SQL Server for saving results. Copy related activity does not impact the process server CPU as much, and will generate heavy IO on the SQL Server.

Tip

It is typically better to have a larger number of small process servers, rather than a few process servers with very high thread counts. This can help reduce CPU scheduler contention, possible network bottlenecks, and allow scaling up or down and environment based on new machines rather than reconfiguring existing hardware or virtual machines.

Warning

The tested recommendation of setting thread count to 1.5x the number of cores for optimal process server performance has been tested and still stands as a current recommendation with 4 cores. This does not mean this is a simple linear scale without limit. For the standard environment, it is not recommended to set up one single larger process server with 1.5x of threads when the core count on the machine is high. When configuring a process server with 1.5x of 12 cores this would result in 18 threads. In this scenario, excessive CPU scheduling can cause performance to degrade.

Technical Detail: Impact of Adjusting Thread Count Too High

The standard recommendation of adjusting the process server threads to 1.5x the number of cores still is recommended in this type of configuration. In testing, each calculation timing were increased by 54% when increasing the thread count to 2x the cores.

Some of the increased timing in the tests showed the contention that can occur when running a higher thread count on the process server than the recommended 1.5x the number of cores.

Threads / Ratio TestDuration
4 Threads (1x) 18 min
6 Threads (1.5x) 16 min
8 Threads (2x) 18 min

Increasing the thread count beyond the recommended 1.5 ratio did not achieve better processing times for the process server either, showing a 13% performance decrease by simply increasing the threads to 2x the cores (in this case 8 thread on a 4 core system)

In our tests, we typically use 4 core process servers and add/remove additional process servers as needed. This would result in 6 threads maximum per server as well as a more scalable infrastructure.

If you have a current AE deployment, then the following SQL query might provide a general estimate of the impact of adding more process servers in overall processing time. This is a rough estimate used to help identify if you might benefit from adding more process servers based on your current load.

SQL: Impact of More Process Servers On Job Timing

SQL Server query to help get Estimated Time to Complete a Job Based on History

/**********************************************
Get Estimated Time to Complete a Job Based on History

>>> Important <<<

Scaling up SQL Server resources along with additional process servers is critical to avoid bottleneck with CPU and memory.
Additionally, adding more cores or process servers just to improve timings does not scale in a linear way. This is due to various bottlenecks that can occur such as:
    - CPU constraint on SQL Server
    - IOPS on SAN (peak of 4000 in our limited set of tests with 6 process servers running recalculations)
    - Network Bandwidth if using storage not local to server
    - Table Contention as many tasks attempt to save at the same time.

This is dependent on your environment, so start with this as an estimate and then test under load to adjust as your requirements dictate.

If you need a general starting point and have no specifications currently for your SQL Server, then this might provide a basic starting point.

| Process Server | Suggested SQL Minimum |
| -------------- | --------------------- |
| 2              | 4 CPU 16GB RAM        |
| 4              | 4 CPU 16GB RAM        |
| 6              | 4-8 CPU 32GB RAM      |

Additionally, if the database is going to be larger, consider adding more RAM to assist with performance, especially with reporting and loading.
**********************************************/

set nocount on;
declare
    @MoreDetail          bit            = 0 -- TO DUMP DAILY STATS, CHANGE TO 1
    ,@CoresPerProcServer int            = 4
    ,@ThreadRatio        decimal(18, 2) = 1.5;

if ( @CoresPerProcServer > 6 )
begin
    raiserror(
        N'ERROR: %s'
        ,16
        ,1
        ,N'High cores per proc server can lead to performance problems. Consider using process servers with 4-6 cores');
    return;
end;
if ( @ThreadRatio > 1.5 )
begin
    raiserror(
        N'ERROR: %s'
        ,16
        ,1
        ,N'Going above 1.5 * Number of Cores as resulted in a degradation of performance. Reduce the 1.5x for shared server environments, and do not increase above 1.5 for best performance.');


    return;
end;

-- this is the recommended configuration, no adjustment needed
-- calculated values
declare @Br char(2) = char(13) + char(10);
begin try
    if object_id('tempdb..#jobs') is not null drop table #jobs;
    if object_id('tempdb..#jobstats') is not null drop table #jobstats;
    if object_id('tempdb..#taskstats') is not null drop table #taskstats;
    select
        job_id                  = qj.Id
        ,job_description        = qj.Description
        ,job_PriorityTypeId     = qj.PriorityTypeId
        ,job_JobStatusId        = qj.JobStatusId
        ,job_SubmissionTime     = qj.SubmissionTime
        ,job_ExecutionLocation  = qj.ExecutionLocation
        ,job_SubmittedByUser    = qj.SubmittedByUser
        ,job_TotalTaskCount     = qj.TotalTaskCount
        ,job_ProcessedTaskCount = qj.ProcessedTaskCount
        ,job_CompleteCount      = qj.CompleteCount
    into    #jobs
    from
        dbo.QueuedJobs as qj
    where
        qj.SubmissionTime >= dateadd(year, -1, cast(getdate() as date));
    if object_id('tempdb..#tasks') is not null drop table #tasks;
    select
        qj.job_id
        ,sqid_task_processed_order_asc  = row_number() over ( partition by qj.job_id order by x.StartProcessingTime asc )
        ,sqid_task_processed_order_desc = row_number() over ( partition by qj.job_id order by x.StartProcessingTime desc )
        ,task_Id                        = x.Id
        ,task_QueuedJobId               = x.QueuedJobId
        ,task_TaskTypeId                = x.TaskTypeId
        ,task_Description               = x.Description
        ,task_StatusId                  = x.StatusId
        ,task_Order                     = x.[Order]
        ,task_StartProcessingTime       = x.StartProcessingTime
        ,task_EndProcessingTime         = x.EndProcessingTime
        ,task_StatusMessage             = x.StatusMessage
        ,task_ProcessedLocation         = x.ProcessedLocation
        ,task_Exception                 = x.Exception
        ,task_PropertyAssetId           = x.PropertyAssetId
        ,task_ScenarioId                = x.ScenarioId
        ,task_PortfolioId               = x.PortfolioId
        ,task_RefPropertyAssetId        = x.RefPropertyAssetId
        ,task_LockDestination           = x.LockDestination
        ,task_NewExternalId             = x.NewExternalId
        ,task_StartDate                 = x.StartDate
        ,task_EndDate                   = x.EndDate
        ,task_Override                  = x.Override
        ,task_DoCalc                    = x.DoCalc
    into    #tasks
    from
        #jobs                             as qj
        inner join dbo.QueuedTasksArchive as x
        on qj.job_id                  = x.QueuedJobId
        and  x.StartProcessingTime >= dateadd(year, -1, cast(getdate() as date));
    select
        query_description = 'Jobs'
        ,j.job_id
        ,TK.TotalTaskCount
        ,j.job_description
        ,j.job_PriorityTypeId
        ,j.job_JobStatusId
        ,j.job_SubmissionTime
        ,j.job_ExecutionLocation
        ,j.job_SubmittedByUser
        ,j.job_TotalTaskCount
        ,j.job_ProcessedTaskCount
        ,j.job_CompleteCount
        ,t.QueuedJobStart
        ,te.QueuedJobFinish
        ,t.calc_job_lag_start_ms
        ,te.calc_job_lag_end_ms
        ,te.job_duration_ms
    into    #jobstats
    from
        #jobs                                                            as j
        left join ( select
                        tks.task_QueuedJobId, TotalTaskCount = count(tks.task_Id)
                    from
                        #tasks as tks
                    group by
                        tks.task_QueuedJobId )                           as TK
        on j.job_id = j.job_id
        cross apply ( select
                            calc_job_lag_start_ms = datediff(
                                                        millisecond
                                                        ,j.job_SubmissionTime
                                                        ,ca_t.task_StartProcessingTime)
                            ,QueuedJobStart       = ca_t.task_StartProcessingTime
                    from
                            #tasks as ca_t
                    where
                            j.job_id                               = ca_t.job_id
                            and ca_t.sqid_task_processed_order_asc = 1 ) as t
        cross apply ( select
                            calc_job_lag_end_ms = datediff(
                                                    millisecond
                                                    ,j.job_SubmissionTime
                                                    ,ca_te.task_StartProcessingTime)
                            ,job_duration_ms    = datediff(
                                                    millisecond
                                                    ,t.QueuedJobStart
                                                    ,ca_te.task_EndProcessingTime)
                            ,QueuedJobFinish    = ca_te.task_EndProcessingTime
                    from
                            #tasks as ca_te
                    where
                            j.job_id                                 = ca_te.job_id
                            and ca_te.sqid_task_processed_order_desc = 1 ) as te
    order by
        j.job_id;
    select
        query_description = 'Task Detail'
        ,t.job_id
        ,t.sqid_task_processed_order_asc
        ,t.sqid_task_processed_order_desc
        ,t.task_Id
        ,t.task_QueuedJobId
        ,TaskTypeName     = QTT.Name
        ,t.task_TaskTypeId
        ,t.task_Description
        ,t.task_StatusId
        ,t.task_Order
        ,t.task_StartProcessingTime
        ,t.task_EndProcessingTime
        ,t.task_ProcessedLocation
        ,t.task_PropertyAssetId
        ,t.task_ScenarioId
        ,t.task_PortfolioId
        ,t.task_RefPropertyAssetId
        ,t.task_LockDestination
        ,t.task_NewExternalId
        ,t.task_StartDate
        ,t.task_EndDate
        ,t.task_Override
        ,t.task_DoCalc
        ,task_duration_ms = datediff(
                                millisecond, t.task_StartProcessingTime, t.task_EndProcessingTime)
    into    #taskstats
    from
        #tasks                         as t
        inner join dbo.QueuedTaskTypes as QTT
        on t.task_TaskTypeId = QTT.Id;
    declare
        @MaxTaskCount int, @AvgTaskCount int;
    select  @MaxTaskCount = max(J.TotalTaskCount), @AvgTaskCount = avg(J.TotalTaskCount)from    #jobstats as J;
    declare @AvgTaskDuration int = ( select avg(task_duration_ms)from   #taskstats );
    select
        QueryDescription = '--- General Current Stats ----'
        ,MaxTaskCount    = @MaxTaskCount
        ,AvgTaskCount    = @AvgTaskCount
        ,AvgTaskDuration = convert(nvarchar(100), dateadd(ms, @AvgTaskDuration, 0), 108);
    if object_id('tempdb..#CalcStats', 'U') is not null drop table #CalcStats;
    create table #CalcStats (

    TaskSize_Max                    int            not null
    ,TaskSize_Avg                   int            not null
    ,ThreadRatio                    decimal(18, 2) not null

    ,CoresPerProcServer             int            not null
    ,CountOfProcServer              int            not null
    ,Task_DurationAvg               int            not null
    ,ThreadCount                    as ( cast(CountOfProcServer * ( CoresPerProcServer * ThreadRatio ) as int))
    ,EstimatedTimeToFinish_MaxTasks as
        ( convert(
            decimal(18, 2)
            ,( TaskSize_Max * Task_DurationAvg ) / ( CountOfProcServer * ThreadRatio )))
    ,EstimatedTimeToFinish_AvgTasks as
        ( convert(
            decimal(18, 2)
            ,( TaskSize_Avg * Task_DurationAvg ) / ( CountOfProcServer * ThreadRatio )))






    );
    insert into #CalcStats ( TaskSize_Max
                            ,TaskSize_Avg
                            ,ThreadRatio
                            ,CoresPerProcServer
                            ,CountOfProcServer
                            ,Task_DurationAvg )
    select

        @MaxTaskCount           -- TaskSize_Max
        ,@AvgTaskCount          -- TaskSize_Avg
        ,@ThreadRatio           -- ThreadRatio
        ,@CoresPerProcServer    -- CoresPerProcServer
        ,x.ProcessServerCount   -- ProcessServerCount
        ,@AvgTaskDuration       -- Task_DurationAvg
    from    ( values ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 )) as x ( ProcessServerCount );
    select
        QueryDescription       = '--- Projected Stats ----'
        ,TaskSize_Max
        ,TaskSize_Avg
        ,ThreadRatio
        ,CoresPerProcServer
        ,CountOfProcServer
        ,Task_DurationAvg      = convert(nvarchar(100), dateadd(ms, Task_DurationAvg, 0), 108)

        ,ThreadCount
        --,EstimatedTimeToFinish_MaxTasks
        --,EstimatedTimeToFinish_AvgTasks

        ,TimeToFinish_MaxTasks = convert(
                                    nvarchar(100)
                                    ,dateadd(ms, EstimatedTimeToFinish_MaxTasks, 0)
                                    ,108)
        ,TimeToFinish_AvgTasks = convert(
                                    nvarchar(100)
                                    ,dateadd(ms, EstimatedTimeToFinish_AvgTasks, 0)
                                    ,108)


    from
        #CalcStats;
    print @Br + @Br + '<<<<<<< IMPORTANT >>>>>>> ';
    print 'QUALIFICATION: Scaling up SQL Server resources along with additional process servers is critical to avoid bottleneck with CPU and memory.'
        + @Br
        + 'Additionally, this does not continue to scale fully linearly as their will be an increase in IO contention on the tables saving the job results. '
        + @Br
        + 'This is dependent on your environment, so start with this as an estimate and then test under load to adjust the numbers as required to meet your goals';
    if ( @MoreDetail = 1 )
    begin
        select
            TaskDate               = cast(ts.task_StartProcessingTime as date)
            ,ThreadCount           = cs.ThreadCount
            ,TaskCount             = count(*)
            ,TaskTotalDuration     = convert(
                                        nvarchar(100), dateadd(ms, sum(ts.task_duration_ms), 0), 108)
            ,EstimatedTimeToFinish = convert(
                                        nvarchar(100)
                                        ,dateadd(
                                            ms
                                            ,( sum(ts.task_duration_ms) / ( cs.ThreadCount ))
                                            ,0)
                                        ,108)
        from
            #taskstats as ts

            cross apply ( select    ca_cs.ThreadCount from

                                #CalcStats as ca_cs

        )              as cs

        group by
            cs.ThreadCount, cast(ts.task_StartProcessingTime as date)
        order by
            TaskDate asc;
    end;
end try
begin catch
    select
        error_number     = error_number()
        ,error_severity  = error_severity()
        ,error_state     = error_state()
        ,error_message   = error_message()
        ,error_line      = error_line()
        ,error_procedure = error_procedure();
end catch;