March 29, 2005 at 9:09 am
We have a table (designed by a vendor for their applicaion specific) having more than 1 crore records and dosen't have any clustered index specified on that table. when i do a DBCC SHOWCONTIG gives the following result
DBCC SHOWCONTIG scanning 'aoldmst' table...
Table: 'aoldmst' (1993058136); index ID: 0, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 426520
- Extents Scanned..............................: 53324
- Extent Switches..............................: 53323
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.98% [53315:53324]
- Extent Scan Fragmentation ...................: 10.70%
- Avg. Bytes Free per Page.....................: 335.0
- Avg. Page Density (full).....................: 95.86%
if i execute a select * from aoldmst, is taking a lot of time..
can anybody suggest how to tune this ?
subban
March 29, 2005 at 11:38 pm
Hi subban,
clustered indexes are usually said to be usefull when selecting and ORDERed recordset or when filtering on the index prefix (leading portion of columns). What do you expect from a SELECT * query on a table of that size ? To complete in 2 seconds ? And what is the exact number of records, I didn't understand that "crore" word. On the other hand, the DBCC result does not show too bad results, having in mind that clustered index does not exist.
Clustered indexes are a good practice (usually as a PK) but there are situations when they may not be necessary/usefull.
An what about other (secondary) non-clustered indexes ? Maybe the vendor application does some filtering on other columns.
Regards,
Goce.
March 30, 2005 at 2:17 am
Subban,
It is quite normal, that your query is taking as much time:
you have 426520 pages (3,5GB) with an average page density of 95.86 and an overal scan density of 99,98% which is quite OK.
So you have no fragmentation issue.
Beside of this you are making a select * from aoldmst without any where clause, by this you are doing a table scan of 426520 pages!
It just cannot be quick!
If you want to defrag you table (not just the indexes) you will have to make one of your indexes clustered. But again your table seems to be in good shape from a fragmentation point of view
Bye
Gabor
March 30, 2005 at 2:47 am
Piggy-backing on Gabor, this might be interesting:
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 30, 2005 at 4:52 am
OK, thanks...
i know that if clustered index exists, Logical Scan Fragmentation can be a measure for the level of fragmentation, since here we didn't have any clustered index and the index is not spanning multiple files i think i can take the scan density as a measure of fragmentation.
thanks all..
subban
March 30, 2005 at 1:40 pm
Subban, you are running a select * from the table with no where clause. As such the index does not even come into play. Were you using a where, or an order by then certainly the index would be a factor, however you are not using it right now (at least not with the query you showed us).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply