SQL 2008 Partitioning RRS feed

  • Question

  • Hi,

    We have blocking (LCK_M_IX) problem with 2 tables and want to see if partitioning the tables can help. Table1 and table2 have millions of rows and are around 6 and 16GB respectively. These tables have high writes and reads. Usually blocking is when the sp tries to delete stale data (older than a month) from these tables. These tables store only one month worth of data.

    Table1 has an identity column - ID (primary key/clustered index) and datetime column whereas the table2 doesn't have datetime or unique column. The primary key/clustered index for the table2 is a combination of column1 - ID and column2 - indexID.

    These tables have no referential integrity. The sp first deletes the data older than a month from table1 (in batches of 10000) and then deletes the related data from table2 (in batches of 10000).

    This sp is run every day and on a average it deletes around 400,000 rows from Table1 and 150 million rows from Table2. Can you please advice the optimum way to partition these tables.

    Also which column should i use for partition function in table2?

    DELETE Table1

    FROM (SELECT TOP 10000 ID FROM Table1WHERE DateTime < @date) AS T1 WHERE Table1.ID=T1.ID

    DELETE TOP(10000) Table2

    FROM Table2

    LEFT JOIN Table1


    Table2.ID = Table1.ID


    Table1.ID IS NULL

    Thanks in advance

    Tuesday, September 4, 2012 8:15 PM


All replies