Repository Issues Troubleshooting - Precisely_EnterWorks - EnterWorks - 11.0

Repository User Preference

Check the user preference used on the listing page. If there is a sort column, make sure there is an index on that attribute. For multiple sort columns, think about creating a composite index. These must be created manually because Enterworks does not have this feature.

Repository Search

For repositories with very large amounts of records, using a general text search across all attributes can be expensive. It is recommended to use an Advanced Search specific to an attribute or set of attributes. The search will be faster if there are indexes on the search conditions.

Database Activity

While in the SQL Server Management Studio, select the EPIM database, and use the sp_whoisactive function to see what kind of queries are currently active.

There may be an expensive query that comes from a Scheduled Export of "View" type. Customers create their own SQL in this type of export, and they may not have considered performance. You can recognize this type of query because it will contain all friendly view names rather than the Enterworks standard names that begin with B_.

Database Indexes

Use the Index Fragmentation Report to identify whether maintenance should be completed for the database. The SQL is described here:

    -- Produces report of index fragmentation
    -- For highly-fragmented indexes, use the table name in column 2 and produce the following SQL commands:
    -- ALTER INDEX ALL ON MyTable REBUILD;
    -- To generate the commands in Excel:
    -- 1. Copy the second column (with header) to a spreadsheet
    -- 2. Remove the duplicate rows (the report lists the tables by index and some tables have more than one index, while the command rebuilds all indexes on the table).
    -- 3. Copy the following formula to the second column in the first table row (row 2):
    -- =CONCATENATE("ALTER INDEX ALL ON ", A2, " REBUILD")
    -- 4. Copy/paste the formula to every row.
    -- 5. Copy the resulting unique values (i.e., remove the duplicates) back to SQL Server Management Studio
    -- 6. Execute each copied command in SQL Server Management Studio.
    -- To determine how frequently indexes should be rebuilt will take re-running this script immediately after rebuilding the indexes to obtain a baseline report and then at periodic intervals (e.g. daily or weekly) and compare the new report to the baseline.
    -- The tables with a large page count and high increase in fragmentation would indicate a need to rebuild either on the measurement interval or more frequently. Note: Some of the tables are transient in nature, meaning rebuilding the index frequently may not have a large positive impact on performance. The likely candidates would be tables that include "TMP" or "BATCH" in the name. Refer to the EnterWorks Maintenance guide for more information.
    SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent, indexstats.page_count 
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats 
    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] 
    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] 
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id 
    WHERE indexstats.database_id = DB_ID() and dbindexes.[name] is not null 
    ORDER BY indexstats.avg_fragmentation_in_percent desc

Tomcat Thread Dump

Sometimes, you might need to check how Enterworks processes requests. If looking at user preferences, indexes, and search conditions doesn't help, you can take thread dumps of the Tomcat process. This can help Engineering see what's happening. It's best to take 2-3 thread dumps a few seconds apart to get a clear picture. Follow these steps:

1. Use the Windows Task Manager to find the pid of the epim Tomcat process.
2. Open a DOS command prompt as Administrator.
3. Change directory (cd) to the Enterworks/jdk/bin folder.
4. Run jstack -F pid > threaddump1.txt (use the pid you found in the first step).