Index Range_scans

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 Administration » Index Range_scans

Add to briefcase

17 posts, Page 1 of 2

1 2 »»

Index Range_scans

Expand / Collapse

Author

Message

Posted Wednesday, August 08, 2012 5:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,191, Visits: 2,115

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 08, 2012 6:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,035, Visits: 7,672

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

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,191, Visits: 2,115

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

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,035, Visits: 7,672

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

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,191, Visits: 2,115

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

Group: General Forum Members
Last Login: Yesterday @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1345048

Posted Wednesday, August 15, 2012 12:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,191, Visits: 2,115

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-Dedicated

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 37,692, Visits: 29,951

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

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,191, Visits: 2,115

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

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 1,035, Visits: 7,672

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

17 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse