I have a database that I believe needs defragmentation. Below is one table example and the script that I used to get these results. At the very bottom is a script I ran to try to defrag but when I ran the script it had no effect. The database is in FULL recovery mode. First, how can I defrag the database and 2nd what should I do to put a maintenance plan in place to resolve this issue?
Database Name Table Name Index Name Index Type Avg Page Frag Page Counts
database CodeList PK_CODELIST CLUSTERED INDEX 87.5 8
SELECT DB_NAME(DATABASE_ID) AS [DatabaseName] ,
OBJECT_NAME(OBJECT_ID) AS TableName ,
SI.NAME AS IndexName ,
INDEX_TYPE_DESC AS IndexType ,
AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation ,
PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') DPS
INNER JOIN sysindexes SI ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
WHERE avg_fragmentation_in_percent > 20
AND OBJECT_NAME(OBJECT_ID) NOT LIKE '%Scorm%'
ORDER BY tablename