Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Optimizing inserts and deletes on a single large table Expand / Collapse
Author
Message
Posted Monday, December 14, 2009 2:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx (5000 held locks, approximately)



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #834149
Posted Monday, December 14, 2009 3:35 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 4,437, Visits: 6,339
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 at GMail
Post #834211
Posted Monday, December 14, 2009 3:48 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #834217
Posted Monday, December 14, 2009 8:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:09 PM
Points: 10, 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..
Post #834268
Posted Monday, December 14, 2009 9:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 4,437, Visits: 6,339
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 at GMail
Post #834273
Posted Tuesday, July 5, 2011 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:37 AM
Points: 2, Visits: 42
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
Post #1137020
Posted Thursday, July 7, 2011 4:04 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, October 26, 2014 7:16 PM
Points: 634, Visits: 810

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
Post #1137941
Posted Thursday, July 7, 2011 12:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 4,437, Visits: 6,339
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 at GMail
Post #1138387
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse