SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan. Part 2 ...some other infos.

Hi guys,

Welcome back!

 

If you missed the first part of this post well click here!

We have seen the physical structure of an index and have talked about Singleton lookup and Range Scan.

 

Today, i will show you other details to complete the discussion.

 

It will be a short post but the next time then we will talk about residual predicates and it will be a very interesting topics!

Finally, thanks to a suggestion of ​​my friend Alessandro Alpi there will also an other post in which we will talk about composite indexes and how, in this case, the index search takes place.

Enjoy the reading!

 

Other infos about Singleton lookup and Range Scan

In the previous post, we talked about Singleton lookup and Range Scan.

Now we know how record are phisically searched inside an index.

 

We have seen that it is not easy to know which method we are using.

In this case DMVs comes to our aid!

 

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are system views and system functions that return system status informations at no cost.
All this without burdening SQL Server's work.

 

DMVs that can help us are:  

• Sys.dm_db_index_operational_stats (that show the number of Range scan and the number of Singleton lookup)
• Sys.indexes

Finally joining this two views we can write the following query:

SELECT
    ISNULL(I.[name], I.[type_desc]) index_name,
    SUM(IOS.range_scan_count) range_scans,
    SUM(IOS.singleton_lookup_count) single_lookups
FROM sys.dm_db_index_operational_stats
(
    DB_ID(N'Nuovo'), 
    OBJECT_ID(N'dbo.ListofNumber', N'U'), 
    NULL, 
    NULL
) IOS
JOIN sys.indexes AS I ON I.[object_id] = IOS.[object_id] AND 
                         I.index_id = IOS.index_id
GROUP BY
    I.index_id,
    I.[name], 
    I.[type_desc]
ORDER BY
    I.index_id;
 

Execute now the following Query:

SELECT ID FROM ListofNumber WHERE id = 123 or id = 124 or id = 125
 

Enabling the statistics I/O we can see that we have 3 scan.

Table 'ListofNumber'. Conteggio analisi 3, letture logiche 6, letture fisiche 2, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
 

Through the DMV we can say that 3 single lookup have been made on the primary key

 

Let's see now whats happen running instead the following Query:

SELECT ID FROM ListofNumber WHERE id BETWEEN 123 and 125
 

This time we have only 1 Scan and only 2 logic read

Table 'ListofNumber'. Conteggio analisi 1, letture logiche 2, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.

 

This time we have a range_scan always on the primary Key.

 

 

And now whats happen if we execute se same query not on the ID field but on the Val field?

 

On the Id field is defined a primary key so the values inside this field are unique.

On the Val field is defined a non clustered index so the values inside this field are ordered by not unique. Therefore we should not have any single_lookups.

SELECT VAL FROM ListofNumber WHERE VAL = 123 or VAL = 124 or VAL = 125
 

Yes, we are three range_scan:

 

 And now?

SELECT VAL FROM ListofNumber WHERE VAL BETWEEN 123 AND 125
 

We went from 3 to 4 range scans, so only 1 range scan was performed:

 

A little note: is always a good idea use the BETWEEN function and avoid a series of OR operators!

That'all from today!

Luca

If you are genuinely and truly interested in one of the products that appear in advertising ... click the adv from my blog to support my site! 

Or click the (    ) button to offer to me an ice-cream!

Previous post: SQL Server, Is a Seek Always Only a Seek? Singleton Lookup and Range Scan