Index Usage - Lookups

Question

Index Usage - Lookups

SQLSACT

SSC-Insane

Points: 21838
More actions
July 23, 2012 at 7:50 am

#261510

Hi All
I'm using the below script to check my Index usage on a particular table and I am seeing a large number of user_lookups against the Clustered Index
I understand that a non-clustered index will do a Lookup operation when the index doesn't cover the query
I don't understand why a Clustered Index would ever do a Lookup operation. Please explain this
Script:
SELECT SO.name,
SI.name IndexName
,SI.type_desc IndexType
,IPS.avg_fragmentation_in_percent
,IPS.index_level
,IOS.range_scan_count
,IUS.user_lookups
,IUS.user_scans
,IUS.user_seeks
,IUS.index_id
FROM sys.indexes SI
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID('databasename'), OBJECT_ID('objectname'), NULL , NULL, N'LIMITED')IPS
ON IPS.object_id = SI.object_id
AND IPS.index_id = SI.index_id
inner join sys.dm_db_index_operational_stats (DB_ID('databasename'), OBJECT_ID('objectname'), NULL , NULL) IOS
on IOS.object_id = SO.object_id
and IOS.index_id = SI.index_id
inner join sys.dm_db_index_usage_stats IUS
on IUS.object_id = SO.object_id
and IUS.index_id = SI.index_id
where SI.name is not null
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The clustered index doesn't do the lookups. It's the target of the lookups. That tells you how many lookups were done to the clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster (7/23/2012)
The clustered index doesn't do the lookups. It's the target of the lookups. That tells you how many lookups were done to the clustered index.

Thanks

I thought that a Lookup operation could only happen on a Heap Table where the NonClustered index has to do a RID lookup when the Index doesn't cover the query

I figured that the moment you have a Clustered Index on a table, you should never see a Lookup operation because the NC indexes would contain the Clustering Key

Unless I have this whole thing wrong.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SQLSACT (7/23/2012)
I thought that a Lookup operation could only happen on a Heap Table where the NonClustered index has to do a RID lookup when the Index doesn't cover the query

Nope. Doesn't matter if the table is a heap or a cluster, if a nonclustered index doesn't cover a query it has to do a lookup to get the rest of the columns. RID lookup if the table is a heap, key lookup if it's a cluster

I figured that the moment you have a Clustered Index on a table, you should never see a Lookup operation because the NC indexes would contain the Clustering Key

Sure, nonclustered indexes contain the clustering key, but that doesn't mean they contain the entire row. If the nonclustered doesn't cover the query it has to get the other columns from somewhere. That somewhere is the table (heap or clustered index)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jake.mayher SSC Enthusiast Points: 140 More actions · Answer 4

The NC indexes do have the clustering key, but it is still a lookup on the clustered index to retrieve the data from the leaf pages. It is similar to using the index in the back of a book, sure it tells you what page the information you want is located in, but you still have to lookup that page to get it.

Jake

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

GilaMonster (7/23/2012)
SQLSACT (7/23/2012)
I thought that a Lookup operation could only happen on a Heap Table where the NonClustered index has to do a RID lookup when the Index doesn't cover the query
Nope. Doesn't matter if the table is a heap or a cluster, if a nonclustered index doesn't cover a query it has to do a lookup to get the rest of the columns. RID lookup if the table is a heap, key lookup if it's a cluster
I figured that the moment you have a Clustered Index on a table, you should never see a Lookup operation because the NC indexes would contain the Clustering Key
Sure, nonclustered indexes contain the clustering key, but that doesn't mean they contain the entire row. If the nonclustered doesn't cover the query it has to get the other columns from somewhere. That somewhere is the table (heap or clustered index)

Thanks

I'm trying to produce a situation where I can see the Key Lookup operator

Consider the following DDL:

Create table Indexing4

( Col1 int primary key Clustered

,Col2 int

,Col3 int

,Col4 int

)

go

create nonclustered Index NCIX on Indexing4 (Col4)

Shouldn't the below query produce a Key Lookup?

select Col4, Col2 from Indexing4 where Col4 = somevalue

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes. Either a index seek + key lookup or a clustered index scan, depending on the % of rows that the query will retrieve

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

savethytrees SSCommitted Points: 1968 More actions · Answer 7

Hi,

If I see several lookups on a clustered index, is there a way to find which non clustered index is missing some columns?

I would like to eliminate the lookups by modifying the current index and covering other columns.

I know I can go thru the Profiler route and check access plans to find which ones have lookups on clustered index but would like to know of a shorter route.

Blog
http://saveadba.blogspot.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

savethytrees (7/23/2012)
Hi,
If I see several lookups on a clustered index, is there a way to find which non clustered index is missing some columns?

Not from the index usage DMV, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 9

GilaMonster (7/23/2012)
Yes. Either a index seek + key lookup or a clustered index scan, depending on the % of rows that the query will retrieve

Thanks

I have created that table twice, one with Zero rows and one with 300 rows

Both of them return a Clustered Index Scan

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Your query is returning too high a % of rows for the seek and key lookup to be cost effective then.

300 rows is too small to test and draw any meaningful conclusions on. It's probably a single page. 300 000 is more like it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 11

GilaMonster (7/24/2012)
Your query is returning too high a % of rows for the seek and key lookup to be cost effective then.
300 rows is too small to test and draw any meaningful conclusions on. It's probably a single page. 300 000 is more like it.

Thanks

Got the key lookup I was looking for

Created the same table.

Using the script in this URL: http://www.sqlservercentral.com/articles/Data+Generation/87901/

I inserted 30000000 rows

Ran my select query and the execution plan showed the NC Seek and the Key Lookup :w00t:

Thanks