Relation between reads and duration

I have a query that performs strangely under different conditions as
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:

>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:

>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql