BUG: Nonclustered Index Range Scans Generate Excessive Data Page Reads

ID: Q185919


The information in this article applies to:

BUG #: NT 18025 (6.50)

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

In its place, use this:

   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 

Last Reviewed: April 20, 1999