Rebuilding / Reorganisation of Indexes

Rebuilding / Reorganisation of indexes is a database maintenance job which should be done by customer.

It can be automated on SQL Server level: https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/rebuild-index-task-maintenance-plan?view=sql-server-ver15

If customer uses SQL Server 2016+, indexes should be reorganized and it can be done "online" (without disconnecting all the clients and etc) as a background process. 

If they using SQL Server < 2016, indexes should be rebuilt and TM Server service should be stopped, the database should be moved to "single user mode", so only one datab administrator will have an access to it.

If it's a lot of indexes that should be de-fragmented, it may take about 1-3 hours.

Just remember that the process can take a while and it's always better to choose some special time for maintenance when the load is the lowest.

Customers can do maintenance themselves: All the information about it is here: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

----------------------------

You can use this script to get a list and status of all fragmented indexes in database:

SELECT s.[object_id]
, OBJECT_NAME(s.[object_id]) as TableName
, s.index_id
, ind.[name] as IndexName
, s.avg_fragmentation_in_percent as FragmPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes AS ind on s.object_id = ind.object_id AND s.index_id = ind.index_id
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
order by FragmPercent desc, TableName

You will need to detect all tables that appear in the results of the query. So indexes for these tables should be defragmented. 

You can manually go into to each table that appears in "defragmented" list and doing the following operation: