Alfresco database Use huge CPU 90%

[rafter]

Hi,

i'll quickly describe you, my architecture:
i've 2 servers :
1-alfresco server ( tomcat )
2-sqlserver 2008 r2 database server where ive created my alfresco database and others like bonita …

when i monitor the database server, ifound the sql query that use 90% of the CPU.
so the consequence that other applications like bonita  are so slow.

here's the request that use CPU :

*****************************
select
            txn.id              as id,
            txn.commit_time_ms  as commit_time_ms,
            (select
                 count(node.id)
             from
                 alf_node node
             where
                 txn.id = node.transaction_id and
                 node.type_qname_id !=  @P0
            ) as updates,
            (select
                 count(node.id)
             from
                 alf_node node
             where
                 txn.id = node.transaction_id and
                 node.type_qname_id =  @P1
            ) as deletes
        from
            alf_transaction txn
         WHERE txn.commit_time_ms >=  @P2
          
          
          
               and txn.commit_time_ms <  @P3 
        order by txn.commit_time_ms ASC, txn.id ASC
*******************************

[rafter]

i've create them after the problem .

[kaynezhang]

In your cpu consuming sql column "commit_time_ms" is used as where condition,you should build index on this column

[rafter]

hi,

I've created this index like this :

CREATE INDEX [Missing_IXNC_alf_transaction] ON [alfresco].[dbo].[alf_transaction] ([commit_time_ms])

but in the sql execution plan of the request i see that the estimated operator ( 1% ) and CPU (0.0048) Cost are so low.
but for this index : CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_E5F15] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id]) , the estimated operator ( 28% ) and CPU ( 0.67 ) Cost are so important.

NB : the avg UC use of this request is between 450 and 1500 ms/s.

[kaynezhang]

Then what do you want to ask?

[rafter]

i have always the same problem ( huge usage of cpu by alfresco ).

how can i resolv this problem and thank you