A heavily written table has eleven indexes, and insert latency has grown as indexes were added over the years. Index usage statistics collected over a full business cycle show that four indexes are never used for reads and one duplicates another's key columns. What should the DBA do?
Select an answer to reveal the explanation.
Short Explanation
Every index is another notebook you have to update each time something changes. If four notebooks are never read and one's a copy of another, you're doing paperwork for nobody. You confirm they're truly unused across a full cycle, then drop them properly; adding yet another index only adds another notebook.
Full Explanation
Every insert, update and delete must also maintain every index on the table, so each additional index adds write overhead and storage. Index usage statistics show how often each index supports seeks, scans or lookups versus how often it is updated. When indexes show no read usage over a representative period, including month-end and other periodic jobs, and when one index duplicates another's key columns, removing them reduces write latency and maintenance cost without harming queries. Verification matters: check for indexes that enforce uniqueness, support foreign keys or serve rare but important reports, and keep a script to recreate them. Adding a covering index for inserts misunderstands how inserts work; an insert does not search for rows by a predicate, and another index means yet more maintenance per write. Rebuilding all indexes addresses fragmentation, but the scenario identifies the number of indexes as the cause, and rebuilds do nothing to remove the per-write maintenance of unused indexes. Vertical partitioning splits columns across tables to separate rarely used data; it does not reduce the count of indexes being maintained on the hot table. Exam caveat: usage statistics often reset when the service restarts, so confirm they cover a full business cycle before acting. Operational check: script the indexes, drop them in a window, monitor insert latency and query plans, and recreate any index whose absence causes a regression.