Rebuilding / Reorganisation of Indexes

Rebuilding / Reorganisation of indexes it's a db maintenance job which should be done by customer, because we don't really have control on it.

No matter who will do the indexes de-fragmentation, 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 rebuild and TM Server should be stopped, the database should be moved to "single user mode", so only one db 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 (depends on how powerful is their machine with SQL Server) 

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: