Index fragmentation in SQL Server: external and internal fragmentation

Подписаться 781

Просмотров 2,2 тыс.

50% 1

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

Опубликовано:

21 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 12

@user-zz5ql5rq6f Год назад

very good content. Thanks

@thetedsingh Год назад

Thank you, this was very informative

@arslanov1886 Год назад

Happy if you found it helpful!

@nodiraarslanova2941 Год назад

Great content!

@amarcse06 6 месяцев назад

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 6 месяцев назад

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 :)

@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 :)

@Toan.nguyen17 20 дней назад

Could i ask your option about should or not shinking database when have alot of unsued space disk

@arslanov1886 19 дней назад

You should shrink it. After shrinking, do not forget to rebuild the index. ================= Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking. ref: learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16#FollowUp ================= Furthermore, be careful with performance issues with blocking: ================ Shrink operations in progress can block other queries on the database, and can be blocked by queries already in progress ref: learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database?view=sql-server-ver16#remarks ================