Sitecore check database fragmentation percentage

1/5/2020 sitecore-8 Sitecore-9 Sitecore-indexs SQL-indexes

The script that helps to identify the avg fragmentation percentage for all the Sitecore database indexes.

SELECT dbschemas.[name] as 'Schema', 
  dbtables.[name] as 'Table', 
  dbindexes.[name] as 'Index',
  indexstats.alloc_unit_type_desc,
  indexstats.avg_fragmentation_in_percent,
  indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Thanks to the @Brain for providing the script here - https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/

I found the above article after a lot of searches, so this article might help me find it out easy next time.

DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor 
    FETCH NEXT FROM TableCursor INTO @TableName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN
        DBCC DBREINDEX(@TableName,' ',80) 
        FETCH NEXT FROM TableCursor INTO @TableName 
    END
CLOSE TableCursor 
DEALLOCATE TableCursor

The above script helps in rebuilding all the indexes. Thanks to Mikael for the article - https://mikael.com/2019/02/defragment-the-sql-server-heap-on-sitecore-databases/