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 - https://www.instagram.com/arslanov_bo...
🧑💼►Follow me on LinkedIn - / bobirmirzo-arslanov-548960173
Watch all my playlists here:
🚀►Full course on Encrypting connections to SQL Server- • Full course on Encrypting connections...
🚀 ►SQL Server replication session: • SQL Server replication
🚀 ►SQL Server Always On Availability Group: • SQL Server Always On Availability Gro...
🚀 ► Full course on Troubleshooting sessions: • Troubleshooting
🚀 ► Course in On-memory OLTP: • Course on In-Memory OLTP