Regular AEM Forms activity creates big load the database
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 EDCPOLICYENTITYwhere (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 EDCLICENSEENTITYwhere (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?
