• Grant Fritchey (10/19/2012)


    Blocking is a natural consequence of inserting and deleting data and can't be avoided. In order for the ACID properties of the transaction to be maintained, SQL Server must place locks on the tables while it's doing inserts/updates/deletes. If you're also seeing lots of deadlocks, it sounds like you might be accessing the tables in different orders within the two sets of queries. You'll need to modify that in order to help avoid deadlocks.

    I have yet to see a performance problem involving blocking and deadlocks be resolved by removing the clustered index. You may have the clustered index in the wrong place, or your queries might not be referencing it correctly to take advantage of it, but removing it is unlikely to resolve your issue. You need to look to the queries themselves to understand if they are operating in an optimal fashion. Have you examined the execution plans? Are they running as fast as possible?

    I would focus on getting the access order the same between the two sets of queries and on tuning the queries to ensure they run faster.

    Hi Grant Fritchey

    Thanks for your reply. Later we identified that insert statement from application is blocking the delete statement. And blocking threshold was set as 1 seconds. We changed to 3 seconds.

    Also we changed the delete to smaller blocks with delay of 0.05 seconds between batches.

    Somewhat we don't see dead blocks and blocking afterwards. Still under observation.

    Thank you.