find index fragmentation

Question

find index fragmentation

pshaship

SSCertifiable

Points: 6125
More actions
March 8, 2010 at 2:34 am

#137585

Hi,
I'm using below query to find index fragmentation. Here why we are selecting index_id > 0? & page_count>1000
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 and page_count>1000
thanks

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

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

pshaship SSCertifiable Points: 6125 More actions · Answer 1

I figured out why index_id>0. Because, A heap has a row in sys.partitions with index_id = 0 and we cannot defrag a heap.

But why page_count>1000? what benefit,we will get by not performing index defrag for indexes having page count less than 1000?

thank you

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

The 1000 pages is a rough guideline for more-or-less where rebuilding an index has a noticeable effect on performance. If you know that you need to rebuild smaller indexes for whatever reason, go ahead.

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Although, below, I think 8 pages (or is it 3, I've forgotten), SQL Server can't defrag the index at all, let alone will defragging an index that size give you any appreciable help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

8 on SQL 2000, 24 on SQL 2005+. 1 or 3 extents. Below that rebuilding the index will just lead to more mixed extents. Above that SQL will use dedicated extents.

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

GilaMonster (3/8/2010)
8 on SQL 2000, 24 on SQL 2005+. 1 or 3 extents. Below that rebuilding the index will just lead to more mixed extents. Above that SQL will use dedicated extents.

Excellent. Thanks. Spaced that one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning