*Please note:
Query 1 - Check Fragmentation
declare @dbid int
select @dbid = db_id()
SELECT OBJECT_NAME(a.object_id) AS table_name,isnull(b.name,'No Index') AS index_name, index_type_desc AS index_type,
index_depth,index_level,page_count,avg_fragmentation_in_percent AS fragmentation
from
sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL , 'DETAILED') a
INNER JOIN sys.indexes b
ON a.object_id = b.object_id AND a.index_id = b.index_id
and avg_fragmentation_in_percent > 30
and page_count > 10
and index_type_desc <> 'HEAP'
Query 2 - Rebuild Indexes
*Prior to executing this script, we strongly recommend backing up your database. For optimal performance, it is advisable to run these queries during off-peak hours.
Print 'Rebuilding indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?'",
@command2 = "ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
GO
Print 'Rebuilding indexes complete.'
Print 'Showing Results...'
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
where dps.index_type_desc <> 'HEAP'
ORDER BY OBJECT_NAME(DPS.OBJECT_ID),DPS.avg_fragmentation_in_percent DESC
GO
Print 'Finished.'
Query 3 - Check Statistics
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('dbo.TABLENAME')
Query 4 - Update /statistics
Query 4 - Update Statistics
update statistics dbo.TABLENAME with fullscan