Blocking on insert

Question

Blocking on insert

jdgonzalez@jdandrachel.com

Hall of Fame

Points: 3161
More actions
March 2, 2010 at 11:59 am

#221229

I'm at a loss on this one. We have a packaged application that's hosted on SQL Server. Throughout the day it will do metric inserts to a table (about 40 times an hour). The table has 47 million records. What I'm trying to figure out is every once in a while (more frequently lately), the insert to this table is blocking other process in other tables.
When I query sys.dm_exec_requests I can only find the request that is being blocked and not the blocking session id. That by itself seems odd. I have to use dbcc inputbuffer(<<spid>>) to see the statement that is causing the blocking.
There is only one index on this table, a clustered index on a date and foreign key column. The index fragmentation on that table is 7%.
I'm going to run in a trace to see if I can trap the execution plan.
Is there a reason as to why this would be occurring or some sort of guidance as to where to look?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Without seeing the structure, it's hard to say, but at a guess, it's the index reordering itself as the inserts come in.

Query the sys.dm_exec_query_stats and sys.dm_exec_query_plan to get the aggregated statistics, including waits, and the execution plan from the cache. You don't have to try to catch it using a trace.

"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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

J.D. Gonzalez (3/2/2010)
the insert to this table is blocking other process in other tables.

One questions, is this problem occuring from last couple of days or earlier was it running fine ?

and how many records are being inserted on avgerage ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 3

We average about 2300 records per hour. However, they are not equally distributed across the hour. We get them in short bursts of 100 or so records.

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 4

Grant Fritchey (3/3/2010)
Without seeing the structure, it's hard to say, but at a guess, it's the index reordering itself as the inserts come in..

I guess what has be a bit stumped is that it doesn't happen with every insert but it happens regularly enough to be annoying. About once a day.

I'll check the DMV that you specified to see what comes up.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

It doesn't sound too mysterious. You say you get bursts of inserts over time, and these bursts can cause page splits or whatever within the index and while things are getting rearranged, blocking occurs.

"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

Paul White SSC Guru Points: 150467 More actions · Answer 6

J.D. Gonzalez (3/2/2010)
...every once in a while (more frequently lately), the insert to this table is blocking other process in other tables.

This is the part that seems remarkable to me.

To analyse the problem, you need to define more precisely what is meant by 'blocking' there.

Blocking occurs when one process is waiting for a resource which is protected by a lock from another process, which is incompatible with that sought by the original process.

For example, if process A holds a Shared lock on a resource, and process B requires an update or exclusive lock, process B will be blocked.

Waits, on the other hand occur when a resource is too busy to respond to a request that would otherwise succeed. Two examples of waits: (1) Waiting for free space in the buffer pool; (2) Waiting for transaction log records to be flushed to disk.

To analyse waits, take a look at This Microsoft White Paper.

A primary resource when analysing blocking due to incompatible locks is the dynamic management view sys.dm_tran_locks. Note that the blocking_session_id on sys.dm_exec_requests is not always populated, even if a session is blocked. That information can be missing if SQL Server cannot easily identify the blocking session, or it chooses not to do so for performance reasons.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi