DBCC UPDATEUSAGE and sp_updatestats

Here is the script to find out the average index fragmentation levels. Based on this value you can either Rebuild or Reorganize the indexes. Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

SELECT DB_NAME(PS.database_id) AS dbName ,

  S.name AS SchemaName ,  O.name AS TableName ,  b.name ,  ps.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDAND ps.index_id = b.index_id
INNER JOIN sys.objects O ON PS.object_id = O.object_idINNER JOIN sys.schemas S ON S.schema_id = O.schema_idWHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20AND PS.index_type_desc IN ( 'CLUSTERED INDEX', 'NONCLUSTERED INDEX' ) -- Only get clustered and nonclustered indexesAND b.is_hypothetical = 0 -- Only real indexesAND O.type_desc = 'USER_TABLE' -- Restrict to user tablesAND PS.page_count > 8 --- ignore tables less tha 64KORDER BY ps.avg_fragmentation_in_percent DESC

Here is the script which can be used for Index rebuild, you can schedule this as a daily/weekly job 

SET NOCOUNT ON

DECLARE
@dbName NVARCHAR(128)DECLARE @SchemaName NVARCHAR(128)DECLARE @TableName NVARCHAR(128)DECLARE @IndexName NVARCHAR(128)DECLARE @avg_fragmentation_in_percent FLOATDECLARE
@nSQL NVARCHAR(4000)DECLARE @index_list TABLE
(dbName NVARCHAR(128) ,SchemaName NVARCHAR(128) ,TableName NVARCHAR(128) ,IndexName NVARCHAR(128) ,avg_fragmentation_in_percent FLOAT)INSERT INTO @index_list
SELECT DB_NAME(PS.database_id) AS dbName ,S.name AS SchemaName ,O.name AS TableName ,b.name ,ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_IDAND ps.index_id = b.index_id
INNER JOIN sys.objects O ON PS.object_id = O.object_idINNER JOIN sys.schemas S ON S.schema_id = O.schema_idWHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20AND PS.index_type_desc IN ( 'CLUSTERED INDEX','NONCLUSTERED INDEX' ) -- Only get clustered and nonclustered indexesAND b.is_hypothetical = 0 -- Only real indexesAND O.type_desc = 'USER_TABLE' -- Restrict to user tablesAND PS.page_count > 8
--- ignore tables less tha 64K--ORDER BY ps.avg_fragmentation_in_percent DESC
SELECT TOP 1@dbName
= dbName ,@SchemaName = SchemaName ,@TableName = TableName ,@IndexName = IndexName ,@avg_fragmentation_in_percent = avg_fragmentation_in_percentFROM @index_listWHILE ( @@rowcount <> 0 )
BEGINIF @avg_fragmentation_in_percent <= 40 -- REORGANIZESET @nSQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.'+ @SchemaName + '.' + @TableName + ' REORGANIZE;'ELSE -- REBUILDSET @nSQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.'+ @SchemaName + '.' + @TableName
+ ' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);'BEGIN TRY
EXECUTE (@nSQL);END TRY
BEGIN CATCH
SET @nSQL = REPLACE(@nSQL, 'ONLINE = ON,', '')EXECUTE (@nSQL);END CATCH--PRINT @nSQLDELETE FROM @index_list
WHERE dbName = @dbName
AND SchemaName = @SchemaName
AND TableName = @TableName
AND IndexName = @IndexName
AND avg_fragmentation_in_percent = @avg_fragmentation_in_percent
SELECT TOP 1@dbName
= dbName ,@SchemaName = SchemaName ,@TableName = TableName ,@IndexName = IndexName ,@avg_fragmentation_in_percent = avg_fragmentation_in_percent
FROM @index_list
END