Table in sql server 2017 takes alot of time to update/insert few rows

Question

Hello
I have a table in sql server 2017 that has around half million records and it is constantly used via website.
Select statement are very fast over it or over its views,but update/insert of 1 or 2 rows takes a lot of time sometimes 1 row needs 15 minutes as if table is busy.
I have other tables that have more rows but less used that don't have this issue.

This table has 1 primary key(1 field integer type) and other 6 non unique,non clustered indexes( some of them are added after doing a sqlprofiler analysis and it gave suggestions to add them).

It has 40 views over it.Only one of them has a clustered index that was created in order to add to it full text index.
Does the high number of views (even without indexes) affects the table update/insert?Or is it the number of indexes?Or both combined?

When running execution plan over one insert shows that :

• 77 % of the cost is over "clustered index insert" that shows all of the indexes over the table
• 12 % of the cost is over "clustered index merged" from the view that has index over it
• 8 % of the cost is over "clustered index insert" that show the index of the view
• 1 % : "clustered index seek" over full text index of the view
• 1 % : "clustered index seek" over primary key on another table that is joined with the indexed view

Initially the table had more non clustered indexes,I deleted around 5 of them and retried the insert with execution plan.The performance issue stayed the only difference is that the cost % over all indexes dropped a bit each time i removed an index

Answer 1

A view with an index will slow down inserts/updates. But the non-indexed views you have have no effect on inserts/updates.
From your description, I suspect your problem is blocking.

Tom

Answer 2

As Tom says, the issue could be blocking. To take that out of the equation, restore the database on a test server and try the insert. And try it with and without the indexed view.

Keep in mind that the percentages you see are based on estimates, and they can be flat wrong with regards to reality.

Answer 3

Hi @mahag ,

Do you test by referencing @Tom Cooper ’s and @Erland Sommarskog ’s replies?
Execute code as next and then update here:

select * from master.sys.sysprocesses

More information: https://support.microsoft.com/en-us/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems

BR,
Mia
If the reply is helped, please do “Accept Answer”.--Mia

Answer 4

Hi @mahag ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia

Answer 5

Hi @mahag ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia