DMVs

Question 1

DMV for Query Optimizer info?

Answer 1

select *
from sys.dm_exec_query_optimizer_info

Question 2

DMV for query stats?

Answer 2

select *
from sys.dm_exec_query_stats

Question 3

DMV to view cache plans?

Answer 3

SELECT *
FROM sys.dm_exec_cached_plans

Question 4

DMV to view current executing requests?

Answer 4

SELECT *
FROM sys.dm_exec_requests

Question 5

DMV to view OS tasks?

Answer 5

SELECT *
FROM sys.dm_os_tasks

Question 6

DMV to view current sessions?

Answer 6

SELECT *
FROM sys.dm_exec_sessions

Question 7

DMV to view current connections?

Question 8

DMV to view executing cursors?

Answer 8

SELECT *
FROM sys.dm_exec_cursors(session_id)

Question 9

DMV to view information about memory allocations by internal memory manages?

Answer 9

SELECT *
FROM sys.dm_os_memory_brokers

Question 10

DMV to view information about physical non-uniform memory access (NUMA)?

Answer 10

SELECT *
FROM sys.dm_os_memory_nodes

Question 11

DMV to view information about CPU node configuration?

Question 12

DMV to view SQL Server memory usage?

Answer 12

SELECT *
FROM sys.dm_os_process_memory

Question 13

DMV to view System memory usage?

Answer 13

SELECT *
FROM sys.dm_os_sys_memory

Question 14

DMV to view Resource Governor configuration?

Answer 14

SELECT *
FROM sys.dm_resource_governor_configuration

Question 15

DMV to view Resource Governor resource pools?

Answer 15

SELECT *
FROM sys.dm_resource_governor_resource_pools

Question 16

DMV to view Resource Governor workload groups?

Answer 16

SELECT *
FROM sys.dm_resource_governor_workload_groups

Question 17

DMV to view all entries in memory cache?

Answer 17

SELECT *
FROM sys.dm_os_memory_cache_entries

Question 18

DMV to view about the current query-resource semaphore status?

Answer 18

SELECT *
FROM sys.dm_exec_query_resource_semaphores

Question 19

DMV to view about the queries that have acquired a memory grant or that still require a memory grant to execute?

Answer 19

SELECT *
FROM sys.dm_exec_query_memory_grants

Question 20

DMV to view cache clock hands?

Answer 20

SELECT *
FROM sys.dm_os_memory_cache_clock_hands

Question 21

DMV to view cache counters?

Answer 21

SELECT *
FROM sys.dm_os_memory_cache_counters

Question 22

DMV to view ring buffers?

Question 23

DMV to view wait stats?

Answer 23

SELECT *
FROM sys.dm_os_wait_stats

Question 24

DMV to view I/O statistics for data and log files?

Answer 24

SELECT *
FROM sys.dm_io_virtual_file_stats(database_id, file_id)

Question 25

DMV to view pending I/O request?

Answer 25

SELECT *
FROM sys.dm_io_pending_io_requests

Question 26

DMV view file space usage?

Answer 26

SELECT *
FROM sys.dm_db_file_space_usage

Question 27

DMV to view active transactions that use row versions?

Answer 27

SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions

Question 28

DMV to view page allocation and deallocation by session in tempDB?

Answer 28

SELECT *
FROM sys.dm_db_session_space_usage

Question 29

DMV to view page allocation and deallocation by task for tempDB?

Answer 29

SELECT *
FROM sys.dm_db_task_space_usage

Question 30

DMV to view the wait queue of tasks that are waiting on some resource?

Answer 30

SELECT *
FROM sys.dm_os_waiting_tasks

Question 31

DMV to view information about all the waits encountered by threads that executed?

Answer 31

SELECT *
FROM sys.dm_os_wait_stats

Question 32

DMV to view currently active lock manager resources?

Answer 32

SELECT *
FROM sys.dm_tran_locks

Question 33

DMV to view low level I/O, locking, latching, and access method activity for each partition of a table or index in the database?

Answer 33

SELECT *
FROM sys.dm_db_index_operational_stats (database_id, object_id, index_id, partition_number)

Question 34

DMV to view index operations and the time each type of operation was last performed?

Answer 34

SELECT *
FROM sys.dm_db_index_usage_stats

Question 35

DMV to view a virtual table for the objects that are producing the most versions in the version store?

Answer 35

SELECT *
FROM sys.dm_tran_top_version_generators

Question 36

DMV to view a virtual table that display all version records in the version store?

Answer 36

SELECT *
FROM sys.dm_tran_version_store