Index Range_scans

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  August 16, 2012 at 8:44 am

  #1526638

  TheSQLGuru (8/16/2012)

  ---

  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).

  Thanks

  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

  I was always getting a seek.

  I was confused as to why it's reporting as a range_scan in sys.dm_index_operational_stats

  Thanks

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 16, 2012 at 9:25 am

  #1526668

  Oops! Too quick read/reply.

  I would use sys.dm_db_index_usage_stats, which does report user_seeks for your situation (at least with the test setup I did).

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