deletes causing chokes

25112 used Ask the Experts™
when you delete a huge quality of data, it sometimes takes long time.. or even makes the system unstable...

what is the term for this problem..?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


is it the lack of index?
or too much log writing?
or shifting of data within the file etc?
Probably your transaction-log table is getting a lot of activity... each DML is either explicitly (BEGIN TRAN) or implicitly contained in a transaction, which populates transaction-log.  If the statement aborts then transaction-log is cleared of those transactions, otherwise, they are committed (eventually) to disk.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Any chance you can DELETE a set number of rows at a time, say 50,000, so that it's not one large transaction (per above experts)?

>when you delete a huge quality of data
Define this.  Is it the entire table, or can it be defined in a WHERE clause?
lcohanDatabase Analyst
Do the tables involved in DELETEs have FKeys? Or CLUSTERED indexes?
If FKEys exists make sure you have a NONCLUSTER index to cover the FKey structure to help the deletes.
I also suggest adding WITH (ROWLOCK) hint and maybe batch them using SET @@ROWCOUNT or some other method.
Top Expert 2012
what is the term for this problem..?
Painful ?

But seriously, define large.  If it is more than say 500K consider breaking it up in batches.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial