none
Implement Lock Escalation on Partitioned Table RRS feed

  • Question

  • Hello, 

    I have a HUGE table which is partitioned and it is a relational Table with many child tables with it. it has a Sliding window partition on datetime column.

    For Purge, i would like to have Delete with On Delete Cascade as we cannot afford to drop FK constraint all the time during the switch out.  

    I would like to implement lock escalation for the Partitioned table. When i implement Lock_Escalation AUTO / DISABLE the table is still locking. Does anyone fixed this and implemented in your experience. Or can please advise if there is a better approach for Purging the oldest ranges instead. 

    Your advise on this is highly appreciated. Thanks 

    Thanks 

    Momen 


    Thank you... MOMEN

    Tuesday, July 9, 2019 6:50 AM

Answers

  • USE tempdb;
    GO
    DROP TABLE 
        dbo.Child1, 
        dbo.Child2,
        dbo.Parent;
    GO
    -- Test tables
    CREATE TABLE dbo.Parent (parent_id INT PRIMARY KEY);
    CREATE TABLE dbo.Child1 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C1_P REFERENCES dbo.Parent);
    CREATE TABLE dbo.Child2 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C2_P REFERENCES dbo.Parent);
    GO
    INSERT dbo.Parent VALUES (1), (2), (3);
    INSERT dbo.Child1 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
    INSERT dbo.Child2 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
    GO
    -- Error 4712
    TRUNCATE TABLE dbo.Parent;
    -- Disable constraint checking on the referecing tables
    ALTER TABLE dbo.Child1 NOCHECK CONSTRAINT FK_C1_P;
    ALTER TABLE dbo.Child2 NOCHECK CONSTRAINT FK_C2_P;
    -- This would still fail
    -- TRUNCATE TABLE dbo.Parent;
    -- Can switch, truncate, and switch back
    CREATE TABLE dbo.Workspace (parent_id INT PRIMARY KEY);
    ALTER TABLE dbo.Parent SWITCH TO dbo.Workspace;
    TRUNCATE TABLE dbo.Workspace;
    ALTER TABLE dbo.Workspace SWITCH TO dbo.Parent;
    -- Remove child rows
    TRUNCATE TABLE dbo.Child1;
    TRUNCATE TABLE dbo.Child2;
    -- Success
    ALTER TABLE dbo.Child1
        WITH CHECK 
        CHECK CONSTRAINT FK_C1_P;
    ALTER TABLE dbo.Child2
        WITH CHECK 
        CHECK CONSTRAINT FK_C2_P;

    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

    • Marked as answer by Momen Azmath Tuesday, July 9, 2019 8:50 AM
    Tuesday, July 9, 2019 8:00 AM
    Moderator

All replies

  • What version you are using? Have a look into SQL TRUNCATE TABLE command

    https://www.sqlshack.com/sql-server-2016-enhancements-truncate-table-table-partitioning/


    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, July 9, 2019 7:20 AM
    Moderator
  • I am using SQL Server 2017, Truncate Partitions works for Non-Relational Tables but doesn't work for Relational Tables, that's why i was going to Switch out but for achiving that need to drop the FK constraint everytime before the Switch out and recreate back after that, but looks like this is not an option for me 

    So, I decided to go with Delete Cascade so that child also gets deleted, but to control table lock the lock escalation i made it to AUTO. Looks like it still blocks. But works fine when doing the other inserts / updates and even select with no lock. 

    Now i got a question about the Data consistency which i am unable to justify .. neither proposed better purge solution. Do you any better ideas for Purge for Partitioned table, if so it would be a big help. 


    Thank you... MOMEN

    Tuesday, July 9, 2019 7:35 AM
  • USE tempdb;
    GO
    DROP TABLE 
        dbo.Child1, 
        dbo.Child2,
        dbo.Parent;
    GO
    -- Test tables
    CREATE TABLE dbo.Parent (parent_id INT PRIMARY KEY);
    CREATE TABLE dbo.Child1 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C1_P REFERENCES dbo.Parent);
    CREATE TABLE dbo.Child2 (child_id INT PRIMARY KEY, parent_id INT NULL CONSTRAINT FK_C2_P REFERENCES dbo.Parent);
    GO
    INSERT dbo.Parent VALUES (1), (2), (3);
    INSERT dbo.Child1 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
    INSERT dbo.Child2 VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (6, 3);
    GO
    -- Error 4712
    TRUNCATE TABLE dbo.Parent;
    -- Disable constraint checking on the referecing tables
    ALTER TABLE dbo.Child1 NOCHECK CONSTRAINT FK_C1_P;
    ALTER TABLE dbo.Child2 NOCHECK CONSTRAINT FK_C2_P;
    -- This would still fail
    -- TRUNCATE TABLE dbo.Parent;
    -- Can switch, truncate, and switch back
    CREATE TABLE dbo.Workspace (parent_id INT PRIMARY KEY);
    ALTER TABLE dbo.Parent SWITCH TO dbo.Workspace;
    TRUNCATE TABLE dbo.Workspace;
    ALTER TABLE dbo.Workspace SWITCH TO dbo.Parent;
    -- Remove child rows
    TRUNCATE TABLE dbo.Child1;
    TRUNCATE TABLE dbo.Child2;
    -- Success
    ALTER TABLE dbo.Child1
        WITH CHECK 
        CHECK CONSTRAINT FK_C1_P;
    ALTER TABLE dbo.Child2
        WITH CHECK 
        CHECK CONSTRAINT FK_C2_P;

    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

    • Marked as answer by Momen Azmath Tuesday, July 9, 2019 8:50 AM
    Tuesday, July 9, 2019 8:00 AM
    Moderator
  • Thanks Uri. 

    Now after the NoCheck, Switchout worked, but not Truncate. 

    now, second question is after the switchout, it just archived the data from the partition, and the same data is still available in the Child tables But I don't have the same date column values from PK - FK tables. How to get this aligned? Also, if I want the Datetime column to be considered as the Partition Key for the Primary Key table, So any other alternate we have without Composite PK 


    Thank you... MOMEN

    Tuesday, July 9, 2019 8:33 AM
  • I am confused,  you said that the tables have PK\FK relation ship, how it is possible dates column is not related?

    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, July 9, 2019 8:41 AM
    Moderator
  • Actually I got the answer from your last post, thanks very much on explaining this with example. 

    sorry to confuse you, Actually, the requirement was to partition a PK/FK relation table which has a relation with ID int field, but i need to do a partition with Datetime time column. To achive the Partition Purge, i had to include the Partition Key column and make the composite PK including the Partitioin Key column with int id column, after that i can switchout, similarly i need to do the same for the Foreign Key also. This required the Column to be added on the FK table. 


    Thank you... MOMEN

    Tuesday, July 9, 2019 8:49 AM