Re: Slow UPDATE and DELETE on SQL Server 2000
From: Doron Hadar (hadardo_at_bezeqint.net)
Date: 11/08/04
- Next message: Aaron [SQL Server MVP]: "Re: Problems with carating Stored Procedure from ADO"
- Previous message: Dmitry: "Problems with carating Stored Procedure from ADO"
- In reply to: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Next in thread: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Reply: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Messages sorted by: [ date ] [ thread ]
Date: 8 Nov 2004 07:18:21 -0800
Hello Anthony.
First, I've ruled out blocking issues as well as triggers,
contstraints and indexes.
Second, in response to your second reply, I didn't claim that the
second update was more complicated. I simply noted that an UPDATE
within a table takes much less time than an UPDATE between tables.
Also, both tables are on the same database, so there is no problem of
linked servers. There is no problem of fragmentation or density.
I took it even further than you suggested. I created two new
databases: one on the new machine and one on the old machine (by the
way, the old machine doesn't have any service pack installed). I
copied the same tables to each of the databases and created keys and
indexes on each machine seperatly. Then I created the plan to the same
UPDATE query as you suggested. I got two different plans!
The plan from the old machine was more complex and has PARALLELISM
(Repartion Streams and Partition columns) while the plan from the new
machine doesn't have them. There is no need to mention that the update
procedure in the new machine still takes 25 hours (any way, I think it
will since I killed the process after two hours and the DiskIO
progress didn't look different from the one I saw previously) while it
takes less then an hour on the old machine.
Everything here suggests that SP3 causes the problem. I can't think of
anything eles.
Doron
"AnthonyThomas" <AnthonyThomas@discussions.microsoft.com> wrote in message news:<0C5B698E-873D-487E-B273-D66891DA4461@microsoft.com>...
> I'm still pondering this one and came up with a few more questions.
>
> The fact that some of the other T-SQL is working as advertised and better
> than your old machine seems to indicate that there is nothing particularly
> wrong with the SQL Server installation and unlikely that there are sub-system
> hardware issues.
>
> As far as your two different UPDATE statements, you claim the second is more
> "complex." That is true as far as the value you are computing to update
> with; however, it is a simple table update. The one you are having
> difficulty with is more complex in the JOIN--it is a multiple table update,
> or, better, a conditional update.
>
> There is something going on with the join. I've only seen this sort of
> behavior when joining to remote sources. By chance, are you using a
> linked-server for the source of your data? In that case, SQL Server will
> have to retreive, or push depending on which server is linked versus where
> the command is being executed, the entire table result set into a work table
> in TempDB BEFORE the JOIN and/or any restriction criteria if the
> linked-server driver parameters are not properly set.
>
> I'd also look at the destination table. Run DBCC SHOWCONTIG against it to
> see if it needs to be reoranized and possibly change the FILL FACTOR and FREE
> SPACE parameters.
>
> Another piece of information would be helpful: execute the SET NOEXEC option
> and SET SHOWPLAN ALL, SET STATISTICS TIME and IO options. This should help
> us get a better idea of what the Optimizer is attempting to do.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
>
- Next message: Aaron [SQL Server MVP]: "Re: Problems with carating Stored Procedure from ADO"
- Previous message: Dmitry: "Problems with carating Stored Procedure from ADO"
- In reply to: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Next in thread: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Reply: AnthonyThomas: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
