January 15, 2008 at 1:09 pm
When I do a "showcontig" on a database I get the following results on a particular table:
Table: 'CONTACT' (757577737); index ID: 0, database ID: 32
TABLE level scan performed.
- Pages Scanned................................: 101
- Extents Scanned..............................: 81
- Extent Switches..............................: 80
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 16.05% [13:81]
- Extent Scan Fragmentation ...................: 97.53%
- Avg. Bytes Free per Page.....................: 2984.5
- Avg. Page Density (full).....................: 63.13%
This is pretty indicative of the results from the other tables as well.
I ran the following to reindex:
USE Hansen_Test
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
I get the following results after a new showcontig:
Table: 'CONTACT' (757577737); index ID: 0, database ID: 32
TABLE level scan performed.
- Pages Scanned................................: 101
- Extents Scanned..............................: 81
- Extent Switches..............................: 80
- Avg. Pages per Extent........................: 1.2
- Scan Density [Best Count:Actual Count].......: 16.05% [13:81]
- Extent Scan Fragmentation ...................: 97.53%
- Avg. Bytes Free per Page.....................: 2984.5
- Avg. Page Density (full).....................: 63.13%
So.... what gives?? Is it fragmented?? Did it or did it not reindex?? And if it did, why are the stats not updating???
Help!!
Thanks!!
Tina
January 15, 2008 at 1:24 pm
Index 0 indates the table is a heap (no clustered index), so you can't reorganize it with DBCC REINDEX.
Your only choices are to completly reload the table or to create a clustered index on the table.
January 15, 2008 at 1:39 pm
Ok... So I just want to make sure I'm clear....
Are you saying since it doesn't have an index, it's not really fragmented?
Also, I'm not sure about reloading the table or creating a clustered index... This db resides on our Enyerprise SQL server, but it is "managed" by a third party vendor (all we were instructed to do for maintenance is to ensure backups) so I'm unsute how the reload or new clutered index will react with the software app.
What do you think?
January 15, 2008 at 2:06 pm
Tina Tysinger (1/15/2008)
...Are you saying since it doesn't have an index, it's not really fragmented?...
No, I said you can't reorganize it with DBCC REINDEX.
January 15, 2008 at 2:14 pm
Ok.... Can't blame me for hoping!!
I know you said the best way around this is to reload the table or create a clustered index.... But I'm still not sure about doing this since the db resides on our Enterprise SQL server, but it is "managed" by a third party vendor. If I create a new clustered index for each table, how will it react with the software app? Or are the clustered indexes an internal function of SQL?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply