Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-98370

Confluence site reindex - Optimize Expensive queries using OPTION (RECOMPILE)

XMLWordPrintable

    • 1
    • 2
    • We collect Confluence feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      Problem

      We have noted that site indexing related SQL queries in Confluence consume about 40% of the CPU load causing a huge performance bottleneck. It would greatly improve the performance of indexing process to optimize these queries.

      Suggested Solution

      Some of the sample queries which have been observed to cause high CPU load in customer environments include (These queries were monitored has high CPU/load events in Azure SQL by Microsoft Teams)

      1. (@P1 nvarchar(4000),@P2 bigint,@P18 nvarchar(4000),@P17 nvarchar(4000),@P16 nvarchar(4000),@P15 nvarchar(4000),@P14 nvarchar(4000),@P13 nvarchar(4000),@P12 nvarchar(4000),@P11 nvarchar(4000),@P10 nvarchar(4000),@P9 nvarchar(4000),@P8 nvarchar(4000),@P7 nvarchar(4000),@P6 nvarchar(4000),@P5 nvarchar(4000),@P4 nvarchar(4000),@P3 nvarchar(4000),@P0 int)select top( @P0 ) customcont0_.CONTENTID as contenti1_18_, customcont0_.HIBERNATEVERSION as hibernat2_18_, customcont0_.TITLE as title4_18_, customcont0_.LOWERTITLE as lowertit5_18_, customcont0_.VERSION as version6_18_, customcont0_.CREATOR as creator7_18_, customcont0_.CREATIONDATE as creation8_18_, customcont0_.LASTMODIFIER as lastmodi9_18_, customcont0_.LASTMODDATE as lastmod10_18_, customcont0_.VERSIONCOMMENT as version11_18_, customcont0_.PREVVER as prevver12_18_, customcont0_.CONTENT_STATUS as content13_18_, customcont0_.PAGEID as pageid14_18_, customcont0_.SPACEID as spaceid15_18_, customcont0_.PLUGINKEY as plugink18_18_, customcont0_.PLUGINVER as pluginv19_18_, customcont0_.PARENTCCID as parentc20_18_ from CONTENT customcont0_ where customcont0_.CONTENTTYPE='CUSTOM' and (customcont0_.PREVVER is null) and customcont0_.CONTENT_STATUS='current' and customcont0_.PLUGINKEY= @P1 and (customcont0_.PAGEID in ( @P2 )) and (customcont0_.TITLE in ( @P3 ,  @P4 ,  @P5 ,  @P6 ,  @P7 ,  @P8 ,  @P9 ,  @P10 ,  @P11 ,  @P12 ,  @P13 ,  @P14 ,  @P15 ,  @P16 ,  @P17 ,  @P18 ))
      
      2. (@P0 bigint,@P1 bigint,@P2 bigint,@P3 bigint)select contentent0_.CONTENTID as contenti1_18_, contentent0_.HIBERNATEVERSION as hibernat2_18_, contentent0_.TITLE as title4_18_, contentent0_.LOWERTITLE as lowertit5_18_, contentent0_.VERSION as version6_18_, contentent0_.CREATOR as creator7_18_, contentent0_.CREATIONDATE as creation8_18_, contentent0_.LASTMODIFIER as lastmodi9_18_, contentent0_.LASTMODDATE as lastmod10_18_, contentent0_.VERSIONCOMMENT as version11_18_, contentent0_.PREVVER as prevver12_18_, contentent0_.CONTENT_STATUS as content13_18_, contentent0_.PAGEID as pageid14_18_, contentent0_.SPACEID as spaceid15_18_, contentent0_.CHILD_POSITION as child_p16_18_, contentent0_.PARENTID as parenti17_18_, contentent0_.PLUGINKEY as plugink18_18_, contentent0_.PLUGINVER as pluginv19_18_, contentent0_.PARENTCCID as parentc20_18_, contentent0_.DRAFTPAGEID as draftpa21_18_, contentent0_.DRAFTSPACEKEY as draftsp22_18_, contentent0_.DRAFTTYPE as draftty23_18_, contentent0_.DRAFTPAGEVERSION as draftpa24_18_, contentent0_.PARENTCOMMENTID as parentc25_18_, contentent0_.USERNAME as usernam26_18_, contentent0_.CONTENTTYPE as contentt3_18_ from CONTENT contentent0_ where (contentent0_.VERSION in (select max(contentent1_.VERSION) from CONTENT contentent1_ where (contentent1_.CONTENTID= @P0 or contentent1_.PREVVER= @P1 ) and contentent1_.CONTENT_STATUS='current' group by contentent1_.LASTMODIFIER)) and (contentent0_.CONTENTID= @P2 or contentent0_.PREVVER= @P3 ) order by contentent0_.VERSION asc option (PLAN PER VALUE(ObjectID = 0, QueryVariantID = 1, predicate_range([sqldb-i-weu-cnf-db].[ADMIN_CONFL].[CONTENT].[PREVVER] = @P3, 100.0, 1000.0),predicate_range([sqldb-i-weu-cnf-db].[ADMIN_CONFL].[CONTENT].[PREVVER] = @P1, 100.0, 1000.0)))
      
      3. (@P0 nvarchar(4000),@P1 bigint,@P2 nvarchar(4000))select bucketprop0_.entity_name as entity_n1_64_0_, bucketprop0_.entity_id as entity_i2_64_0_, bucketprop0_.entity_key as entity_k3_64_0_, bucketprop0_.key_type as key_type4_64_0_, bucketprop0_.boolean_val as boolean_5_64_0_, bucketprop0_.double_val as double_v6_64_0_, bucketprop0_.string_val as string_v7_64_0_, bucketprop0_.text_val as text_val8_64_0_, bucketprop0_.long_val as long_val9_64_0_, bucketprop0_.int_val as int_val10_64_0_, bucketprop0_.date_val as date_va11_64_0_ from OS_PROPERTYENTRY bucketprop0_ where bucketprop0_.entity_name= @P0 and bucketprop0_.entity_id= @P1 and bucketprop0_.entity_key= @P2
      
      4. (@P0 bigint,@P1 bigint)select contentent0_.CONTENTID as col_0_0_, contentent0_.VERSION as col_1_0_, contentent0_.LASTMODDATE as col_2_0_, contentent0_.VERSIONCOMMENT as col_3_0_, contentent0_.LASTMODIFIER as col_4_0_, user2conte1_.SOURCEUSER as col_5_0_ from CONTENT contentent0_ cross join USERCONTENT_RELATION user2conte1_ inner join user_mapping confluence3_ on user2conte1_.SOURCEUSER=confluence3_.user_key inner join user_mapping confluence2_ on contentent0_.LASTMODIFIER=confluence2_.user_key where (contentent0_.PREVVER= @P0 or contentent0_.CONTENTID= @P1 ) and contentent0_.CONTENT_STATUS='current' and contentent0_.CONTENTID=user2conte1_.TARGETCONTENTID and user2conte1_.RELATIONNAME='collaborator' order by contentent0_.VERSION desc option (PLAN PER VALUE(ObjectID = 0, QueryVariantID = 1, predicate_range([sqldb-i-weu-cnf-db].[ADMIN_CONFL].[CONTENT].[PREVVER] = @P0, 100.0, 1000.0)))
      5. (@P0 nvarchar(4000))SELECT PLATFORM_KIND,ID,USER_TIMESTAMP,EVENT FROM AO_BBD998_USER_EVENT WHERE PLATFORM_KIND =  @P0
      

      The teams highly recommend using OPTION (RECOMPILE) optimisation on these queries, which is known to greatly improve the performance of similar operations.

      Why This Is Important

      Currently, site indexing is a process that can affect the overall performance of the instance. Since these are expensive operations, these optimisations can positively improve the performance of the indexing experience.

      Workaround

      No workaround at this point

              Unassigned Unassigned
              sganeshan@atlassian.com Sathya Ganeshan
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: