0

I was trying to capture blocking query using blocked process report past 1 hour as this link https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/

I can get the blocked process query using sqlhandle but for blocking process query i cannot get it because no sqlhandle and inputbuf doesnt have any SQL statement.

enter image description here

Any idea? This is first time i m using this blocked process report.

0

I use this query to monitor all running SPID's and the column Blocked shows you the blocking session ID. Try it

SELECT s.STATUS
    ,db_name(r.database_id) AS DB
    ,s.session_id AS SPID
    ,r.blocking_session_id AS blocked
    ,s.login_name AS userN
    ,s.host_name AS pc
    ,s.program_name AS app
    ,t.TEXT
    ,spu.internal_objects_alloc_page_count AS Pages_Allocated
    ,s.cpu_time AS CPU_T
    ,s.memory_usage AS Memory
    ,s.total_scheduled_time AS Tot_Sch_T
    ,s.total_elapsed_time AS Tot_Ela_T
    ,s.reads
    ,s.writes
    ,s.logical_reads AS Log_Read
    ,s.row_count
    ,s.last_request_start_time AS Last_Strt_T
    ,s.last_request_end_time AS End_T
    ,r.open_transaction_count AS OPENTRAN
    ,CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, s.last_request_start_time, s.last_request_end_time), 0), 108) AS TOTAL_TIME
    ,r.percent_complete
    ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage spu ON s.session_id = spu.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
    AND s.STATUS LIKE 'RUNNING'
ORDER BY s.total_elapsed_time DESC
  • Just a suggestion on that query: if you change DB_NAME(s.database_id) to DB_NAME(r.database_id) and s.open_transaction_count to r.open_transaction_count, it works down to SQL2008R2. I found this worked fine in SQL2017 but those two columns aren't in sys.dm_exec_sessions back in SQL2008R2. Just in case someone finds themselves working on legacy tech. – G.Smith Oct 23 '19 at 20:52
  • Thanks Smith, that is a good point. Updated the Query. – Kaawach Said Oct 24 '19 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.