Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: input on changing partitioning schemes

    Ok. So I have a largish table (~300 million rows) and in production it is partitioned on the clustered key that it is commonly joined on. Great. However, this table is a "Status History" table and it has an incrementing surrogate (identity) that is different the clustering key that the table is partitioned on. What I am thinking I am seeing in my testing on moving the clustering key in dev to the identity column is an increase in INSERT performance. Almost every process in this database, updates this status and creates entries in this Status History table. A quick dependency check shows that about a dozen jobs and dozen reports have a reference to this Status History table. What I am struggling with, since I do not have the hardware anywhere in DEV to reproduce the partitioning schema's is the impact on performance of the operations that read from this table.

    I am considering this because we have one critical process that can not be run as frequently as desired or during business because it is causing a lot of blocking timeouts because it is updating a lot of statuses. I have taken out the cursors, replaced with set based batches, and tweaked indexes and code and the job went from 2 hours to 5 minutes in production. Still not good enough. It is my thinking that the exclusive locks being held open during the status updates could be optimized by making the inserts into the Status History table more efficient by moving the cluster from the natural key to the surrogate. My performance gain looks like by half.

    So my question is, do I swing the dice and risk hosing the other processes and reports that read from this Status History table by changing the paritioning and the cluster on the table, because I can not gauge the impact, to fix this other process and possibly make an improvement system wide? Hmmmmmm.Sounds like a gamble.
    Last edited by Thrasymachus; 11-03-11 at 11:42.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, the 5 minute process is causing the problems? What other traffic is it blocking? And is the 5 minute process doing inserts? Or is it a report that is blocking updates?

    One thing that springs to mind is pushing the historical partitions that will not be updated again to read-only filegroups. Since this takes update activity out of the equation, it prevents a number of blocking scenarios, at least on the read-only data. Since you have already implemented partitioning, you may already have implemented the read-only filegroups, as well, though.

    How does the QA system look? Could it be a possible testbed for this?

    On the other side, how likely are range scans on the natural key (which is currently clustered)?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What I am being told by the DBAs and the Director level guy who spent 10 years building this thing, and perhaps i should back through our error logging to confirm, is the at the exclusive locks on the table where the status updates are occurring that fire the trigger into the Status History table are blocking other processes. Now I have preached my sermon about Wildly Optimistic concurrency in systems like this here among my developer and I fix that in the procedures as I go, but there are very likely reporting queries and other nonsense missing NOLOCK hints or SET TRANie levels going on, but I am not sure I have time to fix that problem now. I am still in the process of pushing reporting off the oltp system like a big boy data company. I do not have production grade hardware until maybe I get to UAT. I need to confirm that. If that is true I will test everything there. I have not looked at the individual procs yet, but it looks like a maybe a dozen SQL job processes, a dozen reports and maybe a couple of web pages from our naming conventions.

    I like the thing about the read only file groups. That is going take some digging to find out how feasible that is.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    nope. no production hardware until production.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    And what they are telling me makes sense too, right? I just rewrote this from a cursor doing all of this crap 1 at a times to processes that do 5000 record set based batches, so the exclusive locks being held open for the inserts now take longer than the single record inserts. The whole job got faster, but the individual locks got longer is what i am thinking. Need to confirm.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Any way to break up the batches? Perhaps (and this would be way cool for a big boy data processing corp), you could mess around a bit with the service broker. Effectively, whatever this former cursor based thing needs to do is put on a queue. Another process RECEIVEs a set number from that queue, and does the updating accordingly. It depends on where the performance bottlenecks are, and what the optimal batch size would be.

    As for a testing system, all you would really need is probably a similar sized data set. Once you have that, you should be able to compare the before and after pictures by the number of reads each setup causes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •