Re: Slow UPDATE and DELETE on SQL Server 2000
From: AnthonyThomas (AnthonyThomas_at_discussions.microsoft.com)
Date: 11/05/04
- Next message: Adam Machanic: "Re: Speeding up like queries"
- Previous message: John Ryan: "Re: MS Acces to SQL issues"
- In reply to: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Next in thread: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Reply: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 5 Nov 2004 09:58:02 -0800
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
"Doron Hadar" wrote:
> Hello Anthony.
> Thanks for the quick reply. Good to know that people really care.
> Well, actually we've checked some of your recommendations prior to my
> post (I suppose I forgot to mention them or thought they were not that
> important):
> 1. There are only a few clients to that specific machine, and none of
> them was connected during submiting these queries.
> 2. The queries were made with the Query Analyzer while building the
> database directly on the server machine.
> 3. The recovery mode was set to SIMPLE before hand.
> 4. The CPU counter, using the TASK MANEGER (and I think that's another
> odd part) was extreemly low - about 2% (while in other statements,
> like plain SELECT or INSERT, it rose up sharply). The Memory usage
> seemed normal (About 50%).
> 5. When I checked the running processes, using sp_who2 procedure, only
> the UPDATE statement was running (well, crawling is the right word)
> and the PageIOLatch seemed to take some resources (WAIT).
>
> About your other questions and recommendations, I'm not at work today,
> so it will have to wait till next week. I'll check them first thing
> next week and post them in this thread.
>
> Thanks again,
> Doron Hadar.
>
> "AnthonyThomas" <AnthonyThomas@discussions.microsoft.com> wrote in message news:<50E306D7-400A-4426-B603-10A386ADD636@microsoft.com>...
> > Looks like you've ruled out most of the posibilities I was thinking of,
> > except one: blocking.
> >
> > Here's a few things to consider:
> >
> > 1. What was the estimated cost on the query plan you examined?
> >
> > 2. Put the database in single-user mode. If it runs quicker, much quicker,
> > then concurrency seems to be the issue. I'd check to see if someone has set
> > the LOCK_TIMEOUT property to 0 (infinite).
> >
> > 3. Change the Recovery Model to SIMPLE.
> >
> > 4. Take a look at master.dbo.sysprocesses and master.dbo.syslockinfo while
> > the process is running. Any waits? What types? What duration? What
> > resources?
> >
> > 5. Where does the call originate from? Stored Procedure? Client or
> > Application server?
> >
> > 6. Have you looked at any of the SQL Server perfmon counters while the
> > process was running?
> >
> > 7. What about CPU, Memory, or Disk counters?
> >
> > Until we know if it is waiting and what on or what the hardware is doing at
> > the time, there's not much to go on.
> >
> > Waiting for your reply.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
>
- Next message: Adam Machanic: "Re: Speeding up like queries"
- Previous message: John Ryan: "Re: MS Acces to SQL issues"
- In reply to: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Next in thread: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Reply: Doron Hadar: "Re: Slow UPDATE and DELETE on SQL Server 2000"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
