January 2, 2009 at 5:34 am
You've got a primary key, why not put a clustered index on it?
Anyhow, try removing the WITH FAST option and see if you get different results. Per BOL "Specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index"
January 2, 2009 at 5:34 am
I suspect it has something to do with FAST switch used here.
FAST
Specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index.
Try running showcontig without it.
MJ
January 2, 2009 at 7:44 am
Nicholas Cain (1/2/2009)
You've got a primary key, why not put a clustered index on it?Anyhow, try removing the WITH FAST option and see if you get different results. Per BOL "Specifies whether to perform a fast scan of the index and output minimal information. A fast scan does not read the leaf or data level pages of the index"
These tables are part of a purchased application at my new job. I don't know why they don't use clustered indexes.
I used FAST because I was worried about locking the tables for too long. Maybe I can try again this weekend.
January 2, 2009 at 7:51 am
I can't stand those third party canned solutions. They are nearly always a nightmare, and if you change even one thing, like an index to improve the performance 90% then they'll tell you that they can't provide support (take Business Objects as an example)
It a very frustrating thing.
January 2, 2009 at 10:26 am
Why do you think you don't have a clustered index? I would take another look at the table - because your results are telling you that you do in fact have a clustered index.
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 0, database ID: 88
TABLE level scan performed.
- Pages Scanned................................: 2870787
- Extents Scanned..............................: 359947
- Extent Switches..............................: 359946
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.69% [358849:359947]
- Extent Scan Fragmentation ...................: 95.51%
- Avg. Bytes Free per Page.....................: 479.4
- Avg. Page Density (full).....................: 94.08%
index ID: 0 is the clustered index.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2009 at 10:48 am
I thought ID0 meant it was a heap and ID1 was the clustered index? (I could easily be mistaken here)
January 2, 2009 at 11:02 am
Jeffrey Williams (1/2/2009)
Why do you think you don't have a clustered index? I would take another look at the table - because your results are telling you that you do in fact have a clustered index.DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 0, database ID: 88
TABLE level scan performed.
- Pages Scanned................................: 2870787
- Extents Scanned..............................: 359947
- Extent Switches..............................: 359946
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.69% [358849:359947]
- Extent Scan Fragmentation ...................: 95.51%
- Avg. Bytes Free per Page.....................: 479.4
- Avg. Page Density (full).....................: 94.08%
index ID: 0 is the clustered index.
I was basing it on the result of querying the system tables and getting this:
table index_name RowsFile index_descriptionindex_keys
Group
CallLogCustomer_ID95882253INDEXESnonclustered located on INDEXESCustomer_ID
CallLogIX_CallLog_CreditTo95882253INDEXESnonclustered located on INDEXESCampaign_ID,CreditTo
CallLogIX_CallLog_Disposition 95882253INDEXESnonclustered located on INDEXESCampaign_ID, Disposition
CallLogIX_CallLog_FK 97478956INDEXESnonclustered located on INDEXESCampaign_ID, Customer_ID
CallLogIX_CallLog_Start 108538812INDEXESnonclustered located on INDEXESStart
CallLogPK_CallLog111736396INDEXESnonclustered, unique, primary key located on INDEXESID
January 2, 2009 at 11:11 am
Oops - I read that wrong. Index ID 0 is a heap - ID 1 is a clustered index.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply