SQL Server – Index Fragmentation Issue

A couple of days ago I came across an issue with our SharePoint administrator regarding the slow index performance on their databases. I had to find out what the current Index Fragmentation Factor is (on all the tables within their various databases) and then find out how this fragmentation issue could be resolved. After doing some search online and reading some articles, I was able to find the solution. So here is what I found:

Checking the Index Fragmentation Factor:

Checking the index fragmentation percentage on the tables of a database
USE DATABASENAME_HERE
 GO
 -- Find the average fragmentation percentage of all indexes

SELECT DB_NAME(database_id) as "Database", OBJECT_NAME(object_id) as "Table Name",avg_fragmentation_in_percent as "Avg Frag. Percent"
 FROM sys.dm_db_index_physical_stats (DB_ID(N'DATABASENAME_HERE), NULL, NULL, NULL, NULL)
 ORDER BY avg_fragmentation_in_percent DESC;
 GO
Returning information about a specific table
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');

IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

De-fragmenting one table based on fragmentation factor

-- between 5 and 30
 USE WSS_Content_1
 GO
 ALTER INDEX ALL ON StorageMetrics
 REORGANIZE
 GO

-- between 30 and 90, you need to rebuild in online mode
 USE WSS_Content_1
 GO
 ALTER INDEX ALL ON BuildDependencies
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
 STATISTICS_NORECOMPUTE = ON)
 GO

NOTE: There is a Microsoft procedure that is supposed to automatically correct the fragmentation issue (proc_DefragmentIndices), but it’s broken and doesn’t do anything. So in my later posts, I’ll try to fix that procedure.

Leave a reply