Home > Articoli > SP_WHO3 – Evoluzione della store procedure SP_WHO2

SP_WHO3 – Evoluzione della store procedure SP_WHO2

SP_WHO2 è la store di SQL Server utilizzata per conoscere in tempo reale il numero di connessioni attive, il numero di utenti connessi, l’indicazione dell’ultimo processo eseguito per ogni connessione, l’indicazione del client da cui arriva la connessione e anche alcune informazioni sull’uso del tempo di CPU e di operazioni di I/O richieste da parte del processo stesso.

L’informazione che mi è sempre mancata durante utilizzo dellla SP_WHO2 è conoscere quale fosse il comando T-SQL eseguito da ogni processo tracciato.  Questa mancanza emergeva, soprattutto in contesti in cui dovevo monitorare l’andamento di un’istanza di SQL Server e mi accorgevo che alcuni processi in stavano consumando un elevato tempo di CPU e operazioni di I/O particolamente onerose. (Lo so esiste anche il profiler, ma quando vedi una query che sta molti secondi in stato "running" e non capisci come mai, il profiler non è molto utile e vorresti qualcosa di più immediato).

Per questo motivo ho iniziato a documentarmi sulla composizione delle tabelle di sistema di SQL Server in cui i dati esposti dalla SP_WHO2 vengono recuperati e parallelamente ho trovato su internet alcune versioni modificate della stessa.

Partendo da questi esempi e con gli approfondimenti necessari sulle tabelle di sistema ho creato la mia versione della store che metto a disposizione degli utenti di UGISS.

Il codice della mia store SP_WHO3:

—————————————————————————
— Autore: Daniele Medone
— Data: 19/07/2007
— Desc: evoluzione della store sp_who2 con l’aggiunta della descrizione
—    del comando/query in esecuzione per ogni processo.
—————————————————————————

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_who3] — 2007/07/19 10:16
@loginame sysname = NULL
as

set nocount on

declare
@retcode int

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
,@batch varchar(4000)
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
,@charMaxLenBatchTSQL varchar(4000)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)

——–

select
@retcode = 0 — 0=OK ,1=KO.

——–defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

————————————————————–
IF (@loginame IS NULL)
GOTO LABEL_17PARM1EDITED

——–

select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL)
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end

——–

IF (lower(@loginame) IN (‘active’))
begin
select @loginame = lower(@loginame)
GOTO LABEL_17PARM1EDITED
end

——–

IF (patindex (‘%[^0-9]%’ , isnull(@loginame,’z’)) = 0)
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end

——–

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN

LABEL_17PARM1EDITED:
——————————————————————————–
–Aggiunta per BatchTSQL
——————————————————————————–
–Restituisce una tabella con SPID e Last batch

–Tabella temporanea per salvare dbcc inputbuffer
create table #InpBuf(
i int identity,
Eventtype nvarchar(30),
Parameters int,
Eventinfo nvarchar(4000),
)

–Tabella contenente risultati
create table #result(
spid1 varchar(11),
event nvarchar(4000)
)

declare
@spid varchar(11)
–Cursore che restituisce gli spid dei processi sul server
declare
#p cursor for
select spid from master.dbo.sysprocesses where dbid<>0

OPEN #p
FETCH NEXT
FROM #p
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #InpBuf
exec (‘dbcc inputbuffer(‘+ @spid +’) WITH NO_INFOMSGS’)
insert into #result
select @spid ,eventinfo from #InpBuf
where i=(select max(i) from #InpBuf)
FETCH NEXT
FROM #p
INTO @spid
END
close #p
deallocate #p
if (object_id(‘tempdb..#InpBuf’) is not null)
drop table #InpBuf
——————————————————————
——————– Processi in sysprocesses. ——————-
——————————————————————

SELECT

spid
,status
,sid
,hostname
,program_name
,cmd
,event
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname,spid as ‘spid_sort’

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ‘ ‘
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as ‘last_batch_char’

INTO #tb1_sysprocesses
from master.dbo.sysprocesses(nolock)
left outer join #result
on
master.dbo.sysprocesses.spid=#result.spid1
———————————————————————

——– Elimino dall’output i processi relativi a comandi di sistema?

IF (@loginame IN (‘active’))
DELETE #tb1_sysprocesses
where lower(status) = ‘sleeping’
and upper(cmd) IN (
‘AWAITING COMMAND’
,’MIRROR HANDLER’
,’LAZY WRITER’
,’CHECKPOINT SLEEP’
,’RA MANAGER’
)

and blocked = 0

 

——– Conversione dei campi in varchar per ridimensionamento della larghezza delle colonne

Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)

 

SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenBatchTSQL =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(4000),event)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
— sid >= @sidlow
— and sid <= @sidhigh
— and
spid >= @spidlow
and spid <= @spidhigh

 

——–Output del risultato.

EXECUTE(

SET nocount off

SELECT
SPID = convert(char(5),spid)

,Status =
CASE lower(status)
When ”sleeping” Then lower(status)
Else upper(status)
END

,Login = substring(loginname,1,’ + @charMaxLenLoginName + ‘)

,HostName =
CASE hostname
When Null Then ” .”
When ” ” Then ” .”
Else substring(hostname,1,’ + @charMaxLenHostName + ‘)
END

,BlkBy =
CASE isnull(convert(char(5),blocked),”0”)
When ”0” Then ” .”
Else isnull(convert(char(5),blocked),”0”)
END

,DBName = substring(CASE
when dbid = 0 then null
when dbid <>0 then db_name(dbid) END,1,’ + @charMaxLenDBName + ‘)
,Command = substring(cmd,1,’ + @charMaxLenCommand + ‘)
,BatchTSQL = substring(Event,1,’ + @charMaxLenBatchTSQL + ‘)
,CPUTime = substring(convert(varchar,cpu),1,’ +
@charMaxLenCPUTime + ‘)
,DiskIO = substring(convert(varchar,physical_io),1,’ +
@charMaxLenDiskIO + ‘)

,LastBatch = substring(last_batch_char,1,’ +
@charMaxLenLastBatch + ‘)

,ProgramName = substring(program_name,1,’ + @charMaxLenProgramName
+ ‘)
,SPID = convert(char(5),spid) –Handy extra for right
–scrolling users.
from
#tb1_sysprocesses –Usually DB qualification is needed in exec().
where
spid >= ‘ + @charspidlow + ‘
and spid <= ‘ + @charspidhigh + ‘

— (Seems always auto sorted.) order by spid_sort

SET nocount on

)

LABEL_86RETURN:

if (object_id(‘tempdb..#tb1_sysprocesses’) is not null)
drop table #tb1_sysprocesses
if (object_id(‘tempdb..#result’) is not null)
drop table #result
return @retcode — sp_who3

 

 

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …