444. An overview of Dynamic management views and functions of SQL 2005

Dynamic management views and functions have been organized into the following categories.
Common Language Runtime Related Dynamic Management Views
	sys.dm_clr_appdomains	Returns a row for each application domain in the server
	sys.dm_clr_properties	Returns a row for each property related to common language runtime (CLR) integration
	sys.dm_clr_loaded_assemblies	Returns a row for each managed user assembly loaded into the server address space
	sys.dm_clr_tasks	Returns a row for all common language runtime (CLR) tasks that are currently running
I/O Related Dynamic Management Views and Functions
	sys.dm_io_backup_tapes	Identifies the list of backup devices and the status of mount requests for backups
	sys.dm_io_cluster_shared_drives	Returns the drive name of the shared drives if the current server is a clustered server
	sys.dm_io_pending_io_requests	Returns a row for each pending I/O request in SQL Server.
	sys.dm_io_virtual_file_stats	Returns I/O statistics for data and log files.
Database Mirroring Related Dynamic Management Views
	sys.dm_db_mirroring_connections	Returns a row for each database mirroring network connection.
Query Notifications Related Dynamic Management Views
	sys.dm_qn_subscriptions	Returns information about the active query notifications subscriptions in the server.
Database Related Dynamic Management Views
	sys.dm_db_file_space_usage	Returns space usage information for each file in the database.
	sys.dm_db_session_space_usage	Returns the number of pages allocated and deallocated by each session for the database.
	sys.dm_db_partition_stats	Returns page and row-count information for every partition in the current database.
	sys.dm_db_task_space_usage	Returns page allocation and deallocation activity by task for the database.
Replication Related Dynamic Management Views
	sys.dm_repl_articles	Returns information about database objects published as articles in a replication topology
	sys.dm_repl_schemas	Returns information about table columns published by replication
	sys.dm_repl_tranhash	Returns information about transactions being replicated in a transactional publication.
	sys.dm_repl_traninfo	Returns information on each replicated transaction
Execution Related Dynamic Management Views and Functions
	sys.dm_exec_background_job_queue	Returns a row for each query processor job that is scheduled for asynchronous (background) execution.
	sys.dm_exec_background_job_queue_stats	Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution
	sys.dm_exec_cached_plans	Returns information about the query execution plans that are cached by SQL Server for faster query execution
	sys.dm_exec_cursors	Returns information about the cursors that are open in various databases
	sys.dm_exec_query_optimizer_info	Returns detailed statistics about the operation of the SQL Server query optimizer.
	sys.dm_exec_query_stats	Returns aggregate performance statistics for cached query plans
	sys.dm_exec_sessions	Returns one row per authenticated session on Microsoft SQL Server
	sys.dm_exec_connections	Returns information about the connections established to this instance of SQL Server on various databases by different users local/remote, and the details of each connection
	sys.dm_exec_plan_attributes	Returns one row per attribute associated with the plan specified by the plan handle
	sys.dm_exec_query_plan	returns the Showplan in XML format for a Transact-SQL batch whose query execution plan resides in the plan cache
	sys.dm_exec_requests	Returns information about each request that is executing within SQL Server
	sys.dm_exec_sql_text	Returns the text of the SQL statement that is given the sql_handle for that statement
Service Broker Related Dynamic Management Views
	sys.dm_broker_activated_tasks	Returns a row for each stored procedure activated by Service Broker
	sys.dm_broker_forwarded_messages	Returns a row for each Service Broker message that an instance of SQL Server is in the process of forwarding
	sys.dm_broker_connections	Returns a row for each Service Broker network connection
	sys.dm_broker_queue_monitors	Returns a row for each queue monitor in the instance. A queue monitor manages activation for a queue.
Full-Text Search Related Dynamic Management Views
	sys.dm_fts_active_catalogs	Returns information on the full-text catalogs that have some population activity in progress on the server
	sys.dm_fts_crawl_ranges	Returns information about the specific ranges related to a full-text index population currently in progress.
	sys.dm_fts_memory_pools	Returns information about the specific ranges related to a full-text index population currently in progress.
	sys.dm_fts_crawls	Returns information about the full-text index populations currently in progress
	sys.dm_fts_memory_buffers	Returns information about memory buffers belonging to a specific memory pool that are used as part of a full-text crawl or a full-text crawl range
SQL Operating System Related Dynamic Management Views
	sys.dm_os_buffer_descriptors	Returns the buffer pool buffer descriptors that are being used by a database on an instance of Microsoft SQL Server
	sys.dm_os_child_instances	Returns a row for each user instance that has been created from the parent database.
	sys.dm_os_cluster_nodes	Returns a row for each node in the virtual server configuration
	sys.dm_os_hosts	Returns all the hosts currently registered in an instance of SQL Server
	sys.dm_os_latch_stats	Returns information about all latch waits organized by class
	sys.dm_os_loaded_modules	Returns a row for each module loaded into the server address space
	sys.dm_os_memory_cache_clock_hands	Returns the status of each hand for a specific cache clock
	sys.dm_os_memory_cache_counters	Returns a snapshot of the health of a cache
	sys.dm_os_memory_cache_entries	Returns information about all entries in caches
	sys.dm_os_memory_cache_hash_tables	Returns a row for each active cache in the instance of SQL Server
	sys.dm_os_memory_clerks	Returns the set of all memory clerks that are currently active in the instance of SQL Server
	sys.dm_os_memory_pools	Returns a row for each object store in the instance of SQL Server
	sys.dm_os_performance_counters	Returns a row per performance counter maintained by the server
	sys.dm_os_schedulers	Returns one row per scheduler in SQL Server. Each scheduler is mapped to an individual processor in SQL Server
	sys.dm_os_stacks	Keep track of debug data such as outstanding allocations Assume or validate logic that is used by SQL Server components in places where the component assumes that a certain call has been made
	sys.dm_os_sys_info	Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server
	sys.dm_os_tasks	Returns one row for each task that is active in the instance of SQL Server
	sys.dm_os_threads	Returns a list of all SQL Server Operating System threads that are running under the SQL Server process
	sys.dm_os_virtual_address_dump	Returns information about a range of pages in the virtual address space of the calling process
	sys.dm_os_wait_stats	Returns information about the waits encountered by threads that are in execution
	sys.dm_os_waiting_tasks	Returns information about the wait queue of tasks that are waiting on some resource.
	sys.dm_os_workers	Returns a row for every worker in the system
	sys.dm_os_memory_objects	Returns memory objects that are currently allocated by the SQL Server
Index Related Dynamic Management Views and Functions
	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
	sys.dm_db_index_physical_stats	Returns size and fragmentation information for the data and indexes of the specified table or view
	sys.dm_db_index_usage_stats	Returns counts of different types of index operations and the time each type of operation was last performed.
Transaction Related Dynamic Management Views and Functions
	sys.dm_tran_active_snapshot_database_transactions	Returns virtual table for all active transactions in all snapshot-enabled databases under the SQL Server instance
	sys.dm_tran_current_snapshot	Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts
	sys.dm_tran_database_transactions	Returns information about transactions at the database level
	sys.dm_tran_session_transactions	Returns correlation information for associated transactions and sessions
	sys.dm_tran_transactions_snapshot	Returns a virtual table for the sequence_number of transactions that are active when each snapshot transaction starts
	sys.dm_tran_active_transactions	Returns information about transactions for the SQL Server instance.
	sys.dm_tran_current_transaction	Returns a single row that displays the state information of the transaction in the current session
	sys.dm_tran_locks	Returns information about currently active lock manager resources
	sys.dm_tran_top_version_generators	Returns a virtual table for the objects producing the most versions in the version store
	sys.dm_tran_version_store	Returns a virtual table that displays all version records in the version store

转载于:https://www.cnblogs.com/yang_sy/archive/2009/05/05/1450262.html