Blog
sp_whoisactive : Processus actifs sur une instance SQL Server
La procédure stockée sp_who2 est nativement disponible sur une instance SQL Server pour consulter la liste des processus actifs sur une instance SQL Server. Bien qu’elle ne permette pas toujours de bénéficier d’un niveau de détail suffisant pour diagnostiquer un problème lié à une ou plusieurs requêtes en cours, le résultat de son exécution reste facilement exploitable en mode ligne de commande via l’utilitaire SQLCMD (voir DAC).
La procédure stockée sp_whoisactive est un complément non négligeable pour obtenir la liste des requêtes coûteuses et/ou bloquantes en cours sur une instance. Elle contient notamment le détail des requêtes au format XML. Pour télécharger la dernière version disponible se rendre sur :
La procédure stockées doit être déployée dans la base de données système master.
Voici le résultat de son exécution :
|
1 |
EXEC sp_whoisactive |
On peut soumettre des paramètres d’entrée à la procédure stockée sp_whoisactive afin d’affiner le résultat en incluant à titre d’exemple le plan d’exécution associé à chaque requête, comme suit :
|
1 |
EXEC sp_whoisactive @get_plans = 1 |
Il est aussi possible de collecter à intervalle régulier le résultat de son exécution dans une table avec les caractéristiques suivantes :
– Rétention de 7 jours
– Création de la table de collecte
– Création d’un index
– Capture du résultat dans la table de collecte
– Purge des données > 7 jours
Remplacer la valeur <table_name> par le nom de la table destination (créée à la volée), en évitant de stocker cette table dans la base de données tempdb.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SET NOCOUNT ON; DECLARE @retention int = 7, @destination_table varchar(500) = 'WhoIsActive', @destination_database sysname = 'master', @schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit; SET @destination_table = @destination_database + '.dbo.' + @destination_table; --create the logging table IF OBJECT_ID(@destination_table) IS NULL BEGIN; EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @return_schema = 1, @schema = @schema OUTPUT; SET @schema = REPLACE(@schema, '<table_name>', @destination_table); EXEC(@schema); END; --create index on collection_time SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0'; SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT'; EXEC sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT; IF @exists = 0 BEGIN; SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)'; EXEC (@SQL); END; --collect activity into logging table EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table; --purge older data SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());'; EXEC (@SQL); |
Auteur
- Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
Derniers articles
- 2019.12.02BI & Big DataEtude des contextes en DAX (4/4) : Transition de contexte
- 2019.11.25BI & Big DataEtude des contextes en DAX (3/4) : Contexte de requête
- 2019.11.17BI & Big DataEtude des contextes en DAX (2/4) : Contexte de filtre
- 2019.11.11BI & Big DataEtude des contextes en DAX (1/4) : Contexte de ligne