Using SQL Server Dynamic Management Views (DMV)

SQL Server includes built-in objects that you can query to monitor SQL Server called Dynamic Management Views or DMV.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.   

There are two types of dynamic management views and functions:

  • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.

  • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
You issue normal select statements against these objects.

COMMON queries:

 sys.dm_exec_cashed_plans To obtain information about a query plan that is cached by SQL server for faster query execution
 sys.dm_tran_database_transactions Returns information on open transactions in a single database.
 sys.dm_exec_sessions    Retrieve cpu or memory useage information by user (this DMV shows user session information)
 sys.dm_exec_query_stats     Can identify long running queries
 sys.dm_exec_sql_text     Returns the sql used in a particular running query (select * from sys.dm_exec_sql_text(<sql_handle>)
 sys.dm_os_wait_stats     Historical information about threads that have waited since the last sql server restart
 sys.dm_exec_requests Provides information about every request being executed on the SQL Server.  
 sys.dm_tran_locks     Current active locks.   Replaces sp_lock

NOTE:   you can often get equivalent information using a SQL Profiler trace.    If performance is an issue, querying a DMV is less resource intensive that running a SQL Profiler trace.