Page History

...

Before descibing each individual technique there are some generic things to consider:

• Do not build indexes unless necessary
  • Indexes that are not used should be removed because their maintenance is expensive in terms of CPU usage and I/O resources demand.
  • Monitoring indexes usage tools functionalities can be used to check if an index is used or not.
  • It is strongly recommended that while monitoring the index usage a representative workload is performed on your system to avoid droping dropping indexes that are used but not by the specified workload sample.
  • See when is necessary to build indexes section for mode details. (Voicu Moldovan add link)
• Consider to indexing key that contains data that is frequently queriedWhen choosing indexes consider to index columns that contains representative data types used in the blueriq model.
  • For example if you work with numeric values more than with string values it is most probable to perform queries that require indexes on numeric columns, so persisting indexes for string column is useless.
• Chose indexing columns for which a high percentage of rows have the same value.
• Avoid using poor selectivity keys or expressions with few distinct value as indexes.
• Avoid indexing columns that are updated frequently because additional processing is required for CREATE, UPDATE and DELETE operations when the indexes are present.
• Measure the performance with and without indexes in order to see if the performance gain on adding indexes is worth the performance loss on CREATE, UPDATE and DELETE operations.
• If keys that represent data types form the model are frequently used together please consider composite indexes.

How to create bitmap indexes

Bitmap indexes are oracle specific indexes that can be used on the columns that have low cardinality, meaning that the column has relatively few unique values. For example in the CASES table, the column STATUS can have only few values. Lets consider OPEN, LOCKED and CLOSED for this example.

In order to create a bitmap index in CASES table on the column STATUS the following query can be executed:

Please consider a bitmap index for any index that with less than 100 distinct values. Also, if your table has one million rows, a column with 10,000 distinct values is a candidate for a bitmap index.You can check how to see an index usage in the section below.

The following columns can represent good candidates considering the above condition:

• In the case table
  • status
  • locked by
• In the tasks table
  • status
  • type
• In attfibutes value table
  • value type
  • boolean value
  • entity value
• In the custom fields table
  • custom fields name
• in the aggregate table
  • type
  • latest

Avoid adding bitmap indexes on any type of id columns.

SQL Server does not support bitmap indexes, but it has something called bitmap filtering. Contact your Database Administrator for more information about this topic.

How to monitor the index usage

...

1. Workload on the system. Please make sure that the workload is representative for the normal usage of the system.

2. Run the following query that will expose how many times the index was used for user queries:

   SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
          I.[NAME] AS [INDEX NAME], 
          USER_SEEKS, 
          USER_SCANS, 
          USER_LOOKUPS, 
          USER_UPDATES 
   FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
          INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
   WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
          AND S.database_id = DB_ID(),

    

3. In order to find indexes that are not used, you need to check the values that have no seeks, scans or lookups but they have updates. 

How to obtain an execution plan

Oracle

 An execution plan is a sequence of operations that Oracle optimizer performs when executing a query. Obtainint the execution plan the following two steps have to be performed:

1. Run explain plan command

   EXPLAIN PLAN FOR SELECT STATUS FROM CASES;

2. Display the execution plan

   SET LINESIZE 130
   SET PAGESIZE 0
   SELECT *
   FROM TABLE(DBMS_XPLAN.DISPLAY);

After running the command from above you should see a similar output with the one below:

Image Added

SQL Server

How to get queries in the logs

...