Script defrag indexes

bibi92
bibi92 used Ask the Experts™
on
Hello,

With this script, I see that some indexes are fragmented, I search the better solution or script for defrag indexes ?

"SELECT TOP 1
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
WHERE s.[object_id] = -999  -- Dummy value just to get table structure.
;

-- Loop around all the databases on the server.
EXEC sp_MSForEachDB    'USE [?];
-- Table already exists.
INSERT INTO #TempFragmentation
SELECT TOP 10
        DatbaseName = DB_NAME()
        ,TableName = OBJECT_NAME(s.[object_id])
        ,IndexName = i.name
        ,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2)
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
      AND i.name IS NOT NULL    -- Ignore HEAP indexes.
    AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC
;
'

-- Select records.
SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
-- Tidy up.
DROP TABLE #TempFragmentation"

Thanks

Regards

bibi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
check this one
 
Defragmenting indexes in SQL Server 2005  
http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60121.aspx 
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try using the script provided in 'D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes' section of the link below:

http://msdn.microsoft.com/en-us/library/ms188917.aspx
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
The best solution depends on the database size context.  

If you have small db's(<5GB), simply run a weekly dbcc redindex.  For bigger databases, take a more selective approach by rebuild/reorg only the indexes that are heavily used in scans, lookups or seeks volume wise.


In other words, going straight to the selective approach simply does not worth the effort in the case of small db's.

Author

Commented:
Thanks bibi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial