|
|
Posted by Erland Sommarskog on 06/14/06 21:51
Cray (cray@nospam.o2.pl) writes:
> On the database we have stored procedure that loads couple thousands
> rows with information, it has to be done sequentially because during
> this load various variables and values has to be changed and modified.
> We have about 10000 rows to process every day - usually it takes about 1
> hour.
>
> Some time ago I have noticed a big decrease in performance of this
> process (it takes about 10 to 20 more time to process 10K), profiler
> showed me that duration of each read from some of the tables is huge and
> this is the reason why this process slows down. I have also a
> maintenance plan that updates statistics. When I run this job during
> processing usually everything goes back to normal and reads are very
> fast, performance is ok.
>
> Could someone explain why this is happening or give me a hint where I
> could find the explanation/answer.
If something triggers a recompile, and the statistics are out of date
this can lead to a bad plan.
A typical example of situation where you quickly can run into bad
statistics is with monotonically growing keys, were queries are on
the most recenly inserted rows. The statistics may make it seem than
an interval is almost empty, when there are in fact plenty of rows.
It's difficult to give suggestions for how to address this without more
information about the application. Possible methods involve query
hints, trying to prevent recompiles, and running UPDATE STATISTCS on
selected indexes more frequently.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|