SQL Server Performance

"T_" system created index

Discussion in 'Performance Tuning for DBAs' started by wryan@webcrue.com, Mar 23, 2006.

  1. wryan@webcrue.com New Member

    Hello,

    I'm new to the forum and SQL Server(2000), I have taken over a database and I have begun to schedule jobs to defrag our larger tables nightly. I have noticed some bizarre indexes that I cannot seem to defrag.

    I run this to find out the shape of the table;
    dbcc showcontig (SEPROPTEXT) with tableresults, all_indexes

    It returns;
    ObjectName ObjectID IndexName IndexID
    SEPROPTEXT1458104235PK_SEPROPTEXT1
    SEPROPTEXT1458104235tSEPROPTEXT255

    The index "tSEPROPTEXT" is my dilema, I can't seem to defrag it, and it's in a horrible state;
    Avg Pg Density = 91.912796020507813
    Scan Density = 99.863317956603453
    BestCount = 52605
    ActualCount = 52677
    LogicalFragmentation = 99.999763488769531
    ExtentFragmentation = 27.294645309448242

    Any help to defrag this index and more importantly how and why it was created would be greatly appreciated.

    Thanks!
    Will
  2. FrankKalis Moderator

  3. satya Moderator

    Does that table have any image or text data types?<br />Just having a confusion with the table name. (I was about to refer the link that talks about index defragmentation practice, I can see Frank redirected from his site [<img src='/community/emoticons/emotion-1.gif' alt=':)' />])<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  4. FrankKalis Moderator

    Oops, sorry, I forgot to clean that. This is no redirection, but simply a bug in the current version of my CMS. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  5. wryan@webcrue.com New Member

    Hi Frank,

    Thanks for the link, but it's not really what I am looking for, when I try to defrag the "tSEPROPTEXT" index it gives me the following error message;

    my command; dbcc indexdefrag ( 0, SEPROPTEXT, tSEPROPTEXT )

    sql response; Server: Msg 2560, Level 16, State 1, Line 2
    Parameter 3 is incorrect for this DBCC statement.

    I have even tried looking to the system table to see what columns are part of the index and can't find an index matching the ID

    Any more ideas of what could be at work?

    Hi Satya,

    Sorry I should have included that in the initial posting;
    my command; sp_columns SEPROPTEXT

    sql response;
    ColumnNameTypeName
    TEXT_ID int
    TEXT_VALUE ntext


    Thanks,
    Will
  6. FrankKalis Moderator

  7. wryan@webcrue.com New Member

    I tried it these ways;

    use master
    SELECT INDEXPROPERTY(OBJECT_ID(SEPROPTEXT), tSEPROPTEXT, 'IsHypothetical')
    use se_content
    SELECT INDEXPROPERTY(OBJECT_ID(SEPROPTEXT), tSEPROPTEXT, 'IsHypothetical')


    Both DB's returned the same error;

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'tSEPROPTEXT'.
    Server: Msg 207, Level 16, State 1, Line 1
    Invalid column name 'SEPROPTEXT'.
  8. satya Moderator

  9. FrankKalis Moderator

Share This Page