You administer a Microsoft SQL Server 2012.
A process that normally runs in less than 10 seconds has been running for more than an hour.
You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?
A.
EXEC sp_who 60
B.
SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
C.
EXEC sp_helpdb 60
D.
DBCC INPUTBUFFER (60)
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms174313.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms176013.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms178568.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187730.aspx
Solution a) is right.
sp_who returns the Information if a process is blocked.
sys.dm_exec_does not give this information
I think Testee is right. A is right..
or use SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
EXEC sp_who 60
That’s correct. Both sys.dm_exec_sessions and SP_WHO return status, but sys.dm_exec_sessions has limited values for status column:
Status of the session. Possible values:
Running – Currently running one or more requests
Sleeping – Currently running no requests
Dormant – Session has been reset because of connection pooling and is now in prelogin state.
Preconnect – Session is in the Resource Governor classifier.
Is not nullable.
Whereas Status column of SP_WHO has:
dormant: SQL Server is resetting the session.
running: The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS).
background: The session is running a background task, such as deadlock detection.
rollback: The session has a transaction rollback in process.
pending: The session is waiting for a worker thread to become available.
runnable: The session’s task is in the runnable queue of a scheduler while waiting to get a time quantum.
spinloop: The session’s task is waiting for a spinlock to become free.
suspended: The session is waiting for an event, such as I/O, to complete.
E. SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
if E is not available A
Shouldn’t that be A ?
Specifically column blk ?
blk
char(5)
Session ID for the blocking process, if one exists. Otherwise, this column is zero.
When a transaction associated with a specified session ID is blocked by an orphaned distributed transaction, this column will return a ‘-2’ for the blocking orphaned transaction.
Answer: A. EXEC sp_who 60
Please check this blog.
Both A and B are correct answers but the question is which is the best to check if the process is blocked.
https://blogs.msdn.microsoft.com/sqldatabasetalk/2013/03/03/troubleshooting-blocking-in-sql-database-the-who/
I believed that B is the best correct answer which give you much information on the process.
What are your thoughts.
or it should be A. because if you will look closely at the query.. there is no column name sessionid in sys.dm_exec_sessions (session_id)
A. is the correct answer.
Good call. Another close but not quite syntax question.
Answer must be A.
B is right as according to question it just wants to know does the process block or not? So this way it will tell you the status of the process if it is suspended
A is correct but if B was like SELECT * FROM sys.dm_exec_requests WHERE session_id = 60
it will best answer than A
But B is incomplete syntax in this question , so I prefer A than B