September 15, 2005 at 8:46 am
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
Does that table have a text column?
September 15, 2005 at 9:54 am
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
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
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
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
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
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
I am having the same issues. I posted my situation here:
http://www.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
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
oh just for the record the logical fragmentation for ttbl_PROPOSAL is sitting at: 99.810249328613281
October 11, 2005 at 1:41 pm
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