My SQL Server appears to be running slowly. What is SQL Server index fragmentation and how do I fix it?

Administration Performance
1

Issue

My SQL Server appears to be running slowly. What is SQL Server index fragmentation, and how do I fix it?

Background

See the Reorganize and Rebuild Indexes MSDN article for information on index fragmentation (choose your version of SQL Server from the drop-down menu).

Solutions

There are two solutions to this issue.

Solution 1

Run either the Rebuild or Reorganize commands then verify if your WorkflowGen indexes have been successfully following the instructions in the above article.

Solution 2

Set up a scheduled defragmentation by scheduling a job to run the defragmentation script.

For instructions on how to schedule a job, see the How to: Schedule a Job (SQL Server Management Studio) MSDN article (choose your version of SQL Server from the drop-down list).

Run the B. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database sample script in the DBCC INDEXDEFRAG (Transact-SQL) MSDN article. (The script can be run as-is.)

Script example

DECLARE @TableName nvarchar(255),
        @SqlQuery nvarchar(max) 

SELECT @TableName = min(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

WHILE @TableName is not null
BEGIN
    SET @SqlQuery='ALTER INDEX ALL ON [' + @TableName + '] REORGANIZE;'
    EXEC SP_EXECUTESQL @SqlQuery
    SELECT @TableName = min(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' and TABLE_NAME > @TableName          
END