Recently have was tasked to troubleshoot a custom BIRT report performance issue for a client for Maximo 7.1  The problem was that the report, which normally ran fine, was now taking over 30 minutes to run and eventually never ran at all, which then in turn resulted in other reports not being able to run.  So how did I resolve this issue?

The first step I did was take a look at the server CPU both on the Maximo server and the database server.  Everything seemed OK, the CPU wasn’t stuck at 100% on both servers.  The Maximo application was running fine with the exception of the reports.  The database was running fine as well.  I could run a SELECT * FROM WORKORDER with no issues.  So what was the problem?

The first thing I checked was the Report Administration and Select Action -> View Report Processing.  From there I could see that there were currently 5 reports running and they were all running for at least 30 minutes. Obviously this was strange as these reported normally run fine.  So we canceled them and tried to run again and same thing happened.  They would take 10 to run before we decided to stop running them.  At this point, I decided to change the default timeout settings for WebSphere.  In the plugin-cfg.xml file I changed the timeout ‘ConnectionTimeout’ to ‘5’ and the ‘ServerIOTimeout’ to 10 minutes.  Then ran the reports, and the same thing happened.  They hung, but this time, after a 10 minute timeout request, the report viewer threw a message saying the “server timedout”.  Which is much better than before.

Next, I decided to turn on logging for BIRT.  It turns out, turning on SQL logging for BIRT is broken in version 7.1.  To enable the SQL logging, I had to run a query:

UPDATE MAXLOGGER SET PARENTLOGGERID = 24 WHERE LOGKEY = 'log4j.logger.maximo.report.birt.sql'

This basically allowed me to see the child loggers of ‘report’ in the Logging application.  Now I was able to turn on SQL logging for any BIRT report by enabling DEBUG mode.

So we ran the report again and found the exact query the report was running.  It was a very complicated report, so I ran the query manually in SQL Server and found the root cause.  This query was taking over 10 minutes to run before I stopped it.  So now, obviously this has now become a database table indexing issue.  Now I’m no DB admin, so I decided to quickly Google a way to find missing table indexes in SQL Server.  That’s when I found this awesome article.  This article has a query which generate SQL commands for tables that are missing indexes.  Here is the query:

SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC

This query is a lifesaver but it only works on SQL Server 2005 and above.  I found and ran 3 new indexes on 2 different tables and ran the BIRT report again and now it ran in under 10 seconds.

The lesson learned here is that, if you are having performance issues with a BIRT report, it’s probably due to the query, so check that first and optimize it as best you can.