Indexes are Good for Performance
Generally speaking, DBAs and developers spend a fair amount of their performance time trying to find which indexes to add to a database to improve performance. If a specific query is having performance problems, one of the strongest tools in our box is indexing.
Indexes are Bad for Performance
Indexes aren’t all good, though. Putting aside the fact that some indexes, particularly low-cardinality indexes, can actually hurt performance, indexes are physically stored on disk. This means that not only do they take up disk space, but they also hurt the performance of operations that insert, update, or delete data. Each additional index represents one more place on disk Db2 has to go insert, update, or delete the same data that is stored in the table. Indexes also have to be maintained through reorgs and runstats and backed up with the rest of the database.
This means that you must decide on your priorities for performance when deciding how many indexes to add. These priorities may be different from table to table within the same database. It is often helpful to categorize tables in your data model to understand their performance priorities.
Many Db2 consultants have horror stories of going into a client and finding 30 or more indexes on a table that is critical to OLTP performance. At least one consultant I know claims to have put his daughters through college and paid for their weddings by dropping indexes.
There is no magic number for the correct number of indexes on a table. For Db2, often a good index that helps multiple queries is better than a perfect index for every query. Often databases on the analytics/BI/Data Warehouse end of the spectrum are likely to have more indexes than those on the OLTP/transaction processing/e-commerce end of the spectrum. This is because the priority in analytics databases is queries, while the priority in transaction processing databases is often the small singleton-row updates, inserts, and deletes that additional indexes can slow down.
Identifying Unused Indexes
With Db2 9.7, IBM introduced a column in several system views, including SYSCAT.INDEXES, that records the last time an object was used. This column, called LASTUSED, is not updated immediately upon usage of an object to prevent performance impact but is generally accurate. There were some issues with early fix packs of 9.7, so the numbers should not really be considered accurate if you’re running older code. However, on later fix packs of 9.7 and the later versions, this information is a very valuable tool.
My favorite query for finding unused indexes is:
select i.lastused,
substr(t.tabschema,1,20) as tabschema,
substr(t.tabname,1,30) as tabname,
substr(i.indschema,1,20) as indschema,
substr(indname,1,40) as indname,
substr(colnames,1,60) as colnames,
bigint(fullkeycard)as fullkeycard,
bigint(card) as table_card,
case
when card > 0 then decimal(float(fullkeycard)/float(card),5,2)
else -1
end as pct_card,
mi.index_scans,
mt.table_scans,
mi.index_only_scans,
mi.page_allocations,
volatile
from syscat.indexes i join syscat.tables t
on i.tabname=t.tabname and i.tabschema=t.tabschema
join table(mon_get_index('','',-2)) as mi on i.iid=mi.iid and i.tabschema=mi.tabschema and i.tabname = mi.tabname
join table(mon_get_table('','',-2)) as mt on i.tabschema=mt.tabschema and i.tabname=mt.tabname
where
indextype not in ('BLOK', 'DIM')
and t.tabschema not like 'SYS%'
and uniquerule='D'
and not exists (select 1
from syscat.references r join syscat.keycoluse k
on r.tabschema=k.tabschema and r.tabname=k.tabname
where t.tabschema=r.tabschema
and r.tabname = t.tabname
and k.colname in ( select colname
from syscat.indexcoluse as ic
where ic.indschema=i.indschema
and ic.indname=i.indname))
and i.lastused < current timestamp - 30 days
order by mi.index_scans, i.lastused, fullkeycard, card
with ur;
Sample output:
LASTUSED TABSCHEMA TABNAME INDSCHEMA INDNAME COLNAMES FULLKEYCARD TABLE_CARD PCT_CARD INDEX_SCANS TABLE_SCANS INDEX_ONLY_SCANS PAGE_ALLOCATIONS VOLATILE
---------- -------------------- ------------------------------ -------------------- ---------------------------------------- ------------------------------------------------------------ -------------------- -------------------- --------------- -------------------- -------------------- -------------------- -------------------- --------
01/01/0001 DBA LOCK_EVENT DBA LOCK_EVENT_IND1 +XMLID -1 -1 -1.00 0 1 0 43
01/01/0001 DBA LOCK_ACTIVITY_VALUES DBA LOCK_ACTIVITY_VALUES_IND1 +XMLID+PARTICIPANT_NO+ACTIVITY_ID+UOW_ID -1 -1 -1.00 0 1 0 1
01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GRG_CONSTRAINT1 +STOREID 1 29509 0.00 0 3738 0 0
01/01/0001 WSCOMUSR GRADDR WSCOMUSR GRADDR_INDEX2 +LASTNAME+FIRSTNAME+STATE 10030 31774 0.31 0 0 0 1
01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GR_INDEX2 +POSTEVENTADDRESS_ID 29509 29509 1.00 0 3738 0 0
01/01/0001 WSCOMUSR GRGFTREG WSCOMUSR GR_INDEX3 +PREEVENTADDRESS_ID 29509 29509 1.00 0 3738 0 0
6 record(s) selected.This query attempts to find indexes that are not required for unique constraints, foreign keys, or block or dimension indexes for MDC tables. It looks for indexes that have not been used in the last 30 days. Consider if your database might have other requirements to consider. Some indexes may only be used at month-end, quarter-end, year-end, or during some special annual period like Black Friday.
Backout Planning
Everything you do in a Db2 environment should have a back out plan. Before dropping an index, make sure you have the syntax to recreate it. db2look is a great tool for this. Be sure to retain this syntax for at least a few months in case the index ends up being something that was needed for a periodic process several months down the line. Re-creating an index if performance problems are encountered is nearly always quick and easy.
Verifying Impact
To understand the impact of a dropped index, it is best to get a baseline in a load testing environment, then drop the index and repeat the load test to see what, if any difference is made. The criteria for success is lack of degradation. You may not see improvement for each index, but you are still saving disk space and other resources.
If you don’t have a load-test environment, it is unlikely that basic functional testing will help, but it can’t hurt, so still do the drop in a non-production environment first. If possible, take a baseline before the drop and compare to after the drop to see if there are any significant differences.
For a truly unused index, you may not see a positive impact, but it is still beneficial to remove it.
Summary
Removing indexes can help performance or help prevent performance degradation, particularly for OLTP environments. It can free up disk space both for the database and backups and make backups and table maintenance run faster. Contact the expert XTIVIA Db2 DBAs today to discuss a project to identify and carefully remove unused indexes.