December 29, 2005 at 9:02 am
We have an Informatica job that reads and updates to the same table in a SQL2000 DB. When only a few hundred K rows are operated upon everything is fast and smooth. But if there's more than a million rows the thing locks itself forever and has to be manually killed.
This was when there was a clustered index on the set of columns upon which the update keyed on but removing the index allows it to work without locking itself. Of course, update performance is miserable without the clustered index.
Why would 300K rows run through OK but 1M locks itself? Without using (NOLOCK) on the read, is there any way to keep the clustered index and not get locked?
Any ideas greatly appreciated.
December 29, 2005 at 9:27 am
It depends on the design of your table. Is there something that will identify the updated records?
If so I would be tempted to have some form of loop to ensure that batches of 100K records are updated.
SET ROWCOUNT 100000
DECLARE @lRowCount INT ,
@dtStart DATETIME
SET @lRowCount = 1
SET @dtStart = GETDATE()
WHILE @lRowCount>0
BEGIN
UPDATE dbo.Table
SET field = VALUE ,
LastUpdatedDate = @dtStart
WHERE LastUpdatedDate < @dtStart
SET @lRowCount = @@ROWCOUNT
END
SET ROWCOUNT 0
This might be physically slower than a single operation but if you know that there is a ceiling above which your server can't cope then this keeps the load below the ceiling.
December 29, 2005 at 9:51 am
Since the Informatica tool is handling the read and update then I can't use something like that.
As I mentioned, the clustered index is based on the update's fields.
January 2, 2006 at 12:34 pm
Try using a non-clustered index. In a clustered index the leaf pages of the index are your acrual data pages whereas in a non-clustered index they are not.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply