Issue Summary

Confluence 10.2.x, instances running on Microsoft SQL Server may experience High Database CPU Usage during background indexing tasks.
This stems from the HQL query in ContentEntityObjectHibernateDao.getAllModifiers, which is used during index flushing.

The SQL Query simialr to below will be observed to consume high CPU on the Database:

select ... 
from CONTENT contentent0_ 
inner join user_mapping confluence1_ on contentent0_.LASTMODIFIER=confluence1_.user_key 
where (
	contentent0_.PREVVER in ( @P0, @P1, ... @P63 ) 
	OR contentent0_.CONTENTID in ( @P64, @P65, ... @P127 )
			)
and contentent0_.CONTENT_STATUS='current'

The query uses an OR across two columns (PREVVER and CONTENTID), so SQL Server may scan the whole c_prevver_idx index. Resulting in high CPU load depending on the size of the CONTENT table.

Steps to Reproduce

Environment: this is tested in Confluence 10.2.19. The code responsible for this SQL can impact Confluence from 9.5.0 onwards

1. Run Confluence 10.2.19 on MSSQL with a large CONTENT table (eg: 15M+ rows)
2. Trigger an incremental reindex or wait for the IndexFlushScheduler to process a large batch of updates.
   • this may happen naturally from user actions such as lots of page edits, or permission change on large page tree
3. Observe DB CPU usage and execution plans for the query above

Expected Results

Index Flushes (that runs regularly) should avoid sending SQL that causes unnecessary/expensive scans on the database table.

Actual Results

Database CPU Usage spikes up during index flushes, where the SQL Query below can be observed to cost high CPU and takes a long time to complete:

select ... 
from CONTENT contentent0_ 
inner join user_mapping confluence1_ on contentent0_.LASTMODIFIER=confluence1_.user_key 
where (
	contentent0_.PREVVER in ( @P0, @P1, ... @P63 ) 
	OR contentent0_.CONTENTID in ( @P64, @P65, ... @P127 )
	)
and contentent0_.CONTENT_STATUS='current'

The number of values for both IN operators may not always be 64
The reason for high CPU mainly come from how SQL Server executes the combination of PREVVER in (...) OR CONTENTID in (...), where no single index is sorted by both columns, so one index cannot find all matches with a seek

The Caesium thread that's working on Index Flush can be observed in Thread Dumps going through getAllModifiers

"Caesium-1-4" daemon prio=1 tid=0x0000000000000000 nid=0 runnable
   java.lang.Thread.State: RUNNABLE
	at java.base@21.0.11/sun.nio.ch.Net.poll(Native Method)
...
	at com.atlassian.confluence.internal.persistence.hibernate.ContentEntityObjectHibernateDao.getAllModifiers(ContentEntityObjectHibernateDao.java:711)
...
	at com.atlassian.confluence.impl.search.v2.extractor.ContentModifiersBulkExtractor.extractAllModifiers(ContentModifiersBulkExtractor.java:74)
...
	at com.atlassian.confluence.impl.search.IndexFlushScheduler.lambda$new$1(IndexFlushScheduler.java:118)

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available