Regular AEM Forms activity creates big load the database

AndrewS1

27-09-2019

We run AEM Forms 6.4 on Windows 2016 Server, with the database on MS SQL Server. We only use the Document Security module. The database is very large - we have ~ 800,000 security policies (EDCPOLICYENTITY table) and ~ 100,000,000 secured documents (EDCLICENSEENTITY and EDCREVOKATIONENTITY tables).

On a regular basis (maybe once a month or so) we see AEM Forms starting some activity which creates a huge load on the database. SQL Server disk queue jumps from 0.1 to 500 - 1,000 range. SQL Profiler catches the following long running queries:

--------------------------------------------------------------------------

exec sp_executesql N'select count(*) , MIN(sequenceNumber), MAX(sequenceNumber) from EDCLICENSEENTITY 

where EDCLICENSEENTITY.issueTime >= @P0        ',N'@P0 bigint',1537660800000

3 - 8 min

--------------------------------------------------------------------------

declare @p1 int

set @p1=1073741825

declare @p2 int

set @p2=180150003

declare @p7 int

set @p7=10000

exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 bigint,@P1 int',N'select top 10000  * from EDCREVOKATIONENTITY where

(EDCREVOKATIONENTITY.sequenceNumber > @P0 and EDCREVOKATIONENTITY.revokeState > @P1) order by

EDCREVOKATIONENTITY.sequenceNumber ASC                ',4104,8193,@p7 output,0,1

select @p1, @p2, @p7


10 - 25 min

--------------------------------------------------------------------------

declare @p1 int
set @p1=1073741841
declare @p2 int
set @p2=180150081
declare @p7 int
set @p7=10000
exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 bigint,@P1 int',N'select top 10000  * from EDCPOLICYENTITY

where (EDCPOLICYENTITY.sequenceNumber > @P0 and EDCPOLICYENTITY.cacheLocalVersion > @P1)

order by EDCPOLICYENTITY.sequenceNumber ASC                ',4104,8193,@p7 output,0,1
select @p1, @p2, @p7


1 - 3 min

--------------------------------------------------------------------------

declare @p1 int
set @p1=1073741843
declare @p2 int
set @p2=180150195
declare @p7 int
set @p7=3762
exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 bigint,@P1 int',N'select top 10000  * from EDCLICENSEENTITY

where (EDCLICENSEENTITY.sequenceNumber > @P0 and EDCLICENSEENTITY.version > @P1)

order by EDCLICENSEENTITY.sequenceNumber ASC                ',4104,8193,@p7 output,0,1
select @p1, @p2, @p7


10 - 15 min

--------------------------------------------------------------------------

My question is - what is this activity, and, more importantly, how can we turn it off?

Accepted Solutions (0)

Answers (5)

Answers (5)

AndrewS1

02-10-2019

Thanks workflowuser​, I already did - twice. First time I was told that this is how the product behaves and it cannot be changed. They could not even explain what causes this. Second time I was told that it's caused by key rollover, and they suggested to increase the rollover frequency. As I said above, we now have it set at 9,999 days, but the issue still happens. Any ideas how to stop this activity would be appreciated. Is there some internal process (scheduler) I can stop?

AndrewS1

28-09-2019

WASIL​ Thank you for the quick reply. Our key rollover frequency is set to 9,999 days (~ 27 years), but we still see the above activity on a regular basis. Any help on how to stop it would be really appreciated.