Index Range_scans

Question

Index Range_scans

SQLSACT

SSC-Insane

Points: 21838
More actions
August 8, 2012 at 5:23 am

#279936

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

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 SSC-Insane Points: 21838 More actions · Answer 2

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 One Orange Chip Points: 29109 More actions · Answer 3

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 SSC-Insane Points: 21838 More actions · Answer 4

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 SSC Guru Points: 182276 More actions · Answer 5

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 SSC-Insane Points: 21838 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSC-Insane Points: 21838 More actions · Answer 8

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 One Orange Chip Points: 29109 More actions · Answer 9

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SQLSACT (8/15/2012)
...
Given all of this, is it safe to say that, where possible, make you indexes unique?

You've seen the benefit that a unique index can bring, but be careful with this conclusion. Adding columns to an index increases the cost of using and maintaining it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

If you are creating an index on a column that you know is unique, make the index unique. If you're creating an index on a column that's not unique, then don't add columns just to make it unique, it's inefficient.

If you can measure the difference between a singleton seek and a range scan that reads one row, I'll be exceedingly surprised .

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 SSC-Insane Points: 21838 More actions · Answer 12

GilaMonster (8/15/2012)
If you are creating an index on a column that you know is unique, make the index unique. If you're creating an index on a column that's not unique, then don't add columns just to make it unique, it's inefficient.
If you can measure the difference between a singleton seek and a range scan that reads one row, I'll be exceedingly surprised .

Thanks For this

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

SQLSACT (8/8/2012)
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

I can't believe anyone didn't catch this (or maybe I missed it) but the answer is because you are getting a CONVERT_IMPLICIT from your WHERE clause. '6365478' is a CHAR value, NOT and INT, which is the column definition. You need to do this:

select Col1 from Indexing2 where Col4 = 6365478

Now assuming that specific value is fewer than roughly 1% of all rows in the table you will get an index seek on NCX and get your Col1 output. I don't even think you will need a bookmark lookup since Col1 is the clustered index and the clustered key is carried as the pointer to all nonclustered index rows. Actually, because of that you should always get a seek, no matter how many rows are returned - the 1% thing was if you asked for a column that wasn't part of the nonclustered index (such as col2).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

The execution plan if you run that query is an index seek with the predicate "Seek Keys[1]: Prefix: [tempdb].[dbo].[Indexing2].Col4 = Scalar Operator(CONVERT_IMPLICIT(int,[@1],0))"

So there is a conversion, but it's the literal being converted to int (int has a higher precedence), not the column to char.

The question wasn't why is it not an index seek (it is), but why sys.dm_db_index_operational_stats reports that as a range scan not a singleton lookup. (a unique index does result in a singleton lookup)

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