Re: delete statement makes SQL Server hang
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 08/25/04
- Next message: Raymond D'Anjou \(raydan\): "Re: Row Counter Rank in View"
- Previous message: Gerry Viator: "loop through the current year date"
- In reply to: Derek Ruesch: "delete statement makes SQL Server hang"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Aug 2004 22:38:24 +0300
A DELETE statement is always fully logged, so if the table has a large
number of rows, this will take a while and also cause the following
problems:
* SQL Server limits the number of locks it allows a single transaction to
acquire. There's a formula determining that number, but its not exposed, and
it relies on several dynamic factors (e.g., free resources in the system).
But generally, a transaction won't be allowed to get more than say, several
thousands of individual row locks. Once your transaction hits this value,
the individual row locks escalate to a full table lock (in a DELETE case, an
exclusive one). Meaning that the whole table is blocked for both committed
reads and writes.
You can capture a lock escalation event with a profiler trace. Use it to
determine the lock escalation point against your table, and divide the
number you got by 2, baring in mind the dynamic nature of the formula.
* The whole process is a single transaction, meaning that the transaction
log space occupied by the transaction can not be overridden until the
transaction is committed and the transaction log is backed up. In a large
delete, this means that your log will expand, and expand, and expand, etc.
Also, if other processes hold locks on the table (either for committed reads
or for writes), your activity will be blocked and seem to hang.
Any way, the way to go with clearing a whole table is by using TRUNCATE
TABLE. This process deallocates whole extents and writes only the extent
addresses to the transaction log, so its faster in orders of magnitude than
a DELETE.
If you cannot use TRUNCATE (either because foreign keys reference the table,
or you need a partial DELETE), split your large DELETE into chunks as
follows:
SET ROWCOUNT 1000 -- or another number that doesn't cause lock escalation
WHILE 1=1
BEGIN
DELETE ...
IF @@rowcount < 1000 BREAK
END
SET ROWCOUNT 0
This process splits the large DELETE to multiple small DELETEs, thus
preventing lock escalation, and allowing transaction log space to be
overridden, assuming you issue backup log operations during the activity
(possibly with an automatic alert).
-- BG, SQL Server MVP www.SolidQualityLearning.com "Derek Ruesch" <anonymous@discussions.microsoft.com> wrote in message news:000e01c48acd$f2b7d840$a501280a@phx.gbl... > I am trying to run a SQL Delete statement on a table and > it causes SQL Server to hang (SQL Server seems to be > processing the statement but it doesn't do anything. No > records are deleted from the table and SQL Server > continues to run the Delete query.). I am only having this > problem on one table. This table does have several users > that have "SELECT" permissions. > > I am running SQL Server 2000. > > The statement that I am trying to run is > > DELETE > FROM tblContract > (I want to delete all records from this table.) > > Why isn't the Delete statment working. I have been able to > delete records from this table before without any problems. > > Thanks for the help. > > Derek Ruesch > >
- Next message: Raymond D'Anjou \(raydan\): "Re: Row Counter Rank in View"
- Previous message: Gerry Viator: "loop through the current year date"
- In reply to: Derek Ruesch: "delete statement makes SQL Server hang"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
