Script from PASS 2005? Working with indexes

Question

Script from PASS 2005? Working with indexes

Carl Uman

SSCommitted

Points: 1993
More actions
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

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

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/

Carl Uman SSCommitted Points: 1993 More actions · Answer 2

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

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/