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
*******************************

[kaynezhang]

This sql seems to be used by solr indexing.
Solr server talks to alfresco and get transactions that have been committed during begin and end time and then updates the indexes.

[rafter]

Hi,

thank you for replying.

So, how can i resolve this huge use of CPU.

have a nice day

[kaynezhang]

I'm sorry I am not a SQLServer DBA.
Then ony recommendation I can give you is :
Check the columns used in JOIN or WHERE clauses in the query, ensure there are indexes built on these columns.

[muralidharand]

Hi,
You can use sql server query execution plan, to get more information.
If you want to find out missing index, you can try the below link.

http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

Thanks,
Murali

[mrogers]

What version of alfresco are you using?   Community does not support SQL server so if you are using enterprise then contact alfresco support.

Sounds like you are missing an index or SQL server has decided not to use it.

[rafter]

hi,

thank you for your replay.

my alfresco version is : 4.1.2
sqlserver : standard edition 2008 R2
i've created some indexes in alfresco database on those tables :
CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_965FB] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id])
CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_E5F15] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id])
CREATE INDEX [Missing_IXNC_alf_child_assoc_parent_node_id_CB96A] ON [alfresco].[dbo].[alf_child_assoc] ([parent_node_id]) INCLUDE ([id], [type_qname_id], [child_node_name_crc], [child_node_name], [child_node_id], [qname_ns_id], [qname_localname], [is_primary], [assoc_index])

but, ive alway a huge usage of CPU on my Database base server.

[kaynezhang]

Have you tried to create an index  on "commit_time_ms "column of table "alf_transaction "?

[rafter]

hi,

when i execut this Missing index sql script, the result dont tell me to create this index :

– Missing Index Script
– Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

[mrogers]

If you are using 4.1.2 then call alfresco support before attempting to fix it yourself.

Did you create those indexes before or after you had problems?