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: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.
|