Status: Solved
Priority: Medium
Security: Public
Views: 46
Last Modified: 2016-08-05

Applying constraints to a large table

I Have had to rebuild a database for a customer and whilst most of this process has worked well I have come up against a problem. On three tables when I run code similar to that below:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_GroupUserMap_User]') AND parent_object_id = OBJECT_ID(N'[GroupUserMap]'))
ALTER TABLE [GroupUserMap]  WITH NOCHECK ADD  CONSTRAINT [FK_GroupUserMap_User] FOREIGN KEY([UserInfoKey])
REFERENCES [UserInfo] ([UserInfoKey])

Open in new window

It is taking an inordinate amount of time to do 6 million rows. Is there a way top improve this performance?

The SQL SERVER 2012 installation is on a virtual server which I have no administrative rights to and the customer has fairly limited IT resources too.

Alyanto

Asked: 2016-08-04

Advertise Here

Microsoft SQL Server

1 Solution

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2016-08-04

Why? The existence of the foreign key is not checked for 6 million rows but only a few rows of metadata in sys.foreign_keys and since your alter table adds a constraint with NOCHECK, this shouldn't take long, as it doesn't check the 6 million rows.

If anything does take long, then it's not this code but something else you don't show here.

Bye, Olaf.

LVL 1

AlyantoAuthor Commented: 2016-08-04

Hey Olaf
That was my thoughts, but there is nothing else in the script that affects this code just more of the same. and yet it runs for a weekend and no completion :( I have pretty much read around the subject as completely is I think possible I am hoping someone else has had a similar problem and found a solution.

LVL 31

Olaf DoschkeSoftware DeveloperCommented: 2016-08-04

Sure, you don't end with something like ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL?
I still stand to this, a constraint added with NOCHECK does not check rows, so the size of the table will not matter. You need to look for something else.

Bye, Olaf.

LVL 1

AlyantoAuthor Commented: 2016-08-04

No Olaf I highlighted the code as written and execute it and I agree with you, I was hoping for someone to have had this experience and found a way around it. That may never happen, I just need to give this a fair amount of time before I decide what to do next.

LVL 27

ZberteocCommented: 2016-08-04

There must be a lock involved. I recommend you to install Adam Machanic's sp_whoisactive stored procedure and while you run that ALTER statement in a separate query window you will execute :

EXEC sp_whoisactive

and check for the blocking_session_id column. If there is a session_id in that column in the row that shows your ALTER statement then you will have to kill/terminate that process.

Here is the procedcure:

http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

Download here: http://sqlblog.com/files/folders/beta/entry42453.aspx

It is free.

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2016-08-05

A lock should be a good explanation but what make a table to be locked for a full weekend?
Do you have any job running? Something like an Import/Export or even a maintenance job scheduled for weekends?

Also if you don't want to use sp_WhoIsActive you can use the built in sp_who2 and check for the rows with blocked column <> 0.

LVL 1

AlyantoAuthor Commented: 2016-08-05

Well hats off to you, Adam Mechanic tool soon found out that a user was simply ignoring the administrators instruction to cease and desist use. Many thanks for the Cluedo tool.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Applying constraints to a large table

Join & Write a Comment Already a member? Login.

Featured Post