March 1, 2005 at 9:24 am
hai,
i have the following result after the execution of dbcc showcontig on a very large table
DBCC SHOWCONTIG scanning 'bholdmst' table...
Table: 'bholdmst' (1993058136); index ID: 0, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 406275
- Extents Scanned..............................: 50793
- Extent Switches..............................: 50792
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [50785:50793]
- Extent Scan Fragmentation ...................: 11.18%
- Avg. Bytes Free per Page.....................: 335.0
- Avg. Page Density (full).....................: 95.86%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
From this can anybody tell me should i do reindex/index defrag.
this table dosen't have nay clustered index and since Logical Fragmentation is not displayed
subban
March 1, 2005 at 12:43 pm
The index ID in the showcontig results are for index ID = 0 , which is not an index, but is the heap (the data itself). If this is the only output you got, then that table has no indexes.
As a general rule, the higher the value for scan density the better, and that value becomes more significant as the table becomes larger (look at pages scanned).
Based on the information that you provided, your table appears to be in excellent condition, and no action should be required.
In this case, if the showcontig showed a problem, you would probably need to create a clustered index, which will defragment the data. The index can then be deleted or kept, whichever you prefer. A meaningful clustered index, though, is usually a good thing.
Steve
March 1, 2005 at 1:16 pm
Maybe this provides additional information:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 2, 2005 at 12:56 am
hai hoo-t,
Ok your suggestion is great abd appreciable..
But the fact is the the table is having Non-clustered index...
subban
March 2, 2005 at 2:33 am
Every table should have a clustered index
March 2, 2005 at 3:50 am
I wouldn't say so quickly that every table should have a clustered index.
In fact having a clusterd index on a table where you have a very large amount of insert or upgrade could hinder the performance. Also if there is no range queries (like, between, >, ...) the fact of having a clustered index doesn't help a lot.
In addition what you have to know: SQLServer is adding behind the scene the columns of the clustered index to each and every nonclustered indexes as well. Therefore it is important to keep the clustered indexes as narrow as possible especially is you have a lot of another nonclustered indexes and even more if you have an important number of columns within your index.
But genarally said it is a good advice to have a clustered index on a table bacause ina "normal" life there is much more select, then insert and update un the table and without clustered index there is no possibilities to defragment a table.
Bye
Gabor
March 2, 2005 at 3:58 am
gabor,
ok you mean to say if there is no clustered index, defagramentation is not required right ?
subban
March 2, 2005 at 5:15 am
No. This is not what I meant.
If there is no clustered index on the table, you are NOT ABLE to defragment that table, even if the table is heavyly fragmented.
Only the clustered index is able to influence the physical order of the records within the base table.
What I've said is that in the real life (and in the IT area more specifically) I don't like such statements that ALLWAYS, EVERY, NEVER... I like to say it depends...
Once you know how your RDBMS is working under the cover you can take the appropriate decision. If this is not quite clear for you the me too I would suggest to have a clustered index on your tables unless you have exactly tested that in some particular cases not having a clustered index improves performance
Bye
Gabor
March 2, 2005 at 10:36 am
subban,
The only information that you posted was for the heap (index id = 0). If you have nonclustered indexes on the table, the dbcc showcontig should have similar output to what you posted for EACH index on the table. Only by seeing that output can we help you decide if the indexes need to be defragmented.
Steve
--------------------------------------------------
hai hoo-t,
Ok your suggestion is great abd appreciable..
But the fact is the the table is having Non-clustered index...
subban
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply