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?

View Entire Topic