top of page

Key Lookups and Cardinality Estimation

Key lookup is required when the index doesn't have all the fields we need.

Even with indexes, there's a tipping point where it's more efficient for SQL to just scan the table once and get out

만약에 필드를 몇개 사용하지 않지만 자주 사용하면 INDEX로 생성하는것이 좋다

 

Output List를 보면 어떤 필드를 사용하는지 알 수 있다.

Index vs Clustered Index를 사용 할 것인가 SQL Server가 선택을 해야함 이 decision은 table을 읽기 전에 선택해야하는것

whenever when you create an index, you will get the statistics with matching name. Statistic is one 8k page worth of metadata about what's inside the index

Statistics help SQL Server

Decide which index to use
What order to process tables/indexes in
Whether to do seeks or scans
Guess how many rows will match your query
How much memory to allocate for the query

DBCC SHOW_STATISTICS('dbo.Users', 'IX_LastAccessDate_Id')

같은 결과 값을 나타내는 쿼리지만 다른 플랜을 가지고 있음

SQL Server hast to decide between:
scannign the entire table, which is great for big data, or
An index seek + key lookup, which is better for small data

It bases this decision on cardinality estimation and it's not perfect

 

we can avoid this problem by widening our nonclustered index

 

CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id, DisplayName, Age)

Age를 바꾸게 된다면 모두 다 sort를 해줘야함

 

CREATE INDEX IX_LastAccessDate_Id_Includes ON dbo.Users(LastAccessDate, Id) INCLUDE(DisplayName, Age);

Age를 바꾸게 된다면 sort를 안해줘도 되고 그냥 수정만 해줘도 됨

  • Index seek + key lookup = we may need wider indexes

  • Statistics help SQL Server pick indexs, methods

  • Cardinality estimation isn't perfect(especially with real-world T-SQL and joins to multiple tables)

  • You can help by understanding SQL's limitations and crafting your T-SQL to avoid them

from https://www.youtube.com/watch?v=9GPwJ0eVBGk&list=PLGyh3dhppvKoEFryHIKmpOn_bwoIDSk0h&index=3

 

bottom of page