none
drop constraint and add constraint

    Question

  • I think when drop foreign constraint for a table, the only thing affecting the time of the task is the locks, if I set the database to single user mode, it would finish quickly no matter the table is big or small. am i correct?

    to understand this better, can someone explain what SQL database engine do (steps by steps) when drop foreign constraint or add the constraint? for example:

    1. request a lock on the table 

    2....

    Thursday, March 29, 2018 5:58 PM

Answers

All replies

  • Adding or dropping a constraint on table requires a Sch-M lock (schema modification) which is the strongest lock there; incompatibile with anything else.

    When you add a foreign-key constraint, there are also locks taken on the target table, but they are plain shared locks, I believe.

    Dropping a CHECK, DEFAULT or FOREIGN KEY CONSTRAINT is always quick. Dropping a PRIMARY KEY or UNIQUE constraint which is the clustered index can by quite time-consuming, since all non-clustered indexes have to be rebuilt.

    Thursday, March 29, 2018 9:09 PM
  • I think when drop foreign constraint for a table, the only thing affecting the time of the task is the locks, if I set the database to single user mode, it would finish quickly no matter the table is big or small. am i correct?

    Well, no.

    Well now, rereading, I'm not sure what you're asking.

    As Erland said, just setting and dropping constraints requires no check, it's always fast, big table or small, unless you say WITH CHECK.

    --

    Or, if you're talking about dropping constraints and running single-user to do heavy processing faster, which is what I thought you were asking, then:

    For reads, the locks are very light weight and you will likely see no difference at all.

    For insert/update/delete that involves checking constraints more locks are taken and evaluated, but the locks themselves are also very light, and I believe they are all still taken even in single-user mode.

    What takes time are the nuts and bolts of processing - reading from the disk, writing the log, checkpointing the data, CHECKING constraints during modifications, and that too will take place in single-user mode because it's about data consistency and not multi-user concurrency.  On modern servers the locks are not very time-consuming.

    Now, if someone else's processing is locking you out or thrashing the disk, then shutting them off will speed you up, sure, but that doesn't seem to be what you're talking about.

    --

    But rereading again, probably Erland already answered what you were asking.

    Josh




    • Edited by JRStern Friday, March 30, 2018 5:52 AM
    Friday, March 30, 2018 5:46 AM
  • As Erland said, just setting and dropping constraints requires no check, it's always fast, big table or small, unless you say WITH CHECK

    Which you should. Well, by default, when you add a new constraint, the correctness of the current data visavi the constraint is checked.

    What takes time are the nuts and bolts of processing - reading from the disk, writing the log, checkpointing the data, CHECKING constraints during modifications, and that too will take place in single-user mode because it's about data consistency and not multi-user concurrency.  On modern servers the locks are not very time-consuming.

    What do you mean here? Foreign-key constraints are checked with shared lock, which is not really the same thing as single-user mode.

    Friday, March 30, 2018 8:50 AM
  • Hi Erland,

    "When you add a foreign-key constraint, there are also locks taken on the target table, but they are plain shared locks, I believe."

    No - SQL Server will only hold a few X-Locks on the tables for schema modification updates. Just a short demo here:

    BEGIN TRANSACTION;
    GO
    	ALTER TABLE dbo.CustomerOrders
    	ADD CONSTRAINT fk_Customers_Id
    	FOREIGN KEY (Customer_Id)
    	REFERENCES dbo.Customers(Id);
    	GO
    
    	SELECT	DTL.resource_type,
    			DTL.request_mode,
    			DTL.request_type,
    			DTL.request_status,
    			CASE resource_type
    				WHEN N'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
    				WHEN N'KEY' THEN OBJECT_NAME(P.object_id)
    				ELSE NULL
    			END
    	FROM	sys.dm_tran_locks AS DTL
    			LEFT JOIN sys.partitions AS P
    			ON DTL.resource_associated_entity_id = P.hobt_id
    	WHERE	request_session_id = @@SPID;
    	GO

    When I run the SELECT to see the hold locks I get the following result set:

    The list is not showing all locks but - as you have mentioned  - SCH-M-Locks are on both tables. The locks are the same when you drop the FK-Constraint. It does not make a difference whether the database in in SINGLE_USER or MULTI_USER mode.

    Happy easter to all forum members from Germany


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)


    Friday, March 30, 2018 9:41 AM
  • Single user mode has no relevance to adding or dropping constraints except reducing the possibility of blocking while the command is running.  It is going to take as long as it takes.

    Friday, March 30, 2018 1:42 PM
    Moderator
  • What takes time are the nuts and bolts of processing - reading from the disk, writing the log, checkpointing the data, CHECKING constraints during modifications, and that too will take place in single-user mode because it's about data consistency and not multi-user concurrency.  On modern servers the locks are not very time-consuming.

    What do you mean here? Foreign-key constraints are checked with shared lock, which is not really the same thing as single-user mode.

    Just that it will take the same time and do the same checking under single-user mode.

    Josh

    Friday, March 30, 2018 3:16 PM
  • Hi Goerge,

    "to understand this better, can someone explain what SQL database engine do (steps by steps) when drop foreign constraint or add the constraint? for example:"

    I was summarizing your questions into a blog post with all details here:

    http://www.db-berater.de/2018/03/inside-the-engine-erstellung-eines-fremdschlssels/

    It is written in German but easy to translate with Chrome :)

    I hope it will give you all answers you need.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)


    • Edited by Uwe RickenMVP Sunday, April 1, 2018 7:55 AM
    • Marked as answer by George Zhou Wednesday, April 4, 2018 8:02 PM
    Sunday, April 1, 2018 7:54 AM