SQL Server DMV's

Dynamic Managment Views

sys.dm_db_mirroring_connections - connection id, connect time,total bytes sent and received etc.

sys.dm_db_missing_index_details – to find out missing index.

sys.dm_exec_requests - Returns information about each request that is executing within SQL Server like: - transaction ID, session id, blocking process, open transactions count and many more. Select * from sys.sysprocesses to get blocking information’s.

sys.dm_os_cluster_nodes - This view returns a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty row set.

sys.dm_os_hosts - Returns all the hosts currently registered in an instance of SQL Server.

sys.dm_os_memory_clerks - This DMV will help how much memory SQL Server has allocated through AWE

sys.dm_db_file_space_usage - This DMV provides the space usage information of TEMPDB database.

sys.dm_db_session_space_usage - This DMV provides the number of pages allocated and de-allocated by each session for the database

sys.dm_db_index_usage_stats - This DMV is used to get useful information about the index usage, for all objects in all databases. This also shows the date and time of seeks and scan for each index, date and time of usage of index. select * from sys.dm_db_index_usage_stats where database_id=db_id('test') and object_id=object_id('reporting.abc'): this query gives information’s about the date and time of last user seek, last user scan, last user update and many more info.

sys.dm_exec_connections - This DMV shows all the connection to SQL Server.

Sys.dm_db_index_physical_stats (dbid,null,null,null,null) – it gives information about index stats like fragmentation etc.

sys.dm_os_buffer_descriptors :- Which object is taking largest amount in buffer pool

sys.dm_db_task_space_usage : - to find out which task is consuming more space in tempdb

SYS.DM_OS_SYS_INFO : - to find out memroy details, gives the information when SQL Server started in SQL 2008.

sys.dm_exec_query_stat - The query against sys.dm_exec_query_stats is an efficient way to determine which query is using the most cumulative CPU.

select * from sys.dm_exec_sql_text(sql_handle) – sql handle is the hash value of sql text. It returns the exact sql statement that executed.