Dynamic Management Views that can be used to monitor and troubleshoot performance issues in SQL Server.

Add Prefix Sys. to view

dm_exec_cached_plans : Provides information about the query execution plans that are cached by SQL Server for faster query execution.

dm_exec_connections :Provides information about the connections established to SQL Server on various endpoints.

dm_exec_sessions :Contains one row per authenticated session on the SQL Server instance.
dm_exec_query_stats :Provides aggregate performance statistics for cached query plans.

dm_exec_query_optimizer_info : Provides detailed statistics about the operation of the SQL Server query optimizer.

dm_io_pending_io_requests :Contains a row for each pending I/O in the system.

dm_os_loaded_modules : Contains a row for each module loaded into the server address space.

dm_os_memory_cache_counters :Provides a general snapshot of the health of the cache.

dm_os_memory_cache_entries : Enables you to view all entries in caches and their statistics.

dm_os_performance_counters :Lists SQL Server 2005 Performance Monitor counters and their current value.

dm_os_waiting_tasks :Provides information on the wait queue of tasks that are waiting on some resource.

dm_tran_locks :Returns one row for every active request to the lock manager that has either been granted or is waiting to be granted (that is, the request is blocked by an already granted request).

dm_tran_current_transaction :Returns a single row that displays state information of the transaction in the current session.

dm_tran_active_transactions :Provides information about active transactions.

dm_clr_properties :If the CLR is enabled, provides information such as CLR version, directory, state, and so on.

dm_clr_appdomains :Returns a row for each CLR application domain in the server.

dm_clr_loaded_assemblies :Contains a row for each CLR user assembly loaded into the server address space.