Question about snapshot article defaults and indexes

I have recently been comparing the size of the source and replicated
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||Hi

First take the system architect out of the building and have him shot.
Then take the IT Manager outside and have her shot for hiring such an
architect.
Then take each developer out and have them shot for not knowing better.

Add indexes. They are one of the basic design fundamentals required with any
database.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1118415503.154653.85890@.g49g2000cwa.googlegro ups.com...
>I can think of no good reason to NOT use indexes; that should be a
> basic ingedient to any performance improvement attempt. It should also
> be transparent to any development staff they have (in other words,
> programmers should suggest what indexes they think would be
> appropriate, but in most cases they shouldn't worry about developing
> them on an as-needed basis).
> If you are going to add indexes, you may also want to place your
> clustered indexes on your data drive, and your other indexes on the log
> drive; this may help improve speed as well. I typically try to have a
> third drive available for indexes, but sometimes that's not an option.
> Just my .02.
> Stu|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||pheonix1t (pheonix1tAThoustonDOTrrDOTcom@.com.com) writes:
> I've been assigned to do performance tuning on an SQL2000 database
> (around 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn
> about why you wouldn't want to use indexes - especially on such a large
> database!

Seems like you have an easy job. Run Profiler to catch a day's workload,
run the Index Tuning Wizard over the result, create indexes. If the
system does not really have any indexes and is still standing up on
that hardware, it's pointless to improve it.

And least of all, the RAID. The only way that system can survive is
because it's able to hold the data in cache.

Then again, I would suspect that if you run this query:

select *
from sysindexes
where indid >= 1 and indid < 255
and indexproperty(id, name, 'IsStatistics') = 0
and indexproperty(id, name, 'IsHypothetical') = 0

That a couple of indexes will show up.

Yet, then again, just because there are indexes, does not mean that
they are the right indexes, so Profiler and Index Tuning Wizard may
still be what you should look at.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||The only reason I can think of is that someone over-indexed or
improperly indexed the schema at one time, and the next guy reacted by
dropping all of them. I hope he left the UNIQUE and PRIMARY KEY
constraints.|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig|||"pheonix1t" wrote:

> hello,
> I've been assigned to do performance tuning on an SQL2000 database (around
> 10GB in size, several instances).
> So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.
> I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
> logs, database and OS(win2k).
> The one thing that was a bit odd to me was that I was told this place
> doesn't use indexes. The company is a house builder. They are pretty
> large.
> The IT manager isn't a programmer so she couldn't explain to me why no
> indexes are used. She told me the programmers just don't use indexes.
> Before I start investing more time on this, I'd really like to learn about
> why you wouldn't want to use indexes - especially on such a large
> database!

In addition to the other comments, I've heard about companies where that
happened this way:

- Bad app is designed where everything runs dog slow

- Users complain that data entry is slow

- Some developer reads that indexes "speed reads, slow writes" and so drops
all indexes and tells the bosses that "reports might run slow, but data
entry will speed up"

- In the middle of all this, hardware gets upgraded, temporarily masking the
problem

Unfortunately, I've seen this happen, but with "constraints" substituted for
"indexes". Figuring out some quick indexes to get you started will be easy.
Fixing the possibly broken data will be harder.

I can envision a few possible situation where indexes wouldn't be used: a
SQL Server used only as a staging server between OLAP and OLTP systems where
all data scrubbing would involve table scans anyway. Also, evaluating
hardware by thrashing it with wild queries unsupported by indexes. However,
I can't think of a single time in an OLTP system where I wouldn't want
indexing (unless I were a saboteur hired by the competition :)

Craig

question about performance issues w/SQL2000 with NO indexes

hello,
I've been assigned to do performance tuning on an SQL2000 database
(around 10GB in size, several instances).
So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM.

I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the
logs, database and OS(win2k).

The one thing that was a bit odd to me was that I was told this place
doesn't use indexes. The company is a house builder. They are pretty
large.

The IT manager isn't a programmer so she couldn't explain to me why no
indexes are used. She told me the programmers just don't use indexes.

Before I start investing more time on this, I'd really like to learn
about why you wouldn't want to use indexes - especially on such a large
database!

Thanks,

OskarI can think of no good reason to NOT use indexes; that should be a
basic ingedient to any performance improvement attempt. It should also
be transparent to any development staff they have (in other words,
programmers should suggest what indexes they think would be
appropriate, but in most cases they shouldn't worry about developing
them on an as-needed basis).

If you are going to add indexes, you may also want to place your
clustered indexes on your data drive, and your other indexes on the log
drive; this may help improve speed as well. I typically try to have a
third drive available for indexes, but sometimes that's not an option.

Just my .02.

Stu

question about indexes

Using SS2000. We have a table that we use mostly for reporting. It has about
150 columns in it. Many different queries are run off of it which use a lot
of different columns in the where and order clauses. Is there any limit to
the number of indexes that can be put on a table? Does performance start to
suffer at some point if too many columns are indexed? I think I read
somewhere that if a column is used in a clustered index then it shouldn't be
used in a nonclustered index. Is that true?
Thanks,
Dan D.
See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table?
1 clustered index and 249 non-clustered indexes
Does performance start to suffer at some point if too many columns are
indexed?
Every new index you create will slow down all inserts and deletes, but may
improve some selects, updates and deletes with where clauses
I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
Not necessarily... Use the clustered index to support range searches or
values with many duplicates..
There is never a need to have 2 indexes with the same keys however.

> Thanks,
> Dan D.
>
|||> Is there any limit to the number of indexes that can be put on a table
Yes. I believe it's 253 non-clustered indexes plus 1 clustered index.
Statistics count as a non-clustered index.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table? Does performance start
to
> suffer at some point if too many columns are indexed? I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
> Thanks,
> Dan D.
>

question about indexes

Using SS2000. We have a table that we use mostly for reporting. It has about
150 columns in it. Many different queries are run off of it which use a lot
of different columns in the where and order clauses. Is there any limit to
the number of indexes that can be put on a table? Does performance start to
suffer at some point if too many columns are indexed? I think I read
somewhere that if a column is used in a clustered index then it shouldn't be
used in a nonclustered index. Is that true?
Thanks,
Dan D.See inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table?
1 clustered index and 249 non-clustered indexes
Does performance start to suffer at some point if too many columns are
indexed?
Every new index you create will slow down all inserts and deletes, but may
improve some selects, updates and deletes with where clauses
I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
Not necessarily... Use the clustered index to support range searches or
values with many duplicates..
There is never a need to have 2 indexes with the same keys however.
> Thanks,
> Dan D.
>|||> Is there any limit to the number of indexes that can be put on a table
Yes. I believe it's 253 non-clustered indexes plus 1 clustered index.
Statistics count as a non-clustered index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table? Does performance start
to
> suffer at some point if too many columns are indexed? I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
> Thanks,
> Dan D.
>sql

question about indexes

Using SS2000. We have a table that we use mostly for reporting. It has about
150 columns in it. Many different queries are run off of it which use a lot
of different columns in the where and order clauses. Is there any limit to
the number of indexes that can be put on a table? Does performance start to
suffer at some point if too many columns are indexed? I think I read
somewhere that if a column is used in a clustered index then it shouldn't be
used in a nonclustered index. Is that true?
Thanks,
Dan D.See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table?
1 clustered index and 249 non-clustered indexes
Does performance start to suffer at some point if too many columns are
indexed?
Every new index you create will slow down all inserts and deletes, but may
improve some selects, updates and deletes with where clauses
I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
Not necessarily... Use the clustered index to support range searches or
values with many duplicates..
There is never a need to have 2 indexes with the same keys however.

> Thanks,
> Dan D.
>|||> Is there any limit to the number of indexes that can be put on a table
Yes. I believe it's 253 non-clustered indexes plus 1 clustered index.
Statistics count as a non-clustered index.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23bEk3SjSEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Using SS2000. We have a table that we use mostly for reporting. It has
about
> 150 columns in it. Many different queries are run off of it which use a
lot
> of different columns in the where and order clauses. Is there any limit to
> the number of indexes that can be put on a table? Does performance start
to
> suffer at some point if too many columns are indexed? I think I read
> somewhere that if a column is used in a clustered index then it shouldn't
be
> used in a nonclustered index. Is that true?
> Thanks,
> Dan D.
>

Question about indexdefrag

Hi,
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX no
r INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the dat
a.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...

quote:

---

> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?

---

|||What kind of "impact" would i have if i create/drop
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?

quote:

---

>--Original Message--
>You cannot defrag datapages which aren't part of an index

---

using DBREINDEX nor INDEXDEFRAG. You can

quote:

---

>create a clustered index, and possibly drop it. Or export

---

and import the data.

quote:

---

>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
+[QUOTE]
tables a[QUOTE]
tabled[QUOTE]
be[QUOTE]
of[QUOTE]
>
>.
>

---

|||BOL documents what locks are acquired when you create a clustered index (an
exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-
clustered indexes are
re-created when you create or drop a clustered index. So, you really have to
test this if it is
feasible in your environment. Also, test that against the export/import stra
tegy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storag
e in any way for pages
which aren't part of an index. I do not right now see any other way to do th
is but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on
the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...[QUOTE]
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> using DBREINDEX nor INDEXDEFRAG. You can
> and import the data.
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> +
> tables a
> tabled
> be
> of|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...

quote:

---

> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?

---

Question about indexdefrag

Hi,
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX nor INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?|||What kind of "impact" would i have if i create/drop
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
>create a clustered index, and possibly drop it. Or export
and import the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
>> Hi,
>> Can anyone explain me:
>> I have a few tables (some with and some without indexes
+
>> some with and some without PK (ex i have a table that
>> doesnt have a primary key NOR indexes)) yes these
tables a
>> fragmented (dbcc showcontig shows fragmentation). I
>> created a job to run the following commands on the
tabled
>> in order to defrag the tables:
>> DBCC DBREINDEX
>> DBCC CHECKTABLE
>> DBCC Cleantable
>> followed by,
>> DBCC CHECKALLOC
>> DBCC UPDATEUSAGE
>> Howevere the tables stay the same (fragmented). I must
be
>> very careful with these tables because they are tables
of
>> a production server that is constantly being used. If
>> suffers alot of updates/inserts/delete and i cannot seem
>> to find a way to defrag the tables.
>> Can anyone help me?
>
>.
>|||BOL documents what locks are acquired when you create a clustered index (an exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-clustered indexes are
re-created when you create or drop a clustered index. So, you really have to test this if it is
feasible in your environment. Also, test that against the export/import strategy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storage in any way for pages
which aren't part of an index. I do not right now see any other way to do this but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> >--Original Message--
> >You cannot defrag datapages which aren't part of an index
> using DBREINDEX nor INDEXDEFRAG. You can
> >create a clustered index, and possibly drop it. Or export
> and import the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Claudia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> Can anyone explain me:
> >>
> >> I have a few tables (some with and some without indexes
> +
> >> some with and some without PK (ex i have a table that
> >> doesnt have a primary key NOR indexes)) yes these
> tables a
> >> fragmented (dbcc showcontig shows fragmentation). I
> >> created a job to run the following commands on the
> tabled
> >> in order to defrag the tables:
> >>
> >> DBCC DBREINDEX
> >> DBCC CHECKTABLE
> >> DBCC Cleantable
> >>
> >> followed by,
> >>
> >> DBCC CHECKALLOC
> >> DBCC UPDATEUSAGE
> >>
> >> Howevere the tables stay the same (fragmented). I must
> be
> >> very careful with these tables because they are tables
> of
> >> a production server that is constantly being used. If
> >> suffers alot of updates/inserts/delete and i cannot seem
> >> to find a way to defrag the tables.
> >>
> >> Can anyone help me?
> >
> >
> >.
> >|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?

Question about index

Hi, all.
Recently one of my collegues asked me to create two indexes:
ix_1 : col1, col2, col3
ix_2 : col1, col2
I wonder this is a good practice to create the second index, ix_2. Because
ix_1 includes all the columns for ix_2.
And another question. Someone asked me to create indexes like following:
ix_1: col1, col2, col4
ix_2: col1, col2, col3
How about this case? Is it a good practice to create each index, or can I
make just one index like following:
ix_1:col1, col2, col3, col4
Any suggesion would be appreciated.See in line
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:uBjyLYxrDHA.3456@.tk2msftngp13.phx.gbl...
> Hi, all.
> Recently one of my collegues asked me to create two indexes:
> ix_1 : col1, col2, col3
> ix_2 : col1, col2
> I wonder this is a good practice to create the second index, ix_2. Because
> ix_1 includes all the columns for ix_2.
Depend on queries being run and the nature of the data.
If the col3 is small then there will be little difference. However, if col3
has an average size of say 1000bytes, then ix_2 will provide some
performance gains in queries that do not need col3. This is because there
will be less physical reads in ix_2
Eg lets say col1 and col2 are ints and col3 is a nvarchar(4000) with an
average of 1024 bytes per record. Also lets say there are 1 million rows in
this table.
So ix_1 will take up about 978Mb (125000) of space, and ix_2 will take up
about 7.5Mb (1000 pages).
Now lets look at the following queries
Select col1, col2 FROM ...
Select col2 FROM ... WHERE col1 = ...
If you do not have ix_2 then you will need to load more pages to get the
results. If you have ix_2 then this will be used as it uses less pages
> And another question. Someone asked me to create indexes like following:
> ix_1: col1, col2, col4
> ix_2: col1, col2, col3
> How about this case? Is it a good practice to create each index, or can I
> make just one index like following:
> ix_1:col1, col2, col3, col4
Similar to above.
> Any suggesion would be appreciated.
>

Question about Extent Scan Fragmentation

I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
either defrags or rebuilds indexes. I wanted to see how well it worked.
Here is the data I recieved.
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1146
- Extents Scanned.......................: 148
- Extent Switches.......................: 150
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 87.84%
- Avg. Bytes Free per Page................: 560.8
- Avg. Page Density (full)................: 93.07%
I was worried about the Extent Scan Fragmentation so I ran.
DBCC DBREINDEX (UserData)
Then the numbers showed:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 77.78%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The extent scan fragementation was still bad, so I tired once more wth:
DBCC DBREINDEX (UserData)
After this try here are the numbers:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 1.39%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Much better this time.
Is this common that it takes multiple tries or am I not understanding
something correctly. This is a bit of new information for me, but I was unde
r
the impression that DBCC DBREINDEX would rebuild my index.
ThanksAs documented in BOL, the extent scan fragmentation calculation does not
work for indexes over multiple files. The first time you rebuilt, I'm
guessing it found space on several files to use and the second time only on
one file.
Logical Scan Fragmentation is what you want to pay attention to anyway - see
the whitepaper below for more details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cooper" <Cooper@.discussions.microsoft.com> wrote in message
news:F7DB6928-1B3E-4B39-88C2-BEB868E4FC71@.microsoft.com...
> I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
> either defrags or rebuilds indexes. I wanted to see how well it worked.
> Here is the data I recieved.
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1146
> - Extents Scanned.......................: 148
> - Extent Switches.......................: 150
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
> - Logical Scan Fragmentation ..............: 0.35%
> - Extent Scan Fragmentation ...............: 87.84%
> - Avg. Bytes Free per Page................: 560.8
> - Avg. Page Density (full)................: 93.07%
> I was worried about the Extent Scan Fragmentation so I ran.
> DBCC DBREINDEX (UserData)
> Then the numbers showed:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 77.78%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The extent scan fragementation was still bad, so I tired once more wth:
> DBCC DBREINDEX (UserData)
> After this try here are the numbers:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 1.39%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Much better this time.
> Is this common that it takes multiple tries or am I not understanding
> something correctly. This is a bit of new information for me, but I was
under
> the impression that DBCC DBREINDEX would rebuild my index.
> Thanks

Question about Extent Scan Fragmentation

I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
either defrags or rebuilds indexes. I wanted to see how well it worked.
Here is the data I recieved.
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1146
- Extents Scanned.......................: 148
- Extent Switches.......................: 150
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 87.84%
- Avg. Bytes Free per Page................: 560.8
- Avg. Page Density (full)................: 93.07%
I was worried about the Extent Scan Fragmentation so I ran.
DBCC DBREINDEX (UserData)
Then the numbers showed:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 77.78%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The extent scan fragementation was still bad, so I tired once more wth:
DBCC DBREINDEX (UserData)
After this try here are the numbers:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 1.39%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Much better this time.
Is this common that it takes multiple tries or am I not understanding
something correctly. This is a bit of new information for me, but I was under
the impression that DBCC DBREINDEX would rebuild my index.
ThanksAs documented in BOL, the extent scan fragmentation calculation does not
work for indexes over multiple files. The first time you rebuilt, I'm
guessing it found space on several files to use and the second time only on
one file.
Logical Scan Fragmentation is what you want to pay attention to anyway - see
the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cooper" <Cooper@.discussions.microsoft.com> wrote in message
news:F7DB6928-1B3E-4B39-88C2-BEB868E4FC71@.microsoft.com...
> I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
> either defrags or rebuilds indexes. I wanted to see how well it worked.
> Here is the data I recieved.
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1146
> - Extents Scanned.......................: 148
> - Extent Switches.......................: 150
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
> - Logical Scan Fragmentation ..............: 0.35%
> - Extent Scan Fragmentation ...............: 87.84%
> - Avg. Bytes Free per Page................: 560.8
> - Avg. Page Density (full)................: 93.07%
> I was worried about the Extent Scan Fragmentation so I ran.
> DBCC DBREINDEX (UserData)
> Then the numbers showed:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 77.78%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The extent scan fragementation was still bad, so I tired once more wth:
> DBCC DBREINDEX (UserData)
> After this try here are the numbers:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 1.39%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Much better this time.
> Is this common that it takes multiple tries or am I not understanding
> something correctly. This is a bit of new information for me, but I was
under
> the impression that DBCC DBREINDEX would rebuild my index.
> Thanks

Question about Extent Scan Fragmentation

I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
either defrags or rebuilds indexes. I wanted to see how well it worked.
Here is the data I recieved.
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1146
- Extents Scanned.......................: 148
- Extent Switches.......................: 150
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 87.84%
- Avg. Bytes Free per Page................: 560.8
- Avg. Page Density (full)................: 93.07%
I was worried about the Extent Scan Fragmentation so I ran.
DBCC DBREINDEX (UserData)
Then the numbers showed:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 77.78%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The extent scan fragementation was still bad, so I tired once more wth:
DBCC DBREINDEX (UserData)
After this try here are the numbers:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 1.39%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Much better this time.
Is this common that it takes multiple tries or am I not understanding
something correctly. This is a bit of new information for me, but I was under
the impression that DBCC DBREINDEX would rebuild my index.
Thanks
As documented in BOL, the extent scan fragmentation calculation does not
work for indexes over multiple files. The first time you rebuilt, I'm
guessing it found space on several files to use and the second time only on
one file.
Logical Scan Fragmentation is what you want to pay attention to anyway - see
the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cooper" <Cooper@.discussions.microsoft.com> wrote in message
news:F7DB6928-1B3E-4B39-88C2-BEB868E4FC71@.microsoft.com...
> I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
> either defrags or rebuilds indexes. I wanted to see how well it worked.
> Here is the data I recieved.
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1146
> - Extents Scanned.......................: 148
> - Extent Switches.......................: 150
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
> - Logical Scan Fragmentation ..............: 0.35%
> - Extent Scan Fragmentation ...............: 87.84%
> - Avg. Bytes Free per Page................: 560.8
> - Avg. Page Density (full)................: 93.07%
> I was worried about the Extent Scan Fragmentation so I ran.
> DBCC DBREINDEX (UserData)
> Then the numbers showed:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 77.78%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The extent scan fragementation was still bad, so I tired once more wth:
> DBCC DBREINDEX (UserData)
> After this try here are the numbers:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 1.39%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Much better this time.
> Is this common that it takes multiple tries or am I not understanding
> something correctly. This is a bit of new information for me, but I was
under
> the impression that DBCC DBREINDEX would rebuild my index.
> Thanks

Question about Clustered Indexes

Using SQL Server 2000 ... hopefully not too dumb a question.

Is there a performance hit using Clustered Index on a table that gets a lot of deletes?

I'm creating a Transaction Log table that will get about 4,000 inserts per day. The value of some of this historical data is worthless after a while, so I delete it.

It occurs to me that this may create a lot of fragmentation. If so, is this cleaned up during weekly "Reorganize data and index pages" in the Maintenance Plan? Do I also need to select "Remove unused space from database files"?

Additional question: I though that care needed to be taken that a clustered key be a value that always increments (datestamp, identity key, etc), yet in this write-up, it shows using randomly generated key values. I'm confused. Wouldn't it have to reorganize everything with greater values to insert the new row into the appropriate spot?
http://www.sql-server-performance.com/gv_clustered_indexes.aspI remember reading that in that article too and it didn't make sense to me either. Using a guid for a pk will certainly cause a lot of fragmentation if your fill factor on the clustered index is high, because page splits will occur often. Note that in 2005, you can use NEWSEQUENTIALID() for a guid pk to avoid this problem. in 2000 you an use Gert Draper's XPGUID.dll for the same feature.

If you set the fill factor on the index high enough, then page splits should occur only rarely if at all since you are deleting data from the table on a regular basis.

I wouldn't use the "remove unused space" task because allocating extents is a rather expensive operation for the server - generally what I do is allocate enough space to your db files to fit the largest amount of data you expect the db to have so that you aren't allocating extents at runtime. if you shrink the files, it seems likely that the server will just have to grow them again (assuming you have autogrowth turned on).|||If you set the fill factor on the index high enough, then page splits should occur only rarely if at all since you are deleting data from the table on a regular basis.Shouldn't that be low enough?


I wouldn't use the "remove unused space" task
+1

Monotonically increasing clustered keys (as you mentioned) are most useful for transactional tables with a high ratio\ number of inserts. With only 4000 a day and an adequte fill factor you should be ok. A reorganise will remove fragmentation. If you are worried, run it nightly (if you have the window) - there's no rule that says you can only reindex on a weekly basis.

Also, deletions will not cause fragmentation but they will reduce the amount of data per page - depending on the question you ask this may be considered a good or bad thing.

HTH|||Jezamine - what on earth is your local time?|||Vich: Clustered indexes on random values are really a bad idea, the indexes got to be fragmented. The only really good candidates to clustered indexes are (semi-)sequential data. Large amount of data with relatively few updates in combination with a suitable fillfactor may also do, but not random data.

With the amount of 4000 inserts a day and a limited lifetime of the data I doubt that you actually need a clustered index, so my advice would be to delete the clustered index and create a nonclustered instead.|||Shouldn't that be low enough?

um, yes, thanks. :o

Jezamine - what on earth is your local time?

i'm in the seattle area, why?

EDIT: here's some justification for why you shouldn't use that "remove unused space" task, which is essentially the same as having autoshrink on, from someone who actually knows what they are talking about - unlike me! ;)

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx|||i'm in the seattle area, why?Because you were active as I got into work 8:00 am bst. Late one?|||Because you were active as I got into work 8:00 am bst. Late one?

bst?? British Summer Time?

Regards,

hmscott|||yea, i'm up late sometimes I guess. it's an addiction. help me!|||bst?? British Summer Time?

Regards,

hmscottYa - our clocks have gone... um... forward.|||It's not that. I just saw BST and I kinda focused in on the first two letters.

Sorry, not much humor there, but it just kinda caught me...

All right folks, nothing to see here, move along...:o

Regards,

hmscott|||Shouldn't that be low enough?

+1

Monotonically increasing clustered keys (as you mentioned) are most useful for transactional tables with a high ratio\ number of inserts.
I would think that clustered indexes:

1. Saves on index maintenance time/space.

2. Decreases seek time. Last week I changed an index to Clustered and an associated update query I was trying to tune up changed from 100 seconds down to 20 seconds (to process about 15,000 rows). The table in question does not get a lot of inserts or deletes.

I can see how it would improve transactional table inserts for reason #1, but I would think that it would potentially vastly improve rarely manipulated but often linked to master tables (like a SKU list) for reason #2.

With only 4000 a day and an adequte fill factor you should be ok. A reorganise will remove fragmentation. If you are worried, run it nightly (if you have the window) - there's no rule that says you can only reindex on a weekly basis.
Thanks for that info - so any damage to seek performance gets cleaned up by Maintenance, therefore no big deal. My weekly should be fine - I'll run the delete process once a week and delete maybe 10%, and it'll run just before weekly maintenance (that currently only takes 10 minutes for the entire database).

Also, deletions will not cause fragmentation but they will reduce the amount of data per page - depending on the question you ask this may be considered a good or bad thing.
HTHI'm afraid I don't understand this. This reveals that I lack a fundamental understanding of how Clustered Tables are actually implemented. I thought they were just sequential by the key value and therefore a delete would cause a fragment and of course any block read may contain logically deleted records. A reorganize would mean the segments are rewritten to remove the fragments. Writing a row that doesn't fit neatly at the top of an existing segment means it has to be inserted by rewriting everything on top of the insert position to create a space. Is my "understanding" wrong or too oversimplified to be useful?

Vich: Clustered indexes on random values are really a bad idea, the indexes got to be fragmented. The only really good candidates to clustered indexes are (semi-)sequential data. Large amount of data with relatively few updates in combination with a suitable fillfactor may also do, but not random data.

Why "relatively few updates"? Does this have to do with how later updating of null columns are implemented in clustered tables. That's a subject that's always been an entire mystery to me (even for non-clustered tables).

I try to think about what happens internally. I insert a row but leave half the columns as "NULL". Isn't the rule in Relational Databases that null columns don't take space?

Some time later, I come along and create values for some NULL columns. Now; for a clustered table, where does it put the values?

Even a clustered row can't be fully pre-extended (given how big a nvarchar(8000) could be). Does such an update later cause reorganization?

With the amount of 4000 inserts a day and a limited lifetime of the data I doubt that you actually need a clustered index, so my advice would be to delete the clustered index and create a nonclustered instead.
Thanks for that brilliant thought. I just realized that I will NEVER use the unique primary key in this transaction file. Most often, I'll be using it to inquire on all transactions for a particular SKU in reverse chronological sequence back to a particular date.

I'd still like to understand more about Clustered Indexes, but clearly I shouldn't make this index Clustered. Perhaps I'll benifit by making the "Transaction Date" index clustered since I'll often want to do a reverse-sequential-read by Transaction Date and they're always inserted in sequence - also; once created they are never changed.|||I would think that clustered indexes:

1. Saves on index maintenance time/space.2. Decreases seek time. Last week I changed an index to Clustered and an associated update query I was trying to tune up changed from 100 seconds down to 20 seconds (to process about 15,000 rows). The table in question does not get a lot of inserts or deletes.
I can see how it would improve transactional table inserts for reason #1, but I would think that it would potentially vastly improve rarely manipulated but often linked to master tables (like a SKU list) for reason #2.My point was about monotonically increasing clustered indexes, not clustered indexes in general.


I'm afraid I don't understand this. This reveals that I lack a fundamental understanding of how Clustered Tables are actually implemented. I thought they were just sequential by the key value and therefore a delete would cause a fragment and of course any block read may contain logically deleted records. A reorganize would mean the segments are rewritten to remove the fragments. Writing a row that doesn't fit neatly at the top of an existing segment means it has to be inserted by rewriting everything on top of the insert position to create a space. Is my "understanding" wrong or too oversimplified to be useful?
Imagine ten rows with one integer column with values 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Clustered index on the integer column.
Rows 1, 2, 3 are in page one. Rows 4, 5, 6, 7 are in page two. Rows 8, 9, 10 are in page three. I delete rows 4, 5, 9. So now:
Page one: 1, 2, 3
Page two: 6, 7
Page three: 8, 10

No fragmentation - the data is still in consecutive pages, order by the values in the key. The pages are just a little less full, which is not the same thing. Performance is still affected but this is not fragmentation. If they were full to begin with and I inserted 5.5 (ok, ok - they were decimals :D) then we will introduce fragmentation because half the rows in page two would have to be moved to page four. Now the page order would be one, two, four, three.

Quickly on some of your other points not addressed at me:
I think roac meant few updates of the clustered index keys.
A nullable column has an extra bit "flag" to indicate when it is null. So no - nulls take up no extra space but nullable columns do take up more space to accommodate the flag.

Have you seen this?
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I would think that clustered indexes.....
.........Actually what were addressing there lol? Just realised there was a bit in the quote about fillfactors and shrinks too.|||EDIT: here's some justification for why you shouldn't use that "remove unused space" task, which is essentially the same as having autoshrink on, from someone who actually knows what they are talking about - unlike me! ;)

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspxIn case you are interested vich Paul Randall is a bit of a guru's guru - he wrote the dbcc dbreindex and a few other console commands and assisted with the paper on fragmentation I linked (though he isn't credited lol). If I see anything by that guy I make sure I read it twice :)|||I think roac meant few updates of the clustered index keys.
Yes I did. And variable-length columns. You may get a page split from updating variable length columns as well, as these are normally stored in-page. So, if you update a varchar(40) from a value 'Test' to 'Test value', that may eventually end up in a page split, since there is not enough room in the page to store 'Test value'. An even better example would of course be to update a variable length columns from NULL to a value.|||Yes I did. And variable-length columns. You may get a page split from updating variable length columns as well, as these are normally stored in-page. So, if you update a varchar(40) from a value 'Test' to 'Test value', that may eventually end up in a page split, since there is not enough room in the page to store 'Test value'. An even better example would of course be to update a variable length columns from NULL to a value.
I'll extend that to mean that even nullable non-variable length columns may cause a page split if populated after more rows are inserted (via an UPDATE statement).

Page split is a new concept for me. In truth, a "page" is a new concept. Is that an SQL managed thing, or is that part of the file system? I guess I need to go read some intro to SQL's physical layer.

It follows that a database is capable of inserting or reordering a page into the logical sequence - by definition deviating from the physical (OS File System) contiguous logical sequence.

I'm making a lot of uninformed leaps of logic here. Anyone got a link for the physical layer ... or at least the layer right above the OS file system? One that focuses on paging would be great.

EDIT: Scratch that ... finally looking at that article referenced above. It seems to cover it. :S Thanks pootle flump.|||I use GUIDs in my databases all the time, often as primary keys, and I have never seen any appreciable performance issues.|||I use GUIDs in my databases all the time, often as primary keys, and I have never seen any appreciable performance issues.Clustered pks? Do you compensate with low fill factors? Batch systems or OLTP? The old GUID or the new sequential one?

I will admit I have never benchmarked a page split cost. Has anyone?|||I'm making a lot of uninformed leaps of logic here. Anyone got a link for the physical layer ... or at least the layer right above the OS file system? One that focuses on paging would be great.

I just HAVE TO recommend Kalen Delaney's Inside SQL Server 2005 - The Storage Engine (http://www.microsoft.com/mspress/books/7436.aspx).|||I just HAVE TO recommend Kalen Delaney's Inside SQL Server 2005 - The Storage Engine (http://www.microsoft.com/mspress/books/7436.aspx).Lol - I was going to do the same until he mentioned he was on top of it. I wonder, though, if she assumes a little knowledge on the part of the reader. I am not sure if it is a good "first pass" on physical storage for someone new to the concept of, for example, data pages. I could be wrong of course. All I know is there are a fair few bits I will need to revisit for it to truly sink in.|||Lol - I was going to do the same until he mentioned he was on top of it. I wonder, though, if she assumes a little knowledge on the part of the reader. I am not sure if it is a good "first pass" on physical storage for someone new to the concept of, for example, data pages. I could be wrong of course. All I know is there are a fair few bits I will need to revisit for it to truly sink in.
Thanks for the recommendation. Amazon has one used for $26. I'll also get T-SQL Querying (http://www.sql.co.il/books/insidetsql2005/), another in the Inside Microsoft SQL Server 2005 series that looks more up my alley (I'm not a DBA ... just a curious programmer who gets saddled with DBA type tasks).

That's a bit more than I was hoping simply to answer this one topic of "the ramifications of clustered indexing" - I guess I can't hope for a single sitting explanation. The linked article is helping to answer a lot however.

If someone has another link to shed another angle on it, I'd appreciate that. However; I think these books will have a chapter or two that will suffice (if I can understand them that is).|||I was honored with a private EMAIL conversation with I Itzik Ben-Gan on this subject. He referenced an interview he did with David Cambell. He noted that he asked a pointed question on Clustered Indexed tables.

Interview here:
http://www.sqlmag.com/article/articleid/96048/96048.html

One quote in particular addresses the concern I was having: ...
Back in the days when you were deeply involved in the shaping of the storage
engine of SQL Server 7.0 you used to participate in some private SQL
newsgroups. Your explanations were so detailed and clear and were considered
pure gold by many of us. For example, I remember how you solved a major
mystery that involved querying a small heap that reported a huge number of
logical reads, after the table underwent an update statement expanding varchar
strings. You explained that the expansion of the varchar strings that didn’t have
room to expand in their hosting pages caused a large number of forwarding
pointers, and that SQL Server had to jump back and forth between the page
holding the pointer and the page holding the pointed record. Suddenly it all
seemed to make sense. For us teachers and students, someone with both a lot
of knowledge and great explanatory skills is a rare sight, and the SQL Server
community can benefit from having this knowledge conveyed through books.
Have you considered/are considering writing a book and passing on what you
know through such means?

I received Itzik's book last night. No time to read his recommendation yet (Chapter 3, Query Tuning - a fairly long chapter).

The question I quote above implies that page splits are done with a pointer to the actual record that must have gotten physically added elsewhere.

I should note that this article references some advice to NOT use incrementing keys. That so thoroughly confused me that I asked him for clarification. He explained that it's for a bigger shop that splits tables across multiple physical drives, and this is a method for taking advantage of simultaneous writing because it's not all getting heaped onto the top.

I think I'm displaying my ignorance here, but that's OK. Just though I'd mention that here in case somebody else found David's recommendations confusing.|||I should note that this article references some advice to NOT use incrementing keys. That so thoroughly confused me that I asked him for clarification. He explained that it's for a bigger shop that splits tables across multiple physical drives, and this is a method for taking advantage of simultaneous writing because it's not all getting heaped onto the top.Isn't that about hot spots and isn't that out of date since SQL 2000? It was an issue in SQL 7.0 but not now?

I only know from my own experience that I recently worked with an inherited medium sized database (~40GB) with about 10 or so very narrow tables (typically 4-5 fields). Not terribly large db but pretty deep tables of several hundred million rows each. Monthly loads in a batch of around 30 million records per batch per table. Changing the clustered keys from "random" to incrementing bought something like a 90% drop in load time.

Good job on getting some first hand knowledge from Itzik Ben-Gan - I haven't read his books yet but some of his articles. Obviously he is very well respected.|||Isn't that about hot spots and isn't that out of date since SQL 2000? It was an issue in SQL 7.0 but not now?

I only know from my own experience that I recently worked with an inherited medium sized database (~40GB) with about 10 or so very narrow tables (typically 4-5 fields). Not terribly large db but pretty deep tables of several hundred million rows each. Monthly loads in a batch of around 30 million records per batch per table. Changing the clustered keys from "random" to incrementing bought something like a 90% drop in load time.

Good job on getting some first hand knowledge from Itzik Ben-Gan - I haven't read his books yet but some of his articles. Obviously he is very well respected.Thanks, he didn't elaborate much - just a couple of sentences to clarify and said that for a single-drive DB ordered keys IS better (darn, my EMAIL is deleted). I can only imagine it would have to be done in a particular way or (even if spread across physical drives) it would go page-split crazy inserting into the center of that section of a heap.

BTW: May I assume that in SQL Server that "Heap" equates to "Cluster Indexed"?

As for Version 7 vs. 2000 (and even 2005) the article mentions that most of the "SQL Engine" is still from version 7, and the subsequent versions have just added Enterprise capability (to expand it beyond just being a DB - mining, reporting, etc). I imagine your question was rhetorical (or directed at some expert) since I'm a comparative rookie.

Surely some aspects of performance have been tuned up (or rewritten) and this could be one of them, do you know if that's the case? Can you tell me if Page Split is done with pointers from the calculated location? Sort of an extension of the calculated (supposed key) location - so the split is physically located close-by or far-away (physically).

If page splitting was and still is handled that way, then the problem described would still be an issue.|||BTW: May I assume that in SQL Server that "Heap" equates to "Cluster Indexed"?No - you may not :) It is EXACTLY the opposite. The definition of a Heap is a table without a clustered index.

I imagine your question was rhetorical (or directed at some expert) since I'm a comparative rookie.Yeah - wondering aloud. Happy for clarification.|||Yeah - wondering aloud. Happy for clarification.
LOL. I have no problem keeping the information flowing mainly in the direction of you guys out. I'm learning to mainly chime in when I have a question or have stumbled across something interesting like Mr. Ben-Gan's interview with Mr. Cambell (aka Mr. SQL Server).

This was the reference to heap and indexing.

3. Watch out for insert rates on clustered indexes with monotonically
increasing keys. (You can distribute inserts sometimes by doing some key
munging tricks)

4. For logging tables with no indexes and high insert rates go with a heap
as we can distribute the inserts across a number of pages.
On #3, I'm guessing that "distribute inserts" means distributing it across multiple drives.

Drrrr, heap! Thanks for the clarification. You can put the brick away now (it was required to get through my thick skull - Hey, I've got lots of stupid questions where that one came from! LOL :p).|||Heh heh - I was tootling around on SQL team and ended back at a classic thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53085&whichpage=2&SearchTerms=guid%2Cindex

Yup - good old Paul Randall again.

You will get an exam on Merry-Go-Round Scans and their implications shortly ;)

Question

I have a table "X" and it has no indexes. However it is
fragmented (dbcc showcontig X). I want to defrag the table
but when i do dbcc dbreindex it doesnt do anything. Stays
the same way. I am experiencing the same problem on
various tables. Can anyone help me please?Do you have a primary key ?
If so look up the command DBCC INDEXDEFRAG.
J
>--Original Message--
>I have a table "X" and it has no indexes. However it is
>fragmented (dbcc showcontig X). I want to defrag the
table
>but when i do dbcc dbreindex it doesnt do anything. Stays
>the same way. I am experiencing the same problem on
>various tables. Can anyone help me please?
>.
>|||Table X does not have a primary key... :(
>--Original Message--
>Do you have a primary key ?
>If so look up the command DBCC INDEXDEFRAG.
>J
>>--Original Message--
>>I have a table "X" and it has no indexes. However it is
>>fragmented (dbcc showcontig X). I want to defrag the
>table
>>but when i do dbcc dbreindex it doesnt do anything.
Stays
>>the same way. I am experiencing the same problem on
>>various tables. Can anyone help me please?
>>.
>.
>|||Although your table probably should have a clustered and some non-clustered
indexes, ( that is another conversation.)
You might simply create a clustered index on the table, then drop it... The
table rows will be cleaned up during the create index.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:050901c3de70$8682d350$a301280a@.phx.gbl...
> I have a table "X" and it has no indexes. However it is
> fragmented (dbcc showcontig X). I want to defrag the table
> but when i do dbcc dbreindex it doesnt do anything. Stays
> the same way. I am experiencing the same problem on
> various tables. Can anyone help me please?|||Hello,
The problem here then is that it can't be ordered. The
reason is that for the defragmentation to work it needs to
know what criteria it needs to do to perform the defrag.
In this case indexes.
On a peronal note can I ask why no indexes ?, that makes
for a very slow database.
J
>--Original Message--
>Table X does not have a primary key... :(
>>--Original Message--
>>Do you have a primary key ?
>>If so look up the command DBCC INDEXDEFRAG.
>>J
>>--Original Message--
>>I have a table "X" and it has no indexes. However it is
>>fragmented (dbcc showcontig X). I want to defrag the
>>table
>>but when i do dbcc dbreindex it doesnt do anything.
>Stays
>>the same way. I am experiencing the same problem on
>>various tables. Can anyone help me please?
>>.
>>.
>.
>

question

anyone know if creating an index on a table that has no
primary key and no indexes it will defrag the table of
fragmentation... if so how does it do that?Creating a clustered index will reorg data pages. When you add a primary
key on a table with no clustered index, SQL Server will create a unique
clustered index to support the constraint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...
> anyone know if creating an index on a table that has no
> primary key and no indexes it will defrag the table of
> fragmentation... if so how does it do that?|||I know Dan knows this but just to clarify for novices...
you can specify a NC index for a PK. However it will default to clustered if
there isn't already a clustered index...
--
Brian
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ux6lLja5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Creating a clustered index will reorg data pages. When you add a primary
> key on a table with no clustered index, SQL Server will create a unique
> clustered index to support the constraint.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> ":)" <anonymous@.discussions.microsoft.com> wrote in message
> news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...
> > anyone know if creating an index on a table that has no
> > primary key and no indexes it will defrag the table of
> > fragmentation... if so how does it do that?
>

Question

I have a table "X" and it has no indexes. However it is
fragmented (dbcc showcontig X). I want to defrag the table
but when i do dbcc dbreindex it doesnt do anything. Stays
the same way. I am experiencing the same problem on
various tables. Can anyone help me please?Although your table probably should have a clustered and some non-clustered
indexes, ( that is another conversation.)
You might simply create a clustered index on the table, then drop it... The
table rows will be cleaned up during the create index.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:050901c3de70$8682d350$a301280a@.phx.gbl...

quote:

---

> I have a table "X" and it has no indexes. However it is
> fragmented (dbcc showcontig X). I want to defrag the table
> but when i do dbcc dbreindex it doesnt do anything. Stays
> the same way. I am experiencing the same problem on
> various tables. Can anyone help me please?

---

question

anyone know if creating an index on a table that has no
primary key and no indexes it will defrag the table of
fragmentation... if so how does it do that?Creating a clustered index will reorg data pages. When you add a primary
key on a table with no clustered index, SQL Server will create a unique
clustered index to support the constraint.
Hope this helps.
Dan Guzman
SQL Server MVP
"" <anonymous@.discussions.microsoft.com> wrote in message
news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...

quote:

---

> anyone know if creating an index on a table that has no
> primary key and no indexes it will defrag the table of
> fragmentation... if so how does it do that?

---

|||I know Dan knows this but just to clarify for novices...
you can specify a NC index for a PK. However it will default to clustered if
there isn't already a clustered index...
Brian
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ux6lLja5DHA.2776@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Creating a clustered index will reorg data pages. When you add a primary
> key on a table with no clustered index, SQL Server will create a unique
> clustered index to support the constraint.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "" <anonymous@.discussions.microsoft.com> wrote in message
> news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...
>

---