Index lookups, seeks and scans

Posted by Chris on October 04, 2006

In my latest article entitled “Dynamic Management Objects”:http://www.hedgate.net/pages/articles/dynamic-management-objects/ I describe a great new feature in SQL Server 2005. With Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) we can get a lot of real-time information regarding what have been going on in the server since the latest restart. I was looking through some of these recently and while testing the DMV called sys.dm_db_index_usage_stats I noticed something that intrigued me. Among a lot of other interesting columns in the output there are three called user_seeks, user_scans and user_lookups. Now, I know what scans and seeks are, but how is a lookup defined? In particular, how does it differ from a seek? I had a hunch that I knew what they indicated, but to verify it I did some quick testing, which is described in the below script.


USE AdventureWorks
GO

– Empty result set (when server is just restarted of course)
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

SELECT * FROM Sales.SalesOrderHeader;

– IndexId 1: 0 user_seeks, 1 user_scans and 0 user_lookups
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID = 70875;

– IndexId 1: 1 user_seeks, 1 user_scans and 0 user_lookups
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

SELECT * FROM Sales.SalesOrderHeader
WHERE SalesOrderID BETWEEN 70875 AND 70879;

– IndexId 1: 2 user_seeks, 1 user_scans and 0 user_lookups
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 28220;

– IndexId 1: 2 user_seeks, 1 user_scans and 1 user_lookups
– IndexId 5: 1 user_seeks, 0 user_scans and 0 user_lookups
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID IN (28220, 25925);

– IndexId 1: 2 user_seeks, 1 user_scans and 2 user_lookups
– IndexId 5: 2 user_seeks, 0 user_scans and 0 user_lookups
SELECT * FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = OBJECT_ID(‘Sales.SalesOrderHeader’);

One thing to note about sys.dm_db_index_usage_stats is that the numbers in all of these columns show the number of ‘operations’, not the number of pages or rows or anything. So, as this script shows, a user_lookup is the operation used in a clustered index (or heap) to fetch a full data row (or rows) using the key(s) returned from a seek operation of a non-clustered index.

Trackbacks

Trackbacks are closed.

blog comments powered by Disqus