Monday, August 02, 2010

Sql Server Fragmentation

Found in an article on SqlServerCentral (VERY useful site), some scripts that show the fragmentation degree of your database objects:

-- check fragmentation on @db
Declare @db     SysName;
Set @db = 'MCD_SAWFC_PROD';

SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name',
CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type',
I.Name As 'Index Name',
avg_fragmentation_in_percent As 'Avg % Fragmentation',
record_count As 'RecordCount',
page_count As 'Pages Allocated',
avg_page_space_used_in_percent As 'Avg % Page Space Used'
FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) S
LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)
AND S.INDEX_ID > 0
ORDER BY avg_fragmentation_in_percent DESC

The following SQL can be used to rebuild all indexes for the specified table;

ALTER INDEX ALL ON <Table Name> REBUILD;


while the following SQL can be used to rebuild a specific index.



ALTER INDEX <Index Name> ON <Table Name> REBUILD;


Alternatively, indexes can be reorganised. The following SQL can be used to reorganise all indexes for the specified table;



ALTER INDEX ALL ON <Table Name> REORGANIZE; 


while the following SQL can be used to reorganise a specific index.



ALTER INDEX <Index Name> ON <Table Name> REORGANIZE; 

No comments: