Plan Hash showing Insert operation Clustered Index cost is 100%,

The stored procedure having (Inserts is part of stored proc) is waiting for long time and Plan Hash showing Insert operation Clustered Index cost is 100%, Wondering how to resolve the issue ?

The insert table has primary index and multiple non clustered indexes, wondering if excessive indexes will have any impact while inserting in the tables ?, Please advise to reduce the cost (100%)

Find more posts tagged with

dpa_sqlserver

dpa for sql server

Accepted answers

All comments

Lofstrand

I would check if you have blockings on that insert query stat makes the insert take long time. Can also check "lock wait time".

All the indexes you have must be updated on every insert. So having more indexes SLOWS all inserts. They are "only" good for read operations. So having more indexes than you need will slow things down.

You can use the table tuning advisor to see if you have duplicate indexes. You can also see if they are used for read operations or not. (if asking SQL this directly, remember statistics are reset upon SQL engine restart). If they are not used for read operations, remove them, and inserts will be faster.

nirvenu

Yes, "lock wait time" is 2 Hrs 22mins

This current stored procedure is not reading this specific table in our insert scope, However, its dependent on the other stored procedures which are reading this table. So, we cannot remove the indexes from this table. Please advise a better options or alternatives

Lofstrand

You could dig around in DPA to find out what query is blocking your insert query, then it's that one that needs updating. If nothing if blocking your insert query, then it's the query itself that is slow.