Process blocking itself

Im ahving a problem running a sql server 2000 query, in that the
process is blocked by itself and doesn't finish, even after waiting 20
hours for it.
The table has 40 million records and im running something like :
UPDATE table SET field2 = db.myfunction(field1)
The function isnt doing anthing with tables, it justs does some string
manipulation on the parameter string.
Someone said it might be dur to parallelism (multiple processes
running at the same time causing the process to lock up / block
itself), but even if i try :
UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
it doesnt make any difference.
Hope someone can help, thanks,
PaulHow do you know that the process is "blocked by itself"? Are you seeing
this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
and of itself, is not a cause for concern, it is just a change in the way
sysprocesses reported waits (I think it started working this way in 2000
SP4). Please see the following KB article for more information:
http://support.microsoft.com/kb/906344
"puma75" <paulroskilly@.gmail.com> wrote in message
news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
>|||On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How do you know that the process is "blocked by itself"? Are you seeing
> this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> and of itself, is not a cause for concern, it is just a change in the way
> sysprocesses reported waits (I think it started working this way in 2000
> SP4). Please see the following KB article for more information:
> http://support.microsoft.com/kb/906344
> "puma75" <paulroski...@.gmail.com> wrote in message
> news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
>
> > Im ahving a problem running a sql server 2000 query, in that the
> > process is blocked by itself and doesn't finish, even after waiting 20
> > hours for it.
> > The table has 40 million records and im running something like :
> > UPDATE table SET field2 = db.myfunction(field1)
> > The function isnt doing anthing with tables, it justs does some string
> > manipulation on the parameter string.
> > Someone said it might be dur to parallelism (multiple processes
> > running at the same time causing the process to lock up / block
> > itself), but even if i try :
> > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> > it doesnt make any difference.
> > Hope someone can help, thanks,
> > Paul- Hide quoted text -
> - Show quoted text -
Thanks.
In Enterprise Manager under Management > Current Activity > Locks/
Process Id it says "spid 73 (blocked by 73)"
It just seems very slow that it hasn't finished after 20 hours and it
didnt seem to be doing anything, little cpu usage, so thinking that it
was deadlocked seemed like a good explanation, must maybe not.|||> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
It's certainly not deadlocking because otherwise SQL Server would have
detected it and terminated one of the deadlock participants.
Perhaps, the UPDATE is not the only SQL statement, and there is still
runaway parallelism going on. I'd check the query plan to be see whether
there is any prallelism.
Linchi
"puma75" wrote:
> On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > How do you know that the process is "blocked by itself"? Are you seeing
> > this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> > and of itself, is not a cause for concern, it is just a change in the way
> > sysprocesses reported waits (I think it started working this way in 2000
> > SP4). Please see the following KB article for more information:
> >
> > http://support.microsoft.com/kb/906344
> >
> > "puma75" <paulroski...@.gmail.com> wrote in message
> >
> > news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> >
> >
> >
> > > Im ahving a problem running a sql server 2000 query, in that the
> > > process is blocked by itself and doesn't finish, even after waiting 20
> > > hours for it.
> >
> > > The table has 40 million records and im running something like :
> >
> > > UPDATE table SET field2 = db.myfunction(field1)
> >
> > > The function isnt doing anthing with tables, it justs does some string
> > > manipulation on the parameter string.
> >
> > > Someone said it might be dur to parallelism (multiple processes
> > > running at the same time causing the process to lock up / block
> > > itself), but even if i try :
> >
> > > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> >
> > > it doesnt make any difference.
> >
> > > Hope someone can help, thanks,
> >
> > > Paul- Hide quoted text -
> >
> > - Show quoted text -
> Thanks.
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
>
>|||On Oct 8, 1:32 pm, puma75 <paulroski...@.gmail.com> wrote:
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
a few suggestions:
- check if you have triggers on that table.
- try to do your updates in batches, of, say, 10K rows.|||Aaron Bertrand [SQL Server MVP] wrote:
> > How do you know that the process is "blocked by itself"? Are you
> > seeing this in sysprocesses, or are you guessing? Usually a spid
> > self-blocking, in and of itself, is not a cause for concern, it is
> > just a change in the way sysprocesses reported waits (I think it
> > started working this way in 2000 SP4). Please see the following KB
> > article for more information:
> >
> > http://support.microsoft.com/kb/906344
puma75 wrote:
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
In this case, I think it's exactly what Aaron said: you are seing that
the process is waiting for I/O or network latches (belonging to that
process). I bet the query is accessing a lot of data and the HDD led is
light-up almost contiuously.
Try to rephrase the query (to optimize it) or try adding appropriate
indexes (if necessary). Also, try updating the statistics before
running the query.
--
Razvan Socol
SQL Server MVP