Fragmentation unchanged after rebuilding index

Question

Fragmentation unchanged after rebuilding index

Olja Dimitrijevic

Ten Centuries

Points: 1010
More actions
February 23, 2008 at 10:59 am

#124037

A number of indices in our database are highly fragmented. After running the rebuild command as in:
ALTER INDEX PK_tbl_ProjectElement ON MYDB.dbo.tbl_ProjectElement Rebuild
the fragmentation is still high! Eg was 50 before and stays 50 after rebuild.
The sql I use to determine which indices need rebuilding is provided below.
All help much appreciated!
Thanks,
Olja
SELECT
OBJECT_NAME(i.object_id) AS TableName
,
i.name AS TableIndexName
,
phystat.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat inner JOIN sys.indexes i
ON i.object_id = phystat.object_id
AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent > 25

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

How big is that table? How many pages?

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

maybe even a sp_updatestats and dbcc updateusage(0) with count_rows will correct the figures you get from the query.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 3

There are only 1318 rows with data 48K and index 16K!

Thanks,

Olja

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

That's a very, very small table. 48k = 6 pages, less than an extent. Fragmentation doesn't have much meaning on such small tables.

I wouldn't worry about fragmentation except on bigger tables (100+ pages)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

IIRC I saw a number from Microsoft once that was not to bother with defrags on less than 1000+ page tables.

Also, is there any free space in the database?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 6

There is plenty of space on the harddrive. Our performance issues in the end weren't related to fragmentation, but it was a good place to start troubleshooting!

It is reasonable enough that sql doesn't bothering with tables below a certain size.

Thanks a lot!

Olja

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I want to point out that I did not ask for the amount of free space on the hard disk, but rather for the free space inside the database file(s). Most people mistakenly let sql server manage their database size and growth, and thus wind up with tremendous OS disk file fragmentation (which slows down performance) and also wind up with a database that never has enough free space inside it for defrags to do anything useful in the first place.

In any regard, you imply that you have your performance problem fixed. Hope this is the case and have a great day!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service