January 1, 2009 at 8:10 pm
So yesterday I ran this on the primary key, non-clustered constraint on a very large table. The physical table resides on the F drive, but recently I've also moved the other indexes to a new file group called INDEXES on another physical drive G.
ALTER TABLE CallLog DROP CONSTRAINT [PK_CallLog]
-- drop constraint in PRIMARY filegroup on F
-- Then recreate on G
ALTER TABLE [dbo].[CallLog] ADD CONSTRAINT [PK_CallLog] PRIMARY KEY NONCLUSTERED ([ID]) ON [INDEXES]
-- Then see how it looks:
-- Individual Indexes recently moved to G
DBCC SHOWCONTIG (calllog, [PK_CallLog]) with fast
DBCC SHOWCONTIG (calllog, [IX_CallLog_CreditTo]) with fast
DBCC SHOWCONTIG (calllog, [IX_CallLog_Disposition]) with fast
DBCC SHOWCONTIG (calllog, [IX_CallLog_FK]) with fast
DBCC SHOWCONTIG (calllog, [IX_CallLog_Start]) with fast
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 18, database ID: 88
LEAF level scan performed.
- Pages Scanned................................: 661225
- Extent Switches..............................: 537342
- Scan Density [Best Count:Actual Count].......: 15.38% [82654:537343]
- Logical Scan Fragmentation ..................: 39.27%
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 4, database ID: 88
LEAF level scan performed.
- Pages Scanned................................: 1121267
- Extent Switches..............................: 341511
- Scan Density [Best Count:Actual Count].......: 41.04% [140159:341512]
- Logical Scan Fragmentation ..................: 6.52%
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 5, database ID: 88
LEAF level scan performed.
- Pages Scanned................................: 824385
- Extent Switches..............................: 248799
- Scan Density [Best Count:Actual Count].......: 41.42% [103049:248800]
- Logical Scan Fragmentation ..................: 5.68%
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 2, database ID: 88
LEAF level scan performed.
- Pages Scanned................................: 715509
- Extent Switches..............................: 234974
- Scan Density [Best Count:Actual Count].......: 38.06% [89439:234975]
- Logical Scan Fragmentation ..................: 11.87%
DBCC SHOWCONTIG scanning 'CallLog' table...
Table: 'CallLog' (2137058649); index ID: 3, database ID: 88
LEAF level scan performed.
- Pages Scanned................................: 268187
- Extent Switches..............................: 38584
- Scan Density [Best Count:Actual Count].......: 86.88% [33524:38585]
- Logical Scan Fragmentation ..................: 1.53%
-- Entire Table
DBCC SHOWCONTIG (calllog) with fast
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%
Why are the numbers for the freshly built index so different from the overall table ? What else should I do, if anything ? (There's no clustered index)
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply