I have inherited a system that sometimes exhibits extremely poor single record insert performance.
The table is something like 8 million rows, so is relatively large but not exceptionally so. The following simple insert took over 7 minutes and blocked the entire system up in the process:
INSERT INTO dbo.Communication(comm_action,comm_channelid,...) VALUES (N'Deskwork',11,...)
The overall database design is fairly poor. For example, although this particular table does have a Clustered Index, it has 34 other indexes as well despite being an OLTP system with heavy insert/update activity.
There are no triggers that I can see and the performance of inserts for the most part is reasonably good. Good being a relative term.
Could the number of indexes really account for such poor performance and is there any approach I could take to prove this before undertaking work.
Are there any other areas I could look into if the consensus is not the indexes being a problem?
The customer is very sensitive to changes, so I will need evidence before being able to make any changes.
I'm using SQL Sentry to highlight the blocking and the insert is the first block that shows up, followed by many others.
It's actually weirder than just there being another process blocking the inserter. I have a process (51) blocked by process 105. However, when I check what process 105 is about, there is no process 105.
I've repeated this several times, each time with a different process saying it's being blocked for a prolonged period of time, but the "blocker" not really existing. Does anyone know why sys.dm_exec_requests can say something is being blocked, but the same DMV say the blocker isn't really there?
What happens is, we get rows in dm_exec_requests with a blocking session id of, say 127. However, although there is a session in dm_exec_sessions, it says it's sleeping and there are no equivalent rows in the requests DMV.
I'm not clear on how a session can block something for 30 minutes despite seemingly not doing any requests?
For the blocked requests, in the case I had this morning it was waiting for a
LCK_M_S. Any ideas?