Top 15 SQL Dynamic management Views
1.sys.dm_db_index_physical_stats: This dynamic management function returns information regarding data and index information for data tables and views
2.sys.dm_exec_requests: This view shows information regarding each request occurring in the SQL Server instance.
3.sys.dm_exec_sessions: This returns one row per authenticated session on SQL Server. This view is useful when you want to find out all of the users who are connected to the database server.
4.sys.dm_os_performance_counters: The information returned by this view lists performance statistics directly related to SQL Server. The information returned represents the internal performance counters as displayed through the Windows Performance Monitor.
5.sys.dm_exec_sql_text: This is a dynamic management function that returns the SQL Server statement based upon a sql handle
6.sys.dm_db_file_space_usage: Returns space usage information for each file in the database.
7.sys.dm_db_mirroring_connections - Returns a row for each connection established for database mirroring.
8.sys.dm_db_mirroring_auto_page_repair - Returns a row for every automatic page-repair attempt on any mirrored database on the server instance. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database
9.sys.dm_db_index_operational_stats- Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
10.sys.dm_db_index_usage_stats- Returns counts of different types of index operations and the time each type of operation was last performed.
11.sys.dm_db_missing_index_details- Returns detailed information about missing indexes, excluding spatial indexes.
12.sys.dm_db_missing_index_groups- Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes.
13.sys.dm_exec_cached_plans- Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
14.sys.dm_io_pending_io_requests- Returns a row for each pending I/O request in SQL Server.
15.sys.dm_io_virtual_file_stats- Returns I/O statistics for data and log files.
16.sys.dm_repl_articles- Returns information about database objects published as articles in a replication topology.
2.sys.dm_exec_requests: This view shows information regarding each request occurring in the SQL Server instance.
3.sys.dm_exec_sessions: This returns one row per authenticated session on SQL Server. This view is useful when you want to find out all of the users who are connected to the database server.
4.sys.dm_os_performance_counters: The information returned by this view lists performance statistics directly related to SQL Server. The information returned represents the internal performance counters as displayed through the Windows Performance Monitor.
5.sys.dm_exec_sql_text: This is a dynamic management function that returns the SQL Server statement based upon a sql handle
6.sys.dm_db_file_space_usage: Returns space usage information for each file in the database.
7.sys.dm_db_mirroring_connections - Returns a row for each connection established for database mirroring.
8.sys.dm_db_mirroring_auto_page_repair - Returns a row for every automatic page-repair attempt on any mirrored database on the server instance. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database
9.sys.dm_db_index_operational_stats- Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
10.sys.dm_db_index_usage_stats- Returns counts of different types of index operations and the time each type of operation was last performed.
11.sys.dm_db_missing_index_details- Returns detailed information about missing indexes, excluding spatial indexes.
12.sys.dm_db_missing_index_groups- Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes.
13.sys.dm_exec_cached_plans- Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
14.sys.dm_io_pending_io_requests- Returns a row for each pending I/O request in SQL Server.
15.sys.dm_io_virtual_file_stats- Returns I/O statistics for data and log files.
16.sys.dm_repl_articles- Returns information about database objects published as articles in a replication topology.
