row_number
row_number 是窗口函数,语法如下:
row_number() over (partition by <用于分组的字段名> order by <用于组内排序的字段名>)
SELECT
p.session_id,
p.request_id,
p.start_time,--查找阻塞的语句(正在运行)
p.status,
p.command,
p.blocking_session_id,
p.wait_type,
p.wait_time,
p.wait_resource,
p.total_elapsed_time,
p.open_transaction_count,
p.transaction_isolation_level,
SUBSTRING (
qt.text,
p.statement_start_offset/ 2,
(
CASE
WHEN p.statement_end_offset=- 1 THEN
len( CONVERT ( nvarchar ( MAX ), qt.text ) ) * 2 ELSE p.statement_end_offset
END - statement_start_offset
) / 2
) AS "SQL statement",
p.statement_start_offset,
p.statement_end_offset,
batch = qt.text
FROM
master.sys.dm_exec_requests p CROSS apply sys.dm_exec_sql_text ( p.sql_handle ) AS qt
WHERE
p.session_id> 50