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
Showing posts with label blocked. Show all posts
Showing posts with label blocked. Show all posts
Saturday, February 25, 2012
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||
Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
process block itself
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||
No, it isn't a DTS package but I have found where is problem...
I use a procedure like this to retry a block of istructions when i have an error
Procedure 2
.....
create table #tmp.....
declare @.n int
declare @.maxcicle int
declare @.cicle int
set @.n = 100
set @.maxcicle = 10
set @.cicle = 1
while @.n = 100
begin
begin try
begin transaction
--
-- instructions that use #tmp
--
commit tran
set @.n = 0
end try
begin catch
set @.cicle = @.cicle + 1
set @.n = 100
rollback transaction
if @.clicle > @.maxcicle
begin
raise error
end
waitfor delay '00:00:10'
end catch
end
end
I call this procedure from Procedure 1
procedure 1
begin try
begin transaction
exec procedure 2
commit transaction
end try
begin catch
rollback transaction
end catch
end
when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.
Only to restart SQL Server service process was destroy becouse tempdb restart.
Thanks
Luca
|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.
-Sue
Subscribe to:
Posts (Atom)