Reindexing

Question

Reindexing

premkuttan

SSCommitted

Points: 1934
More actions
May 7, 2011 at 12:20 pm

#389470

I do reindex for 1 clustered index and 16 nonclusterd in my table using the cmd
ALTER INDEX ALL ON [TBL name] REBUILD WITH (FILLFACTOR = 90)
and i got the result as successful
but when i checked the index using the below dmv
select database_id,index_type_desc,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(12,null,null,null,null)
where object_id=2117373291
it gives result as
dbid index type desc avg fragmentation in percent
12CLUSTERED INDEX 0
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX66.6666666666667
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX66.6666666666667
12NONCLUSTERED INDEX66.6666666666667
12NONCLUSTERED INDEX66.6666666666667
12NONCLUSTERED INDEX66.6666666666667
12NONCLUSTERED INDEX50
12NONCLUSTERED INDEX75
12NONCLUSTERED INDEX50
why the value exceeds more than 50 even after doin reindexing ...?? or am i doing something wrong

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

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

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

Let me guess, the indexes are 2-3 pages in size?

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

premkuttan SSCommitted Points: 1934 More actions · Answer 2

How to identify that the indexes is for 2-3 pages ? if it is in that way can't i do reindexing for that ......???

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

Page count in sys.dm_db_index_physical_stats.

It's not that you can't, it's that it's totally pointless. The point where reindexing has a performance impact is usually somewhere around 1000 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

premkuttan SSCommitted Points: 1934 More actions · Answer 4

yeah i got it

i had pasted the page_count value in this

page_count

204

2

4

2

3

2

3

4

2

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

As Gail mentioned, Fragmentation gets affected only if the Index pages are usually > 1000. With such small indexes you mentioned, reindex doesn't have any impact.

Thank You,

Best Regards,

SQLBuddy.

smackie1973 SSC Enthusiast Points: 136 More actions · Answer 6

smackie1973

SSC Enthusiast

Points: 136

May 9, 2011 at 2:43 am

#1321585

I usually just reindex when page count > 9999