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