Database Indexes

Database indexes are utilized by K2 to improve performance. You may need to maintain or rebuild these indexes form time to time to maintain performance of the K2 environment.

Maintaining Indexes for performance

The Stored Procedure Utility.Maintain_IndexDefrag can be used to maintain indexes for performance. If you are running large volumes of process instances a day, you may want to schedule this stored procedure to execute once a day.

Rebuilding Indexes manually

On upgrades, the indexes for K2’s largest databases - K2Server, K2ServerLog and K2SmartBroker – may take a long time to rebuild.  K2 utilizes SQL Server jobs to execute stored procedures that rebuild the indexes.  These jobs can be run during upgrades by clicking Repair on the K2 Server > Rebuild Indexes node in Configuration Analysis.  To successfully create and execute the jobs, the SQL Server Agent service must be started and the Installer Account must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role.

For more information, please see Create Jobs: http://msdn.microsoft.com/en-us/library/ms186273.aspx

In environments that either cannot allow SQL Server jobs to be created or that have large databases that may require additional planning for downtime, indexes should be rebuilt manually by executing the following stored procedures as either part of a job (recommended) or directly (not recommended). Note that these Stored Procedures are only available in Legacy (i.e. non-consolidated) databases.

K2 blackpearl Server should be stopped during the rebuilding of indexes.  Depending on the size of your databases, the load on your SQL server, and the server’s processing power, these stored procedures may take a long time to complete.

All other database indexes will continue to use the existing inline .sql logic and be upgraded automatically by the installer.

To check the status of the indexes rebuild, the Configuration Analysis should be used to run Analyze on the K2 Server > Database Rebuild Indexes node.  The Analyze node will have three  potential statuses. 

Additionally, the following stored procedures can be executed and the results analyzed.  Any returned rows indicate the indexes that have not yet been built.  Zero returned rows indicate that all indexes have been rebuilt successfully. Note that these Stored Procedures are only available in Legacy (i.e. non-consolidated) databases.

Rebuild indexes Task

1.       The analysis tool does this indexing by creating jobs using the SQL SERVER AGENT

 

 2.       The analysis tool checks for

 

 3.       Reporting