Buradasın
SQL Sunucu Çalışma Planları: Key Lookup ve RID Lookup Kavramları
youtube.com/watch?v=C6XXKFz07D0Yapay zekadan makale özeti
- Kısa
- Ayrıntılı
- Bu eğitim videosunda, Band adlı bir eğitmen SQL sunucu çalışma planlarında sıkça karşılaşılan Key Lookup ve RID Lookup kavramlarını açıklıyor. Video, indexlerin nasıl çalıştığını ve heap tabloları hakkında temel bilgiler gerektirdiğini belirtiyor.
- Video, Employee tablosu üzerinde örneklerle ilerliyor. Önce tabloya index eklenmeden sorgu performansı inceleniyor, ardından non-clustered index eklendikten sonra Key Lookup ve RID Lookup işlemlerinin nasıl gerçekleştiği gösteriliyor. Eğitmen, hangi durumlarda hangi işlem daha performanslı olabileceğini açıklıyor ve bu kararın sorgu yapısı, index içeriği ve veri dağılımı gibi faktörlere bağlı olduğunu vurguluyor.
- 00:03Introduction to Indexes and Execution Plans
- The video discusses two common operations in server execution plans: Key Look Up and RID Look Up, which are important concepts for SQL performance tuning.
- The video uses an Employee table with a clustered index, meaning the physical storage of data rows is sorted according to the clustered index.
- The script creates an Employee table and inserts one million rows, which is the same script used in previous videos in the series.
- 01:20Query Execution Without Index
- When querying employees by name without an index, the execution plan shows a Table Scan operation that reads all one million rows in the table.
- This results in a high estimated subtree cost of 77 withs, indicating poor performance.
- 02:10Query Execution with Non-Clustered Index
- Creating a non-clustered index on the name column significantly improves performance, reducing the estimated subtree cost to 0.003.
- The execution plan now shows an Index Seek operation followed by a RID Lookup operation.
- The RID Lookup operation uses the row identifier from the non-clustered index to locate the specific employee record in the Employee table.
- 03:41Clustered Index and Key Look Up
- When the name column is in the select list, the execution plan shows an Index Seek operation without a RID Lookup because the non-clustered index already contains the name column.
- The data rows in the Employee table are physically stored in the order of the clustered index on the employee ID column.
- When querying with a clustered index on employee ID and a non-clustered index on name, the leaf nodes of the non-clustered index store employee names alphabetically along with the employee ID.
- 05:24Performance Comparison
- The video compares Key Look Up and RID Look Up operations in different select statements.
- The overall estimated subtree cost for a select statement with all columns is 0.67.
- The performance impact of Key Look Up and RID Look Up depends on various factors like query requirements, index columns, and data distribution, which can only be determined through testing and analyzing statistics.