The following SQL is based on an article about the sys.processes system view on SQLServerCentral I read today and is another good example of using SQL system views and the new DMV's (Data Management Views).
I have combined some of the example code from the article into a helpful query for analysing your current processes to find long running queries that maybe causing issues with your system. Read the comments within the code for more details.
-- Using the sys.processes system table to find current process details
DECLARE @oldStats TABLE( os_thread_id int, kernel_time bigint, usermode_time bigint)
/* Insert current threads
The KPID is useful in that it helps us tie up what has been passed to the operating system to run commands and is actually working.
Although the SPID is constant throughout the life of the connection a KPID is allocated to each task that needs to be carried out.
The KPID maps back to an actual windows thread and so it is possible using performance monitor to get actual physical statistics
about a task instead of the purely logical statistics which SQL shows through the CPU column.
The KPID is the actual o/s thread id and you can use the "Thread" performance counter using "ID Thread" and "% Processor Time" to
match the thread to the actual cpu stats.
*/
INSERT INTO @oldStats
SELECT os_thread_id, kernel_time, usermode_time
FROM sys.dm_os_threads
WHERE os_thread_id IN (SELECT KPID
FROM sys.sysprocesses
WHERE kpid <> 0
AND spid>50)
-- wait for 2 seconds
WAITFOR DELAY '0:0:2'
/* Compare previous data to our current processes to see which task are consuming
the most CPU.
If records appear with a KPID of 0 and Physical Time of NULL then it means the
O/S thread is no longer active.
Investigate processes that have high physical times, high CPU, long wait times, and blocked
*/
SELECT sp.KPID, sp.SPID, sp.CPU AS LogicalCPU
,(new.kernel_time + new.usermode_time) - (old.kernel_time + old.usermode_time) AS PhysicalTime
,waittime,lastwaittype,blocked
,blockingSQL = CASE WHEN blocked > 0 AND blocked <> sp.SPID THEN (SELECT SUBSTRING((SELECT TEXT FROM fn_get_sql(sql_handle)), stmt_start/2,
CASE stmt_end
WHEN -1 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle)))) - (stmt_end/2)
WHEN 0 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle))))
ELSE stmt_end /2
END
) FROM sys.sysprocesses WHERE SPID = sp.blocked) ELSE NULL END
,last_batch,open_tran,sp.status,loginame,hostname,cmd
,(SELECT SUBSTRING((SELECT TEXT FROM fn_get_sql(sql_handle)), stmt_start/2,
CASE stmt_end
WHEN -1 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle)))) - (stmt_end/2)
WHEN 0 THEN LEN(CONVERT(VARCHAR(8000), (SELECT TEXT FROM fn_get_sql(sql_handle))))
ELSE stmt_end /2
END
) FROM sys.sysprocesses WHERE SPID = sp.SPID) as TSQL
FROM sys.sysprocesses SP
LEFT OUTER JOIN
@oldStats old
ON SP.kpid = old.os_thread_id
LEFT OUTER JOIN
sys.dm_os_threads new
ON sp.kpid = new.os_thread_id
ORDER BY PhysicalTime DESC
Store and Analyse Blocked Processes
To view your blocked processes in more detail either set up a loop with a WAITFOR DELAY or an MS Agent job that runs once a minute to log into a table the output from the following SQL. The SQL make use of a recursive CTE to link together all the processes affected by a blocking action which is useful for seeing the action that has caused the blocking and all the processes being affected by the blocking. You can view all databases on the server or filter by a particular database name or partial name.
DECLARE @DatabaseName nvarchar(255) --leave null to use current DB OR 'ALL' For all DBS
DECLARE @PROCESSES TABLE(SPID int, blockingSPID int, databaseName nvarchar(255), programName nvarchar(500), loginName nvarchar(255), ObjectName nvarchar(max), Definition nvarchar(max))
INSERT INTO @PROCESSES
SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
FROM sys.sysprocesses s
CROSS APPLY
sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50 AND
1 = CASE
WHEN @DatabaseName IS NULL AND s.dbid = db_id() THEN 1
WHEN @DatabaseName = 'ALL' THEN 1
WHEN COALESCE(@DatabaseName,'')<>'' AND DB_NAME(s.dbid) LIKE @DatabaseName + '%' THEN 1
END
;WITH Blocking(SPID, BlockingSPID, DatabaseName, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT s.SPID, s.BlockingSPID, s.DatabaseName, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM @PROCESSES s
JOIN @PROCESSES s1 ON s.SPID = s1.BlockingSPID
WHERE s.BlockingSPID = 0
UNION ALL
SELECT r.SPID, r.BlockingSPID, r.DatabaseName, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM @PROCESSES r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
Posts
