TSQL - Index Fragmentation Percentage in SQL Server

We are very well aware of that SQL Server Management Studio is one of the great tools which are capable to give us a wealth of information about its data components. It’s already specified that SQL Server stores data on 8KB pages means whenever any new record insert into the data table then SQL Server is responsible to allocate one page to store that data unless the data inserted is more than 8KB in which it would span multiple pages.

SQL Server provides two types of fragmentation such as Internal Fragmentation and External Fragmentation.

For Internal Fragmentation, SQL Server Internal Fragmentation is caused by pages that have too much free space and we can understand that a table data is distributed in 100 pages but containing only 70% of space due to ongoing data movements. So, we have to face query performance issues because we have to scan 100 pages having 30% free space.

For External Fragmentation, SQL Server External Fragmentation is caused by pages that are out of order. Ongoing T-SQL queries is leaving some empty space on one or more pages and trying to fit space into other pages.

The following is a simple T-SQL query that will list every index on every table in your database, ordered by percentage of index fragmentation–

SELECT S.name as 'Schema', ----- Name of the data table T.NAME AS TableName, ----- Name of the index in database I.NAME AS IndexName, ----- Index Types I.TYPE_DESC AS IndexType, IPS.alloc_unit_type_desc AS AllocationUnitType, IPS.avg_fragmentation_in_percent AS AvgFragmentPercnt, IPS.page_count AS PageCount FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS IPS INNER JOIN SYS.TABLES T ON T.OBJECT_ID = IPS.OBJECT_ID INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = IPS.OBJECT_ID AND IPS.INDEX_ID = I.INDEX_ID WHERE IPS.DATABASE_ID = DB_ID() ORDER BY IPS.AVG_FRAGMENTATION_IN_PERCENT DESC

How to see Index Fragmentation –

In SQL Server Management Studio, expends the indexes and right click any index to choose index properties as given below- 

After click on properties, it will open Index properties window where you can see the index fragmentation as given below -