Rebuild index

Question

Rebuild index

tony28

SSCertifiable

Points: 7112
More actions
November 6, 2012 at 4:33 pm

#265170

Hello,
I added job for rebuild indexes one times per week (saturday), it rebuild indexes, where fragmentation is higher than 15 ( i am not sure,if it is ok, because I read, that rebuild is for higher than 30%, but we have lot of table, where are few rows and it update them.)
so and I do with code below
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT distinct OBJECT_NAME(OBJECT_ID)
FROM sys.dm_db_index_physical_stats (DB_ID(N'MCS_MESDB'), NULL, NULL, NULL , 'SAMPLED')
WHERE page_count>='20' AND avg_fragmentation_in_percent>'15'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD '
exec (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
AND I HAVE QUESTION if I change the mode to DETAILED, the avg_fragmentation_in_percent is different. Anybody knows why?
I read that the scan all database, but there have to be same data for fragmentation no ? i am little confuse.
But if I check the fragmentation of index inside the properties windows of index, there is like in SAMPLED mode..

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You're probably looking at the fragmentation of the non-leaf levels.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7112 More actions · Answer 2

thank you for fast response.

so what is better ? or according to which do i have follow ?

Because in properties of indexes is thus SAMPLED.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Unless you have a really good reason otherwise, just look at the fragmentation of the leaf level of the index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7112 More actions · Answer 4

So do i have change it? because

1 ur response that I read non-leaf (SAMPLED)

2 responsne if i dont have good reason i can read just leaf ..(DETAILED?)

sorry, maybe I ask the wrong 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Doesn't matter whether you use detailed or sampled, but unless you have a really good reason, just look at the fragmentation of the leaf level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7112 More actions · Answer 6

So If it doesn't matter, i don't understand why I get different value ... and how can i see leaf level ( just for learning ) , because i thought that i did it

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You're getting different values because sampled only shows the leaf level, detailed shows all levels and the filters you have do not filter out the non-leaf levels, they only filter on page count and fragmentation

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7112 More actions · Answer 8

yes i checked this and understood.

so last question.

is good idea change to detail level and rebuild the next level of index, for example where page count is higher than 50? will it be helpful ?

or it can stay like this with sample just for 0level ?

because lot of indexes with level 1 have fragmentation higher than 30, mainly tables, where is lot of delete,insert, update - table for tracking you know

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

GilaMonster (11/6/2012)
Unless you have a really good reason otherwise, just look at the fragmentation of the leaf level of the index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tony28 SSCertifiable Points: 7112 More actions · Answer 10

so i have good reason 🙂

thank you for your cooperation and your patient with me