Tracking Lookup Operations in 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.

CREATE TABLE Customers
(
	CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
	CustomerName CHAR(100) NOT NULL,
	CustomerAddress CHAR(100) NOT NULL,
	Comments CHAR(185) NOT NULL,
	Value INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX idx_Test ON Customers(Value)
GO

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

DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
	INSERT INTO Customers VALUES
	(
		@i,
		'CustomerName' + CAST(@i AS CHAR),
		'CustomerAddress' + CAST(@i AS CHAR),
		'Comments' + CAST(@i AS CHAR),
		@i
	)
	
	SET @i += 1
END
GO

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

SELECT * FROM Customers WITH (INDEX(idx_Test))
WHERE Value < 80001
GO

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:

SELECT singleton_lookup_count, *
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('Customers'), 1, NULL)
GO

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