Update causing view to update indexes, which means incredibly slow updates.

Dale 2013-08-27 22:45:59

I am running a sproc to update a single row in a table. This table is referenced by a view, which forces an index seek on all tables referenced by the view. Up until recently, it was running pretty fast. Now it has slowed to ~5 minutes.

Is this normal behavior? How can I optimize this situation?

Dan Holmes 2013-08-27 22:55:14
Has the view that is being referenced always been an indexed view? Perhaps it wasn't and now it is and that is why the performance changed suddenly.
Dale 2013-08-27 23:10:22
The view is neither indexed nor created with schemabinding, which was my first guess as well.
SQLkiwi 2013-08-28 01:11:31
The query plan clearly shows an indexed view being maintained as part of the update.
Dale 2013-08-28 20:06:49
My bad. I was looking at the wrong view. you're right. It is indexed. I don't know when it changed though. Looking into it.
Dale 2013-08-29 22:37:07
So it appears that it was the indexed view. It's been there apparently for a couple years, but only recently started causing issues. I think it reached a tipping point in terms of performance, but I can't be sure of that. Removing all indexes, including the PK, restored performance back to normal speed. Thanks all.