Index fragmentation in SQL Server: external and internal fragmentation

In this video, we will talk about how index fragmentation(external and internal) happens, how to check index fragmentation and performance impact of index fragmentation in SQL Server.
Furthermore, we will briefly talk about read ahead and clustered indexing in SQL Server.
Chapters:
0:00 - Introduction
0:30 - Clustered index (briefly)
1:36 - Read ahead, scatter-gather read
3:50 - How index fragmentation happens
6:10 - Index fragmentation types
6:55 - T-SQL to check fragmentation
7:50 - Performance impact of fragmentation
9:16 - Summary
T-SQL:
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
OBJECT_NAME(ips.object_id) AS object_name,
i.name AS index_name,
i.type_desc AS index_type,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent,
ips.page_count,
ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND
ips.index_id = i.index_id
ORDER BY page_count DESC;
📚 ►SQL Server internals: • SQL Internals
📷 ►Follow me on Instagram - arslanov_bo...
🧑‍💼►Follow me on LinkedIn - / bobirmirzo-arslanov-54...
Watch all my playlists here:
🚀►Full course on Encrypting connections to SQL Server- • Full course on Encrypt...
🚀 ►SQL Server replication session: • SQL Server replication
🚀 ►SQL Server Always On Availability Group: • SQL Server Always On A...
🚀 ► Full course on Troubleshooting sessions: • Troubleshooting
🚀 ► Course in On-memory OLTP: • Course on In-Memory OLTP

[@amarcse06]

thanks mate for the beautiful session❤ ...I looked into the next video to learn about maintaining and resolving index fragmentation, but the relevant information was not available.

[@arslanov1886]

Hello mate. Thank you for your comment.
I am sorry. Did not have time for that video.
We have very detailed documentation on this: learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16
Please kindly go through the above. It is very detailed and helpful and answers your doubts :)

[@user-zz5ql5rq6f]

very good content. Thanks

[@thetedsingh]

Thank you, this was very informative

[@arslanov1886]

Happy if you found it helpful!

[@nodiraarslanova2941]

Great content!

[@brillianttourslk5758]

Thanks a lot great explanation I'm really confused about the difference between Physical order and Logical order?

[@arslanov1886]

Logical order is expected order (order in RAM) while physical order is an actual order on the disk :)

[@AishwariyaGupta]

very confusing for a beginner