Index fragmentation, rebuild and reorganize through TSQL

We need to look into index fragmentation of our database indexes on regular intervals. Once find fragmented then we need to rebuild and reorganize at some point.

to do all three steps. we need to search on many places to find how to find fragmentation and rebuild or reorganize query. I have gathered all 3 steps here

Step 1: Find fragmentation percent of your running indexes.

========================= Code segment  starts ==================================

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > 0
ORDER BY ps.OBJECT_ID
========================= Code segment ends ===================================

screen shot shows how much an index has been fragmented.

Step2: If indexes are over 10 percent to 40 percent you need to reorganize these indexes. Keep in mind it will effect performance during re-Organization.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REORGANIZE
GO
========================= Code segment ends ===================================

Step3: If indexes are over 40 percent fragmented then you need to rebuild these indexes. Keep in mind it will effect performance during re-Organization. Query used to rebuild all indexes on mentioned table. You can mention indexes name for one.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REBUILD
GO
========================= Code segment ends ===================================