Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Foreign Keys,Deadlocks, Partioning,OLTP ......what is the best setup? Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 112, Visits: 434
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
Posted Friday, January 25, 2013 9:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 42,468, Visits: 35,536
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
Posted Friday, January 25, 2013 10:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 15,528, Visits: 27,910
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
Posted Friday, January 25, 2013 3:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 1,970, Visits: 2,911
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1411941
Posted Tuesday, January 29, 2013 3:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 112, Visits: 434
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
Posted Tuesday, January 29, 2013 4:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 42,468, Visits: 35,536
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
Posted Tuesday, January 29, 2013 4:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 15,528, Visits: 27,910
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
Posted Tuesday, January 29, 2013 4:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 112, Visits: 434
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
Posted Tuesday, January 29, 2013 4:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 15,528, Visits: 27,910
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
Posted Tuesday, January 29, 2013 5:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 42,468, Visits: 35,536
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 »

Add to briefcase 12»»

Permissions Expand / Collapse