Question Re: Statistics on index usage in Technology Q&A

Statistics on index usage

Former Member — Tue, 17 Feb 2015 14:07:29 GMT

v12.01.4142

Are there any system tables that would help in determining which indexes are being used and which are not?

Re: Statistics on index usage

VolkerBarth — Tue, 17 Feb 2015 14:29:41 GMT

Why do you expect such information in system tables? - As SQL Anywhere does usually optimize statements when they are executed (and not beforehand, i.e. not when views, stored procedures and the like are created/altered), it will depend on the actual queries whether an index will be used or not. You can have a look at the plans of a query to see what indexes are used.

Here are some possible helpful links from the v12.0.1 docs:

Use indexes effectively
Index Consultant - that may help to get general information of useful indexes based on a single query or a particular workload
Query execution plans

Or do you relate to the automatically created indexes for primary, unique and foreign keys? They will be used to check for unique key values and/or existing FK values (besides the normal usage of indixes to access rows efficiently).

Re: Statistics on index usage

Former Member — Tue, 17 Feb 2015 14:55:37 GMT

What we are trying to do is to eliminate un-needed indexes. Ex. On one table, we have 10 indexes and we would like to drop indexes that are not being used or rarely used.

Re: Statistics on index usage

VolkerBarth — Tue, 17 Feb 2015 15:10:36 GMT

So we can assume these inxexes are "secondary" ones, i.e. not needed for PKs/UNIQUE KEYs/FKs? - Then you may use "typical queries" on this table to find out whether these indexes are used. The Index Consultant may be helpful here, too, as it can check whether secondary indexes are used or not.

Re: Statistics on index usage

justin_willey — Tue, 17 Feb 2015 15:37:37 GMT

I can see how this would be useful; it's something that Gupta SQLBase used to have - a UseCount field in the sysindexes system table. We monitored it for indexes where the count wasn't increasing over a fairly long period and dropped them if they never got used.

However I imagine that there is considerable overhead to maintain such data, and it's noticeable that later versions of SQLBase don't support it.

If you have a lot of dynamic SQL, effective checking of plans for which indexes are selected for use is going to be tricky, and can change as the distribution of data changes. At the end of the day I think you are going to have to rely on your knowledge of your product and therefore they sort of queries that are going to be run, to decide what is likely to be useful.

There are two things in your favour though, SQL Anywhere automatically optimizes duplicate indexes and only maintains one physical version. Second, unless you have very high transaction throughput, the actual overhead of maintaining indexes seems to be pretty low, compared with everything else going on.