High lock waits

Last Post 15 Dec 2011 08:59 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Anupinder Rai
New Member
New Member

15 Dec 2011 07:56 AM

Hi I have a heavy Insert operation job running in the morning.
About 1 million rows are inserted.
I have Server fine tuned based on the best practices like

Decrease DOP to 1.

Disable index on that table are recreate it.

I am constrained with hardware resources and cannot increase physical RAM.

What other aspects I can lok into ?





New Member
New Member

15 Dec 2011 08:59 AM
The big question would be what are the inserts being blocked by? Is there a trigger on the table? What does the DMV for wait stats say you are waiting on most during the inserts? Are you inserting the rows one at a time? If so then wrap a batch of 1000 or so in a BEGIN TRAN - COMMIT to reduce the activity in the log etc. But what you need to do depends on many factors. Are you constrained on hardware? For istance is the log file on its own array? If not and you get logwrite waits then you may need to change the hardware. If the insert is being blocked by another process then why? Does that other process not have the proper indexes to work properly. If there are other activities going on at the same time as the inserts then disabling the indexes may not be wise and might be hurting you more than helping.

Acceptable Use Policy