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;