Ideas to monitor queued jobs

[sudarshan]

Hi Community,

We have a few scheduled calcs and consolidations running on an hourly basis, and sometimes these start queuing up and cause other DM jobs to fail/get cancelled by the system. Is there any good way to monitor or alert the admins via email if there are more than a few DM jobs getting queued up?

We already have a scheduled task which queries the TaskActivity table for any failed jobs periodically and then sends out an email to the admins to address them. But this idea doesn't work for the Queued Jobs as this DM also gets "Queued" along with the others.

Thanks,

Sudarshan

[JackLacava]

Is this for an On-Premises or Cloud environment? If it's the latter, you should probably give a call to Support to discuss it.

[sudarshan]

On-prem

[JackLacava · Accepted solution]

Suggestion n.1: get the System Diagnostics solution from Marketplace. It will help keeping an eye on your server in many ways, and chances are that it will have this sort of feature in the future (it already has Live Monitoring capabilities) and retrieve more metrics than OneStream does by default.

Suggestion n.2: OneStream can be configured to continuously log metrics about itself and the enviroment, by editing the Application Server and setting the desired parameters in the Environment Monitoring property. This is the full list of non-SQL metrics it can dump in v.7.2.2:

Environment.Workload.Assigned Tasks
Environment.Workload.Avg. Env CPU Utilization
Environment.Workload.Consolidation Tasks
Environment.Workload.Cube View Tasks
Environment.Workload.Data Management Tasks
Environment.Workload.Logged On Users
Environment.Workload.Long Running Tasks
Environment.Workload.Queued Tasks
Environment.Workload.Running Tasks
Environment.Workload.Stage Tasks
Environment.Workload.Tasks
Environment.Workload.Waiting Tasks
ServerSet.Workload.Assigned Tasks
ServerSet.Workload.Avg. Env CPU Utilization
ServerSet.Workload.Consolidation Tasks
ServerSet.Workload.Cube View Tasks
ServerSet.Workload.Data Management Tasks
ServerSet.Workload.Long Running Tasks
ServerSet.Workload.Queued Tasks
ServerSet.Workload.Running Tasks
ServerSet.Workload.Stage Tasks
ServerSet.Workload.Tasks
ServerSet.Workload.Waiting Tasks
SQL DB Ent.Workload.Blocked SQL Statement Count
Windows Server.Memory.Available MBytes
Windows Server.Memory.Pages/sec
Windows Server.Paging File.% Usage
Windows Server.Workload.% CPU Utilization
Windows Server.Workload.Consolidation Tasks On Server
Windows Server.Workload.Cube View Tasks On Server
Windows Server.Workload.Data Management Tasks On Server
Windows Server.Workload.Long Running Tasks On Server
Windows Server.Workload.Running Tasks On Server
Windows Server.Workload.Stage Tasks On Server
Windows Server.Workload.Tasks On Server

The values are recorded in the Framework database; this query should be a decent start (you'll want to further limit it by date or something like that):

select MetricName, MetricValueNum, MetricValueString, CapturedTime 
from MetricValue 
inner join MetricDefinition on MetricValue.MetricDefinitionID = MetricDefinition.MetricDefinitionID
order by capturedTime desc

Obviously, as you've experienced, trying to access this sort of monitoring from inside OneStream, when under heavy workload, is going to be problematic. The best strategy is to actually use a process outside of the application itself, like a Powershell script scheduled to run periodically via the Windows Task Scheduler.