Post reply

Script from PASS 2005? Working with indexes

  • January 13, 2006 at 10:49 am

    #167745

    I was looking at a script I got from a session at PASS 2005 and Don't see the code that the comments are referring to.  Basically the full scripts get all the tables that need to be reindexed.  Below section of code in question.  I see where it checks for index of 30% and statistic but I don't see where it checks for heap or text/image.  The #Contig table just holds data from SHOWCONTIG.  Am I missing it or do the comments just not line up with the code?  If comments are not matching code then should I check to see if the row text/image and if so how?
     
    Thanks much
    Carl
     
     
     --  Get a list of all the tables where the Logical Fragmentation of at least

     --    one index is over 30% and is not a statistic, a heap or Text / Image.

     DECLARE curFrag CURSOR STATIC LOCAL

     FOR

      SELECT c.name + '.' + b.name

      FROM #Contig a

      JOIN dbo.sysobjects b on a.ObjectId = b.id

      JOIN dbo.sysusers c on b.uid = c.uid

      WHERE LogicalFragmentation >= 30 AND IndexID BETWEEN 1 AND 254

      AND INDEXPROPERTY (ObjectId, IndexName, 'IsStatistics') = 0

      GROUP BY c.name + '.' + b.name

  • January 14, 2006 at 1:58 am

    #614782

    Heaps have indexid = 0, text/image data have indexid = 255. So it is included in the where clause. Personally, I would change the where clause to indexid BETWEEN 1 AND 250 since 251-254 are reserved (but not used).

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • January 17, 2006 at 6:45 am

    #615126

    Thank you very much for the information.  My script is nearly complete  Not sure how many people would be interested in it.  Basically gathers contig data from all DBs and tables then reindexes the ones that need it.  Maybe a script like that already exists here but I didn't see it if it does.

    Thanks again

    Carl

  • January 17, 2006 at 9:10 am

    #615194

    I am sure there exists similar ones, but I have no idea how good they are or what features they have. If you want to I am sure the admins would be happy if you posted your script to the script area here at SSC.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

Viewing 4 posts - 1 through 3 (of 3 total)

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