SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimizing inserts and deletes on a single large table


Optimizing inserts and deletes on a single large table

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34694 Visits: 11359
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx (5000 held locks, approximately)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31452 Visits: 8670
Paul White (12/14/2009)
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx (5000 held locks, approximately)


Assuming good cardinality then there could be 5000 or so PAGES locked if the engine decided that page locks were appropriate (which is likely for any reasonable rows estimated that don't automatically trigger a table lock). That is many more rows than 5000 stated as the break point. Good number, just variable thingy locked. :-)

But again we are back to the query that does the work and the estimates the optimizer works with and finally decides for the actual INSERT and especially DELETE queries. OP?? Any followup here??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34694 Visits: 11359
TheSQLGuru (12/14/2009)
Assuming good cardinality then there could be 5000 or so PAGES locked if the engine decided that page locks were appropriate (which is likely for any reasonable rows estimated that don't automatically trigger a table lock). That is many more rows than 5000 stated as the break point. Good number, just variable thingy locked. :-)

Oh yes absolutely. I just posted the link to illustrate where the 5000 number came from.

TheSQLGuru (12/14/2009)
But again we are back to the query that does the work and the estimates the optimizer works with and finally decides for the actual INSERT and especially DELETE queries. OP?? Any followup here??

With any luck he's reading about waits and queues or hiring a consultant ;-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
billmcknigh
billmcknigh
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 111
Have read the paper SQL Server 2005 Waits and Queues, which I guess is a locus classicus of the waits and queues methodology. The delete statement, BTW, is a DELETE FROM table WHERE ID IN (SELECT TOP 3000 ID FROM table). In themselves, the deletes are very fast. The Inserts are ordinary row level (fired by a trigger) inserts, but 1000 per minute are arriving continuously Finally, should mention that we had to add memory to our system because SS was wanting to use more than we had, so some significant sluggishness was due to having the server page to disk..
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31452 Visits: 8670
billmcknigh (12/14/2009)
Have read the paper SQL Server 2005 Waits and Queues, which I guess is a locus classicus of the waits and queues methodology. The delete statement, BTW, is a DELETE FROM table WHERE ID IN (SELECT TOP 3000 ID FROM table). In themselves, the deletes are very fast. The Inserts are ordinary row level (fired by a trigger) inserts, but 1000 per minute are arriving continuously Finally, should mention that we had to add memory to our system because SS was wanting to use more than we had, so some significant sluggishness was due to having the server page to disk..


Hmm, that DELETE seems curious. You have no order by for the top, so you could get ANY 3000 rows, right? And regardless of that (which is certainly not what I would want to be doing in almost all cases I can construct), why not use this syntax:

DELETE TOP 3000 FROM table

But I would really like to know why you don't care what 3000 rows get deleted.

As for the INSERTs, are you saying that 1000 inserts are done individually to another table and that causes a trigger to fire that inserts each one of those rows into this other table that has the DELETEs?

Do you get blocking much?

Have you checked for what locks are being held for the table?

Run perfmon checking for locking counts/durations?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Danny Cook-424584
Danny Cook-424584
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 44
In terms of the delete (and depending on indexes), instead of using "IN" in your where clause, I'd use something like:

DECLARE @Id INT
SELECT @Id = MAX(Id) FROM (SELECT TOP 3000 ID FROM MyTable ORDER BY Id)

DELETE a
FROM
MyTable a
INNER JOIN MyTable b ON b.ID = a.ID AND b.ID < @Id
gofrancesc
gofrancesc
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1037 Visits: 812
Hello,
as Kevin said your DELETE instruction lacks on a WHERE clause, I suppose you coded something like
DELETE TOP 3000 FROM table WHERE too_old_record

Do you need to run this delete process every few minutes?. Maybe you can schedule it to a point in time with few activity and execute it daily or weekly, avoiding run it on stressed working hours.

Francesc
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31452 Visits: 8670
Hey guys, please check the dates on threads you are going to reply to. Please do not reply to 1.5 year old threads! Thx.:-)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search