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?
