SQL SERVER – Index Seek & Scan
Scans and Seeks are the iterators that SQL Server uses to read data from tables and indexes.
What is the difference between a scan and a seek?
A Scan returns the entire table or index.
A Seek efficiently returns rows from one or more ranges of an index.
There are a few basic operations which SQL will perform when looking for the data that you need. Here they are listed in the order of worst to best.
• Table Scan
• Clustered Index Scan
• Index Scan
• Clustered Index Seek
• Index Seek
What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?
SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats - that are extremely useful for monitoring which indexes are used as well as how and when they are used. Both DMVs report similar statistics on information such as the number of scans, seeks, and updates to different indexes.
The main difference between these DMVs is simple but important:
sys.dm_db_index_usage_stats records how many times the query optimizer uses an index in a plan. This usage information is recorded again each time the plan is executed. (Compiling a plan alone is not sufficient to record an index's usage.) However, and this is the important part, for the purposes of computing the statistics, it does matter how many times the query processor executes the specific operator that references the index.
sys.dm_db_index_operational_stats records how many times the storage engine executes a specific operation on the index. These statistics do depend on how many times the query processor executes each operator. If an operator is never executed, the storage engine does not perform any operations on the index and the DMV reports that the index was not used. If an operator is executed multiple times, the storage engine performs multiple operations on the index and the DMV reports that the index was used multiple times.
Let's try an example to see this difference in action. I'll use the following simple schema.
USE tempdb
CREATE TABLE T(A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TA ON T(A)
CREATE UNIQUE INDEX TB ON T(B)
As expected, immediately after creating this table, the stats are zero (or just non-existent):
SELECT index_id, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID(' tempdb ') and object_id = OBJECT_ID(' tempdb ..t')
ORDER BY index_id
SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID(' tempdb '), OBJECT_ID(' tempdb ..t'), NULL, NULL)
ORDER BY index_id
Output:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
(0 row(s) affected)
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 0 0
2 0 0
(2 row(s) affected)
Now suppose that we do a scan of the clustered index:
SET SHOWPLAN_ALL ON;
select * from T
Output: StmtText
SELECT * FROM T
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))
Repeating the DMV queries, we see that the clustered index shows one scan in both DMVs. SQL Server records the scan even though the table contains no rows and the query returns an empty result:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- ----------------
1 0 1 0 0
(1 row(s) affected)
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 0
2 0 0
(2 row(s) affected)
Next let's try a singleton lookup on the clustered index:
select * from Test where A=1
Output:StmtText
SELECT * FROM T WHERE A = 1
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
Again the table contains no rows and the query returns an empty result. Nevertheless, the DMVs now report one seek and one singleton lookup:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------
1 0 1 0 0
(1 row(s) affected)
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 0
2 0 0
(2 row(s) affected)
Now let's try something a little more interesting. Let's run a bookmark lookup:
Output:StmtText
SELECT * FROM T WHERE B = 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[T].[TB]), SEEK:([tempdb].[dbo].[T].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)
As expected sys.dm_db_index_usage_stats reports a seek on index TB (index id 2) and a bookmark lookup on the clustered index (index id 1). However, sys.dm_db_index_operational_stats reports only the singleton lookup on index TB but does not report any new activity on the clustered index:
index_id user_seeks user_scans user_lookups user_updates ----------- -------------------- -------------------- -------------------- -------------------- 1 1 1 1 0 2 1 0 0 0
index_id range_scan_count singleton_lookup_count ----------- -------------------- ---------------------- 1 1 1 2 0 1
Next, let's insert three rows into the table and run another bookmark lookup experiment. I'm using a hint to force a bookmark lookup plan. Without the hint, the optimizer would simply use a clustered index scan since the query returns all three rows in the table:
Output:StmtText INSERT T VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2)
SELECT * FROM T WITH (INDEX (TB))
INSERT T VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2)
|--Clustered Index Insert(OBJECT:([tempdb].[dbo].[T].[TA]), OBJECT:([tempdb].[dbo].[T].[TB]), SET:([tempdb].[dbo].[T].[A] = [Union1003],[tempdb].[dbo].[T].[B] = [Union1004],[tempdb].[dbo].[T].[C] = [Union1005]))
|--Top(ROWCOUNT est 0)
|--Constant Scan(VALUES:(([@1],[@2],[@3]),([@4],[@5],[@6]),([@7],[@8],[@9])))
SELECT * FROM T WITH (INDEX (TB))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
|--Index Scan(OBJECT:([tempdb].[dbo].[T].[TB]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)
This time sys.dm_db_index_usage_stats reports a scan on index TB and a bookmark lookup on the clustered index (plus the updates from the insert statement). But, sys.dm_db_index_operational_stats reports a scan on index TB and three bookmark lookups on the clustered index:
index_id user_seeks user_scans user_lookups user_updates ----------- -------------------- -------------------- -------------------- -------------------- 1 1 1 2 1 2 1 1 0 1
index_id range_scan_count singleton_lookup_count ----------- -------------------- ---------------------- 1 1 4 2 1 1
When the server executes the above query, it runs the clustered index seek three times - once for each row returned by the index scan. We ran the query only once but it performed three bookmark lookups. Thus, as in the prior example, the server updates sys.dm_db_index_usage_stats to indicate that it executed a query plan that includes a bookmark lookup on table T, but unlike the prior example,it updates sys.dm_db_index_operational_statsto indicate that the query performed three actual bookmark lookups.