none
Issues with implementing Partitioning in current DB design

    Question

  • We have several tables which are connected by foreign keys created on ID columns, all tables have a common CreateDate Column as well
    Now We want to implement partitioning on the basis of CreateDate column so that when we try to purge data, we could do so without any problems with dependent data, but the problem is if we have Partition Scheme dependent on createDate column, then it should be part of clustered index and also requires all the primary keys to include CreateDate column as well, that accounts for modification of foreign keys.

    Can someone please advice how can this be implemented without altering existing pk-fk relationship.

    Regards

    Avijit 


    Tuesday, March 11, 2014 4:38 AM

All replies

  • What is the purpose of partition in that case? Optimize DELETION? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 11, 2014 6:01 AM
    Answerer
  • Yes Uri,

    The purpose is to optimize deletion.

    The DB will be getting billions of records everyday, and the availability of tables is highly reqd.

    So when we want to remove the older data from all these tables, we won't have to get lock over these tables.


    Tuesday, March 11, 2014 6:44 AM
  • I would suggest you the below techniques for optimized deleteion

    DECLARE @x INT
    SET @x = 1
    WHILE @x < 44,000,000  -- Set appropriately
    BEGIN

        DELELET FROM Table WHERE ID BETWEEN @x AND @x + 10000

        SET @x = @x + 10000
    END

    PS. Make sure that ID column should have CI.

    --------------------------
    WHILE 1 = 1
    BEGIN
       DELETE TOP(2000)
       FROM TableName
       WHERE <predicate>;

       IF @@ROWCOUNT < 2000 BREAK;
    END


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 11, 2014 6:52 AM
    Answerer
  • We have already seen the approach specified above, but since all tables are connected on ID columns, to delete the relevant data we would require joins between the tables, and this process of deleting in batches will be very time consuming, also locks will be held on the tables, which we are trying to avoid.

    The batched deletes is not the preferred option for us. Simply rolling off the old partitions achieves exactly what we want - except the requirements of having to include the partitioned column in PK/unique keys.

    We are absolutely looking for a way to make the partitions work - which it doesn't look possible. Let me know if there is a way.

    Tuesday, March 11, 2014 7:12 AM

  • Can someone please advice how can this be implemented without altering existing pk-fk relationship.


    How about dual table approach?

    TableA   records with CreateDate <= 48 hours

    TableB   records with CreateDate  > 48 hours

    That approach would give you lots of flexibility to achieve your goals since it can be customized to your needs.

    Article: "There are two types of data partitioning: horizontal partitioning and vertical partitioning. In Horizontal Partitioning, a table is split horizontally with a subset of rows to form another similar table with the same number of columns. The structure of the table will remain the same."

    LINK: http://johnjacobm.wordpress.com/2007/04/13/data-partitioning-in-sql-server-2000/

    Note: This is do-your-own-partitioning, not SS partitioning.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Thursday, March 20, 2014 4:23 PM
    Moderator