DROP and CREATE INDEXES

I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
SHOWCONTIG on the table both befor and after dropping and recreating the
indexes, including PK constraints, the results are the same? Any insight? I
thought this would reorganize the data to be more efficient? What am I doing
wrong?
DROP INDEX [PD7333].[F98741].[F98741_1]
ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
[ESEVSEQ])
ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
NONCLUSTERED ([ESEVSK], [ESEVSEQ])
DBCC SHOWCONTIG scanning 'F98741' table...
Table: 'F98741' (277576027); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned........................: 30049
- Extents Scanned.......................: 3786
- Extent Switches.......................: 3785
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
- Extent Scan Fragmentation ...............: 97.94%
- Avg. Bytes Free per Page................: 221.6
- Avg. Page Density (full)................: 97.26%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.That is because your table is a HEAP which means there is no clustered
index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
proper column and create a clustered index and your problem will go away.
By the way you should use DBCC DBREINDEX instead of dropping and creating.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> SHOWCONTIG on the table both befor and after dropping and recreating the
> indexes, including PK constraints, the results are the same? Any insight?
> I
> thought this would reorganize the data to be more efficient? What am I
> doing
> wrong?
> DROP INDEX [PD7333].[F98741].[F98741_1]
> ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> [ESEVSEQ])
> ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>
> DBCC SHOWCONTIG scanning 'F98741' table...
> Table: 'F98741' (277576027); index ID: 0, database ID: 14
> TABLE level scan performed.
> - Pages Scanned........................: 30049
> - Extents Scanned.......................: 3786
> - Extent Switches.......................: 3785
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> - Extent Scan Fragmentation ...............: 97.94%
> - Avg. Bytes Free per Page................: 221.6
> - Avg. Page Density (full)................: 97.26%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.|||Thanks. I had started with DBREINDEX but when I saw it made no change, I
resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and I
don't know if I can add and/or change these indexes to clustered...I'll have
to find out.
"Andrew J. Kelly" wrote:
> That is because your table is a HEAP which means there is no clustered
> index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
> proper column and create a clustered index and your problem will go away.
> By the way you should use DBCC DBREINDEX instead of dropping and creating.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> > I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> > SHOWCONTIG on the table both befor and after dropping and recreating the
> > indexes, including PK constraints, the results are the same? Any insight?
> > I
> > thought this would reorganize the data to be more efficient? What am I
> > doing
> > wrong?
> >
> > DROP INDEX [PD7333].[F98741].[F98741_1]
> >
> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> >
> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> > [ESEVSEQ])
> >
> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
> >
> >
> >
> > DBCC SHOWCONTIG scanning 'F98741' table...
> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
> > TABLE level scan performed.
> > - Pages Scanned........................: 30049
> > - Extents Scanned.......................: 3786
> > - Extent Switches.......................: 3785
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> > - Extent Scan Fragmentation ...............: 97.94%
> > - Avg. Bytes Free per Page................: 221.6
> > - Avg. Page Density (full)................: 97.26%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
>
>|||Well you need to tell them to get their act together<g>. You can create a
clustered index and then drop it to defrag the table but each table really
should have a clustered index on it.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:E8335A7A-73EA-427B-9DB3-1BBE473488D0@.microsoft.com...
> Thanks. I had started with DBREINDEX but when I saw it made no change, I
> resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and
> I
> don't know if I can add and/or change these indexes to clustered...I'll
> have
> to find out.
> "Andrew J. Kelly" wrote:
>> That is because your table is a HEAP which means there is no clustered
>> index. You can not reduce fragmentation on a HEAP by reindexing. Choose
>> a
>> proper column and create a clustered index and your problem will go away.
>> By the way you should use DBCC DBREINDEX instead of dropping and
>> creating.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
>> > I'm trying to reduce fragmentation of indexes on tables. When I run
>> > DBCC
>> > SHOWCONTIG on the table both befor and after dropping and recreating
>> > the
>> > indexes, including PK constraints, the results are the same? Any
>> > insight?
>> > I
>> > thought this would reorganize the data to be more efficient? What am I
>> > doing
>> > wrong?
>> >
>> > DROP INDEX [PD7333].[F98741].[F98741_1]
>> >
>> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
>> >
>> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
>> > [ESEVSEQ])
>> >
>> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
>> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>> >
>> >
>> >
>> > DBCC SHOWCONTIG scanning 'F98741' table...
>> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 30049
>> > - Extents Scanned.......................: 3786
>> > - Extent Switches.......................: 3785
>> > - Avg. Pages per Extent..................: 7.9
>> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
>> > - Extent Scan Fragmentation ...............: 97.94%
>> > - Avg. Bytes Free per Page................: 221.6
>> > - Avg. Page Density (full)................: 97.26%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>>