As with most questions asked about monitoring/assessing/administering MS SQL Server installations, the answer is "
it depends".
There are many other blogs/resources/etc. online that can get into the specifics--think of this mini-blog as a snake-bit kit! :) Overall, to get to a list of objects that fall under the DMV (
Dynamic Management Views) category, type this:
-- This will show you the views and the functions of all dmv objects
SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE 'dm_%'
-- and type = 'V'
ORDER BY name
Here are a few useful DMVs to get started with...
-- Query Plans
-- stats about cached query plans
select * from sys.dm_exec_query_stats
-- identifies query plans that are cached
select * from sys.dm_exec_cached_plans
-- I/O
select * from sys.dm_io_pending_io_requests
select * from sys.dm_io_virtual_file_stats(null,null) -- // stats for all data and log files
-- Reqs/Executions
-- lists active user/internal task connects
select * from sys.dm_exec_sessions
-- established connects
select * from sys.dm_exec_connections
-- lists info about all reqs executing
select * from sys.dm_exec_requests