MS SQL Server – index fragmentation – amended

admin — Thu, 28 Apr 2011 23:39:00 +0000

From http://wp.daveheavyindustries.com/2011/02/07/ms-sql-server-index-fragmentation/

Having had to use this on a very large database, I've made some small changes to the index fragmentation statistics script. this time to include table names (object_name) as well as database_name.

SELECT

ps.database_id,

ps.OBJECT_ID,

OBJECT_NAME(ps.OBJECT_ID,db_id()) as [object_name],

DB_NAME(ps.database_id) as [database_name],

ps.index_id,

b.name,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b on ps.OBJECT_ID = b.object_id and b.index_id = ps.index_id

WHERE ps.database_id = DB_ID()

ORDER BY ps.avg_fragmentation_in_percent

MS SQL Server – index fragmentation

admin — Sun, 06 Feb 2011 23:03:05 +0000

Sometimes it doesn't make sense to rebuild all of your indexes as part of a maintenance plan. You may have mirroring running with large db tables, and your indexes may be several GB. As part of your maintenance schedule, I recommend manually finding fragmented indexes and rebuilding them yourself (or scripting this)

To do this you, you need to locate the indexes -- anything over 50% can be considered a problem.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

And then defrag those indexes

DBCC IndexDefrag ('DBNAME','TABLENAME','INDEXNAME' )

http://msdn.microsoft.com/en-us/library/aa258286(SQL.80).aspx

or a full rebuild with

DBCC DBREINDEX ('dbname.ns.tablename','INDEXNAME' )

http://msdn.microsoft.com/en-us/library/aa258828(SQL.80).aspx

Dave Heavy Industries » fragmentation

MS SQL Server – index fragmentation – amended

MS SQL Server – index fragmentation