Managing Indexes in a Database

Question

Managing Indexes in a Database

ramoha

SSCommitted

Points: 1909
More actions
November 30, 2007 at 5:56 pm

#180873

I have a database which has a over 80 to 100 indexes. How do we maintain these indexes, I think re-indexing all of them is not a good idea. Is there any other way of monitoring these indexes in SQL Server 2005 for fragmentation etc. Your insight is appreciated!!
Regards,
Razi, M.
http://questivity.com/it-training.html

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

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

You can monitor fragmentation with dbcc showcontig and then based on the results, decide which indexes you want to rebuild. Lots of scripts here on the site.

Other than that, SQL Server maintains the indexes fairly well.

ramoha SSCommitted Points: 1909 More actions · Answer 2

Thanks Steve. Guess what I told this to an interview but he still wanted to see what else we can do? I was stuck:)

I think there are some system DMVs in 2005 that show statistical information regarding indexes.

Regards,

Razi, M.
http://questivity.com/it-training.html

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

Yes, as far as checking to see if indexes are being used, you can use the DMVs so see what's being used.

This blog post can help: http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx