How to reduce time in adding PK constraint on a table?

Question

How to reduce time in adding PK constraint on a table?

Viewing 12 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 1

Oh wow. I didn't realize that. To be perfectly honest, I'd never created a PK constraint in this fashion before, so I just assumed that the index that was created when you normally created a PK had more to do with the clustered index than the PK constraint. It all makes sense now. So, I can go and create the non-clustered unique index leaving the table online, then just add the constraint QUICKLY afterwards.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Doing it something like this:

alter table MyTable

Add Constraint [MyPrimaryKey] Primary Key (MyColumn)

WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

Be sure to verify the complete syntax in BOL.

You may also want the NOCHECK option.

Again, check the syntax in BOL.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

coronaride (7/3/2012)
So, I can go and create the non-clustered unique index leaving the table online, then just add the constraint QUICKLY afterwards.

Err... No.

When you create a constraint, SQL creates the enforcing index for you. It's an integral part of creating a constraint. If you create an index, then create the constraint you will end up with 2 indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 4

Ok. You're right, of course. That's obviously what happened in my test environment. But riddle me this - why was it that it took me only 20 seconds to add the constraint (as well as the second index, apparently) *after* I added the (first) non-clustered unique index? All previous attempts were well over 5 minutes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Because SQL can read the existing index to create the new one whereas without that inde it would have had to read the entire table.

Compare the time that creating the index + creating the constraint took vs just creating the constraint

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 6

Ok, and the bulk of the time required for creating the constraint is creating the enforcing index? Alright, makes sense. Thank you, and thank everyone else on this thread.

andersg98 SSCertifiable Points: 5180 More actions · Answer 7

Whew, I was on the edge of my seat while reading this. Wasn't sure if we were ever going to get here. 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

coronaride (7/3/2012)
Ok, and the bulk of the time required for creating the constraint is creating the enforcing index?

Yes. All there is to a constraint is an index and some metadata.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 9

Lynn Pettis (7/3/2012)
Doing it something like this:
alter table MyTable
Add Constraint [MyPrimaryKey] Primary Key (MyColumn)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
Be sure to verify the complete syntax in BOL.
You may also want the NOCHECK option.
Again, check the syntax in BOL.

Assuming tempdb is reasonbly tuned, I would add the:

SORT_IN_TEMPDB = ON

option to the build, i.e.:

WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON, SORT_IN_TEMPDB = ON);

That could (really should?) help reduce the time (altho it will likely use more overall total disk space during the creation process (only -- the space will be released on the build is complete)).

Also, make sure you have pre-allocated (and thus pre-formatted) space available in the db's log file before you issue the command, particularly if you have a small log autogrow amount or, worse, a percentage growth.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 10

TheGreenShepherd

SSCertifiable

Points: 6979

July 3, 2012 at 2:03 pm

#1508481

Thank you.

TheGreenShepherd SSCertifiable Points: 6979 More actions · Answer 11

Alright. Educational session in understanding what a PK constraint actually is today. Thanks a lot.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

coronaride (7/3/2012)
Alright. Educational session in understanding what a PK constraint actually is today. Thanks a lot.

Everyone's a winner. Thanks for the feedback, coronaride.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden