Search

Monday, May 14, 2012

Findout the Fragmented Table and Index Name

Step-1.
CREATE FUNCTION dbo.index_name (@object_id int, @index_id int)
RETURNS sysname
AS
BEGIN
  RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id)
END;

Step-2.

SELECT 
 OBJECT_NAME(object_id) AS tblName
,dbo.index_name(object_id, index_id) AS ixName
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 20
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

Step-3:
To fix execute:

dbcc indexdefrag (ADVANTUREWORKS,'sales.storecontact',AK_StoreContact_rowguid)

No comments:

Post a Comment