If you are wondering what the SQL server is doing, try to fire this statement, it shows the following.
– SPID – The system process ID.
– Status – The status of the process (e.g., RUNNABLE, RUNNING, SLEEPING).
– Login – Login name of the user.
– HostName – Machine name of the user.
– Blocked By – If the process is getting blocked, this value is the SPID of the blocking process.
– DBName – Name of database the process is using.
– Command – The command currently being executed (e.g., SELECT, INSERT)
– CPUTime – Total CPU time the process has taken
– DiskIO – Total amount of disk reads for the process.
– Last batch – Last time a client called a procedure or executed a query.
– Program name – Application that has initiated the connection (e.g., Microsoft Dynamics Ax, Visual Basic, MS SQL Query Analyzer)
– SPID 2 – The process ID repeated without the ECID for easy reading.
– Request
– Type – (e.g., CURSOR, NOT CURSOR, EXEC sp_execute)
– SQL Text – The SQL Text including parameters etc. If Cursor this is holding the SQL Statement behind the cursor, If sp_execute (I have a little trouble with this one :-))
– SQLPlan – Is holding the SQL plan which the statement is fired
If you find some errors or inputs etc, please mail it to seo@seosoft.dk.
DECLARE @dbName AS NVARCHAR(30)
SET @dbName = 'AX2009_PROD'
DECLARE @SPWHO2 TABLE (SPID INT, [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime INT NULL, DiskIO INT NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 INT NULL, Request VARCHAR(1000) NULL)
INSERT INTO @SPWHO2
EXEC sp_who2 'Active'
SELECT
a.*
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN 'CURSOR'
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN 'EXEC sp_execute'
ELSE 'NOT CURSOR'
END
) AS [TYPE]
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN
(
SELECT TOP 1 t.text
FROM sys.dm_exec_cursors (a.SPID) d
CROSS APPLY sys.dm_exec_sql_text (d.sql_handle) t
)
ELSE
(
CASE
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN
(
SELECT TOP 1 f.TEXT
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.plan_handle) f
WHERE e.session_id = a.SPID
AND f.TEXT <> ''
)
ELSE
c.text
END
)
END
) AS SQLTEXT
,(
CASE
WHEN SUBSTRING(c.text, 1, 16) = 'FETCH API_CURSOR' THEN
(
SELECT TOP 1 d.sql_handle
FROM sys.dm_exec_cursors (a.SPID) d
)
ELSE
(
CASE
WHEN SUBSTRING(c.text, 1, 15) = 'EXEC sp_execute' THEN
(
SELECT TOP 1 e.sql_handle
FROM sys.dm_exec_requests e
CROSS APPLY sys.dm_exec_sql_text(e.plan_handle) f
WHERE e.session_id = a.SPID
AND f.TEXT <> ''
)
END
)
END
) AS SQLHANDLE,
CAST(TQP.query_plan AS XML)
FROM
@SPWHO2 AS a
,sys.dm_exec_connections b
INNER JOIN sys.dm_exec_requests AS R
ON b.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text (b.most_recent_sql_handle) c
CROSS APPLY sys.dm_exec_text_query_plan
(
R.plan_handle
, R.statement_start_offset
, R.statement_end_offset
) AS TQP
WHERE
DBName = @dbName
AND a.SPID = b.session_id
--AND a.SPID =
ORDER BY
-- a.CPUTime DESC
a.DiskIO DESC