Dynamic Management View in SQL Server 2005

The Dynamic management View in SQL server 2005 provides a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000.
The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

1. Common Language Runtime Related Dynamic Management Views:
2. I/O Related Dynamic Management Views and Functions
3. Database Mirroring Related Dynamic Management Views
4. Query Notifications Related Dynamic Management Views
5. Database Related Dynamic Management Views
6. Replication Related Dynamic Management Views
7. Execution Related Dynamic Management Views and Functions
8. Service Broker Related Dynamic Management Views
9. Full-Text Search Related Dynamic Management Views
10. SQL Operating System Related Dynamic Management Views
11. Index Related Dynamic Management Views and Functions
12. Transaction Related Dynamic Management Views and Functions

Few are very common views:
Sessions (To view current session):
SELECT session_id, login_name, last_request_end_time, cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51
GO
Connections (To view current connections):
SELECT connection_id, session_id, client_net_address, auth_scheme
FROM sys.dm_exec_connections
GO

Request (To view current Request):
SELECT session_id, status, command, sql_handle, database_id
FROM sys.dm_exec_requests
WHERE session_id >= 51
GO
SQL Text (To view current SQL text):
SELECT st.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 56
GO
Security:
In order to query these views a user needs specific permissions granted. To view the server-wide DMVs the user must be granted the VIEW SERVER STATE on the server. Administrator can give the permission using following statement:
GRANT VIEW SERVER STATE to username