MS SQL Server: sp_processi, estensione della sp_who

Estensione della sp_who

La stored procedure sp_processi è un’estensione di sp_who. Serve per visualizzare informazioni più dettagliate sui processi attivi in SQL Server.

Come lanciarla

Esegui il seguente comando per mostrare solo i processi attivi:

EXEC sp_processi 'active'

Descrizione

Questa procedura mostra le connessioni attive, i comandi in esecuzione e altri dettagli utili per l’amministrazione del database. Può essere utile per diagnosticare blocchi o sessioni sospese.

Script completo

Di seguito lo script T-SQL per creare la stored procedure sp_processi nel database master:

USE [master];
GO

/****** Object: StoredProcedure [dbo].[sp_processi] Script Date: 24/04/2015 09:47:25 ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE [dbo].[sp_processi]
@loginame sysname = NULL
AS
SET NOCOUNT ON;

DECLARE @retcode INT;
DECLARE @sidlow VARBINARY(85),
        @sidhigh VARBINARY(85),
        @sid1 VARBINARY(85),
        @spidlow INT,
        @spidhigh INT;

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);

-- Valori iniziali
SELECT @retcode = 0;
SELECT @sidlow = CONVERT(VARBINARY(85), REPLICATE(CHAR(0), 85));
SELECT @sidhigh = CONVERT(VARBINARY(85), REPLICATE(CHAR(1), 85));
SELECT @spidlow = 0, @spidhigh = 32767;

-- Se non è stato specificato un login, mostra tutti
IF (@loginame IS NULL)
  GOTO LABEL_17PARM1EDITED;

-- Controlla se il login esiste
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;

-- Mostra solo i processi attivi
IF (LOWER(@loginame) IN ('active'))
BEGIN
  SELECT @loginame = LOWER(@loginame);
  GOTO LABEL_17PARM1EDITED;
END;

-- Se è un numero, filtra per SPID
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:
-- Cattura processi dal sistema
SELECT
  spid, status, sid, hostname, program_name, cmd, 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);

-- Filtra i processi inattivi
IF (@loginame IN ('active'))
  DELETE #tb1_sysprocesses
  WHERE LOWER(status) IN ('sleeping', 'background')
  AND UPPER(cmd) IN (
    'AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER',
    'CHECKPOINT SLEEP', 'RA MANAGER', 'TASK MANAGER',
    'RESOURCE MONITOR', 'LOG WRITER', 'LOCK MONITOR'
  )
  AND blocked = 0;

-- Ritorna i risultati
EXECUTE('
SELECT
  SPID = CONVERT(CHAR(5), a.spid),
  Status = CASE LOWER(status)
             WHEN ''sleeping'' THEN LOWER(status)
             ELSE UPPER(status)
           END,
  Login = a.loginname,
  DBName = DB_NAME(dbid),
  Command = a.cmd,
  CPUTime = CONVERT(VARCHAR, cpu),
  DiskIO = CONVERT(VARCHAR, physical_io),
  ProgramName = a.program_name,
  HostName = a.hostname
FROM #tb1_sysprocesses a
ORDER BY Login, a.spid
');

LABEL_86RETURN:
IF (OBJECT_ID('tempdb..#tb1_sysprocesses') IS NOT NULL)
  DROP TABLE #tb1_sysprocesses;

RETURN @retcode;
GO

Note finali

• Questa procedura può essere utile per analizzare blocchi e processi sospesi.
• Puoi adattarla per includere più campi o modificare i filtri.
• Ricordati di eseguirla come utente con privilegi di amministratore.