Rebuilding Index

Question

Rebuilding Index

toparsi

SSCrazy

Points: 2292
More actions
September 30, 2008 at 12:48 pm

#154770

Hello Guys,
I am right now totally confused with indexing (rebuilding and reorganizing) I have a database and I am using this query to find out the fragmentation percentage
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO
After I rebuild all the indexes in that database using rebuild index task in maintanance, I see that fragmentation percentage does not change at all.
Can anyone explain to me, what I am doing wrong. Also, can anyone explain, structure, allocation difference between rebuilding and reorganizing. I always thought reindex would get the fragmentation to zero.
Any help would be appreciated,
Nikhil

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

This is a very commonly asked question, there are several factors that could be causing this, but you should try searching this site and/or Google first.

Here is a good start: http://www.sqlservercentral.com/Forums/Topic438525-145-1.aspx ... some good information here.

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

How big is the table in question? If I had to take a guess, I'd say it's fairly small, under 25 or so pages.

You can get the page count from sys.dm_db_index_physical_stats

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

toparsi SSCrazy Points: 2292 More actions · Answer 3

toparsi

SSCrazy

Points: 2292

September 30, 2008 at 1:17 pm

#879117

thanks for that link,

it helped me a lot..

toparsi SSCrazy Points: 2292 More actions · Answer 4

Thanks Gilamonster for the reply,

Yes,

The tables which had few tables created problem for me. Though, after realizing that, I did rebuilding on tables which had more than 100,000 rows, and still I could see 9 to 10 % fragmentation which was initially 25 %. Do you know why this is the case. Also, how many fragmentations should be considered, I read logical, physical and so on. I dont know which one is important and also what it means.

Any help would be appreciated,

Thanks,

Nikhil