May 5, 2008 at 4:40 am
Hi,
Please help me to find out solution for :-
1) Need to know what indexs are fragemented for table into one database
2) How to solve or defragement index
Server :- Sql server 2005
Thanks!
May 5, 2008 at 4:57 am
The DMV sys.dm_db_index_physical_stats will give you the information which indexes are fragmented.
What action you should take depends on the fragmentation. MS recommends rebuilding an index if the fragmentation is higher thatn 30%. Between 10% and 30% reorganizing the index should be sufficient and below 10% fragmentation no action is required. Personally I also look at the size of the index. On Indexes with very few pages rebuild/reorganize will have almost no effect on performance.
If you look in BOL under sys.dm_db_index_physical_stats you will find a sample script which evaluates the degree of fragmentation and then takes the recommended action.
[font="Verdana"]Markus Bohse[/font]
May 5, 2008 at 11:57 am
I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size. It uses the DMV sys.dm_db_index_physical_stats to check the fragmentation.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Ola Hallengren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply