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: 101168

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".