You are a database administrator for your company managing all your company’s SQL Server 2008 databases.
The Prod_details database is the main database accessed by users in the company. The head office of thecompany receives data from other branches.
After the employees of the audit department have verified the data,the database is updated with the data.
Some employees in the audit department complain that either the update process is very slow, or timeout errormessages are displayed when they try to update the data in the database.
You suspect concurrency issues to bethe cause of the problem and decide to monitor the transaction locks held on the database objects by using theSQL Server Profiler.
Which event should you monitor in the SQL Server Profiler?
A.
the Lock:Timeout event
B.
the Lock:Acquired event
C.
the Lock:Released event
D.
the Lock:Cancel event
Explanation:
You should monitor the Lock:Acquired event in the SQL Server Profiler to view details regarding the transactionlocks held on the database objects. The Lock:Acquired event indicates that a lock has been acquired on aresource in the database. The details provided in the data columns for this event can be used to determineinformation, such as the time at which a lock was acquired on a database object, the type of lock held on anobject, and the users holding locks on an object. You should not monitor the Lock:Timeout event. The Lock:Timeout
event indicates that the request for a lockon a database resource has timed out because the resource was locked by another process. This event is usedto determine the when timeouts for locking conditions occurred in a database. The details provided in the datacolumns for this event can be used to determine whether the timeouts are adversely affecting databaseperformance and to determine the objects involved in locks that resulted in timeouts. The Lock:Timeout event cannot be used to monitor the time and the type of locks held on the database objects. You should not monitor the Lock:Released event because this event indicates the time at which a lock on adatabase resource is released. This information will not be helpful in this scenario because you are required tomonitor the time at which locks are acquired on database resources. You should not monitor the Lock:Cancel event because this event indicates the time at which the process ofacquiring a lock is cancelled. This information will not be helpful in this scenario because you are required tomonitor the time at which locks are acquired on database resources.Objective:
Monitoring and Troubleshooting SQL ServerSub-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 > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Locks Event Category > Lock:Acquired Event Class TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Locks Event Category