SQL Server Performance

Vertical Partitioning

Discussion in 'General Developer Questions' started by amitm79, Jan 10, 2005.

  1. amitm79 New Member

    Hi All,
    Can anybody tell me a practical scenario wherein you have used vertical partitioning to solve an issue and it has improved performance? I want to prototype Horizontal/Vertical/Hybrid/No partitioning based on my data and see if it improves performance. Horizontal partitioning is giving me significant improvement. Now i want to implement vertical partitioning to check the results. I am not able to relate the concept to its practical use as keeping all records in the same table takes me back to the issue of un-partitioned data theoretically. Any help/link on it is welcome.

    Regards,
    amitm79
  2. satya Moderator

    What is the size of database you want to consider this vertical partitioning?

    It's important when you break your data apart in a vertical partitioning scheme to retain the relational integrity of your database. Depending upon your database's construction, that can be a design challenge. I think although vertical partitioning creates a performance benefit and a logical uniformity, it also creates operational complexity that you may or may not be able to live with.

    Refer this linkhttp://www.sqlteam.com/Item.ASP?ItemID=684 for information on data partitioning.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. amitm79 New Member

    Hi Satya,
    My database size is about 100 GB for now. It can go upto 0.5 TB in future. Another problem here is that my primary key is a composite key i.e. i will either have to replicate it across all vertical partitions or introduce a surrogate key. This further adds to the complexity.

    Regards,
    Amit
  4. amitm79 New Member

    Hi All,
    If i partition a table vertically into two tables, the table and index space used is less. Why is it so? I am even taking the overhead of replicating the primary key into both the partitions then why the space consumed is less?

    Regards,
    Amit
  5. amitm79 New Member

    Hi,
    One more doubt. I don't think i need a view in case of Vertical partitioning as i know which columns are in which partition. Can anybody comment on that?

    Regards,
    Amit

Share This Page