Merge with too many indexes.

Question

Hi experts,

I have a MERGE statement that it is taking too long… On the execution plan, this is 50% of the workload.

As you can see, I think the MERGE is taking long because it has to update every index… I created the indexes some time ago per requests of people complaining certain reports were slow… I created them as “test…” but never checked how much were they used.

How can I check if they are really used or they are wasting space/resources…?

 

I also noticed the difference between actual/estimated number of rows, but I don’t see outdated statistics, how come this happens???

Thanks in advance!!

Answer 1

hi, these indexes are very degragmented? did you run some rebuild/reorganize task on them?

Answer 2

They are not. None of them exceeds 10% of fragmentation...

Answer 3

>How can I check if they are really used or they are wasting space/resources…?

sys.dm_db_index_usage_stats (Transact-SQL)

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

Yes, maintaining indexes has a cost.

I guess I never look at update plans, I only look at the plans for the select/where that drives them, or I'd see some horror stories like yours, I have some tables with way too many indexes, because once you have them, yes, they have to be updated, and hope they don't cause you deadlocks as they are.

As to the estimated/actual rows, that tends to increase with the scale of your database, data is often "lumpy" or "spikey" and for some value combinations the statistics are just not fine-grained enough and mistakes are made.  SQL Server just does the best it can with what it has.  Sometimes an option(recompile) will improve the plan with a fresh "sniff" of the values and less concern about about other values it may encounter - but it can't do much about index updates.

Josh

Answer 5

Hello,

implementing the correct index strategy is a job, it can't be explained in a post. Generally speaking every index could help in read (and even in writes) operation but every index has to be mantained i.e. it should be updated when the indexed columns are updated.

sys.dm_db_index_usage_stats

gives you an overview of each index usage, you should take care of the "scan count" and "seek count" counters togheter with the "updates".

Answer 6

Note that the info in sys.dm_db_index_usage_stats is reset after index rebuild (unless you are at a very recent patch level, I don't have the details here - but this is easy to test).

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 7

Well, disable the indexes with ALTER INDEX ix ON tbl DISABLE and see if this affects the execution time for the MERGE. Be careful not to disable the clustered index or any index that is read in the query plan.

You re-enable an index by rebuilding it.