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

re-indexing job takes more time

We have a re-indexing all DBs schedule job in our SQL 2000 box,
normally it took 7 hours to complete but all of the sudden now it
takes more than 20 hours.
What do you think it cause this problem? We have no clue."Frank" <soal6570@.yahoo.com> wrote in message
news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> We have a re-indexing all DBs schedule job in our SQL 2000 box,
> normally it took 7 hours to complete but all of the sudden now it
> takes more than 20 hours.
> What do you think it cause this problem? We have no clue.

Have you checked general CPU and I/O activity on the server, using Perfmon?
There may be another process which is taking up resources, or perhaps it
could be a symptom of Slammer infection, if the MSSQL service has a very
high CPU load. You might also want to use Profiler to check if there's any
unusual activity within MSSQL itself, which might be creating problems.

Simon|||It could also be caused by other activity blocking the rebuilds, or badly
fragmented freespace if the indexes have grown.

On a seperate note, here's a link to a whitepaper you may want to read to
determine if reindexing everything is the right thing to do for your
particular situation.

http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

Regards

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:409a67f2$1_1@.news.bluewin.ch...
> "Frank" <soal6570@.yahoo.com> wrote in message
> news:42601b2.0405052143.66cdbe69@.posting.google.co m...
> > We have a re-indexing all DBs schedule job in our SQL 2000 box,
> > normally it took 7 hours to complete but all of the sudden now it
> > takes more than 20 hours.
> > What do you think it cause this problem? We have no clue.
> Have you checked general CPU and I/O activity on the server, using
Perfmon?
> There may be another process which is taking up resources, or perhaps it
> could be a symptom of Slammer infection, if the MSSQL service has a very
> high CPU load. You might also want to use Profiler to check if there's any
> unusual activity within MSSQL itself, which might be creating problems.
> Simon

reindex or update stat

Hi,
I have a database that will have couple thousands row inserted normally, but
there will be 3 or 4 days in a year that will have 20000 row inserted daily
. I set up the Maintenance Plan to have Optimizations to reorganize the data
and index, and it runs onc
e a week. During that busy week how can I imporve the performance? ThanksJen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||Thanks. In the past we did experience some problem in that few days. actuall
y it's about 60,000 in one table, and every record in this table will be dou
bled in another table, that is 120,000. How much will be the red line?
-- Andrew J. Kelly wrote: --
Jen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||That's imposable to tell without a breakdown of the data. Essentially if
the selectivity of the new data is about the same as the existing data it
will most likely not be any different. If it is drastically different there
may be some changes in the plans. But in either case if you have auto
update statistics turned on sql server should deal with it appropriately.
If not or you just don't want to leave it to chance you can simply issue an
update stats when your done with the import. If the data being imported is
going to cause a lot of page splits you may just want to reindex afterwards.
The number of rows is not the main deciding factor in if it will affect
performance. It's more the type of data, the schema and the way it is
imported.
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:DF02E261-55FC-4AB1-A170-FE1B51128260@.microsoft.com...
> Thanks. In the past we did experience some problem in that few days.
actually it's about 60,000 in one table, and every record in this table will
be doubled in another table, that is 120,000. How much will be the red line?
> -- Andrew J. Kelly wrote: --
> Jen,
> 20,000 rows is really not all that many and will most likely not
cause a
> change in the way the queries are run. It's possible but not enough
to get
> excited about. The once a week will most likely be fine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jen" <anonymous@.discussions.microsoft.com> wrote in message
> news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
normally,
> but there will be 3 or 4 days in a year that will have 20000 row
inserted
> daily. I set up the Maintenance Plan to have Optimizations to
reorganize the
> data and index, and it runs once a week. During that busy week how
can I
> imporve the performance? Thanks
>
>

reindex or update stat

Hi
I have a database that will have couple thousands row inserted normally, but there will be 3 or 4 days in a year that will have 20000 row inserted daily. I set up the Maintenance Plan to have Optimizations to reorganize the data and index, and it runs once a week. During that busy week how can I imporve the performance? ThanksJen,
20,000 rows is really not all that many and will most likely not cause a
change in the way the queries are run. It's possible but not enough to get
excited about. The once a week will most likely be fine.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> Hi,
> I have a database that will have couple thousands row inserted normally,
but there will be 3 or 4 days in a year that will have 20000 row inserted
daily. I set up the Maintenance Plan to have Optimizations to reorganize the
data and index, and it runs once a week. During that busy week how can I
imporve the performance? Thanks|||Thanks. In the past we did experience some problem in that few days. actually it's about 60,000 in one table, and every record in this table will be doubled in another table, that is 120,000. How much will be the red line?
-- Andrew J. Kelly wrote: --
Jen
20,000 rows is really not all that many and will most likely not cause
change in the way the queries are run. It's possible but not enough to ge
excited about. The once a week will most likely be fine
--
Andrew J. Kell
SQL Server MV
"Jen" <anonymous@.discussions.microsoft.com> wrote in messag
news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com..
> Hi
> I have a database that will have couple thousands row inserted normally
but there will be 3 or 4 days in a year that will have 20000 row inserte
daily. I set up the Maintenance Plan to have Optimizations to reorganize th
data and index, and it runs once a week. During that busy week how can
imporve the performance? Thank|||That's imposable to tell without a breakdown of the data. Essentially if
the selectivity of the new data is about the same as the existing data it
will most likely not be any different. If it is drastically different there
may be some changes in the plans. But in either case if you have auto
update statistics turned on sql server should deal with it appropriately.
If not or you just don't want to leave it to chance you can simply issue an
update stats when your done with the import. If the data being imported is
going to cause a lot of page splits you may just want to reindex afterwards.
The number of rows is not the main deciding factor in if it will affect
performance. It's more the type of data, the schema and the way it is
imported.
--
Andrew J. Kelly
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:DF02E261-55FC-4AB1-A170-FE1B51128260@.microsoft.com...
> Thanks. In the past we did experience some problem in that few days.
actually it's about 60,000 in one table, and every record in this table will
be doubled in another table, that is 120,000. How much will be the red line?
> -- Andrew J. Kelly wrote: --
> Jen,
> 20,000 rows is really not all that many and will most likely not
cause a
> change in the way the queries are run. It's possible but not enough
to get
> excited about. The once a week will most likely be fine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jen" <anonymous@.discussions.microsoft.com> wrote in message
> news:1FB99858-33E7-43A8-A561-51BD048C53F6@.microsoft.com...
> > Hi,
> > I have a database that will have couple thousands row inserted
normally,
> but there will be 3 or 4 days in a year that will have 20000 row
inserted
> daily. I set up the Maintenance Plan to have Optimizations to
reorganize the
> data and index, and it runs once a week. During that busy week how
can I
> imporve the performance? Thanks
>
>