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
billmcknigh
billmcknigh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 111
Our company has a very large table (150 million rows) to which we add 1000 rows per minute via a trigger on another table. At the same time, we are deleting older rows through a job which runs every few minutes. I am wondering if anyone would have any recommendations for ensuring that the delete process and insert trigger do not lead to much contention. THe rows that we delete are at the other end of the table, so to speak, from the rows that we are inserting. But occasionally we experience some sluggishness in the whole process.

We are using Sql Server 2005.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14644 Visits: 9518
You would probably want to partition this table across multiple Volumes, additionally insuring that none of these volumes shared any physical disks. You should probably use a partitioning function that put the new instered records on a different volume(s) than the old to-be-deleted records.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
billmcknigh
billmcknigh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 111
Partioning is a good idea, but in addition is it worth it to try to put in hints like WITH ROWLOCK on the transactions that delete and insert, or is that not going to make much difference?
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14644 Visits: 9518
I doubt that ti would make much difference. Though you could always test it to see for you specific case.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
billmcknigh
billmcknigh
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 111
Forgot to mention that we are using Standard Edition and Not Enterprise, so I guess the partitioning of the table is out, right, or rather it would have to be simulated with a partition view??
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15568 Visits: 11355
Yes - partitioning is an Enterprise-only feature. You could indeed consider using a partitioned view, but you should probably take a step back before making such a big change. One very sound approach is to identify bottlenecks in the existing system, and then make changes to improve matters. The key is to work based on evidence rather than simply applying whatever 'best practice' comes to hand.

The subject is a complex one, and there is nowhere near enough detail in your posts to make any recommendations. For example, if your system is constrained by logging or free buffer pool pages, rather than data file I/O, partitioning tables and placing them on separate physical disks is highly unlikely to make much of an impact.

The best papers available, to my knowledge, discussing how to approach this sort of project are linked to below - both feature senior figures from Microsoft. The basic strategy is to measure the 'waits and queues' over the system as a whole, and then apply targeted measures to improve those things most in need.

Troubleshooting Performance Problems in SQL Server 2008
SQL Server 2005 Waits and Queues



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
What you need to do is find out WHY things appear to be sluggish at times. Is it due to blocking or is it due to IO stalls or some other form of wait? Each of those possibilities should be analyzed while the activities are in progress. If for some reason (such as complexity of the query leading to bad estimation, improper indexing) table locks (or perhaps even page locks) are being acquired then you could indeed be seeing blocking and if so row lock hints could be a help. If the queries that insert/delete are suboptimal then whatever locks are taken are being held too long and tuning could be the magic bullet you seek.

LOTS of possibilities here. I recommend getting a tuning consultant in for a few hours remote work. Should be all it takes, and you can learn from how they do their work as well so you can better fix your own problems in the future.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Andrew Gothard-467944
Andrew Gothard-467944
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 2357
Any idea how many rows are normally deleted by your delete process?
If it's more than about 5000, then you're likely to be getting table locks due to lock escalation, in which case it may be worth considering amending your delete to remove the records in batches of, say, 4000 which should at least prevent this issue
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
Andrew Gothard-467944 (12/14/2009)
Any idea how many rows are normally deleted by your delete process?
If it's more than about 5000, then you're likely to be getting table locks due to lock escalation, in which case it may be worth considering amending your delete to remove the records in batches of, say, 4000 which should at least prevent this issue


Can I ask where the 5000 number you pick came from?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Andrew Gothard-467944
Andrew Gothard-467944
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 2357
Good question. I'll have to have a dig there.
It was from a MS article on lock escalation to the effect that threshold for Lock Escalation was c. 3000 rows for 2000, c. 5000 for 2005 - but you're quite right, it's pretty bad form really to post without reference to the appropriate source. Will try to rectify

cheers
Andrew
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