This table has two indexes: a Primary Key and an index on a foreign_key refe
rrant (refers to primary key in another table)
When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS, A
LL_INDEXES, NO_INFOMSGS
I receive 4 rows: one each for the indexes listed above, plus two others. O
ne of the others shows no index name, and the other shows an index name of t
Shipping_Log_Element. Bot of these two are highly fragmented based on Logica
l and ExtentFragmentation.
I ran DBCC DBREINDEX on all tables last night, so am not expecting to see mu
ch 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...
quote:
> 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)
quote:
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
quote:
> 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.
quote:
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
quote:
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
quote:|||What's the index Id of the nameless index? The tShipping_Log_Element index
> Thanks.
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...
quote:
> 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)
quote:
> When I do a DBCC SHOWCONTIG (Shipping_Log_Element) WITH FAST,TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
quote:
> 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.
quote:
> I ran DBCC DBREINDEX on all tables last night, so am not expecting to see
much fragmentation.
quote:
> What are these? What does the fragmentation on these mean? (i.e. what
should I do about it)?
quote:|||Thanks. The tShipping_Log_Element is indeed index id 255. So should I be co
> Thanks.
ncerned about this? ie. is this hurting performance?
The other 'unknown' index with fragmentation has an index id of 0. Any thoug
hts on what that is?
There are several tables in this condition. To bcp out/in would be an effor
t and require an outage. What about dropping statistics, dropping indexes, a
nd 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 i
t 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 indexe
s. I would start
investigating why you have tables without clustered indexes in the first pla
ce. 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=...ls
erver
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:9EBF3C3E-E486-4524-89F0-3694BFFEC54D@.microsoft.com...
quote:
> Thanks. The tShipping_Log_Element is indeed index id 255. So should I be concerned about
this?
ie. is this hurting performance?
quote:
> The other 'unknown' index with fragmentation has an index id of 0. Any tho
ughts 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?
quote:|||Yes, we should have no tables without clustered indexes. (sounds like the ol
d">
> Is there any other solution besides bcp out/in? (short of Yukon)
> Thanks for the help!
d song... no bananas).
I'll follow up on that.
Thanks to all!!!
Lindasql
No comments:
Post a Comment