Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index Range_scans Expand / Collapse
Author
Message
Posted Wednesday, August 8, 2012 5:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 1,380, Visits: 2,698
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



Post #1341796
Posted Wednesday, August 8, 2012 6:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:41 AM
Points: 1,191, Visits: 9,887
Because it's not a UNIQUE index, so it's retrieving a range of values that match your criteria rather than a singleton lookup.
Post #1341812
Posted Friday, August 10, 2012 4:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 1,380, Visits: 2,698
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
Post #1343256
Posted Friday, August 10, 2012 4:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:41 AM
Points: 1,191, Visits: 9,887
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...
Post #1343260
Posted Friday, August 10, 2012 4:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 1,380, Visits: 2,698
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
Post #1343267
Posted Tuesday, August 14, 2012 3:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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
Post #1345048
Posted Wednesday, August 15, 2012 12:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 1,380, Visits: 2,698
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


Post #1345137
Posted Wednesday, August 15, 2012 1:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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 2008, MVP
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

Post #1345145
Posted Wednesday, August 15, 2012 4:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:21 AM
Points: 1,380, Visits: 2,698
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?
Post #1345199
Posted Wednesday, August 15, 2012 4:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:41 AM
Points: 1,191, Visits: 9,887
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.
Post #1345205
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse