Insert into clustered index taking too long

mprokop 2013-04-15 19:44:18

We are using a stored procedure that inserts into a table. According to the plan explorer the hang up is on the clustered index. The fill factor is around 92% and there is less than 1% fragmentation.

Aaron Bertrand 2013-04-15 19:55:23
Well, on a quick glance, it looks like you are updating 3 tables (does the base table have indexed views, or an instead of insert trigger?), and 17 non-clustered indexes. Index updates are not free. We do not have enough information to know whether all 17 indexes are necessary, but I'd start by examining those and eliminating any that are not frequently used (e.g. depending on how long your instance has been up, you can look at sys.dm_db_index_usage_stats to determine whether certain indexes have been used in user queries).
mprokop 2013-04-15 20:03:29
It does update a couple other tables as well. The table that the hold up is happening on has 11 other indexes. The oddity is that we have hundreds of other databases that are set up almost identically that do not have this issue so I am mostly trying to track down where the unique factor is with this database.

There are no indexed views or instead of insert triggers. I am checking in to what indexes are all necessary with our developers as I know some of them are legacy items.

TheSQLGuru 2013-04-17 13:50:28
Other databases that do not have this problem may have much fewer rows and/or less concurrent insert/update/delete activity and thus less blocking. Either way you lose. Check for blocking in this database during the update. I recommend the awesome free sp_whoisactive from sqlblog.com for that.

Often when I see stuff like this at a client it is from misuse of Database Tuning Advisor. That thing often does REALLY bad things when used by untrained devs/dbas. Tons of overlapping indexes (DTA LOVES covered indexes and does nothing to check for existing indexes that could be just one additional column from covering the currently-analyzed query).

Do beware if/when you go to drop indexes though. Check ALL your code for hard-coded index hints first. Also do not drop unique indexes of any type (unless it is a duplicate of another unique index).