Long running SQL scripts

Epicor ERP 9
1

Does anyone know if these two queries are system generated queries or know what process is running them? They are our two heaviest running queries that is impacting performance when it gets ran but can’t pinpoint what is executing the queries to look into.

Query #1:
(@P1 int)
SELECT PROGRESS_RECID,
“SysTaskNum”
FROM “dbo”.“SysTask”
WHERE ((“SysTaskNum” > @P1))
ORDER BY “SysTaskNum” DESC

Query #2:
(@P1 varchar(8),@P2 int)
SELECT PROGRESS_RECID,
“company”,
“alertnum”,
“errlognum”
FROM “dbo”.“AlertLog”
WHERE ((“Company” = @P1)
AND (“Alertnum” = @P2))
ORDER BY “company” DESC,
“alertnum” DESC,
“errlognum” DESC

2

Global Alrts and System Tasks it is normal for them to run often.
If they are taking too long reindex or clean up your old system tasks and alerts

2 Likes
3

So are those queries related to global alerts and system task or BAQs and BPMs? Trying to narrow it down to see what is causing it.

5

We are seeing high execution times (4-6 minutes) to execute them and throughout the day, it is our top 3 highest execution queries.

6

Is there a way to see in SQL Studio when these global alerts and system tasks are running?

7

You can use SQL Server Profile to see it “live” (danger Will Robinson)
This method is very intrusive and will blow up your performance. But it can be done.

8

I was thinking of using Profiler in our test environment where there are minimal processes running and run both the Global Alerts and System Tasks to see if we can determine if it is exactly those processes to narrow it down.

1 Like
9

Good plan!

10

Before going the Profiler route, I ran both global alerts and system tasks but did not see them in SQL Studio in the “recent expensive queries” section in Activity Monitor…

11

There is a BAQ called “zES_JobOperations” that uses the JobOper table. Do you know where the zES_JobOperations BAQ is used?

Query #3:
SELECT PROGRESS_RECID,
“Company”,
“JobNum”,
“AssemblySeq”,
“OprSeq”
FROM “dbo”.“JobOper”
ORDER BY “Company”,
“JobNum”,
“AssemblySeq”,
“OprSeq”

12

We believe the Process MRP task is running the Query #3 script that is taking a long time. Anyone have any suggestions to improve running the Process MRP since the query runs it wide open with no “where” clause and maybe created “as designed”. Below are our parameters.

image
image.png581×635 12.7 KB

13

Chia - How long does your MRP process take? We run our regen after 2nd shift, which takes about 90 minutes. Our net change takes about 10 minutes, though…

A.

14

Our Regen takes about 90 minutes with 426,000 parts. Our other company’s Regen takes about 40 minutes with 131,000 parts.

How many parts do you have? Is this normal?