Symptoms

Some questions around K2 database indexes/SQL side performance:

1. 
   
2.  
   Is there any statistics included in K2 database schema OOB and is it OK to use automatical updates for statistics in accordance with SQL Server internal logic/rules (auto_update_statistics=on)?
   
   
3.  
   It seems that indexes are partially missing in K2 database, there are some missing indexes which probably may influence performance.
    
   
   
    

Diagnoses

Partially information on indexes and statistics can be found here:

K2 Blackpearl Documentation - Developer Reference - Database Indexes

To address questions above specifically:

1. 
   
2. There is no statistics included in K2 shipped database schemas, in case there are some statistics added by client there is no issues with the automatic updates.
   
3. These indexes are specific to the customers workload, the indexes that K2 Provides out of the box are targeted to a wider variety of scenarios.
   
   The official recommendation is to avoid modifications of any database definition or database content unless specifically instructed to do so by K2. No changes to the K2 Database(s) definitions or content are supported unless specifically instructed by K2.
   See this section of K2 documentation for details:
   
    

K2 Blackpearl Documentation - Developer Reference - Database Reference

In theory adding of non-unique indexes do not change business logic and should not pose a threat to the integrity of the product, so it is possible to try and add those but using the following precautions/best practices:

- A documented naming convention should be used, e.g.:

.IX_SecurityCredentialCache_IsPrimary_UserName_X].
 

This will allow you to easily identify and DROP all custom indexes prior to upgrades – which MUST be done, as any changes to the database are extremely likely to break the installation process.

Indexes decrease the performance of writes to the database, but increase the performance of reads. This means that an index that SQL recommends can in fact negatively impact the performance of K2, therefore:

• 
  
•  
  You should add one index at a time, in descending order of impact.
  
  
•  
  Evaluate K2 performance after introducing each index.
  
  
   

Under no circumstances whatsoever can a UNIQUE index be created on K2 database.
 

Resolution

See Diagnosis section.