Odd query problem

All,
Sql 2000, sp3, Server 2000 sp4
I have 2 servers, a production and a warm standby that I
use for some reporting. The problem I am having is that on
my production server, execution plan is showing a 55% use
for a sort. That same sort is not on the standby server.
The standby completes queries in 10% of the time on
production. They are not identical servers, my production
is a much faster machine. This problem has just started in
the last week. I have done update statistics for all the
tables in the db. I do indexdefrags about once a month, my
db is 350 gbs in size. There is one table that I have not
touched at all yet, there are 1.5 billion rows in this
table. Any other ideas, before I tackle this table.
Interesting note, if I do a select top 100 on production
the ep is similar to the one on my standby. Any reasons?
Thanks in advance all,
Gary AbbottProbably due to other load and use on your production server. You can try
clearing data and procedure cache on the production box (NOTE: This may
cause a severe performance bottleneck if your server is experiencing any
kind of load. Do this during way-off-peak hours only).
To clear data cache: DBCC DROPCLEANBUFFERS
To clear procedure cache: DBCC FREEPROCCACHE
If you are comparing query performance on two different systems, this is the
only way to make sure current cache state doesn't overly influience the
outcomes.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Gary Abbott" <anonymous@.discussions.microsoft.com> wrote in message
news:ed1901c3f0c0$0fe1f700$a501280a@.phx.gbl...
> All,
> Sql 2000, sp3, Server 2000 sp4
> I have 2 servers, a production and a warm standby that I
> use for some reporting. The problem I am having is that on
> my production server, execution plan is showing a 55% use
> for a sort. That same sort is not on the standby server.
> The standby completes queries in 10% of the time on
> production. They are not identical servers, my production
> is a much faster machine. This problem has just started in
> the last week. I have done update statistics for all the
> tables in the db. I do indexdefrags about once a month, my
> db is 350 gbs in size. There is one table that I have not
> touched at all yet, there are 1.5 billion rows in this
> table. Any other ideas, before I tackle this table.
> Interesting note, if I do a select top 100 on production
> the ep is similar to the one on my standby. Any reasons?
> Thanks in advance all,
> Gary Abbott|||you need to provide more info on the query, relevent
indexes in each db, and the execution plan details,
if there is a difference in the execution plan, then
either one has difference indexes or the statistics are
different, what does the execution plan show for estimated
row count in each case?
>--Original Message--
>All,
>Sql 2000, sp3, Server 2000 sp4
>I have 2 servers, a production and a warm standby that I
>use for some reporting. The problem I am having is that
on
>my production server, execution plan is showing a 55% use
>for a sort. That same sort is not on the standby server.
>The standby completes queries in 10% of the time on
>production. They are not identical servers, my production
>is a much faster machine. This problem has just started
in
>the last week. I have done update statistics for all the
>tables in the db. I do indexdefrags about once a month,
my
>db is 350 gbs in size. There is one table that I have not
>touched at all yet, there are 1.5 billion rows in this
>table. Any other ideas, before I tackle this table.
>Interesting note, if I do a select top 100 on production
>the ep is similar to the one on my standby. Any reasons?
>Thanks in advance all,
>Gary Abbott
>.
>