Skip to main content

Re-index

On large databases, the index fragmentation can cause stored procedures using tables with fragmented indexes to perform 2-3 times slower or more, which may not be visible in most PerfSproc log entries. Individual stored procedures may be sub-millisecond either way; however, when adding up thousands of stored procedure calls, especially when running auto discovery or rendering pages in solutions with many resources (like PDRs, user classes and target systems), tiny differences slowly add up.

Some tables in the backend database are rebuilt from scratch during psupdate , fragmenting their indexes, and with large tables, that leads to reduced performance. You can improve performance by:

  1. Analyze the PerfSproc entries in the following ways in idmsuite.log. This should reveal which stored procedures take most time; address those and the large tables they use first.

  2. Remove data according to company data retention policies:

    • Trim unnecessary tables; for example, the cookie table.

    • Back up and trim historical tables the daily use of the system doesn't require. For example, sesslog_full, *_audit, and*.hst.

    • Back up and trim invalid data from the tables the system does use, especially used in the stored procedures which take the most time. For example, account, acctattr, nosgroup, profile, userattr, reqbatch, and managegrp.

If those steps don't increase performance enough, then look at index maintenance.

Rebuilding all indexes is simple in itself; however, it takes time to get the parameter values just right and find the correct strategy regarding the frequency and time of day in relation with other product and database server scheduled processes. Consider the following:

  • Only tables with over millions of records will benefit from being index-optimized, and will result in a meaningful increase in speed for long-running tasks like psupdate, idtrack, queue processing in services, or if they significantly impact user experience due to WebUI slowness on complex pages.

  • Indexes on historical/audit tables that tend to add entries at the end of the index list, can do with a smaller fill (70% to 90%).

  • For indexes that can get new entries all over the index, the fill should be lower (50-% to 70%).

Experimentation with these settings can lead to a good balance between maintenance time and improved performance results.

Make sure that you do not reindex the database while psupdate, autores, idtrack, or other tasks are running.

If reindex execution is force-configured with no locks, that will invalidate the execution plans that the database creates for the stored procedures used in those tasks, and often cause deadlock-like symptoms.

The following is an example script to use to identify fragmented indexes, check what slow stored procedures cause the unwanted slowdown, what tables they use and the high percentage of the stored procedure execution those tables use in the execution plan, and rebuild only those.

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'<database_name>'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESCand fill the query-fix-indexing.sql with the correct maintenance script, for example:

USE <database_name>;
ALTER INDEX ALL ON <table_name1> REBUILD WITH (FILLFACTOR=90);
ALTER INDEX ALL ON <table_name2> REBUILD WITH (FILLFACTOR=70);

Ensure that you have sqlcmd installed on the primary node.

The actual SQL script requires experimenting with the index rebuild settings to create a good balance of maintenance time versus performance after scripted maintenance is applied:

  • For tables with multiple indexes, rather than rebuilding ALL, individual indexes can be rebuilt (the ones which affect specifically-sought performance).

  • In a production environment you will have to find a way to call sqlcmd or a similar Microsoft utilility without spelling out the credentials on the command line.

If all this has been completed and performance is still an issue for specific stored procedures, contact support@bravurasecurity.com to ask if it's possible to optimize the specific stored procedures that slow down binary execution . In your email, include the steps you followed to increase performance, and:

  • A log containing PerfSproc procedure entries for the binary runs that need optimization,

    or

  • A verbose log for those binaries that did not have the PerfSproc option in the logging service.

In this section: