August 24, 2020 at 7:02 am
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
August 24, 2020 at 8:18 am
15 min for one row is indeed very long.
Number of views shouldn't affect it unless it's an indexed view.
The number of indexes affected, more indexes: more housekeeping to keep everything in sync.
Is there an index that matches your update logic (to reduce locking)? So that SQL Server can quickly narrow down the affected records/housekeeping? Update/insert logic is SARGABLE?
Any foreign keys involved?
You might want to post a testcase and queryplan so it's easier to help
August 24, 2020 at 12:35 pm
Without seeing the full structures, it's hard to say for certain. As was already stated, a normal view would not in any way affect this. Only the materialized/indexed views will. It's all down to the maintenance of the indexes in support of the INSERT.
It's likely that it's just the maintenance of all the various indexes that is slowing things down. That should be visible within the execution plan. However, it's also possible it's something hardware related or possibly due to load or... not sure what. Follow this method to capture the wait statistics for the insert in order to understand where things are slowing down and why. That will give you a lot more information to understand why this is occurring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 24, 2020 at 2:02 pm
Actually, the number of views against this table are a pretty good indication of how much this table is used. Since it is so heavily used, it may be that the insert(s) or update(s) cause a page split in some of the indexes. It may also be that the one indexed view is really a problem, as well, because it may have to do a shedload of calculations with a ton of scans on the underlying tables after any insert or update. Lastly, the views may be taking a toll on when the table can be updated, especially if they're complicated and/or poorly written... especially that indexed view because it's compelled to do an update to anything that changes any of it's data and an INSERT would certainly do that. An UPDATE could also do that if it's one of the columns used in the indexed view.
All of that extra work and page usage may simply mean that the insert/update simply has to wait that long to get locks on the rows or pages it needs to do take the action.
To Joe Pattyn's excellent point, if you have FKs pointing at this table and the FK columns in those other tables aren't properly indexed (which I suspect is the actual problem based only on previous experience), that can be a huge issue (I've run into that problem a whole lot of times on people's databases).
In this case, the wait stats that Grant mentioned are certainly important but I'll also state that they'll be easier to understand if you were to attach a copy of the Actual Execution Plan (not just a graphic but the actual execution plan stored in a ZIP file so you don't have to futz with renaming it to attach it).
Also, I don't suspect it in this case but you (to be sure) also need to see if there are any triggers on the table.
For additional information on how to post performance issues, please see the article at the second link in my signature line below. Help us help you.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2020 at 2:56 pm
Oh great point. Check for triggers. Secret little turkeys will get you every time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply