how to confirm or refute that performance degradation is due because of too many indexes maintained We know that indexes improve select statement and at the same time, indexes have a cost of maintenance on every update/insert/delete statement. 

I don't know how to measure/evaluate this cost.

Some context: There are a dozen key tables involved with several inserts/updates, having between 20M and 80M rows. 1400 transaction per second average.

In the lasts months, several indexes were added to optimize performance on different parts of the system. This worked right, but now some degradation on another part of the system appears.

I have this theory, but I don't know how to validate or refute it.

EDIT: I've read some other post, but most are SQL Server oriented, I'm running on SYBASE ASE 15.7, as tagged


How to know when/if I have too many indexes?
How to determine if an Index is required or necessary
Where can I find some guidance on index strategies?
Before trying to determine what indexes to disable (not doable in Sybase ASE) or drop, I'd want a better understanding of what 'some degradation on another part of the system appears' means.  I'd want a lot more details as to the type and degree of degradation.

For the sake of discussion I'm going to assume 'degradation' refers to some queries that are now performing poorly, in which case I'd want to look at what work's been done to (re)tune said queries; are query plans showing the 'wrong' indexes being used? are query plans showing the 'wrong' join order?

Yes, the performance degradation could be tied to the increase in the number of indexes, but what I'm thinking is that the degradation doesn't have anything to do with the overhead of updating the indexes (again, assuming 'degradation' is related to queries now performing poorly) but rather the generation of 'poor' query plans.



When optimizing a query, Sybase ASE is going to attempt to evaluate every possible join order, utilizing every possible index, utilizing every possible statistic on all possible columns and indexes. Net result is the optimizer has a LOT of work to do for queries that reference a large number of tables, indexes and column stats.

While it's technically possible that the Sybase ASE optimizer could go off for an hour while trying to optimize some big-arse, gnarly query ... and yes, the optimizer does have some 'smarts' that allow it to quickly eliminate some join/index combinations ... in reality the optimizer has limitations (via configuration parameters) on how long it can search for the 'best' query plan, with the (unfortunate) result being that for really large, complicated queries the optimizer could time-out before it finds the 'best' query plan, thus leaving us with a 'best up to this point' (ie, not-so-efficient) query plan.

Assuming your degradation issue is related to some queries that have all-of-a-sudden started performing poorly, I'm guessing the additional indexes could have increased the optimizer's workload to the point that it's now timing out before it can find the 'best' query plan.

Under this scenario there are a few options for (re)tuning the queries ... give the optimizer more time to find the 'best' (or at least a 'better') query plan ... provide hints (eg, index hints, abstract query plans) to limit the combinations the optimizer has to consider ... rewrite the query to simplify the optimizer's workload ...



While there may be some other explanations for the degradation you're seeing, the first step would be to get more details about the type and degree of degradation ... something we're not given in this thread ... and (unfortunately) a process that is likely going to be too large/complicated to cover in detail in this medium.