EXEC DynamicsPerf.dbo.SP_PROCESS_STATS_LOW_PRIORITY keeps running (SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY)

[wvhoof]

We have some customers where lately the SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY keeps on running for hours and hours until the IT administrators decide to kill this SQL job. It even ran more than a day in some occasions before we killed it.

These are all AX2012 customers.

On some customer installations this happens on a complete new installation of DynamicsPerf (2.20) so with only a small DynamicsPerf database. On some customers DynamicsPerf has been running well for quite a while and this behavior did recently start. Whereas it used to run for like 20 seconds before.

On some customer installations : after killing the SQL job once, the SQL job starts running normal afterwards again. For other installations it keeps on getting 'stuck'.

We had to kill the job as it keeps on taking CPU time while running.

Is this some known issue or something we can fix?

I could see in the blog post https://community.dynamics.com/blogs/post/?postid=777ee586-95a1-477a-85c0-e3cebe0d695d that this SQL job was written to avoid blocking and slow performance as of version 2.0.

Thanks in advance!
kind regards
Wim

Yes I know what this is. Sent from mobile

…

________________________________ From: wvhoof ***@***.***> Sent: Thursday, February 29, 2024 3:03:39 AM To: PFEDynamics/DynamicsPerf ***@***.***> Cc: Subscribed ***@***.***> Subject: [PFEDynamics/DynamicsPerf] EXEC DynamicsPerf.dbo.SP_PROCESS_STATS_LOW_PRIORITY keeps running (SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY) (Issue #22) We have some customers where lately the SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY keeps on running for hours and hours until the IT administrators decide to kill this SQL job. It even ran more than a day in some occasions before we killed it. These are all AX2012 customers. On some customer installations this happens on a complete new installation of DynamicsPerf (2.20) so with only a small DynamicsPerf database. On some customers DynamicsPerf has been running well for quite a while and this behavior did recently start. Whereas it used to run for like 20 seconds before. On some customer installations : after killing the SQL job once, the SQL job starts running normal afterwards again. For other installations it keeps on getting 'stuck'. We had to kill the job as it keeps on taking CPU time while running. Is this some known issue or something we can fix? I could see in the blog post https://community.dynamics.com/blogs/post/?postid=777ee586-95a1-477a-85c0-e3cebe0d695d that this SQL job was written to avoid blocking and slow performance as of version 2.0. Thanks in advance! kind regards Wim — Reply to this email directly, view it on GitHub<#22>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AB6JOAO7FTPABJWDUN66TCTYV4FIXAVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43ASLTON2WKOZSGE3DAOJZGEZDAMY>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[wvhoof]

Thanks Aaron!

Do you also have an idea on how it can be solved?

thanks,
grtz
Wim

[Alexander-Weurding]

It is possible to kill this job, with a job that is running every 5 minutes. Because this job should be done really fast.

…

-- stops a job named Weekly Sales Data Backup USE msdb ; GO EXEC dbo.sp_stop_job N'Weekly Sales Data Backup' ; GO

On Fri, Mar 1, 2024 at 9:01 AM wvhoof ***@***.***> wrote: Thanks Aaron! Do you also have an idea on how it can be solved? thanks, grtz Wim — Reply to this email directly, view it on GitHub <#22 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABNOLJCEEWX73KLSO4TLQELYWAYWHAVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNZSG4YDCMJQGY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Groeten Alexander

[wvhoof]

We can kill it indeed.
But then next time it restarts it takes again a very long time to complete and seems to slow down users working in AX.

[Alexander-Weurding]

Is it every time? you can kill it automatically.

…

On Fri, Mar 1, 2024, 09:41 wvhoof ***@***.***> wrote: We can kill it indeed. But then next time it restarts it takes again a very long time to complete and seems to slow down users working in AX. — Reply to this email directly, view it on GitHub <#22 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABNOLJCGRERN3WNEWZUPBMLYWA5KLAVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNZSG42TINRSHE> . You are receiving this because you commented.Message ID: ***@***.***>

[wvhoof]

Yes every time unfortunately :(.

[Alexander-Weurding]

I would check the table and indexes used and update the stats and do a defrag. And what will happen if you run this in a new query window? Btw do you get any locking. You can drop me an email with a teams invite if you like. So we can check it together.

…

On Fri, Mar 1, 2024 at 10:16 AM wvhoof ***@***.***> wrote: Yes every time unfortunately :(. — Reply to this email directly, view it on GitHub <#22 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABNOLJFHOADVFE76DNGDVFLYWBBP5AVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNZSHAYDSOBYG4> . You are receiving this because you commented.Message ID: ***@***.***>

-- Groeten Alexander

[wvhoof]

Thanks Alexander.
We were able to solve the issue it seems. The job is running fine again now.

Thanks!
Grtz
Wim

[Alexander-Weurding]

Great work! and and and what did you doo :) ? Thanks! Best regards Alexander PerfXit

…

On Tue, Mar 12, 2024 at 9:51 AM wvhoof ***@***.***> wrote: Thanks Alexander. We were able to solve the issue it seems. The job is running fine again now. Thanks! Grtz Wim — Reply to this email directly, view it on GitHub <#22 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABNOLJFGZTAYSF2RGPAHWBDYX26YTAVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJRGA3DKNJQHA> . You are receiving this because you commented.Message ID: ***@***.***>

-- Groeten Alexander

[wvhoof]

We did add a reindexing job for the DynamicsPerf database. As I noticed the DB was excluded from the regular reïndexing jobs.

However afterwards when starting the job, it still ran for like 8 hours before we killed it again.
This morning I did start it, and id started running fine.

This is the job that the Dynamics Performance tool runs to collect sys.datamart data into the DynPerf database which you can use to analyze the performance of your indexes and queries. If you’re having troubles, I’d suggest analyzing the performance data collected by this tool to figure out where your server is chugging. Sent from mobile

…

________________________________ From: wvhoof ***@***.***> Sent: Tuesday, March 12, 2024 2:13:19 AM To: PFEDynamics/DynamicsPerf ***@***.***> Cc: Aaron Stackpole ***@***.***>; Comment ***@***.***> Subject: Re: [PFEDynamics/DynamicsPerf] EXEC DynamicsPerf.dbo.SP_PROCESS_STATS_LOW_PRIORITY keeps running (SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY) (Issue #22) We did add a reindexing job for the DynamicsPerf database. As I noticed the DB was excluded from the regular reïndexing jobs. However afterwards when starting the job, it still ran for like 8 hours before we killed it again. This morning I did start it, and id started running fine. — Reply to this email directly, view it on GitHub<#22 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AB6JOAJ7D22HJ4KBWV2DYJDYX3BK7AVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJRGEYTKMBTHA>. You are receiving this because you commented.Message ID: ***@***.***>

There might be another job that is disabled by default which can clean up older data in the dynperf db, take a look around for that as well! Good luck! Sent from mobile

…

________________________________ From: Aaron Stackpole ***@***.***> Sent: Tuesday, March 12, 2024 7:41:07 AM To: PFEDynamics/DynamicsPerf ***@***.***>; PFEDynamics/DynamicsPerf ***@***.***> Cc: Comment ***@***.***> Subject: Re: [PFEDynamics/DynamicsPerf] EXEC DynamicsPerf.dbo.SP_PROCESS_STATS_LOW_PRIORITY keeps running (SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY) (Issue #22) This is the job that the Dynamics Performance tool runs to collect sys.datamart data into the DynPerf database which you can use to analyze the performance of your indexes and queries. If you’re having troubles, I’d suggest analyzing the performance data collected by this tool to figure out where your server is chugging. Sent from mobile

________________________________ From: wvhoof ***@***.***> Sent: Tuesday, March 12, 2024 2:13:19 AM To: PFEDynamics/DynamicsPerf ***@***.***> Cc: Aaron Stackpole ***@***.***>; Comment ***@***.***> Subject: Re: [PFEDynamics/DynamicsPerf] EXEC DynamicsPerf.dbo.SP_PROCESS_STATS_LOW_PRIORITY keeps running (SQL Job DYNPERF_PROCESS_TASKS_LOW_PRIORITY) (Issue #22) We did add a reindexing job for the DynamicsPerf database. As I noticed the DB was excluded from the regular reïndexing jobs. However afterwards when starting the job, it still ran for like 8 hours before we killed it again. This morning I did start it, and id started running fine. — Reply to this email directly, view it on GitHub<#22 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AB6JOAJ7D22HJ4KBWV2DYJDYX3BK7AVCNFSM6AAAAABD7WNP66VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJRGEYTKMBTHA>. You are receiving this because you commented.Message ID: ***@***.***>