Index Defragmentation

Question

Index Defragmentation

PradeepVallabh

SSC Eights!

Points: 987
More actions
August 18, 2010 at 12:00 pm

#228084

My production servers are not high OLTP system but i have to defragment some of the indexe's daily(fragmentation level sometimes goes as high as 85%).....I am trying to resolve this issue by analyzing the indexes....What are the various factors i need to consider
Thanks in advance
Regards,
Pradeep

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

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

could be the fill factor is too low or the clustered index is on a column with lots of inserts\updates and the values are random (nonconsecutive)

or the table could be a heap........

---------------------------------------------------------------------

pankushmehta Ten Centuries Points: 1217 More actions · Answer 2

You should check page splits through perfmon. if there are a lot of page splits occuring, then you need to look and adjust the fill factor. Also look at the cluster index keys, and if there a lot of ramdon inserts happening, you might want to change the keys.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

PradeepVallabh (8/18/2010)
What are the various factors i need to consider

USE following query for every concerned table dbcc showcontig( 'tablename')

for more details google "dbcc showcontig( 'tablename')"

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

Don't use ShowContig. It's deprecated, will be removed in a future version of SQL server and should not be used for new development.

The replacement is 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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

PradeepVallabh (8/18/2010)
My production servers are not high OLTP system but i have to defragment some of the indexe's daily(fragmentation level sometimes goes as high as 85%).....I am trying to resolve this issue by analyzing the indexes....What are the various factors i need to consider
Thanks in advance
Regards,
Pradeep

Make sure that you are not only looking blindly at the % fragmentation. If the index is small a high fragmentation level is not a problem. You only need to be concerned with large indexes (above 1000 pages or so) that are heavily fragmented.

How large (number of pages) are the problematic indexes ?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I HIGHLY recommend the database maintenance stuff (FREE!!) from ola.hallengren.com. Learn to use that stuff and stay away from maintenance plans!!

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

removed

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)