BUG: Nonclustered Index Range Scans Generate Excessive Data Page Reads

SYMPTOMS

When a query performs a nonclustered index scan to retrieve data, all the data pages in the range, including those which do not match the criteria, are read. This behavior is not specific to the LIKE operator. The following scenario demonstrates the problem:

   use pubs
   go
   set nocount on
   Create Table PrbLike (lname char(25),szconstant char(50))
   go

   declare @x int
   select @x=1
   while @x<10000
      begin
      insert PrbLike values (ltrim(str(@x))+"_namepadding", replicate("x",
      30))
      select @x=@x+1
   end
   go
   create index idx_name on PrbLike(lname)
   go
   set nocount off
   set statistics io on
   set showplan on
   go
				

First Query

   select *
   from PrbLike
   where lname like "11_0%"
   go
				

Result

   STEP 1
   The type of query is SELECT
   FROM TABLE
   PrbLike
   Nested iteration
   Index: idx_name
   lname                     szconstant
   ------------------------- ---------------------------------
   1100_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1110_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1120_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1130_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1140_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1150_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1160_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1170_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1180_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1190_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

   (10 row(s) affected)

   Table: PrbLike  scan count 1,  logical reads: 116,  physical
   reads: 0,  read ahead reads: 0
				

Second Query

   select *
   from PrbLike
   where lname like "11%"
				

Result

   STEP 1
   The type of query is SELECT
   FROM TABLE
   PrbLike
   Nested iteration
   Index: idx_name
   lname                     szconstant
   ------------------------- ----------------------------------
   11_namepadding            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   ...
   1199_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

   (111 row(s) affected)

   Table: PrbLike  scan count 1,  logical reads: 116,  physical
   reads: 0,  read ahead reads: 0
				

   select *
   from PrbLike
   where lname like "11_0%"
				

   select P2.*
   from PrbLike P1, PrbLike P2
   where P2.lname = P1.lname
     and P1.lname like "11_0%"
				

   STEP 1
   The type of query is SELECT
   FROM TABLE
   PrbLike P1
   Nested iteration
   Index: idx_name
   FROM TABLE
   PrbLike P2
   Nested iteration
   Index: idx_name
   lname                     szconstant
   ------------------------- ---------------------------------
   1100_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1110_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1120_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1130_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1140_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1150_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1160_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1170_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1180_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   1190_namepadding          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

   (10 row(s) affected)

   Table: PrbLike  scan count 1,  logical reads: 5,  physical
   reads: 0,  read ahead reads: 0
   Table: PrbLike  scan count 10,  logical reads: 40,  physical
   reads: 0,  read ahead reads: 0