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.
LVL 1
AlyantoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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.
AlyantoAuthor Commented:
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.
Olaf DoschkeSoftware DeveloperCommented:
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

FREE DOWNLOAD
AlyantoAuthor Commented:
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.
ZberteocCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
AlyantoAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.