July 2, 2012 at 9:42 pm
Yes, I know. And it isn't.
July 3, 2012 at 4:22 am
coronaride (7/2/2012)
It's not the index that's the problem. In fact, there's already a clustered index. It's the Primary Key constraint.
I'm really confused by this, since most effort on this thread so far has been to point out how to reduce the time taken for an index build, or point out that with Enterprise edition you can build/rebuild indexes online (LOB's notwithstanding). So, when you say "It's not the index that's the problem", what exactly do you mean? Where are we all going wrong?
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
July 3, 2012 at 10:15 am
To be perfectly honest, I'm astounded with the responses I've gotten. Most people don't seem to know that there's a difference between an index and a primary key constraint. You can have a clustered index on a table and, in this case, I do. However, there is no primary key constraint, which is required by replication when doing anything above snapshot replication. My issue is with running the following code:
alter table MyTable
Add Constraint [MyPrimaryKey] Primary Key (MyColumn)
The best advice that I saw on here was to use "WITH NOCHECK" on the alter table command. From what I know about that option, this should've improved my situation, but in side-by-side tests in my environment, it didn't help.
July 3, 2012 at 10:41 am
coronaride (7/3/2012)
To be perfectly honest, I'm astounded with the responses I've gotten. Most people don't seem to know that there's a difference between an index and a primary key constraint. You can have a clustered index on a table and, in this case, I do. However, there is no primary key constraint, which is required by replication when doing anything above snapshot replication. My issue is with running the following code:alter table MyTable
Add Constraint [MyPrimaryKey] Primary Key (MyColumn)
The best advice that I saw on here was to use "WITH NOCHECK" on the alter table command. From what I know about that option, this should've improved my situation, but in side-by-side tests in my environment, it didn't help.
Considering how large the table is, 5 minutes doesn't seem to be that bad for creating a primary key constraint and the associated index. So I guess the real question is what is the real problem? Does this 5 minutes cause a problem of some sort?
Also, have you tried adding this to the ALTER table statement?
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON) -- use what ever fill factor you deem appropriate, this is from BOL)
July 3, 2012 at 10:46 am
Yeah, like I mentioned in the original post, this table is part of our live website, which gets millions of hits a day. For it to be down/offline for 5 minutes would be pretty bad. If I could get it down to a minute or less, I might be able to convince the stakeholders to let me move forward but as it is, that's just too costly.
July 3, 2012 at 11:02 am
Did you catch the other part of my post? If you build it using the ONLINE = ON, you may be able to do it with out much impact. It is worth testing.
Since you are going to create a nonclustered index to support the primary key constraint, this should work. Please check out this link:
July 3, 2012 at 11:04 am
I thought that the ONLINE specifiers were for indexes only. I'm only talking about a constraint. There's already a clustered index on the table and I don't want to change that.
July 3, 2012 at 11:07 am
coronaride (7/3/2012)
To be perfectly honest, I'm astounded with the responses I've gotten. Most people don't seem to know that there's a difference between an index and a primary key constraint. You can have a clustered index on a table and, in this case, I do. However, there is no primary key constraint, which is required by replication when doing anything above snapshot replication. My issue is with running the following code:alter table MyTable
Add Constraint [MyPrimaryKey] Primary Key (MyColumn)
The best advice that I saw on here was to use "WITH NOCHECK" on the alter table command. From what I know about that option, this should've improved my situation, but in side-by-side tests in my environment, it didn't help.
What do you think a PK constraint is enforced by?
Is your clustered index unique?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 3, 2012 at 11:10 am
PRIMARY KEY CONSTRAINT does not equal CLUSTERED INDEX.
Why do you keep coming back to that? I didn't say anything about changing the clustered index.
The only thing that comes to my mind with this is that the current clustered index also happens to be the same as primary key contraint you want to create.
July 3, 2012 at 11:11 am
coronaride (7/3/2012)
I thought that the ONLINE specifiers were for indexes only. I'm only talking about a constraint. There's already a clustered index on the table and I don't want to change that.
A PK constraint IS an index. There's no black box for verifying that a new key might not br unique, it's checked against the key.
We might get some progress now.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 3, 2012 at 11:12 am
What do you mean when you say "enforced?" No, the clustered index is not unique.
I think I may see what you're getting at. Are you saying that when you apply a constraint to a table it will use any available index to help speed the process?
July 3, 2012 at 11:14 am
22Gb is the size of the table. It takes around 5 minutes to create the PK constraint. But as mentioned by Lynn, you should create it as Online = ON.It will still take 5 minutes but it will keep the table online and thus your end users should not face much issue. These 5 minutes wont be your downtime..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 11:15 am
No, what they're saying is that when you create a constraint (unique or primary key), SQL creates an index associated with that constraint to enforce the uniqueness requirement.
The only way at the moment SQL can enforce uniqueness on a column is with an index, hence a unique constraint is backed by (and enforced by) a unique index.
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
July 3, 2012 at 11:15 am
A primary key constraint will create an index to enforce the uniqueness of the primary key.
Since you already have a clustered index defined on the table, this index will be a nonclustered index.
July 3, 2012 at 11:17 am
coronaride (7/3/2012)
What do you mean when you say "enforced?" No, the clustered index is not unique.I think I may see what you're getting at. Are you saying that when you apply a constraint to a table it will use any available index to help speed the process?
You're nearly there. A PK constraint works best with a unique index on the PK column(s). Without the index, SQL Server will perform a table scan whenever a key is added or changed.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply