Microsoft KB Archive/185919

= BUG: Nonclustered Index Range Scans Generate Excessive Data Page Reads =

Article ID: 185919

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q185919



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: kbbug kbpending KB185919

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.