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...
>
>
|||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...[vbcol=seagreen]
> 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: