February 5, 2020 at 4:41 pm
I have this huge table that is causing me some issues in regard to inserts. The table has 55 million records and 18 indexes on it (all non clustered). Numerous records get added to this table throughout the day. However at least once a day 50 or so inserts will hit that table right around the same time and one of the inserts will block the rest of the inserts from happening and they time out. I have used a combination of sp_who and sp_who2 as well as the activity monitor and some custom scripts to identify the culprit inserts. Has anyone else ran into a similar issue? There are 2 indexes that I could probably drop - but I'm also not certain that would fix the issue. Any recommendations on how to avoid these blocks so that the inserts happen without issue?
February 6, 2020 at 1:28 pm
ALL nonclustered? so it's a heap? - I have no idea how SQL will handle 55 million rows with regard to page splits at that point
I'm afraid we have all hit this issue - it's part of the pain point of locking, writers will block other writers. Without a query plan or a bit more technical detail I doubt anyone will be able to give you a fix.
do the records get added from another table (i.e. insert into x select from z)? if so then it could be the select statement.
also - try either a profiler trace or an extended event trace to watch for both "lock timeouts" and "deadlocks"
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply