Index Selection - Inside the SQL Server Query Optimizer - page 129

However, the values that we will be inspecting in this exercise, user_seeks ,
user_scans , user_lookups , and user_updates are all set to 0.
Now run the following query, let's say, three times:
SELECT * FROM dbo . SalesOrderDetail
Listing 4-28.
This query is using a Table Scan operator, so, if you rerun the code in Listing 4-27,
the DMV will show the value 3 on the user_scans column. Note that the column
index_id is 0, denoting a heap, and the name of the table is also listed (as a heap is
just a table with no clustered index).
Run the next query, which uses an Index Seek, twice. After the query is executed, a new
record will be added for the non-clustered index, and the user_seeks counter will show
a value of 2.
SELECT ProductID FROM dbo . SalesOrderDetail
WHERE ProductID = 773
Listing 4-29.
Now, run the following query four times, and it will use both Index Seek and RID Lookup
operators. Since the user_seeks for the non-clustered index had a value of 2, it will be
updated to 6, and the user_lookups value for the heap will be updated to 4.
SELECT * FROM dbo . SalesOrderDetail
WHERE ProductID = 773
Listing 4-30.