<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 2008
»
SQL Server 2008 - General
»
Foreign Keys,Deadlocks, Partioning,OLTP...
13 posts, Page 1 of 2
1
2
»»
Foreign Keys,Deadlocks, Partioning,OLTP ......what is the best setup?
Rate Topic
Display Mode
Topic Options
Author
Message
DominantDBA
DominantDBA
Posted Friday, January 25, 2013 8:22 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 73,
Visits: 271
Hi All
I am a DBA and I have just started a new job working on a large scale OLTP system.
This system will be loading 1 million rows of data into 3 or 4 main tables per hour when it goes live
These tables so far are not partitioned and there are plenty of foreign keys.
The code has mostly been writtten and I have now joined and being asked to performance tune this system. We are receiving a lot of deadlocks on this system due to lock contention when doing the foreign key check on the large data sets. My question is this:
In a high volume OLTP environment what is the best way to manage you FK's and Partitioning.
Most articles I read say "Keep FKs...end of" Does the game change at high volume and you need to move some of your referential integrity into the application? If you keep the FK's then you can no longer partition switch as "The source table cannot be referenced by a foreign key in another table."
Is that the price you pay?
So if the FK's are kept on the DB you then still partition the tables,keep the foreign keys, archive data by bulk copy? Resolving deadlocks by query hints and retries. A point to note read committed snapshot isolation doesnt remove the FK deadlocks.
Im ideally looking for someone who has worked on Large volume OLTP environments to answer this.
Thanks
Chris
Post #1411760
GilaMonster
GilaMonster
Posted Friday, January 25, 2013 9:15 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 38,095,
Visits: 30,388
Keep foreign keys (unless you like dealing with garbage data), partitioning is for management of data, not performance. Query and index hints are the very last resort for tuning queries when nothing else persuades the optimiser to generate the plan that you absolutely know is best (and why it's best) and you know why the optimiser is not generating the plan.
Deadlocks: optimise the queries, tune indexes. If that still doesn't get rid of them, consider one of the snapshot isolation levels.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1411790
Grant Fritchey
Grant Fritchey
Posted Friday, January 25, 2013 10:39 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436,
Visits: 25,281
Even on large systems, it comes down to the code, the indexes and the statistics. You've got to structure the indexes appropriately to support the queries you need to run. You need to write your queries so that they use the indexes you've built. You absolutely must maintain your statistics, especially if you're adding large amounts of data.
And everything Gail said, +100.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1411843
ScottPletcher
ScottPletcher
Posted Friday, January 25, 2013 3:12 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333,
Visits: 1,803
Just curious:
How did you determine that the deadlocking was due to FK lookups?
Legitimate FKs should be used when possible. FK definitions in SQL itself are vastly more efficient than any code you write to try to do the same thing yourself.
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1411941
DominantDBA
DominantDBA
Posted Tuesday, January 29, 2013 3:49 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 73,
Visits: 271
Hi Guys
Thanks for your responses. I just wanted to clarify my position
So i started here 3 weeks ago on a system thats been developed in c# and t-sql(supposedly agile but not!). Its been pretty poorly architected and they did not take partitioning into consideration until now. So they have now decided we need to partition which is great. So on a lot of the tables when we implement partitioning new appropriate partition key columns need to be added to a number of the
tables as the developers thought using char for date columns was a good idea! So this new partition key column will then need to be added to any child tables that this table is referenced by and this has a knock on affect on development time which the project cant really afford.
So I was asked by the architect to run a test where we replace all foreign keys with triggers to enforce referential integrity. Collect perfmon stats and then management will decide what the best course to take. The big thing for them is not slipping on the go live date and they are very much in love with the trigger idea.
I obviously want to maintain referential integrity because I dont want to be doing a cleanup in the future. My issue I guess is more about navigating the politics of the organisation. Is the trigger idea an acceptable compromise?
Grant with regard to the deadlocking the resource thats causing the contention is a primary key on a table that is constantly being updated while a table that references is also continually being inserted to and my thoughts are that the FK constraint isnt helping. Im still at the point with deadlocks and lock escalation that Im not entirely sure because mostly dealocks just give me a headache and make me want to take a coffee break.The fix is an architecural one. I have recommended that they use SSIS to do de-duping and bulk inserts into the insert table and move the field in the parent table to another table. Still learnng in this area though!
Sorry bit of a ramble
Have a great day
Chris
Post #1412871
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 4:01 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 38,095,
Visits: 30,388
Why are you partitioning? What's the goal there?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1412882
Grant Fritchey
Grant Fritchey
Posted Tuesday, January 29, 2013 4:15 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436,
Visits: 25,281
One point worth making to the management team, elimination of foreign key constraints also takes choices away from the optimizer.
I have an example up on my blog
.
And I'm with Gail, why are we partitioning? For data management or for performance. If the latter, you might be digging a hole. Partitioning can help performance, but only if you can guarantee that your queries will always reference the partitioning key in such a way that it eliminates partitions, focusing data access to a single location. And, to really gain the full benefits of partitioning for performance, you have to be also be able to throw disks at the problem. If you'r partitioning for performance and you can't guarantee the data access method, it will be MUCH worse performance than with a regular table.
Deadlocks give me headaches too. But usually it's because of accessing the data in different directions in combination with poorly performing queries. Adjust the data access and tune the queries and most deadlock problems go away. Then, you might consider looking at using snapshot isolation. But then, only if you have tempdb well and truly in hand with plenty of space, especially for a large system like you're working with.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1412894
DominantDBA
DominantDBA
Posted Tuesday, January 29, 2013 4:43 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 10:02 AM
Points: 73,
Visits: 271
There is a business requirement that the database needs to be opreational within 2 hours if it is lost
Partitioning is so we can recover the latest filegroups and files and get up and running as soon as
Our backup window is also pretty short so partitioning can really be helpful in that case with read only etc
Post #1412918
Grant Fritchey
Grant Fritchey
Posted Tuesday, January 29, 2013 4:54 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436,
Visits: 25,281
OK. That's probably a good reason for it, but, know that you are potentially looking at radically slower performance. Partitioning requires pretty disciplined code to work well. From the sounds of things, you might not be there.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #1412930
GilaMonster
GilaMonster
Posted Tuesday, January 29, 2013 5:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 38,095,
Visits: 30,388
DominantDBA (1/29/2013)
There is a business requirement that the database needs to be opreational within 2 hours if it is lost
Log shipping, database mirroring?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1412959
« Prev Topic
|
Next Topic »
13 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.
