Tracking Lookup Operations in SQL Server

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Last week during my SQL Server Performance Tuning Workshop I got quite an interesting question regarding Bookmark Lookup Operations against Clustered Indexes:

“How can I find out on which Clustered Index I have the most Lookup Operations performed?”

The great thing about SQL Server is that SQL Server logs a lot of information in the various Dynamic Management Views, so you just have to ask SQL Server for the answer of this question. And the answer is given you by the Dynamic Management Function sys.dm_db_index_operational_stats.

Within that DMF, SQL Server reports you in the column singleton_lookup_count how many Lookup Operations you have performed. Let’s have a look on a conrete example. The following listing creates a new table, and creates a Non-Clustered Index on a single column.

And finally let’s insert 80000 rows into that table:

And now let’s just enforce with a simple Query Hint a Lookup Operation against the Clustered Table, which is executed 80000 times:

When you now query the Dynamic Management Function sys.dm_db_index_operational_stats, SQL Server returns you precisely how many Lookup Operations you have performed:

Tracking Lookup Operations in SQL Server

As I have already blogged multiple times, Lookup Operations in general are not bad, but they can get really expensive when you do them too often, like in our case with the Query Hint in place, or when you have problems regarding out-of-date Statistics, or some Parameter Sniffing problems.

Thanks for your time,

-Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top