SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Range_scans


Index Range_scans

Author
Message
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 2969
Hi All

I'm struggling to understand why my Index is reporting Range_scans

DDL:

Table:
CREATE TABLE [dbo].[Indexing2](
[Col1] [int] IDENTITY(1,1) NOT NULL,
[Col2] [int] NULL,
[Col3] [int] NULL,
[Col4] [int] NULL
)



Indexes:
CREATE CLUSTERED INDEX [CX] ON [dbo].[Indexing2] ([Col1]) ;
CREATE NONCLUSTERED INDEX [NCX] ON [dbo].[Indexing2] ([Col4])



Query:

select Col1 from Indexing2 where Col4 = '6365478'



I'm tracking Index usage on this table using the sys.dm_db_index_operational_stats and when I run the select query, the range_scan_count increments.

How does my select statemement qualify as a range_scan?


Thanks
HowardW
HowardW
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6347 Visits: 9892
Because it's not a UNIQUE index, so it's retrieving a range of values that match your criteria rather than a singleton lookup.
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 2969
HowardW (8/8/2012)
Because it's not a UNIQUE index, so it's retrieving a range of values that match your criteria rather than a singleton lookup.


Thanks

I don't understand this because my result returns one value

Why does it show a Range_scan?

Thanks
HowardW
HowardW
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6347 Visits: 9892
It's not relevant how many rows were actually returned, it still has to access a range in the index if it's not marked as UNIQUE as it doesn't know how many rows may be returned until it's performed the seek.

Please note that this is not the same as a table/index scan as you'd see it in an execution plan. It's still only scanning the rows that match the criteria...
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 2969
HowardW (8/10/2012)
It's not relevant how many rows were actually returned, it still has to access a range in the index if it's not marked as UNIQUE as it doesn't know how many rows may be returned until it's performed the seek.

Please note that this is not the same as a table/index scan as you'd see it in an execution plan. It's still only scanning the rows that match the criteria...


Thanks

So anytime SQL has to access more that one page to return the desired result (irrespective of the number of rows returned). It counts as a range_scan ?

I always though that a range_scan only happens when you have
where column between 'some-value' and 'some value'




Thanks for the clarification
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40018 Visits: 14412
SQLSACT (8/10/2012)
HowardW (8/10/2012)
It's not relevant how many rows were actually returned, it still has to access a range in the index if it's not marked as UNIQUE as it doesn't know how many rows may be returned until it's performed the seek.

Please note that this is not the same as a table/index scan as you'd see it in an execution plan. It's still only scanning the rows that match the criteria...


Thanks

So anytime SQL has to access more that one page to return the desired result (irrespective of the number of rows returned). It counts as a range_scan ?

It has nothing to do with how many pages are read, it has to do with how many leaf-level index entries may need to be read. If you ask the engine to return the answer to this query:

select Col1 from Indexing2 where Col4 = '6365478'

and there is an non-unique index on Col4 then SQL Server has to prepare to scan the index to find all records you requested. In your case you know you are only retrieving one row however the index is not unique so SQL Server has to check at minimum 2 records (might be on the same index page, might not be) to return you one row, i.e. it seeks to the initial row but then continues by scanning the index until Col4 != '6365478'. The fact that you only had one row in your index where Col4 = '6365478' is irrelevant, SQL Server still had to do a range scan to satisfy your query.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 2969
opc.three (8/14/2012)
SQLSACT (8/10/2012)
HowardW (8/10/2012)
It's not relevant how many rows were actually returned, it still has to access a range in the index if it's not marked as UNIQUE as it doesn't know how many rows may be returned until it's performed the seek.

Please note that this is not the same as a table/index scan as you'd see it in an execution plan. It's still only scanning the rows that match the criteria...


Thanks

So anytime SQL has to access more that one page to return the desired result (irrespective of the number of rows returned). It counts as a range_scan ?

It has nothing to do with how many pages are read, it has to do with how many leaf-level index entries may need to be read. If you ask the engine to return the answer to this query:

select Col1 from Indexing2 where Col4 = '6365478'

and there is an non-unique index on Col4 then SQL Server has to prepare to scan the index to find all records you requested. In your case you know you are only retrieving one row however the index is not unique so SQL Server has to check at minimum 2 records (might be on the same index page, might not be) to return you one row, i.e. it seeks to the initial row but then continues by scanning the index until Col4 != '6365478'. The fact that you only had one row in your index where Col4 = '6365478' is irrelevant, SQL Server still had to do a range scan to satisfy your query.


Would fragmentation affect this process negatively?

but then continues by scanning the index until Col4 != '6365478'

This is not the same as an Index Scan, right?

If we had a query like this: select Col1 from Indexing2 where Col4 between '6365478' and '7365478'. Would we definately see a range_scan irrespective if the index is Unique or not?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226341 Visits: 46325
SQLSACT (8/15/2012)
Would fragmentation affect this process negatively?


If the range scan is large enough and is from disk, yes probably.

If we had a query like this: select Col1 from Indexing2 where Col4 between '6365478' and '7365478'. Would we definately see a range_scan irrespective if the index is Unique or not?


Yup, that'll be a range scan too. It's a range scan any time SQL may have to return more than one row, ie not a equality match on a unique index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQLSACT
SQLSACT
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5014 Visits: 2969
GilaMonster (8/15/2012)
SQLSACT (8/15/2012)
Would fragmentation affect this process negatively?


If the range scan is large enough and is from disk, yes probably.

If we had a query like this: select Col1 from Indexing2 where Col4 between '6365478' and '7365478'. Would we definately see a range_scan irrespective if the index is Unique or not?


Yup, that'll be a range scan too. It's a range scan any time SQL may have to return more than one row, ie not a equality match on a unique index.


Given all of this, is it safe to say that, where possible, make you indexes unique?
HowardW
HowardW
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6347 Visits: 9892
SQLSACT (8/15/2012)


Given all of this, is it safe to say that, where possible, make you indexes unique?


You should only make an index unique if the data is unique and guaranteed to stay so through your business rules. But yes, if the data is unique, make the index unique.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search