INDEX range_scan_count

• etl2016

  Default port

  Points: 1459

  More actions

  December 24, 2017 at 12:51 pm

  #334173

  Hi,

  I am experimenting with various permutations and combinations to understand how the range_scan_count gets populated by the function sys.dm_db_index_operational_stats.

  Below case study is bit hard to understand. Could you please share your thoughts, thanks

  Step-1:    Create a Table and Index

  CREATE TABLE T (A INT, B INT, C INT)
  CREATE UNIQUE CLUSTERED INDEX TA ON T(A)

  Step-2    Insert 2 records

  INSERT T VALUES (1,2,3), (4,5,6)

  Step-3:  select * from t where B = 10

  Step-4: 

  SELECT index_id, range_scan_count, singleton_lookup_count
  FROM sys.dm_db_index_operational_stats (DB_ID('tempdb'), OBJECT_ID('tempdb..t'), NULL, NULL)
  ORDER BY index_id

  Step-4   gives range_scan_count = 2

  How is it arriving at a count of 2?

  As I understand, since the SELECT is being done on non-index column B, its a classic example of table-scan, and while doing the full table scan, the question of RANGE_SCAN does not come into picture at all.   Of course, from the result, its obvious that I am missing an important point. Please let me know what it is.

  thanks you in advance.

• ScottPletcher

  SSC Guru

  Points: 100262

  More actions

  December 26, 2017 at 9:11 am

  #1973473

  From Books Online, under sys.dm_db_index_operational_stats:

  range_scan_count  bigint  Cumulative count of range and table scans started on the index or heap.

  SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!