DBCC SHOWCONTIG

I have a table called Shipping_Log_Element
This table has two indexes: a Primary Key and an index on a foreign_key referrant (refers to primary key in another table)
When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I receive 4 rows: one each for the indexes listed above, plus two others. One of the others shows no index name, and the other shows an index name of tShipping_Log_Element. Bot of these two are highly fragmented based on Logical and ExtentFragmentation.
I ran DBCC DBREINDEX on all tables last night, so am not expecting to see much fragmentation.
What are these? What does the fragmentation on these mean? (i.e. what should I do about it)?
Thanks.Hi,
Table fragmentation occurs through the process of data modifications
(INSERT, UPDATE, and DELETE statements) made against the table.
To remove the fragmentation you can execute,
DBCC INDEXDEFRAG / DBCC REINDEX / DROP & CREATE Indexes
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:A04732B3-57E1-48B3-B70D-1C07523874A3@.microsoft.com...
> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key
referrant (refers to primary key in another table)
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
> I receive 4 rows: one each for the indexes listed above, plus two others.
One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
> Thanks.|||What's the index Id of the nameless index? The tShipping_Log_Element index
will have ID=255 and is the IAM chain containing all text/image data for the
table. It's fragmentation cannot be addressed in SQL Server 2000 or before
except by re-creating the table using bcp out/in (however, we've added that
functionality in SQL Server Yukon).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:A04732B3-57E1-48B3-B70D-1C07523874A3@.microsoft.com...
> I have a table called Shipping_Log_Element
> This table has two indexes: a Primary Key and an index on a foreign_key
referrant (refers to primary key in another table)
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
> I receive 4 rows: one each for the indexes listed above, plus two others.
One of the others shows no index name, and the other shows an index name of
tShipping_Log_Element. Bot of these two are highly fragmented based on
Logical and ExtentFragmentation.
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
> Thanks.|||Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about this? ie. is this hurting performance?
The other 'unknown' index with fragmentation has an index id of 0. Any thoughts on what that is?
There are several tables in this condition. To bcp out/in would be an effort and require an outage. What about dropping statistics, dropping indexes, and then recreating?
Is there any other solution besides bcp out/in? (short of Yukon)
Thanks for the help!|||You could do a test with your app before and after export/import to see if it hurts performance. As
with most things, it depends. If the data stored in image column isn't much for each row, and you
have good selectivity for the queries, I doubt it will hurt much.
As for the indid 0, these are data pages for tables without clustered indexes. I would start
investigating why you have tables without clustered indexes in the first place. There is no easy way
to "defrag" such, one method is to create a clustered index and then drop it. Or export/import.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:9EBF3C3E-E486-4524-89F0-3694BFFEC54D@.microsoft.com...
> Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about this?
ie. is this hurting performance?
> The other 'unknown' index with fragmentation has an index id of 0. Any thoughts on what that is?
> There are several tables in this condition. To bcp out/in would be an effort and require an
outage. What about dropping statistics, dropping indexes, and then recreating?
> Is there any other solution besides bcp out/in? (short of Yukon)
> Thanks for the help!|||Yes, we should have no tables without clustered indexes. (sounds like the old song... no bananas)
I'll follow up on that
Thanks to all!!
Linda