viewing recent executions in SQL Server

1. 2005 and above, using DMV to view current and recent executions.

select * from sys.dm_exec_connections c
cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) q

dbcc inputbuffer(sql handle)

select s.text,r.* from sys.dm_exec_requests r
cross apply sys.dm_exec_text(r.sql_handle) s

SELECT spid, db_name(s.dbid), text

FROM master.dbo.sysprocesses as s

cross apply fn_get_sql(s.sql_handle)

basic idea is to use sys.dm_exec_sql_text and dbcc inputbuffer, fn_get_sql to view the content.

2. 2000, use dbcc inputbeffer and fn_get_sql. use cursor to loop each of the sql_handle in sysprocesses, or retrieve the information in two separate steps.

Looping through a Resultset in SSIS

1. create an Execute SQL task, set its ResultSet to be Full result set. In the resultSet, define the result name as 0. variable name is type of Object.
2.create a foreach loop container, define its collection to be "Foreach ADO enumerator", and specify the ADO object source variable to be the one defined in step 1.
3.in the variable mappings, define the user variable to receive value for each column. each column is defined by sequence from 0.

check processes in SQL Server

sp_who2 active
--use dbcc to check the query text
dbcc inputbuffer (59)

Detect blocking processes in SQL Server

this is for SQL Server 2005. in SQL Serer 2008, you have to replace sys.sysprocesses to be dbo.sysprocesses in master database.

blocking is not deadlock.

First query, it retrieves the sql statements by using cross apply. output is put into temporary table for further analysis.

SELECT
s.spid, s.blocked BlockingSPID, DB_NAME(s.dbid) DatabaseName ,
s.program_name, s.loginame, OBJECT_NAME(objectid, s.dbid) ObjectName, CAST(text AS VARCHAR(MAX)) Definition
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT
s.SPID, s.BlockingSPID, 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.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