Finding Index Fragmentation

Index Fragmentation is defined as a condition where more than optimal amount of disk I/O is performed when accessing a table. In a simple term the SELECT is taking longer than usual to retrieve data from disk or table.

The database is used mainly to storing and retrieving key data of any organizations. Sometime, the data stored may  be modified to keep it current. In order words, the data gets deleted and modified frequently on a OLTP database environment. The frequency depends on the type of business and the nature of data you collect in your database. The frequent INSERT, UPDATE or DELETE statements make the disk to fragment causing your Index to fragment as well.

The fragmentation may reduce the speed of DML operation like SELECT, UPDATE and DELETE. It also occupies more disk space than required. To prevent this from happening, DBA should monitor fragmented Index and fix it as soon as possible. A slow system sometime may take the system down and it is often advised to monitor regularly and fix the fragmentation.

SQL Server has a tool to display fragmented Index and will give you an option to rebuild or re-organize it. Oracle does not have any such tools but they have system views where database stores the Index fragmentation information.

How to find Index fragmentation? First Analyze the Index and check deleted leaf row from index_stats view.

ANALYZE INDEX  emp_id_idx  VALIDATE STRUCTURE;
SELECT
    name
  , del_lf_rows
  , lf_rows - del_lf_rows lf_rows_used
  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness 
FROM   index_stats;

Only consider rebuilding Index if DELETED LEAF row is over 20%. Whenever there is over 10% data changes inside a table then you may consider rebuilding Index but I would always recommend using above stats before consider rebuilding it. Also checking the size of Index may help in some situations. I have seen Index size greater than the size of table which is a clear indication of fragmentation.

Ok, how to prevent Index fragmentation? There is nothing you can do to prevent Index fragmentation because when you are asked to make modification on data, you can’t say no because you might run a risk of Index fragmentation. Sometime a good database design may help but not always.

Index is a part of table which we were concerned about fragmentation because Fragmented Index may not enhance the query search result instead it degrade the query performance. Don’t you think the table gets fragmentation as a result of frequent DML operations? Of course it does and we will talk about table fragmentation also know as High Water Mark (HWM) in our next article. HWM is very interesting topic and is very useful during SQL performance optimization but often neglected.