Dynamic Partitioning and Dynamic Indexing

  • Raghavendra Narayana

    SSC Enthusiast

    Points: 177

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/nraghavendra/3210.asp

  • Adrian Hains

    Ten Centuries

    Points: 1322

    Along these same lines - anyone who is interested in optimizing their indexes based on live usage patterns should check out this blog entry from sqlcat on related dmv's:

    http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx

    And you may want to play with this (unsupported) script from the sql query optimization team, it can automatically implement recommended index changes for your db:

    http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx

  • noeld

    SSC Guru

    Points: 96590

    This a very "risky" proposition which I would doubt most people would dive into.


    * Noel

  • Mahesh Sawaiker

    SSC Rookie

    Points: 32

    Very interesting article indeed, few things I found that might actually hamper the performance considered for the solution in the share market scenario, your concept of implementation creates a scenario where there is one write(the common sproc) for every read, after thinking abt it for some time I thought that may be this can be just queued in some way(through another thread in the front end) and the more critical read executed. Another way it to have decision making channels that essentially dictate the way data is read, eg. stocks in news will be queried more... but then this is probably overshooting, another consideration is how long do the trends persist... you dont want to get into a situation where u r continuously re configuring ur partitions at a time when the trend no longer holds, more like how some people's hair styles are constantly behind the latest fashion 😀

    But over all must say it made some interesting reading and the concept still holds.

  • dma333

    Right there with Babe

    Points: 772

    Though I understand the concepts (which were presented eloquently), I would like to know more about how the table is actually used to dynamically recreate the partitions. Given the fact that you would probably only need to partition a table in the first place if it were quite large, would you actually let some process move your data around to repartition it without monitoring it?

    I would also like to see some examples as to how you can dynamically add these partitions, since I plan on using a more predictable partitioning scheme (i.e. adding a new partition per quarter for a date-partitioned table). Can anyone provide such an example? Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply