Solved

Applying constraints to a large table

Posted on 2016-08-04
7
33 Views
Last Modified: 2016-08-05
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
Comment
Question by:Alyanto
7 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41741940
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
 
LVL 1

Author Comment

by:Alyanto
ID: 41742202
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41742284
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:Alyanto
ID: 41742376
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
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41742595
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41744006
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
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 41744155
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question