BUG: Nonclustered Index Range Scans Generate Excessive Data Page Reads

• Microsoft SQL Server versions 4.2x, 6.0, 6.5

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 

WORKAROUND

To work around the problem replace the following section of code:

   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%" 

Result

   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 

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 4.x, 6.0, and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

This behavior is reproducible on build 6.50.201 and in all service packs.

Additional query words: prodsql

Keywords : kbbug6.50 kbbug4.2x kbbug6.00
Version : WinNT:4.x,6.0,6.5
Platform : winnt
Issue type : kbbug