Post reply

Phantom Index from ShowContig?

  • September 15, 2005 at 8:46 am

    #165954

    When I issue a DBCC SHOWCONTIG command for a table, it often shows information for indexes that are not accessable thru the Enterprise Mangager's 'Manage Indexes' tool. They seem to be named T.

    What are they? Where do the come from. Why can't I DBREINDEX them?

    OBJECTNAME OBJECTID INDEXNAME INDEXID ...

    ------------------------ ----------- ------------------------ ----------- ...

    BAT_AUDIT 1838785758 PK_BAT_AUDIT 1 ...

    BAT_AUDIT 1838785758 TBAT_AUDIT 255 ...

  • September 15, 2005 at 8:56 am

    #590174

    Does that table have a text column?

  • September 15, 2005 at 9:54 am

    #590204

    Yes, it does. Hmmm. Pointer to data stored outside of table?

    Statistics would indicate that there is a lot of fragmentation from that index. Can I/Should I reorg? Can you point me to reference.

    Is there anyway to have SHOWCONTIG ignore these so my nightly reorg script isn't bothered by them? Having trouble finding details in BOL.

  • September 15, 2005 at 10:06 am

    #590210

    I imagine that this is a pointer, as for refferences I'll let the other dbas take it as I have very little experience there.

  • September 16, 2005 at 11:32 am

    #590692

    Nothing is 'phantom'. Based on  the output you posted:

    OBJECTNAME OBJECTID INDEXNAME INDEXID ...

    ------------------------ ----------- ------------------------ ----------- ...

    BAT_AUDIT 1838785758 PK_BAT_AUDIT 1 ...

    BAT_AUDIT 1838785758 TBAT_AUDIT 255 ...

    PK_BAT_AUDIT index has INDEXID of 1 and is a clustered index

    TBAT_AUDIT has an INDEXID of 255 indicates the table has text/image data

    See the sysindexes table reference in the following download link:

    http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 28, 2005 at 1:59 pm

    #593828

    I'm having similar problem.  It may not be a phantom but why doesn't it get optimized with the other indexes?

  • September 28, 2005 at 2:18 pm

    #593840

    actually looking a bit farther the index doesn't show up in the sysindexes table.

     

    Any ideas or suggestions?

  • September 28, 2005 at 3:18 pm

    #593856

    could you post your query and results ? Also, add the following just above your query:

     

    select db_name()

    go

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 29, 2005 at 8:40 am

    #594056

    I am having the same issues.  I posted my situation here:

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=223992

    I am still working on solving this issue and will post if I uncover anything.  It is highly frustrating trying to track down an index that doesn't appear in any of the index tables.

  • September 29, 2005 at 10:40 am

    #594102

    select db_name()

    go

    DBCC SHOWCONTIG ('tbl_Proposal')

      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

    go

    SELECT * FROM sysindexes where name = 'ttbl_PROPOSAL'

    go

    DBCC INDEXDEFRAG (broadway,tbl_proposal,ttbl_PROPOSAL)

    go

    Results

    broadway

    go

    ObjectName   ObjectId    IndexName  IndexId Level Pages Rows

    tbl_PROPOSAL 813245952 pk_PROPOSAL 1 0 902 NULL 

    tbl_PROPOSAL 813245952 idxProposalADV_ID 2 0 61 NULL 

    tbl_PROPOSAL 813245952 idxProposalCOMP_ID 3 0 77 NULL 

    tbl_PROPOSAL 813245952 idxProposalCT_ID 4 0 77 NULL

    tbl_PROPOSAL 813245952 idxProposalRATE_ID 6 0 67 NULL 

    tbl_PROPOSAL 813245952 idxtblProposalAE_ID 9 0 77 NULL 

    tbl_PROPOSAL 813245952 ttbl_PROPOSAL 255 0 527 31309 

    -- notice the index ttbl_PROPOSAL

    go

    813245952 2 0xE55D00000100 255 0xE55D00000100 0 0 1 0 809 528 0 0 0 0 0 0 0 0 0 0xE65D00000100 -1 0 0 NULL ttbl_PROPOSAL NULL 8000 0

    go

    (i was wrong... it DOES show up in sysindexes)

    yet when i try DBCC INDEXDEFRAG (broadway,tbl_proposal,ttbl_PROPOSAL) i get the following error:

    Server: Msg 2560, Level 16, State 1, Line 1

    Parameter 3 is incorrect for this DBCC statement.

    all the other indexes listed from the dbcc showcontig work just fine

    DBCC INDEXDEFRAG (broadway,tbl_proposal,idxProposalCT_ID)

    results:

    Pages Scanned   Pages moved    Pages Removed

    77 72 0

  • September 29, 2005 at 10:42 am

    #594103

    oh just for the record the logical fragmentation for ttbl_PROPOSAL is sitting at: 99.810249328613281

     

  • October 11, 2005 at 1:41 pm

    #596647

    anyone with any ideas on this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply