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