<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Optimizing inserts and deletes on a single...
18 posts, Page 1 of 2
1
2
»»
Optimizing inserts and deletes on a single large table
Rate Topic
Display Mode
Topic Options
Author
Message
billmcknigh
billmcknigh
Posted Saturday, December 12, 2009 3:30 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 10, 2011 12:42 PM
Points: 10,
Visits: 108
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.
Post #833412
RBarryYoung
RBarryYoung
Posted Sunday, December 13, 2009 7:18 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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."
Post #833479
billmcknigh
billmcknigh
Posted Sunday, December 13, 2009 11:30 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 10, 2011 12:42 PM
Points: 10,
Visits: 108
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?
Post #833525
RBarryYoung
RBarryYoung
Posted Sunday, December 13, 2009 12:13 PM
SSCrazy Eights
Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855,
Visits: 9,374
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."
Post #833526
billmcknigh
billmcknigh
Posted Sunday, December 13, 2009 12:46 PM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, May 10, 2011 12:42 PM
Points: 10,
Visits: 108
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??
Post #833532
Paul White
Paul White
Posted Monday, December 14, 2009 1:07 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 10,990,
Visits: 10,541
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #833637
TheSQLGuru
TheSQLGuru
Posted Monday, December 14, 2009 7:57 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 3,582,
Visits: 5,130
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 at GMail
Post #833840
Andrew Gothard-467944
Andrew Gothard-467944
Posted Monday, December 14, 2009 10:13 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194,
Visits: 2,357
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
Post #833977
TheSQLGuru
TheSQLGuru
Posted Monday, December 14, 2009 10:23 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 3,582,
Visits: 5,130
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 at GMail
Post #833980
Andrew Gothard-467944
Andrew Gothard-467944
Posted Monday, December 14, 2009 10:30 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194,
Visits: 2,357
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
Post #833987
« Prev Topic
|
Next Topic »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
