July 2, 2012 at 1:23 pm
I'm a DBA stepping into an ugly situation. Amongst many bad things, I'm trying to get better throughput from their production systems to their reporting systems (their previous DBA had set up a complicated and oft-failing nightly backup/copy/restore procedure for their numerous databases, several over 400GB). I immediately put the kaibosh on that and moved several of the more problematic instances over to log-shipping, but that has its weaknesses as well, so I've been putting together a plan to move them over to replication. Part of the condition for this was that the data needed to be updated regularly in the reporting environment. No sweat, I've set up complex replication schema before, I've got this...
Except that when I went to set up transactional replication (we're initializing from a backup) one of the tables couldn't be used, as SOMEONE DIDN'T CREATE IT WITH A PRIMARY KEY! Jesus. Ok, so I've been testing it out in my test environment, and the best that I can do is about 5 minutes for adding the primary key constraint to the table, given the same table, the same hardware. It's 46MM rows, about 22GB. The filegroup is stored on a 3PAR SAN volume. This database is fundamental to site traffic and we field about millions of hits a day and this table is, in particular, being written to constantly.
Does anyone have any bright ideas on how I can reduce the time it takes to add the Primary Key constraint?
July 2, 2012 at 4:39 pm
Will you be creating a clustered index as part of the primary key? If so, drop all nonclustered indexes first, add the clustered primary key index and then recreate the nonclustered indexes.
July 2, 2012 at 4:55 pm
No, there is already a clustered index on the table.
July 2, 2012 at 5:03 pm
What edition of SQL Server are you running?
July 2, 2012 at 5:06 pm
2008 Enterprise
July 2, 2012 at 5:29 pm
You can use the "WITH NOCHECK" option when creating the Primary key.
Example:
ALTER TABLE test WITH NOCHECK
ADD CONSTRAINT PK_test PRIMARY KEY (Id)
The above statement will create a new unique nonclustered index for the primary key. If you want this to be clustered, you must first drop the existing clustered index and then recreated as a primary key clustered.
ALTER TABLE test WITH NOCHECK
ADD CONSTRAINT PK_test PRIMARY KEY CLUSTERED (Id)
July 2, 2012 at 5:46 pm
Interesting idea, but it actually took 25% longer to implement in my test environment.
July 2, 2012 at 5:52 pm
What took 25% longer to implement? Adding a non-clustered primary key with nockeck? This is not possible. WITH NOCHECK skips the checking of data before the primary key is created. Something else must have changed in your testing environment. Did you drop or create any other indexes? Anything else that could have caused it to take a bit longer? What is the code that you are using when implementing the primary key?
July 2, 2012 at 5:56 pm
Not really going to argue with you on this. I used basically the same code as you except without the "WITH NOCHECK". Offhand, you're right, I would've guessed that would create the constraint faster as well. I've run it twice now. For some reason, it's taking longer.
July 2, 2012 at 6:08 pm
Since you are running Enterprise Edition, you should be able to drop the clustered index and create the new primary key clustered index using the ONLINE = ON option, if the table does not have any LOB datatype columns ( text, ntext, image, xml, varchar(max), nvarchar(max), or varbinary(max))
This should allow the application to keep working while you drop the clustered index and create the primary key.
If you just want to add a non-clustered primay key, you can also do that with the ONLINE = ON option.
Either way, the important thing is not how long it takes, but how long it blocks the application. With the ONLINE = ON option, the only blocking is for a small schema change at the very end when the new index comes online.
July 2, 2012 at 6:09 pm
It's not the index that's the problem. In fact, there's already a clustered index. It's the Primary Key constraint.
July 2, 2012 at 6:14 pm
Are you adding the primary key to an existing column in the table?
July 2, 2012 at 6:20 pm
Yes
July 2, 2012 at 6:34 pm
It will be helpful if you can send me some t-sql code. Also, is there any foreign keys defined on this table? Or is there any views based on this table?
July 2, 2012 at 8:23 pm
Please remember, the primary key does NOT have to be the clustered index.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply