Categories
DBA

List of DMVs & DMFs available in SQL Server 2005

I’ve listed down all the DMV’s that are related to SQL server 2005

Server-Scoped:

CLR related dmvs
Sys.dm_clr_appdomains
Sys.dm_clr_loaded_assemblies
Sys.dm_clr_properties
Sys.dm_clr_tasks

Database related dmvs
Sys.dm_db_file_space_usage
Sys.dm_db_session_space_usage

Database Mirroring related dmvs
Sys.dm_db_mirroring_connections

Execution related DMVs and functions
Sys.dm_exec_background_job_queue
Sys.dm_exec_background_job_queue_stats
Sys.dm_exec_cached_plans
Sys.dm_exec_connections
Sys.dm_exec_cursors
Sys.dm_exec_plan_attributes
Sys.dm_exec_query_memory_grants
Sys.dm_exec_query_optimizer_info
Sys.dm_exec_query_plan
Sys.dm_exec_query_resource_semaphores
Sys.dm_exec_query_stats
Sys.dm_exec_requests
Sys.dm_exec_sessions
Sys.dm_exec_sql_text

Full Text Search Related DMVs
Sys.dm_fts_active_catalogs
Sys.dm_fts_index_population
Sys.dm_fts_memory_buffers
Sys.dm_fts_memory_pools
Sys.dm_fts_population_ranges

InputOutput Related DMVs and Functions
Sys.dm_io_backup_tapes
Sys.dm_io_cluster_shared_drives
Sys.dm_io_pending_io_requests
Sys.dm_io_virtual_file_stats

Index Related DMVs
Sys.dm_db_index_operational_stats
Sys.dm_db_index_physical_stats
Sys.dm_db_index_usage_stats
Sys.dm_db_missing_index_columns
Sys.dm_db_missing_index_details
Sys.dm_db_missing_index_group_stats
Sys.dm_db_missing_index_groups

Query Notifications Related DMVs
Sys.dm_qn_subscriptions

Service Broker Related DMVs
Sys.dm_broker_activated_tasks
Sys.dm_broker_connections
Sys.dm_broker_forwarded_messages
Sys.dm_broker_queue_monitors

SQL Server Operating System Related DMVs
Sys.dm_os_buffer_descriptors
Sys.dm_os_child_instances
Sys.dm_os_cluster_nodes
Sys.dm_os_hosts
Sys.dm_os_latch_stats
Sys.dm_os_loaded_modules
Sys.dm_os_memory_cache_clock_hands
Sys.dm_os_memory_cache_counters
Sys.dm_os_memory_cache_entries
Sys.dm_os_memory_cache_hash_tables
Sys.dm_os_memory_clerks
Sys.dm_os_memory_objects
Sys.dm_os_memory_pools
Sys.dm_os_performance_counters
Sys.dm_os_schedulers
sys.dm_os_stacks
sys.dm_os_sys_info
sys.dm_os_tasks
sys.dm_os_threads
sys.dm_os_virtual_address_dump
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_os_workers

Transaction Related DMVs & Functions
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

Database scoped

Database related dmvs
Sys.dm_db_partition_stats

Replication Related DMVs
Sys.dm_repl_articles
Sys.dm_repl_schemas
Sys.dm_repl_tranhash
Sys.dm_repl_traninfo

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*