This command shows the last statement executed for a particular SPID. This statement is mostly used for troubleshooting purposes to determine the exact command a particular SPID is running. You must be a member of the sys admin fixed server role, or have VIEW SERVER STATE permission (if SQL 2005+). As for a real world use, I will mostly use this command after using sp_who2 to find out which SPID is taking a lot of resources. To execute, simply replace the SPID 55 below with the one you want to spy on. DBCC INPUTBUFFER(55) And here are the results: The results you are looking for are displayed in the third column above. This shows the last statement that has been or is currently being executed by the SPID. Another alternative to the DBCC INPUTBUFFER is to use the dm_exec_requests DMV. This query will show the currently executing statement for a SPID: SELECT SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END – er.statement_start_offset)/2 ) FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt WHERE er.session_id = 54 This […]Continue reading ...
- Caching stored procedure results using a hash key There are a lot of different design patterns that lend themselves to creating
- SQL Server Database Optimization Guide In the troubleshooting guide we went over the different physical bottlenecks that can
- Yet Another Temp Tables Vs Table Variables Article The debate whether to use temp tables or table variables is an old
- Using Union Instead of OR Sometimes slow queries can be rectified by changing the query around a bit.