January 25, 2013 at 8:22 am
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
January 25, 2013 at 9:15 am
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
January 25, 2013 at 10:39 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2013 at 3:12 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2013 at 3:49 am
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
January 29, 2013 at 4:01 am
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
January 29, 2013 at 4:15 am
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[/url].
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 4:43 am
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
January 29, 2013 at 4:54 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 5:40 am
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
January 29, 2013 at 8:17 am
We are using AlwaysOn to a DR site so we do have High Availability
The 2 hour restore is if we lose both it has to be available within 2 hours
Overkill?
January 29, 2013 at 8:23 am
DominantDBA (1/29/2013)
We are using AlwaysOn to a DR site so we do have High AvailabilityThe 2 hour restore is if we lose both it has to be available within 2 hours
Overkill?
Nah, business requirements. Whatever they are is what they are as long as they're physically possible and within the budget being provided. But, it does sound like you might be solving one problem and introducing a second with partitioning. I'd recommend some VERY thorough testing around these changes, especially dropping the FKs for triggers. I think that one is crazy, especially if you're already experiencing performance problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2013 at 9:51 am
DominantDBA (1/29/2013)
We are using AlwaysOn to a DR site so we do have High AvailabilityThe 2 hour restore is if we lose both it has to be available within 2 hours
Overkill?
No, but I'd probably consider something like log shipping to a tertiary (maybe an hour behind primary) for the 'must be up in 2 hours', rather than jumping through hoops with tweaking backups. But then, last system I worked on with requirements like that was over 1 TB and restoring backups was a 4-6 hour process.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply