You are a database administrator for AIOTestKing.com. The company’s financial forecasting application uses a SQL Server 2005 database that enables business users to run both ad hoc and predefined queries. You suspect that some queries consume an excessive amount of server resources. You need to identify which queries consume the most resources. You want to achieve this goal as quickly as possible. What should you do?
A.
Use the sqldiag utility.
B.
Use the sys.dm_exec_query_stats dynamic management view (DMV).
C.
Use the DBCC INPUTBUFFER command.
D.
Use the SHOWPLAN session option.
Explanation:
Sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans. The view contains one row per query plan and the lifetime of the row is tied to the plan itself. When a plan is removed from the cache, the corresponding row is eliminated from this view.