• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

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.
0
Alyanto
Asked:
Alyanto
1 Solution
 
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.
1
 
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.
0
 
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.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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.
0
 
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.
1
 
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.
1
 
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.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now