Hi,

I have a stored Procedure im MS SQL 2000 and It runs normally. After
alter the table, it suddenly slow down and I have tried to run
sp_updatestats and there are no improvement on it. It runs on
Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating.

I have restored the database on other machine (Win2000/SQL2000) with
dual Xeno 700 Mhz and the performance of the stored procedure is okay.

Did anyone face similar problem and can share your expereience with me.
Thanks

Calvin

Calvin wrote:
> Hi,
>
> I have a stored Procedure im MS SQL 2000 and It runs normally. After
> alter the table, it suddenly slow down and I have tried to run
> sp_updatestats and there are no improvement on it. It runs on
> Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating.
>
> I have restored the database on other machine (Win2000/SQL2000) with
> dual Xeno 700 Mhz and the performance of the stored procedure is okay.
>
> Did anyone face similar problem and can share your expereience with
> me. Thanks
>
> Calvin

Please provide us with the DDL of the table and indexes, the query, and
what changes to you made to the table that caused the problem, as well
as the execution plan and execution plan after (or at least a
description of what you see as the differences between the two).

Thanks.


--
David Gugick
Imceda Software
www.imceda.com

you can use `profiler` to trace these sql , BTW, your stored procedure is
encryption ?

> I have a stored Procedure im MS SQL 2000 and It runs normally. After
> alter the table, it suddenly slow down and I have tried to run
> sp_updatestats and there are no improvement on it. It runs on
> Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating.
> I have restored the database on other machine (Win2000/SQL2000) with
> dual Xeno 700 Mhz and the performance of the stored procedure is okay.

Calvin (cal_fung@hotmail.com) writes:
> I have a stored Procedure im MS SQL 2000 and It runs normally. After
> alter the table, it suddenly slow down and I have tried to run
> sp_updatestats and there are no improvement on it. It runs on
> Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating.
>
> I have restored the database on other machine (Win2000/SQL2000) with
> dual Xeno 700 Mhz and the performance of the stored procedure is okay.
>
> Did anyone face similar problem and can share your expereience with me.

Since you did not provide any details whatsoever about the table, indexes
or the stored procedure, you can only get an answer in general terms.

SQL Server uses a cost-based optimizer. The optimzer evaluates different
query plans, and chooses the one with lowest *estimated* cost. (Actually,
it may not evaluate all plans, but pick one that looks "good enough", as
for a complex query, evaluating all possible plans would take too much
time.) The input for the estimations are the statistics saved about the
table. The statistics are samples of the data.

The problem with this is that small errors in samples or estimates can
give huge consequences when a plan contains many steps.

Two common casese where the optimizer often goes "wrong" is with parallelism
and the use of non-clustered indexes. With parallelism, the optimizer
appears to be overly optimistic about the benefits of parallelism, and
picks a parallel plan that is ineffecient. The problem with non-clustered
index is that they are often combined with a bookmark lookup to get the
data. If there are too many hits in the index, using the index will be
less effective than scanning the table. However, the optimizer appears
to overly pessimistic and often chooses a scan when the NC index would
have been better.

You can inspect the query plan by running the procedure in Query Analyzer,
if you first select Query->Display execution plan from the menu. You can
also use the Profiler to catch the query plan.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Could just be using an old query plan (as indicated by working on another
server). You can try running the procedure with recompile, or stopping and
starting the SQL Server service.

The reason for this type of behaviour is that SQL can keep old query plans
in memory and re-use them (when they are no longer efficient). The reason
for this is to prevent unnecessary recompilation of queries, but it does
mean that you can use old query plans (this is a trade-off...)

If this does not cure the problem, you will need to tune the query as
suggested in other replies.


"Calvin" <cal_fung@hotmail.com> wrote in message
news:1110511160.823117.123060@z14g2000cwz.googlegr oups.com...
> Hi,
>
> I have a stored Procedure im MS SQL 2000 and It runs normally. After
> alter the table, it suddenly slow down and I have tried to run
> sp_updatestats and there are no improvement on it. It runs on
> Win2000/SQL2000 with Dual Xeon 3GHz with Hyper-threating.
>
> I have restored the database on other machine (Win2000/SQL2000) with
> dual Xeno 700 Mhz and the performance of the stored procedure is okay.
>
> Did anyone face similar problem and can share your expereience with me.
> Thanks
>
> Calvin
>