déterminer sir des processus sont bloqués

Pascal

14/09/2005 à 16:46

"jbw" wrote in message
news:
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

J'ai trouvé :

use master select spid, case blocked when 0 then 'blocking' else
'blocked' end as status from sysprocesses with (nolock) where spid > 10
and ( blocked <> 0 or spid in (select blocked from sysprocesses with
(nolock) where blocked <> 0) ) order by blocked

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/68f188cfb1357fe9

Pascal

Bouarroudj Mohamed

14/09/2005 à 17:02

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a partir
d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail :
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID =
'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created in
dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" wrote in message
news:
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a partir
d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail : mbouarroudj@sqldbtools.com
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID =
'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created in
dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" <jean-baptiste.watremez@jci.com> wrote in message
news:1126703048.608653.233300@g47g2000cwa.googlegroups.com...
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

Vous avez filtré cet utilisateur ! Consultez son message

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a partir
d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail :
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID =
'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created in
dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" wrote in message
news:
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

jbw

14/09/2005 à 18:01

Merci beaucoup ! Je pense que c'est effectivement ce que je cherchais.

J'avais pourtant cherché ici, mais je n'avais pas trouvé ce script.

Etant donné qu'il n'y a actuellement aucun SPID de bloqué dans ma
base, ce petit script ne m'a retourné aucun enregistrement. Ce qui,
quelque part, est plutôt bon signe.

Par contre, pour combler ma curiosité, si tu voulais (toi ou un autre)
bien m'expliquer comment ce script en arrive à ses fins, je t'en serai
reconnaissant. En effet, quand je le lis, dès le cinquième mot, je
commence progressivement à perdre pied...

Ce que je crois comprendre :
-Qu'il retourne les SPID bloqués ou bloquant.
-Que le champ "Status" est formatté pour indiquer si le psus est en
effet bloqué ou bloquant.
-Que les SPID sont affichés dans l'ordre de blocage selon la cascade
bloquante (le 1er de la liste est le bloquant).
-Que la récupération de ces SPID se fait de façon "dirtyread" (with
nolock ?!).

Mes questions :
-Pourquoi en "with nolock" ?
-Pourquoi "where spid >10" ?
-Si "blocked <> 0" Alors pourquoi "case blocked whene 0 then..." ?

Juste pour m'éclairer, si vous pouviez me corriger et/ou me
compléter...

Encore merci.

Jbat.

Jérôme Campo

15/09/2005 à 11:28

Bonjour,

Il existe aussi une procédure stockée fournit par le Support Microsoft
sp_blocker_pss80 , cf.
http://support.microsoft.com/default.aspx?kbid'1509 - How to monitor SQL
Server 2000 blocking

En fait il faut déclencher la sp avec un lag de 10 ou 15 s (en dessous, ça
peut pas mal ralentir la machine), puis ensuite rechercher dans l'ouput des
chaines caractères du genre blocking. C'est un peu fastidieux mais ça
fonctionne.

Sinon, de manière rapide, il faut execute les procédures sp_who (équivalent
de Gestion/ActivitésEnCours dans Enterprise Manager) ou sp_who2 pour voir
les spid, sp_lock pour voir les locks. Utiliser DBCCINPUTBUFFER(spid) pour
voir quel ordre execute un spid.

Maintenant, la sp de Mohamed a l'air très bien, je la testerai dès que
l'occasion se présentera ;-)

Cordialement,

--
Jérôme Campo -
SQL Regional Program Manager - Microsoft France

"Bouarroudj Mohamed" wrote in message
news:

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a
partir d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail :
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid,
@cpu, @cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID
= 'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created
in dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" wrote in message
news:
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

Bonjour,

Il existe aussi une procédure stockée fournit par le Support Microsoft
sp_blocker_pss80 , cf.
http://support.microsoft.com/default.aspx?kbid'1509 - How to monitor SQL
Server 2000 blocking

En fait il faut déclencher la sp avec un lag de 10 ou 15 s (en dessous, ça
peut pas mal ralentir la machine), puis ensuite rechercher dans l'ouput des
chaines caractères du genre blocking. C'est un peu fastidieux mais ça
fonctionne.

Sinon, de manière rapide, il faut execute les procédures sp_who (équivalent
de Gestion/ActivitésEnCours dans Enterprise Manager) ou sp_who2 pour voir
les spid, sp_lock pour voir les locks. Utiliser DBCCINPUTBUFFER(spid) pour
voir quel ordre execute un spid.

Maintenant, la sp de Mohamed a l'air très bien, je la testerai dès que
l'occasion se présentera ;-)

Cordialement,

--
Jérôme Campo - jcampo@online.microsoft.com
SQL Regional Program Manager - Microsoft France

"Bouarroudj Mohamed" <mbouarroudj@yahoo.com> wrote in message
news:Oap9m1TuFHA.664@tk2msftngp13.phx.gbl...

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a
partir d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail : mbouarroudj@sqldbtools.com
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid,
@cpu, @cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID
= 'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created
in dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" <jean-baptiste.watremez@jci.com> wrote in message
news:1126703048.608653.233300@g47g2000cwa.googlegroups.com...
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

Vous avez filtré cet utilisateur ! Consultez son message

Bonjour,

Il existe aussi une procédure stockée fournit par le Support Microsoft
sp_blocker_pss80 , cf.
http://support.microsoft.com/default.aspx?kbid'1509 - How to monitor SQL
Server 2000 blocking

En fait il faut déclencher la sp avec un lag de 10 ou 15 s (en dessous, ça
peut pas mal ralentir la machine), puis ensuite rechercher dans l'ouput des
chaines caractères du genre blocking. C'est un peu fastidieux mais ça
fonctionne.

Sinon, de manière rapide, il faut execute les procédures sp_who (équivalent
de Gestion/ActivitésEnCours dans Enterprise Manager) ou sp_who2 pour voir
les spid, sp_lock pour voir les locks. Utiliser DBCCINPUTBUFFER(spid) pour
voir quel ordre execute un spid.

Maintenant, la sp de Mohamed a l'air très bien, je la testerai dès que
l'occasion se présentera ;-)

Cordialement,

--
Jérôme Campo -
SQL Regional Program Manager - Microsoft France

"Bouarroudj Mohamed" wrote in message
news:

select * from master.dbo.sysprocesses with(nolock)
where blocked <> 0

Vous pouvez aussi utiliser mon script ci-dessous.
Il creer une stored procedure "usp_BlockedProcessTrace" qui log les
"processes" bloqués (depuis un temps x) dans une table
dbo.BlockedProcessTrace, vous devez executer cette stored procedure a
partir d'un SQL Job a un intervalle regulier
(voir deploiement a la fin du script)

Vous pouvez aussi changer facilement la SP usp_BlockedProcessTrace pour
qu'elle log les resultats sur l'ecran au lieu d'une table

--- debut du script
use master -- ou utiliser une autre BD dedié
go

--- 1. create working table
if Object_id('dbo.BlockedProcessTrace') is null
begin
print 'create table dbo.BlockedProcessTrace'
create table dbo.BlockedProcessTrace
(
ID int identity(1,1) not null,
creationdate datetime,
spid smallint,
blocked1 smallint,
blocked2 smallint,
waittime int,
waittype binary,
lastwaittype nchar(32),
waitresource nchar(256),
dbid smallint,
cpu int,
physical_io bigint,
memusage int,
cmd varchar(1000),
loginame nchar(128),
open_tran smallint,
QueryBlocked varchar(255),
QueryBlockedBy1 varchar(255),
QueryBlockedBy2 varchar(255)
)
end
go

--- 2. create stored procedure 'dbo.usp_BlockedProcessTrace

if object_id('dbo.usp_BlockedProcessTrace') is not null
drop proc dbo.usp_BlockedProcessTrace
go

Create Proc dbo.usp_BlockedProcessTrace
(
@WaitTimeInSeconde int
)
as

/*
*********************************************************************
Description : Log blocked processes in dbo.BlockedProcessTrace table
Author : Bouarroudj Mohamed
E-mail :
Date : November 2004
*********************************************************************
*/

set nocount on

---------------------------------------------------------------------
-- Declarations
---------------------------------------------------------------------

declare
@Query varchar(50),
@CurrentDate datetime,
@QueryBlocked varchar(255), -- the Query blocked
@QueryBlockedBy1 varchar(255), -- The Query that blocks the 1st one
@QueryBlockedBy2 varchar(255), -- The Query that blocks the seconde
one, generally NULL
@spid smallint,
@blocked1 smallint,
@blocked2 smallint,
@waittime int,
@waittype binary,
@waitresource nchar(256),
@dbid smallint,
@cpu int,
@cmd varchar(1000),
@loginame nchar(128),
@open_tran smallint,
@lastwaittype nchar(32),
@physical_io bigint,
@memusage int

---------------------------------------------------------------------
-- Initializations
---------------------------------------------------------------------

set @CurrentDate = GetDate()

create table #dbc
(
EventType varchar(15),
Parameters int,
EventInfo varchar(255)
)

if @WaitTimeInSeconde is null
set @WaitTimeInSeconde = 60 * 1000 -- 1 minute
else
set @WaitTimeInSeconde = @WaitTimeInSeconde * 1000 -- convert to ms

---------------------------------------------------------------------
-- Processing
---------------------------------------------------------------------

declare processes_cursor cursor fast_forward
for
select T1.spid, T1.blocked, T1.waittime, T1.waittype, T1.waitresource,
T1.dbid, T1.cpu, T1.cmd, T1.loginame,
T1.open_tran, T1.lastwaittype, T1.physical_io, T1.memusage,
T2.blocked
from master.dbo.sysprocesses T1 with(nolock)
join master.dbo.sysprocesses T2 with(nolock) on T2.spid = t1.blocked
where T1.blocked <> 0
and T1.waittime > @WaitTimeInSeconde

open processes_cursor

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid, @cpu,
@cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2
while (@@fetch_status <> -1)
begin
delete from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@spid as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlocked = EventInfo from #dbc

set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked1 as varchar(10)) + ')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy1 = EventInfo from #dbc

if (@blocked2 > 0 and @blocked2 is not null)
begin
set @Query = 'DBCC INPUTBUFFER(' + Cast(@blocked2 as varchar(10)) +
')'
insert #dbc EXEC(@Query)
select @QueryBlockedBy2 = EventInfo from #dbc
end

insert into dbo.BlockedProcessTrace
(
creationdate,
spid,
blocked1,
blocked2,
waittime,
waittype,
lastwaittype,
waitresource,
dbid,
cpu,
physical_io,
memusage,
cmd,
loginame,
open_tran,
QueryBlocked,
QueryBlockedBy1,
QueryBlockedBy2
)

values
(
@CurrentDate,
@spid,
@blocked1,
@blocked2,
@waittime,
@waittype,
@lastwaittype,
@waitresource,
@dbid,
@cpu,
@physical_io,
@memusage,
@cmd,
@loginame,
@open_tran,
@QueryBlocked,
@QueryBlockedBy1,
@QueryBlockedBy2
)

fetch next from processes_cursor
into @spid, @blocked1, @waittime, @waittype, @waitresource, @dbid,
@cpu, @cmd, @loginame,
@open_tran, @lastwaittype, @physical_io, @memusage, @blocked2

end

close processes_cursor
deallocate processes_cursor
go

/*

Deployement : We suggest the creation of SQL Job that run every x minutes
Test :

1. Create SQL Job or run the following script

use master

while 1=1
begin
exec dbo.usp_BlockedProcessTrace @WaitTimeInSeconde = 1
WAITFOR DELAY '00:01:00'
end

2. open new window in Query Analyser and run :

use northwind

begin tran
update dbo.Customers set ContactName = 'M. Maria Anders' where CustomerID
= 'ALFKI'
--commit

3. open new window in Query Analyser and run :

use northwind

begin tran
select * from dbo.Customers

4. the process will be blocked and after 1 minute a new entry is created
in dbo.BlockedProcessTrace table

select * from master.dbo.BlockedProcessTrace

*/

"jbw" wrote in message
news:
Salut,

Je cherche à savoir via une requette si des SPID sont en état
bloqué.

Dans entreprise manager, on voit ça dans
Gestion/ActivitésEnCours/VerrousIdDeProcessus. Ceux qui sont bloqués
apparaissent avec un petit carré ou cadenas rouge.

Par avance, merci.

jbat

Pascal

15/09/2005 à 15:21

"jbw" wrote in message
news:

Par contre, pour combler ma curiosité, si tu voulais (toi ou un autre)
bien m'expliquer comment ce script en arrive à ses fins, je t'en serai
reconnaissant. En effet, quand je le lis, dès le cinquième mot, je
commence progressivement à perdre pied...

C'est la CASE qui fait perdre pied ?
C'est simplement une fonction d'évaluation.

Le script recherche la liste des bloqués et des bloquants.

Mes questions :
-Pourquoi en "with nolock" ?

Dans ce cas je ne vois pas bien l'interêt.

-Pourquoi "where spid >10" ?

Je me suis posé la question, 50 me semble plus approprié pour filtrer les
process system

-Si "blocked <> 0" Alors pourquoi "case blocked whene 0 then..." ?

Il y a un OR
blocked <> 0 or spid in ...
Le case sert juste à evaluer le 'blocked ' et retourne qq chose de plus
compréhensible.

Juste pour m'éclairer, si vous pouviez me corriger et/ou me
compléter...

Un peu d'explication ici:
http://www.microsoft.com/technet/technetmag/issues/2005/05/SystemTables/default.aspx

Pascal

jbw

15/09/2005 à 17:06

Re-salut,

Le script que m'a fourni Pascal me semble tout à fait convenir à mon
besoin. Il est simple et rapide.

Par contre, un collègue m'a dit qu'il risquait de détecter non
seulement les PSID bloqués indéfiniments (c'est ceux-là que je
souhaite détecter) mais aussi ceux bloqués temporairement dans le
cadre d'un fonctionnement ordinaire lié à la concurence d'accès
(ceux-là ne m'interessent pas).

Qu'en est-il selon vous ?

Jbat.

PS : Voici le script de pascal :
use master select spid, case blocked when 0 then 'blocking' else
'blocked' end as status from sysprocesses with (nolock) where spid > 10
and ( blocked <> 0 or spid in (select blocked from sysprocesses with
(nolock) where blocked <> 0) ) order by blocked

ps2 : merci a tous les autres qui m'ont proposés d'autres solutions.
;-)

Pascal

15/09/2005 à 18:11

"jbw" wrote in message
news:
Re-salut,

Tu peux connaître différent temps :

use master
select spid,
case blocked when 0 then 'blocking' else 'blocked' end as status ,
datediff(second,login_time, getdate()) as ConnectedSeconds ,
[waittime],
[lastwaittype]
from sysprocesses with (nolock) where spid > 10
and ( blocked <> 0 or spid in (select blocked from sysprocesses with
(nolock) where blocked <> 0) ) order by blocked

Et évaluer ce qu'il faut faire, attention je pense que le waittime est en
ms.

Sinon la solution de Mohamed semble bonne aussi.

Pascal

Bouarroudj Mohamed

16/09/2005 à 15:16

Le fait qu'un process soit bloqué n'est pas mauvais en soit, SQL Server
utilise plusieurs mécanique pour bloquer les ressources afin de garantir
l'INTEGRITE de votre base de données, voir la section "Understanding Locking
in SQL Server" du BOL.

Généralement les drivers de connexion a la BD ont un time-out par défaut
(~3min), donc c'est rare qu'un process soit bloqué indéfiniment sinon il
faut réviser votre "design".

Vous pouvez ajouter comme le propose Pascal un filtre de type "and waittime
> ? (valeur en ms), c'est a vous de juger quel est le temps acceptable qu'un
process soit bloquant/bloqué. SQL Server détecte les situations de DeadLock
pour les situations de Lock indéfiniment il faut specifier un seuil avec la
commande
SET LOCK_TIMEOUT

"Pascal" wrote in message
news:23hWe.12715$

"jbw" wrote in message
news:
Re-salut,

Tu peux connaître différent temps :

use master
select spid,
case blocked when 0 then 'blocking' else 'blocked' end as status ,
datediff(second,login_time, getdate()) as ConnectedSeconds ,
[waittime],
[lastwaittype]
from sysprocesses with (nolock) where spid > 10
and ( blocked <> 0 or spid in (select blocked from sysprocesses with
(nolock) where blocked <> 0) ) order by blocked

Et évaluer ce qu'il faut faire, attention je pense que le waittime est en
ms.

Sinon la solution de Mohamed semble bonne aussi.

Pascal

déterminer sir des processus sont bloqués

8 réponses

Veuillez sélectionner un problème