SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Foreign Keys,Deadlocks, Partioning,OLTP ......what is the best setup?


Foreign Keys,Deadlocks, Partioning,OLTP ......what is the best setup?

Author
Message
DominantDBA
DominantDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 463
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87117 Visits: 45267
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, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39717 Visits: 32639
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7872 Visits: 7149
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
DominantDBA
DominantDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 463
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87117 Visits: 45267
Why are you partitioning? What's the goal there?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39717 Visits: 32639
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
DominantDBA
DominantDBA
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 463
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
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39717 Visits: 32639
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87117 Visits: 45267
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, MVP, M.Sc (Comp Sci)
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


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