You are the database administrator for your company and manage all the SQL Server 2008 databases of thecompany. The production database named Prod1 contains all the product and sales-related data of the company. John, a database user, complains that he is not able to update the data in the tables. You suspect that a usersession is blocking John’s request. You want to identify the blocking sessions. Which dynamic management view should you use to identify these sessions?

A.
sys.dm_tran_locks

B.
sys.dm_exec_requests

C.
sys.dm_exec_sessions

D.
sys.dm_tran_active_transactions

Explanation:

The sys.dm_exec_requests dynamic management view should be used to determine the session ids of theblocking sessions in the database. The sys.dm_exec_requests view provides information about every requestbeing executed on the SQL server. The columns in the view can be used to determine details of the requests. The blocking_session_id column returns the session ID of the session that is blocking the request. Other columns,such as wait_type and wait_time , can be used to determine the type of waits held and the duration of the waitsin milliseconds. The sys.dm_tran_locks dynamic management view cannot be used to determine the session IDs of the blockingsessions in the database. The sys.dm_tran_locks
dynamic management view provides information regardingthe currently active lock manager resources. Every row in the view represents an active request either for the lock Monitoring and Troubleshooting SQL Server that has been granted or for the lock that is waiting to be granted. The sys.dm_exec_sessions dynamic management view cannot be used to determine the session ids of theblocking sessions in the database. The sys.dm_exec_sessions view provides information regarding eachauthenticated session in the SQL server. The view contains one row for each authenticated session in thedatabase. The sys.dm_tran_active_transactions dynamic management view cannot be used to determine the session idsof the blocking sessions in the database. The sys.dm_tran_active_transactions view provides informationregarding the different transactions in the SQL Server instance. The view contains information, such as thetransaction ID of the transaction, the start time of the transaction, and the state of the transaction. The transactionID is displayed at the instance level, particularly at the SQL Server instance, and not at the database level and isunique across all databases.

Objective:
Monitoring and Troubleshooting SQL Server

Sub-Objective:
Identify concurrency problems.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL)>Execution Related Dynamic Management Views and Functions (Transact-SQL) > sys.dm_exec_requests(Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL) >Transaction Related Dynamic Management Views and Functions (Transact-SQL) > sys.dm_tran_locks (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL)>Dynamic Management Views and Functions (Transact-SQL)>Execution Related Dynamic Management Views and Functions (Transact-SQL) > sys.dm_exec_sessions(Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL) >Transaction Related Dynamic Management Views and Functions (Transact-SQL) >sys.dm_tran_active_transactions (Transact-SQL)