You are the database administrator for your company. You maintain a database named Prod1 on a SQL Server2008 instance named Sql1.
The Prod1 database contains all the orders and product-related information for yourcompany.
Indexes have been created on the key columns of the database tables.
The data in the tables isconstantly updated, and new rows are added to the table.
You notice performance on the database server is degrading.
You consider a high number of indexes in thedatabase to be the cause of this problem.
You are required to identify the lock and latch information and the access methods used for these indexes.
Which function should you use to obtain the required information?

A.
sys.dm_tran_current_transaction

B.
sys.dm_db_index_operational_stats

C.
sys.dm_db_index_physical_stats

D.
sys.dm_db_index_usage_stats

Explanation:

You should use the sys.dm_db_index_operational_stats function to obtain the required information. The sys.dm_db_index_operational_stats function provides the current locking and latching information about apartition, either in a database table or in an index. This function also provides statistics related to the accessmethod and the physical I/O of the table or index partition. The information retrieved from this function is useful inanalyzing the characteristics of a database table or index.
You should not use the sys.dm_tran_current_transaction function. The sys.dm_tran_current_transaction function displays the state information of the transaction in the current session, but does not provide lock or latchinformation. You should not use the sys.dm_db_index_physical_stats function. The sys.dm_db_index_physical_stats function obtains information regarding the fragmentation of data and indexes of either a database table or adatabase view. The details provided in the columns of the returned table can be used to detect both logical andextent fragmentation of data in a database. You should not use the sys.dm_db_index_usage_stats function. The sys.dm_db_index_usage_stats functioncalculates the different operation types performed on indexes and the time at which these operations were lastperformed. The different types of operations displayed in the output of this function are seek, scan, lookup, andupdate.

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) >Index Related Dynamic Management Views and Functions (Transact-SQL) > sys.dm_db_index_operational_stats(Transact-SQL)