Thursday, 5 February 2009

Reasons for slower Delete in SQL Server

Delete statement plays a major role in many of the database maintenance activities. Extreme care should be taken before executing the DELETE statements
Before executing the DELETE statement it is better to check the “Estimated execution plan”, so that we can create proper indexes to speed up the execution .I have given some of the possible reasons for slower delete.


1. Locking / Blocking - If it is a production database process is having Lock / Block on the table

2. Fragmentation - The Index pages are fragmented due to excessive delete on the table (Defragment the Indexes and try again)

3. The table you are trying to delete is referred by many tables as foreign key and those columns are not indexed.

4. There might be hanging transactions on the table - Try to truncate the Log and do

5. You can also change the Recovery Mode to simple and try - but not advised in case of production DB

Also it is recommended to execute the DELETE statements in smaller batches so that the Log space & Roll back of the records can be easier as shown below


// assume you wanted to delete 100000 records, we can split them into 10 batches as
DECLARE @V_Count INT =0
WHILE @V_Count < 100000
BEGIN
DELETE TOP(10000)
FROM Table
WHERE COLUMN = Condition
SET @V_Count =@V_Count+10000
END

3 comments:

Anonymous said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Anonymous said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Anonymous said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03