locked
what is the best setup Deadlocks, Partioning,OLTP ? RRS feed

  • Question

  • 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.
    • Moved by David Dye Thursday, January 31, 2013 4:53 PM Post ask for direction in database design, partitioning, foreign keys
    Thursday, January 31, 2013 4:45 PM

Answers

  • 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.
        

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Evne Maximoy Thursday, January 31, 2013 6:30 PM
    Thursday, January 31, 2013 6:08 PM

All replies

  • Angelo I moved this post to database design from the T-SQL forum.  I believe that you will receive better more directed responses here.


    David Dye My Blog

    Thursday, January 31, 2013 4:56 PM
  • 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.
        

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Evne Maximoy Thursday, January 31, 2013 6:30 PM
    Thursday, January 31, 2013 6:08 PM
  • >there are plenty of foreign keys

    You should index all foreign keys over 1000 (ballpark) rows.

    Are the PRIMARY KEYs INT (4 bytes)?

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback/Kindle: SQL Server 2012 Pro

    Friday, February 1, 2013 3:19 PM
  • So my answer ended up being wide with lots of guesses, but this is a really tough question, because there are a lot of variables on table size/performance. Kalmon is dead on in the indexing of foreign keys values, but if the deadlocks are on the PK's, then the issue could be large referencing tables with indexes that need maintained and end in scans.  Like Ahsan, for OLTP, I would always suggest FKs, most because you need to do that validation somewhere. For tables with a fixed domain that no user can update, perhaps you could drop them, but the problem is rarely with tiny tables, but really large ones.

    When you say "loading" do you mean just typical singleton inserts? Or large multi-row? It also interests me that read committed snapshot didn't alleviate the deadlocks. So can you be a bit more specific/provide a script of th objects that you are getting deadlocks on? Most of the deadlocks I see are of the variety where scans are happening that people don't expect. Maybe a bit of a look at the plan of the deadlocking queries would help too, if you can see that. Some discussion on your clustering key datatype/actual size/distribution of new data should be considered as well. If your PK isn't the clustering key, you may be doing horrible bookmark lookup operations.

    Millions of rows an hour is a fairly high rate, so you might want to look at contention at the disk level and perhaps do some spreading onto different filegroups, increasing ram, CPU, or etc. One place to look would be to see how much reading is going on versus writing. I wrote a blog about this here (http://sqlblog.com/blogs/louis_davidson/archive/2009/06/20/read-write-ratio-versus-read-write-ratio.aspx) and looking at those queries helped me to see that most of our performance issues were from reads (I had assumed writes for sure, but it was like 95% reads for our OLTP system). It could even be that using compression might help if you are CPU and not Disk bound.

    Finally, with such high rates of data creation, two things are common, getting your structures fragmented and statistics getting out of date, both leading to ineffective indexes.  So I might consider doing a bit of reading along those lines because the system doesn't tune itself well in some cases, particularly when it comes to fragmentation.

    Even partitioning might be a consideration, depending on the types of queries you are doing, but it is not where I would go first. It really depends on your actual system needs (and could help with an archiving strategy because you can end up with billions and even trillions of rows pretty fast.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.


    Friday, February 1, 2013 9:32 PM