Performance Tuning [editor] [in progress]

In this section we present some techniques that can be used in order to find the performance bottlenecks or to increase the performance when working with database servers. 

Before descibing each individual technique there are some 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 indexes that are used but not by the specified workload sample.
• When 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

How to monitor the index usage

Indexing can both help and hurt performance. Maintaining indexes can slow things down and also require additional store. These are the main reasons why the indexes that are not used should be removed.

Oracle

A way of monitoring indexes in Oracle is presented bellow step by step:

1. Start monitoring the index usage

   ALTER INDEX index MONITORING USAGE
2. Workload on the system. Please make sure that the workload is representative for the normal usage of the system.

3. Stop monitoring the index usage

   ALTER INDEX index NOMONITORING USAGE

4. Query for the index in the V$OBJECT_USAGE to see if the index has been used.

   SELECT USED FORM V$OBJECT_USAGE WHERE INDEX_NAME = 'index'

   The query result will be either yes or no.

SQL Server

SQL Server's Dynamic Management Views are the key in this situation. Steps are presented below:

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

How to get queries in the logs

In order to see the queries in the logs you have to do the following settings: