Requêtes en cours – Scripts Center

Ce script affiche les requêtes en cours d’exécution avec des informations détaillées (utilisateur, machine, attentes, niveau d’isolation, blocage, texte de la dernière requête, plan d’exécution, etc…)

/*-------------------------------------------------------------------
[SCRIPT] running_queries.sql   
[DATABASE] master
[DESCRIPTION] Running Queries
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- What SQL Statements Are Currently Running?
SELECT   sp.session_id AS spid
       , program_name AS program
       , start_time
       , DB_NAME(sp.database_id) AS DBName
       , USER_NAME(er.user_id) AS username
       , nt_user_name AS nt_username
       , nt_domain
       , host_name
       , er.status AS status
       , wait_type AS wait
       , er.last_wait_type
       , er.blocking_session_id
       , CASE er.transaction_isolation_level
              WHEN 1 THEN 'ReadUncommitted'
              WHEN 2 THEN 'ReadCommitted'
              WHEN 3 THEN 'Repeatable'
              WHEN 4 THEN 'Serializable'
              WHEN 5 THEN 'Snapshot'
              ELSE 'Unspecified'
         END AS IsolationLevel
       , er.command
       , SUBSTRING(
             qt.text
           , er.statement_start_offset / 2
           , ( CASE WHEN er.statement_end_offset = -1 THEN
                        LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                    ELSE er.statement_end_offset
               END - er.statement_start_offset ) / 2) AS IndividualQuery
       , qt.text AS ParentQuery
       , qp.query_plan
FROM     sys.dm_exec_sessions sp
         JOIN sys.dm_exec_requests er ON er.session_id = sp.session_id
         OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
         OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) AS qp -- ajout
WHERE    sp.session_id > 50 -- Ignore system spids 
         AND sp.session_id NOT IN ( @@SPID ) -- Ignore the current session
         AND er.status IN ( 'running', 'runnable', 'suspended' ) -- Only statements currently running
--	AND er.blocking_session_id <> 0     -- Only blocked requests
ORDER BY 1
       , 2;

/*-------------------------------------------------------------------

[SCRIPT] running_queries.sql

[DATABASE] master

[DESCRIPTION] Running Queries

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- What SQL Statements Are Currently Running?

SELECT sp.session_id AS spid

, program_name AS program

, start_time

, DB_NAME(sp.database_id) AS DBName

, USER_NAME(er.user_id) AS username

, nt_user_name AS nt_username

, nt_domain

, host_name

, er.status AS status

, wait_type AS wait

, er.last_wait_type

, er.blocking_session_id

, CASE er.transaction_isolation_level

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

ELSE 'Unspecified'

END AS IsolationLevel

, er.command

, SUBSTRING(

qt.text

, er.statement_start_offset / 2

, ( CASE WHEN er.statement_end_offset = -1 THEN

LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE er.statement_end_offset

END - er.statement_start_offset ) / 2) AS IndividualQuery

, qt.text AS ParentQuery

, qp.query_plan

FROM sys.dm_exec_sessions sp

JOIN sys.dm_exec_requests er ON er.session_id = sp.session_id

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) AS qp -- ajout

WHERE sp.session_id > 50 -- Ignore system spids

AND sp.session_id NOT IN ( @@SPID ) -- Ignore the current session

AND er.status IN ( 'running', 'runnable', 'suspended' ) -- Only statements currently running

-- AND er.blocking_session_id <> 0 -- Only blocked requests

ORDER BY 1

, 2;