SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Table Partition question!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bill_C
Constraint Violating Yak Guru

United Kingdom
296 Posts

Posted - 10/01/2012 :  06:54:21  Show Profile  Reply with Quote
I have three processes that access the same table for updates to different date ranges on columns and occasionally two of them clash with one of them failing.

If I partition the table would the table partitions be able to be updated independantly on these dates or would a lock be on the whole table whilst one of the processes is updating it?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  08:36:37  Show Profile  Reply with Quote
I don't really know the answer to whether partitioning will help, but these are my thoughts/questions

When you said two updates clash and one of them fails, did you mean that one of the updates times out, or there is a deadlock, or something else?

Assuming it is time-out, partitioning may or may not help. Unless you are explicitly locking the tables, I would infer that it is lock escalation that results in table-level locks. If that is the case, partitioning may help. But that may also bring with it higher likelyhood of deadlocks where there may have been none before partitioning. I am saying this because it is possible that two different updates need to lock the hobts for the same set of partitions, but might go about it in different order.

So, like I said earlier, I don't know if partitioning will help or hurt. But, before you do the partitioning, it would be best to get a detailed understanding of what the updates do and why/how they are failing - whether timeout is the issue, whether the timeout interval can be increased, whether the updates can be cut up into smaller pieces so a single update wouldn't keep the tables locked etc.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000