|
Home > Archive > MS SQL Server > July 2005 > performance question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
performance question
|
|
| ChrisR 2005-07-14, 8:23 pm |
| Why does Query1 below have a Scan with 21 Logical Reads for 31 records
returned (a small percentage of Logicalreads), when Query 2 has no Scan,
only returns 6 records, and has 14 Logical reads. (a higher percentage)
In other words, the more data, the fewer Logical reads but no Scan. The less
data, higher reads but a scan. What gives?
SQL2K sp3a
TIA, ChrisR
| |
| Robbe Morris [C# MVP] 2005-07-14, 8:23 pm |
| what query? what data types are the columns?
--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ChrisR" <noemail@bla.com> wrote in message
news:OLWXZnMiFHA.3672@TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
>
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
>
> SQL2K sp3a
>
> TIA, ChrisR
>
| |
| Andrew J. Kelly 2005-07-15, 3:23 am |
| The scan on a clustered or nonclustered index at the leaf level is pretty
simple since it goes from leaf page to leaf page in order. It can read
relatively few pages to get a lot of rows that way. A seek has to traverse
the B-Tree from root to leaf level for each row returned. Depending on the
size of the index tree it can read a minimum of 2 pages per row seeked.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@bla.com> wrote in message
news:OLWXZnMiFHA.3672@TK2MSFTNGP10.phx.gbl...
> Why does Query1 below have a Scan with 21 Logical Reads for 31 records
> returned (a small percentage of Logicalreads), when Query 2 has no Scan,
> only returns 6 records, and has 14 Logical reads. (a higher percentage)
>
> In other words, the more data, the fewer Logical reads but no Scan. The
> less data, higher reads but a scan. What gives?
>
> SQL2K sp3a
>
> TIA, ChrisR
>
|
|
|
|
|